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

问题描述

我有一个应用程序，通过将动态mysql查询与mysql(mysqli)实际转义字符串结合使用，将大大受益.如果我通过mysql真正的转义程序运行了从用户那里收到的所有数据，那么它与使用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真正的转义从用户那里接收到的所有数据，那么它与使用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 do not "sanitize" anything.
• Escaping has nothing to do with injections.
• Escaping has nothing to do with user input.

转义只是一种字符串格式而已.
当需要时-尽管有注射的可能，但仍需要它.
当您不需要它时，即使有一点注射也无济于事.

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";

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

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

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

在这种情况下为了安全起见，请参阅我的答案，以说明完整的SQL注入保护操作方法

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

长话短说:只有对您的初次陈述进行2次必要的更正和1次补充之后，您才能认为自己安全:

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