問題描述

歷史上，我一直使用

mysql_real_escape_string()

來自最終接觸數據庫的用戶的所有輸入.

for all input derived from users that ends up touching the database.

現在我已經完全轉換到 MySQLi 并且我正在使用帶有綁定參數的準備好的查詢，我是否有效地消除了 SQL 注入攻擊的可能性?

Now that I've completely converted over to MySQLi and I'm using prepared queries with bound parameters, have I effectively eliminated the possibility of SQL injection attacks?

我說我不再需要了嗎

mysql_real_escape_string()?

這是我的理解和我的一個項目的基礎:http://sourceforge.net/projects/mysqldoneright/files/Base/MysqlDoneRight-0.23.tar.gz/download

This is my understanding and the basis of a project of mine: http://sourceforge.net/projects/mysqldoneright/files/Base/MysqlDoneRight-0.23.tar.gz/download

這不是我想要出錯的事情，盡管現在我已經發布了它，它也可能影響其他人.

This is not something I want to get wrong though as now that I've released it, it could affect others as well.

所有用戶提供的輸入現在都將在 bind_parms 中結束.
準備階段提供的查詢是靜態的.

All user provided input will now end up in bind_parms.
The queries provided in the prepare phase are static.

推薦答案

事情沒那么簡單.您可以使用綁定參數而不是將應用程序變量插入到 SQL 表達式中來代替 文字值:

It's not so simple. You can use bound parameters instead of interpolating application variables into SQL expressions in place of literal values only:

$sql = "SELECT * FROM MyTable WHERE id = ".$_GET["id"]; // not safe

$sql = "SELECT * FROM MyTable WHERE id = ?"; // safe

但是，如果除了字面值之外，您還需要使查詢的一部分動態化怎么辦?

But what if you need to make part of the query dynamic besides a literal value?

$sql = "SELECT * FROM MyTable ORDER BY ".$_GET["sortcolumn"]; // not safe

$sql = "SELECT * FROM MyTable ORDER BY ?"; // doesn't work!

該參數將始終被解釋為一個值，而不是一個列標識符.您可以使用ORDER BY 'score' 運行查詢，這與ORDER BY score 不同，使用參數將被解釋為前者——常量字符串'score'，而不是名為 score 的列中的值.

The parameter will always be interpreted as a value, not a column identifier. You can run a query with ORDER BY 'score', which is different from ORDER BY score, and using a parameter will be interpreted as the former -- a constant string 'score', not the value in the column named score.

因此，在很多情況下，您必須使用動態 SQL 并將應用程序變量插入到查詢中才能獲得想要的結果.在這些情況下，查詢參數幫不了你.您仍然必須保持警惕并進行防御性編碼以防止 SQL 注入缺陷.

So there are lots of cases where you have to use dynamic SQL and interpolate application variables into the query to get the results you want. In those cases, query parameters can't help you. You still have to be vigilant and code defensively to prevent SQL injection flaws.

沒有任何框架或數據訪問庫可以為您完成這項工作.您始終可以構建包含 SQL 注入缺陷的 SQL 查詢字符串，并且在數據訪問庫看到 SQL 查詢之前執行此操作.那么它應該如何知道什么是故意的，什么是缺陷?

No framework or data-access library can do this work for you. You can always construct a SQL query string that contains a SQL injection flaw, and you do this before the data-access library sees the SQL query. So how is it supposed to know what's intentional and what's a flaw?

以下是實現安全 SQL 查詢的方法:

Here are the methods to achieve secure SQL queries:

• 過濾輸入. 跟蹤插入到 SQL 查詢中的任何變量數據.使用輸入 filters 去除非法字符.例如，如果您需要一個整數，請確保輸入被限制為一個整數.

• Filter input. Trace any variable data that gets inserted into your SQL queries. Use input filters to strip out illegal characters. For instance, if you expect an integer, make sure the input is constrained to be an integer.

轉義輸出. 此上下文中的輸出可以是您發送到數據庫服務器的 SQL 查詢.您知道可以對值使用 SQL 查詢參數，但是列名呢?您需要一個用于標識符的轉義/引用函數，就像舊的 mysql_real_escape_string() 用于字符串值一樣.

Escape output. Output in this context can be the SQL query which you send to the database server. You know you can use SQL query parameters for values, but what about a column name? You need an escaping/quoting function for identifiers, just like the old mysql_real_escape_string() is for string values.

代碼審查.讓別人成為第二雙眼睛并檢查您的 SQL 代碼，以幫助您發現您忽略使用上述兩種技術的地方.

Code reviews. Get someone to be a second pair of eyes and go over your SQL code, to help you spot places where you neglected to use the above two techniques.

這篇關于PHP MySQLi 準備的帶有綁定參數的查詢是否安全?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！