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

问题描述

从历史上看，我一直都在使用

mysql_real_escape_string()

对于最终接触数据库的所有来自用户的输入.

现在，我已经完全转换为MySQLi，并且正在使用带有绑定参数的准备好的查询，是否有效消除了SQL注入攻击的可能性?

我正确地说我不再需要

mysql_real_escape_string()?

这是我的理解，也是我的一个项目的基础: http://sourceforge.net/projects/mysqldoneright/files/Base/MysqlDoneRight-0.23.tar.gz/download

这不是我想弄错的东西，尽管自从我发布它以来，它也可能会影响其他人.

所有用户提供的输入现在都将以bind_parms结尾.
在准备阶段提供的查询是静态的.

解决方案

这不是那么简单.您可以使用绑定参数，而不是将应用程序变量插值到SQL表达式中，以代替文字值:

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

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

但是，如果除了文字值之外，您还需要使查询的一部分动态化?

$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的列中的值.

因此，在很多情况下，您必须使用动态SQL并将应用程序变量插值到查询中才能获得所需的结果.在这种情况下，查询参数无法为您提供帮助.您仍然必须保持警惕和防御性代码，以防止SQL注入漏洞.

没有框架或数据访问库可以为您完成这项工作.您始终可以构造一个包含SQL注入缺陷的SQL查询字符串，并在数据访问库看到该SQL查询之前执行此操作.那么，应该如何知道意图是什么和缺陷是什么?

以下是实现安全SQL查询的方法:

• 过滤器输入.跟踪插入到SQL查询中的所有变量数据.使用输入过滤器去除非法字符.例如，如果您希望使用整数，请确保将输入限制为整数.

• 转义输出.在此上下文中的输出可以是您发送到数据库服务器的SQL查询.您知道可以对值使用SQL查询参数，但是列名呢?您需要对标识符使用转义/引号功能，就像旧的mysql_real_escape_string()用于字符串值一样.

• 代码评论..请其他人重新审视一下您的SQL代码，以帮助您发现您忽略使用上述两种技术的地方.

Historically, I've always used

mysql_real_escape_string()

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

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?

Am I correct in saying I no longer need

mysql_real_escape_string()?

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.

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

解决方案

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!

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.

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.

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?

Here are the methods to achieve secure SQL queries:

• 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.

• 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.

• 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屋！