問題描述
如果我使用 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.
這篇關于如果我使用 MySQLi 準備好的語句，是否需要轉義我的變量?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！