使用 sql 转义的动态 mysql 查询与准备好的语句一样安全吗? [英] Are dynamic mysql queries with sql escaping just as secure as prepared statements?

问题描述

我有一个应用程序，它可以通过将动态 mysql 查询与 mysql (mysqli) 真实转义字符串结合使用而受益匪浅.如果我通过 mysql real escape 运行从用户那里收到的所有数据，它会和使用 mysql 准备好的语句一样安全吗?

I have an application which would greatly benefit by using dynamic mysql queries in combination with mysql (mysqli) real escape string. If I ran all data received from the user through mysql real escape would it be just as secure as using mysql prepared statements?

推荐答案

绝对不会.

虽然标题中的问题含糊不清，可以被解释为动态 mysql 查询是否具有每个部分格式正确..."，因此得到肯定的回答，正文中的问题不是:

Definitely NO.

While question in the title is ambiguous and can be interpreted as "Are dynamic mysql queries with every it's part properly formatted..." and thus have a positive answer, the question in the body is not:

如果我通过 mysql real escape 运行从用户那里收到的所有数据，它会和使用 mysql 准备好的语句一样安全吗?

If I ran all data received from the user through mysql real escape would it be just as secure as using mysql prepared statements?

如果你仔细看这个问题，你就会明白这只是一个魔法语录的化身！这个耻辱、弃用和删除的功能的真正目的正是通过转义运行所有用户输入".
现在每个人都知道魔术报价是不好的.那为什么要正面回答?

If you look to this question closer, you will understand that this is just a magic quotes incarnation! The very purpose of this disgraced, deprecated and removed feature is exactly to "run all user input through escape".
Everyone knows nowadays that magic quotes are bad. Why positive answer then?

好吧，看来又要解释了，为什么批量转义不好.

Okay, it seems that it needs to be explained again, why bulk escaping is bad.

问题的根源是一个相当强烈的妄想，几乎每个PHP用户都有:
每个人都有一个奇怪的信念，即逃避会对危险人物"(他们是什么?)做一些事情，使他们安全"(如何?).不用说，这只是一个彻头彻尾的垃圾.

The root of the problem is a quite strong delusion, shared by almost every PHP user:
Everyone have a strange belief that escaping do something on "dangerous characters" (what are they?) making them "safe" (how?). Needless to say that it's but a complete rubbish.

事实是:

• 逃避不会净化"任何东西.
• 转义与注射无关.
• 转义与用户输入无关.

转义只是一种字符串格式，没有别的.
当你需要它时 - 尽管有注射的可能性，你仍然需要它.
当您不需要它时 - 它对防止注射甚至一点帮助都无济于事.

Escaping is merely a string formatting and nothing else.
When you need it - you need it despite of injection possibility.
When you don't need it - it won't help against injection even a little.

说到与预处理语句的区别，至少有一个问题(在sql-injection标签下已经多次提到过):
像这样的代码

Speaking of difference with prepared statements, there is at least one issue (which already mentioned many times under sql-injection tag):
a code like this

$clean = mysql_real_escape_string($_POST['some_dangerous_variable']);
$query = "SELECT * FROM someTable WHERE somevalue = $clean";

将帮助您避免注射.
因为转义只是一种字符串格式化工具，无论如何都不是注入防止器.
去搞清楚.

will help you NOT against injection.
Beause escaping is just a string formatting facility, not injection preventer by any means.
Go figure.

然而，转义与准备好的语句有一些共同点:
如果

However, escaping have something in common with prepared statements:
Them both doesn't guarantee you from injection if

• 您仅针对臭名昭著的用户输入"使用它，而不是构建任何查询的严格规则，尽管有数据源.
• 以防您需要插入的不是数据而是标识符或关键字.

为了在这些情况下安全，请参阅我的解释完整的 sql 注入保护方法

To be safe in these circumstances, see my answer explaining FULL sql injection protection how-to

长话短说:只有在您对初始声明进行 2 次必要更正和一次补充时，您才能认为自己是安全的:

Long story short: you can consider yourself safe only if you make 2 essential corrections and one addition to your initial statement:

如果我运行所有从用户接收到的数据通过mysql真正的转义并且总是用引号将它括起来(并且，正如ircmaxell提到的，mysqli_set_charset() 用于使 mysqli_real_escape string() 真正发挥作用(在这种罕见的情况下使用一些奇怪的编码，如 GBK))它会像使用 mysql 准备好的语句一样安全吗?

If I ran all data received from the user through mysql real escape and always enclose it in quotes (and, as ircmaxell mentioned, mysqli_set_charset() is used to make mysqli_real_escape string() actually do it's work (in such a rare occasion of using some odd encoding like GBK)) would it be just as secure as using mysql prepared statements?

遵循这些规则 - 是的，它将与本机准备好的语句一样安全.

Following these rules - yes, it would be as secure as native prepared statements.

这篇关于使用 sql 转义的动态 mysql 查询与准备好的语句一样安全吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！