如果使用MySQLi预备语句，是否需要转义变量? [英] Do I need to escape my variables if I use MySQLi prepared statements?

问题描述

如果我使用如下所示的MySQLi预备语句:

If I use MySQLi prepared statements like below:

$stmt = $con1->prepare("UPDATE Login SET Session='LoggedOut' where Session=?");
$stmt->bind_param('s',$Session);
$stmt->execute();
$stmt->close();

我是否仍需要像下面这样用mysqli_real_escape_string();转义$Session这样的变量:

Do I still need to escape my variables like $Session with mysqli_real_escape_string(); like below:

$Session = mysqli_real_escape_string($con1, $_COOKIE['Session']);
$stmt = $con1->prepare("UPDATE Login SET Session='LoggedOut' where Session=?");
$stmt->bind_param('s',$Session);
$stmt->execute();
$stmt->close();

推荐答案

否，如果您在应用程序中的所有位置使用准备好的语句，则可以避免SQL注入.但是，重要的陷阱"是二阶注入攻击，当某些查询使用准备好的语句而其他不使用准备好的语句时，就会发生这种攻击.

No, if you use prepared statements everywhere in your application you are safe from SQL injection. However, an important "gotcha" is 2nd order injection attacks which happen when some queries use prepared statements and others don't.

根据此答案，类似关于SO的问题:

According to this answer of a similar question on SO:

准备好的语句/参数化查询足以防止对该语句进行一阶注入.如果您在应用程序中的其他任何地方使用未经检查的动态sql，则仍然容易受到二阶注入的攻击.<​​/p>

prepared statements / parameterized queries are sufficient to prevent 1st order injection on that statement. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

总而言之，准备好的语句在要发送的数据和SQL查询本身之间建立了分隔，以确保不会将数据误解为SQL查询.但是，攻击者仍可以将SQL作为数据输入，并且如果使用准备好的语句，虽然在首次存储SQL时将不会执行该SQL，但是在检索该结果时仍必须谨慎.准备好的语句可以在那个特定位置保护您的应用程序，但是由于仍然允许将SQL存储在数据库中，因此如果您以后在不使用参数化的情况下使用该数据，则您的应用程序是不安全的.

In summary, prepared statements create a separation between the data being sent and the SQL query itself, ensuring that the data can not be misinterpreted as the SQL query. However, an attacker can still enter SQL as data, and although it will not be executed when it is first stored if you are using prepared statements, you must still use caution when retrieving said results. Prepared statements protect your application in that particular place, but because SQL is still allowed to be stored in the database, your application is unsafe if you're later using that data without parameterization.

这篇关于如果使用MySQLi预备语句，是否需要转义变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！