是否需要从数据库中转义用户输入?

So I know about MySQL injection and always escape all my user input before putting it in my database. However I was wondering, imagine a user tries to submit a query to inject, and I escape it. What if I then at a later moment take this value from the database, and use it in a query. Do I have to escape it again?

所以我知道MySQL注入，在将它放入数据库之前，我总是转义所有的用户输入。然而，我想知道，假设一个用户试图提交一个查询来注入，然后我转义它。如果稍后我从数据库中获取该值，并在查询中使用该值，该怎么办?我还要再逃一次吗?

So: (sql::escape() contains my escape function)

(sql::escape()包含我的escape函数)

$userinput = "'); DROP `table` --";
mysql_query("INSERT INTO `table` 
             (`foo`,`bar`) 
             VALUES 
             ('foobar','".sql::escape($userinput)."')");

// insert php/mysql to fetch `table`.`bar` into $output here

mysql_query("INSERT INTO `table2` 
            (`foo`,`bar`) 
            VALUES
            ('foobar','".$output."')");

Does MySQL automatically escape their output or something like that, or should I escape in the second query as well?

MySQL会自动转义它们的输出吗?或者我应该在第二个查询中转义吗?

This is a testcase but this occurs in some other ways within my program and I'm wondering how tight the security has to be for cases like this.

这是一个测试用例，但这在我的程序中以其他方式发生，我想知道对于这种情况，安全性有多严格。

EDIT

编辑

My escape function

我逃避函数

static function escape($string){

    if(get_magic_quotes_gpc()) 
        $string = stripslashes($string); 

    return mysql_real_escape_string($string);

}

4 个解决方案

#1

Does MySQL automatically escape their output or something like that, or should I escape in the second query as well?

MySQL会自动转义它们的输出吗?或者我应该在第二个查询中转义吗?

You need to escape in the second query as well. MySQL does not do any escaping on its output.

您还需要在第二个查询中转义。MySQL不会对输出进行任何转义。

Long answer: MySQL string escaping does not modify the string that is being inserted, it just makes sure it doesn't do any harm in the current query. Any SQL injection attempt still remains in the data.

长答:MySQL字符串转义不会修改正在插入的字符串，它只会确保在当前查询中不会造成任何伤害。任何SQL注入尝试仍然保留在数据中。

#2

Yes, you have to escape the string in the second query too.

Escaping the string sounds magical to many people, something like shield against some mysterious danger, but in fact it is nothing magical. It is just the way to enable special characters being processed by the query.

对很多人来说，逃离绳子听起来很神奇，就像保护自己不受某些神秘的危险，但事实上，它并不是什么神奇的东西。它只是启用查询处理的特殊字符的方法。

The best would be just to have a look what escaping really does. Say the input string is:

最好的办法就是看看逃跑到底是怎么回事。输入字符串为:

'); DROP `table` --

after escaping:

越狱后:

\'); DROP `table` --

in fact it escaped only the single slash. That's the only thing you need to assure - that when you insert the string in the query, the syntax will be OK!

实际上它只漏了一个斜杠。这是惟一需要确保的事情——当您在查询中插入字符串时，语法将是OK的!

insert into table set column = '\'); DROP `table` --'

It's nothing magical like danger shield or something, it is just to ensure that the resultant query has the right syntax! (of course if it doesn't, it can be exploited)

它并不像“危险之盾”之类的魔法，它只是为了确保生成的查询具有正确的语法!(当然，如果没有的话，它可以被利用)

The query parser then looks at the \' sequence and knows that it is still the variable, not ending of its value. It will remove the backslash and the following will be stored in the database:

然后，查询解析器查看\的序列并知道它仍然是变量，而不是其值的结尾。它将删除反斜杠，以下内容将存储在数据库中:

'); DROP `table` --

which is exactly the same value as user entered. And which is exactly what you wanted to have in the database!!

与用户输入的值完全相同。而这正是您想要在数据库中拥有的!!

So this means that the if you fetch that string from the database and want to use it in the query again, you need to escape it again to be sure that the resultant query has the right syntax.

这意味着如果您从数据库中获取该字符串并希望再次在查询中使用它，那么您需要再次转义，以确保生成的查询具有正确的语法。

But, in your example, very important thing to mention is the magic_quotes_gpc directive!

This feature escapes all the user input automatically (gpc - _GET, _POST and _COOKIE). This is an evil feature made for people not aware of sql injection. It is evil for two reasons. First reason is that then you have to distinguish the case of your first and second query - in the first you don't escape and in the second you do. What most people do is to either switch the "feature" off (I prefer this solution) or unescape the user input at first and then escape it again when needed. The unescape code could look like:

这个特性自动转义所有用户输入(gpc - _GET、_POST和_COOKIE)。这是一个为不知道sql注入的人而设计的邪恶特性。它的邪恶有两个原因。第一个原因是，您必须区分第一个和第二个查询的情况——在第一个查询中，您没有转义，在第二个查询中，您有转义。大多数人所做的是要么关闭“特性”(我更喜欢这个解决方案)，要么首先取消对用户输入的转义，然后在需要时再次转义。unescape代码可以如下所示:

function stripslashes_deep($value)
{
        return is_array($value) ?
               array_map('stripslashes_deep', $value) :
               stripslashes($value);
}

if (get_magic_quotes_gpc()) {
        $_POST = stripslashes_deep($_POST);
        $_GET = stripslashes_deep($_GET);
        $_COOKIE = stripslashes_deep($_COOKIE);
}

The second reason why this is evil is because there is nothing like "universal quoting". When quoting, you always quote text for some particular output, like:

这是邪恶的第二个原因是因为没有任何东西像“普遍引用”。当引用时，你总是引用一些特定输出的文本，比如:

1. string value for mysql query
2. 用于mysql查询的字符串值
3. like expression for mysql query
4. 类似于mysql查询的表达式
5. html code
6. html代码
7. json
8. json
9. mysql regular expression
10. mysql正则表达式
11. php regular expression
12. php的正则表达式

For each case, you need different quoting, because each usage is present within different syntax context. This also implies that the quoting shouldn't be made at the input into PHP, but at the particular output! Which is the reason why features like magic_quotes_gpc are broken (never forget to handle it, or better, assure it is switched off!!!).

对于每种情况，都需要不同的引用，因为每种用法都存在于不同的语法上下文中。这也意味着引用不应该在PHP的输入，而是在特定的输出!这就是为什么magic_quotes_gpc这样的功能被破坏(永远不要忘记处理它，或者更好的是，确保它被关闭!)

So, what methods would one use for quoting in these particular cases? (Feel free to correct me, there might be more modern methods, but these are working for me)

那么，在这些特殊情况下，我们应该用什么方法来引用呢?(请随时纠正我的错误，或许有更现代的方法，但这些方法对我很有效)

1. mysql_real_escape_string($str)
2. mysql_real_escape_string(str)美元
3. mysql_real_escape_string(addcslashes($str, "%_"))
4. mysql_real_escape_string(addcslashes(str美元,“% _”))
5. htmlspecialchars($str)
6. htmlspecialchars函数(str)美元
7. json_encode() - only for utf8! I use my function for iso-8859-2
8. json_encode()——只适用于utf8!我用函数表示iso-8859-2
9. mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case because backslash would be escaped two times!
10. mysql_real_escape_string(addcslashes(str美元,”^。[]()美元| * + ? { }”)),在这种情况下,您不能使用preg_quote因为反斜杠将逃过两次!
11. preg_quote()
12. preg_quote()

#3

I'd say that whole idea of this question is wrong.

我认为这个问题的整个想法是错误的。

You're taking this problem absolutely wrong way.
One doesn't have to count his queries, if it's first or second or 100th.
Same goes for the the user input: it doesn't matter, where the data come from!

你把这个问题看错了。如果查询是第一次、第二次或第100次，则不必计算查询次数。用户输入也是如此:数据来自哪里并不重要!

Data destination, not source should be your concern. Is this string going to database? Escape it! With no questions. This rule is plain and simple and require no query counting or anything.

您应该关注的是数据目的地，而不是源。这个字符串会进入数据库吗?逃避它!没有问题。这条规则简单明了，不需要查询计数或其他任何东西。

But that's not only fault in your question.
One:

但这不仅仅是你的问题。一:

Does MySQL automatically escape their output or something like that?

MySQL会自动转义输出吗?

That's a very bad idea. Funny part, you're fighting with a consequence of the same idea in your code, by applying get_magic_quotes_gpc(). What are these magic quotes if not such automatic escaping?

这是个坏主意。有趣的是，通过应用get_magic_quotes_gpc()，您正在与代码中相同思想的结果进行斗争。如果不是自动转义的话，这些神奇的引语是什么呢?

Two:
moreover, using get_magic_quotes_gpc() in your escaping function is a very bad idea again :)

第二:此外，在转义函数中使用get_magic_quotes_gpc()也是一个非常糟糕的主意:)

imagine you have magic quotes on and using your function to protect your "second query". And there is some blob that contain \' sequence in the data. Your function will strip the slash and spoil the data. In fact, stripslashes has absolutely nothing to do with any escaping function. do it separately, on the data where it belongs - on the user input.

想象一下，你有神奇的引号，并使用你的函数来保护你的“第二个查询”。还有一些blob数据中包含\'序列。函数将删除斜线并破坏数据。实际上，stripslash与任何转义函数都没有关系。在它所属的数据上分别进行操作——在用户输入上。

Three:
mysql_real_escape_string() is not some magic function that "makes everything safe". In fact, to create dynamic mysql query, one have to escape four kinds of data:

3:mysql_real_escape_string()不是“使一切安全”的魔法函数。事实上，要创建动态mysql查询，需要转义四种数据:

• strings
• 字符串
• numbers
• 数字
• identifiers
• 标识符
• operators
• 运营商

while mysql_real_escape_string() escaping only one of them. And your query stand absolutely naked in all three other cases. Funny, eh?

而mysql_real_escape_string()只转义其中一个。在其他三种情况下，你的查询是完全透明的。有趣的,不是吗?

Most disappointing part: I know that all this ultimate knowledge is in vain and would be read scarcely by few noobs and never change either overall knowledge level of PHP community in general, nor answers quality on SO in particular. :(

最令人失望的部分:我知道所有这些最终的知识都是徒劳的，很少有noobs会去阅读它们，并且不会改变PHP社区的整体知识水平，也不会特别去回答质量问题。:(

#4

Try to use PHP's PDO for database access if you can. There are two important reasons for this:

如果可以，尝试使用PHP的PDO进行数据库访问。有两个重要的原因:

1. You can use PDO's prepare function to compile your query. This is efficient if you need to issue the same query with different input (as is often the case). So, compile once and execute multiple times.
2. 您可以使用PDO的prepare函数来编译查询。如果您需要用不同的输入发出相同的查询(通常是这种情况)，那么这是有效的。因此，编译一次并执行多次。
3. Compiling the query with prepare has other nice effects. Once the query is compiled, the database engine knows the exact syntactic structure of the query, and does not allow any input that changes this syntactic structure. This is good because in SQL injection, the injected input changes the syntax of the query.
4. 编写查询和准备有其他好的效果。一旦编译了查询，数据库引擎就知道查询的确切语法结构，并且不允许任何输入更改这个语法结构。这很好，因为在SQL注入中，注入的输入会改变查询的语法。

Warning: This doesn't prevent all kinds of SQL injection, but it prevents the most common kind.

警告:这并不能防止所有类型的SQL注入，但是可以防止最常见的类型。

References:

引用:

1. Are PDO prepared statements sufficient to prevent SQL injection?
2. PDO准备的语句是否足以防止SQL注入?
3. http://php.net/manual/en/pdo.prepare.php
4. http://php.net/manual/en/pdo.prepare.php