PHP:使用准备好的语句并防止SQL注入与转义 [英] PHP: using prepared statements and protecting against SQL injection vs escape

问题描述

我确实知道，准备好的语句是寻求防止SQL注入的最终方法.但是，它们以有限的方式提供覆盖范围；例如，如果我让用户决定按操作的顺序如何(即是ASC还是DESC?等)，那么准备好的语句就不会覆盖那里.

I do understand that the prepared statements is the ultimate way to seek protection against the SQL injection. However, they provide coverage in a limited fashion; for example, in cases where I let the user to decide how the order by operation to be ( i.e, is it ASC or DESC? etc ), I get no coverage there with the prepared statements.

我了解可以将用户输入映射到预定义的白名单.但是，只有在可以事先创建或完全猜测白名单的情况下，这才有可能.

I understand that I can map the user input to a pre-defined white list for that. But, this is only possible when a whitelist can be created or guessed thoroughly beforehand.

例如，在我上面提到的情况下(ASC或DESC)，可以很容易地将其映射并针对接受值列表进行验证.但是，是否存在无法根据白名单验证SQL语句部分的情况?

For example, in the cases I mention above ( the ASC, or DESC ), this can easily be mapped and verified against a list of accepted values. But isn't there a situation where the portion of the SQL statement cannot be verified against a white list?

如果存在这种情况，那么推荐的方法是什么?

If such a situation exists, then what's the recommended approach?

如果我要使用基础数据库的内置转义实用程序(例如mysql的mysqL_real_escape_string)对用户输入进行转义，那我将在哪里失败?

If I were to escape the user_input using the underlying database's built-in escape utility (such as mysqL_real_escape_string for mysql) across the board, where would I fail?

我问这个问题的前提是我总是用带引号的值构造我的sql语句-甚至对于整数...

I'm asking this question with the assumption that I always construct my sql statements with quoted values - even for integers...

让我们看一下下面的示例并进行思考.

Let's take a look at the following example and reflect upon it..

select {$fields} from {$table} where Age='{$age}' order by {$orderby_pref}

假定所有变量都是用户提供的.

Assume all vars are user supplied.

如果我要使用mysql_real_escape_string上面的SQL中的所有变量(而不是使用准备好的语句，这只覆盖了我一半就强迫我列出无法帮助的另一半的白名单)，不是吗?同样安全(并且更容易编码)?如果不是，在哪个输入场景转义实用程序将失败?

If I were to mysql_real_escape_string all the variables in the above SQL ( as opposed to using prepared statements which covers me only half-way forcing me to come up whitelists for the other half that it cannot help), wouldn't it be equally safe (and easier to code)? If not, in which input scenario escape utility would fail?

$fields       = mysql_escape($fields);
$table        = mysql_escape($table);
$age          = mysql_escape($age);
$orderby_pref = mysql_escape($orderby_pref);

select {$fields} from {$table} where Age='{$age}' order by {$orderby_pref}

推荐答案

无论使用预备语句还是 >转义功能.

问题在于表名和列名未用单引号或双引号引起来，因此，如果您使用专门用引号将这些字符引号的函数(当然还有更多……)，它将对您的表不起作用名称.

The problem is that table names and column names are not quoted in single or double quotes, so if you use a function that specifically quotes these characters (and some more of course...), it will do nothing for your table name.

考虑表名称my_table; DELETE * FROM mysql; SELECT * FROM my_table.该字符串中的任何内容都不会被mysql的转义函数转义，但绝对是您要对照白名单检查的字符串.

Consider the table name my_table; DELETE * FROM mysql; SELECT * FROM my_table. Nothing in this string will get escaped by mysql's escape functions but it is definitely a string you would want to check against a white-list.

除了mysql转义功能存在字符集有问题之外，这些字符集会使它们变得无用，因此使用预备语句总是会更好.

Apart from that the mysql escape functions have a problem with character sets that can render them useless, so you are always better off with prepared statements.

这篇关于PHP:使用准备好的语句并防止SQL注入与转义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！