PHP MySQLi 准备的带有绑定参数的查询是否安全? [英] Are PHP MySQLi prepared queries with bound parameters secure?

问题描述

历史上，我一直使用

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 准备的带有绑定参数的查询是否安全?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！