如果我使用 MySQLi 准备好的语句，是否需要转义我的变量?
如果我使用 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.
根据 this 类似的回答关于 SO 的问题:
According to this answer of a similar question on SO:
准备好的语句/参数化查询足以防止对该语句的一阶注入.如果您在应用程序的任何其他地方使用未检查的动态 sql，您仍然容易受到二阶注入的影响.
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 存储在数据库中，如果您稍后在没有参数化的情况下使用该数据，您的应用程序是不安全的.
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.
相关文章