哪些字符实际上能够在 mysql 中导致 SQL 注入

Which characters are actually capable of causing SQL injection in mysql

提问by schizodactyl

We all know that we should use prepared statements or the appropriate replacement/formatting rules in order to prevent sql injection in our applications.

我们都知道我们应该使用准备好的语句或适当的替换/格式化规则来防止我们的应用程序中的 sql 注入。

However, when taking a look at MySQL's list of character literals, I noticed that it includes the following characters:

但是，在查看 MySQL 的字符文字列表时，我注意到它包含以下字符：

• \0An ASCII NUL (0x00) character.
• \'A single quote (') character.
• \"A double quote (") character.
• \bA backspace character.
• \nA newline (linefeed) character.
• \rA carriage return character.
• \tA tab character.
• \ZASCII 26 (Ctrl+Z). See note following the table.
• \\A backslash (\) character.
• \%A %character.
• \_A _character.

• \0一个 ASCII NUL ( 0x00) 字符。
• \'单引号 ( ') 字符。
• \"双引号 ( ") 字符。
• \b退格字符。
• \n换行符（换行符）。
• \r一个回车符。
• \t一个制表符。
• \ZASCII 26 ( Ctrl+ Z)。请参阅表格后面的注释。
• \\反斜杠 ( \) 字符。
• \%一个%字符。
• \_一个_字符。

Now, while the %and _characters need to be escaped in order to prevent injection of unwanted wildcards into LIKE statements, and while the '(single quote), \(backslash), and "(double quote) all need to be escaped in order to prevent injection of arbitrary SQL - could having any of these other characters unescaped lead directly to a SQL injection vulnerability that would not otherwise be present? Does anyone have any real world examples of such an exploit?

现在，虽然%和_字符需要转义以防止将不需要的通配符注入 LIKE 语句，而'(单引号)、\(反斜杠) 和"(双引号) 都需要转义以防止注入任意 SQL - 将这些其他字符中的任何一个未转义是否会直接导致原本不会存在的 SQL 注入漏洞？有没有人有这种利用的真实世界的例子？

Let's assume we are building our query like:

假设我们正在构建我们的查询，如：

SELECT * FROM users WHERE username='$user'

Is there any value for $userwhere the only unescaped character literals are \b(backspace), \0(NUL), \n(newline), \r(linefeed), \t(tab) or \Z(Ctrl+Z) that allows the injection of arbitrary SQL into this query?

$user唯一未转义的字符文字是\b(backspace), \0(NUL), \n(newline), \r(linefeed), \t(tab) 或\Z( Ctrl+ Z)是否有任何值允许将任意 SQL 注入此查询？

采纳答案by Uours

Considering the below lines from mysql_real_escape_string()manual :

考虑mysql_real_escape_string()手册中的以下几行：

MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. mysql_real_escape_string()quotes the other characters to make them easier to read in log files.

MySQL 只需要转义反斜杠和用于引用查询中字符串的引号字符。mysql_real_escape_string()引用其他字符以使它们更容易在日志文件中阅读。

SQL injection in MySQL should not be possible with these special characters alone by themselves : \b\0\n\r\t\Z.

MySQL 中的 SQL 注入不应该单独使用这些特殊字符：\b\0\n\r\t\Z。

However String Literals manual states the following but the reasons specified ( or not ) does not relate to SQL injection :

然而，字符串文字手册陈述了以下内容，但指定的（或不）原因与 SQL 注入无关：

If you want to insert binary data into a string column (such as a BLOBcolumn), you should represent certain characters by escape sequences. Backslash (“\”) and the quote character used to quote the string must be escaped. In certain client environments, it may also be necessary to escape NUL or Control+Z. The mysqlclient truncates quoted strings containing NUL characters if they are not escaped, and Control+Z may be taken for END-OF-FILE on Windows if not escaped.

如果要将二进制数据插入到字符串列（例如BLOB列）中，则应使用转义序列表示某些字符。必须对反斜杠 (“\”) 和用于引用字符串的引号字符进行转义。在某些客户端环境中，可能还需要转义 NUL 或 Control+Z。在MySQL的客户端截断援引包含NULL字符的字符串，如果他们没能逃脱，并控制+ Z，可以采取档案结尾在Windows上，如果没有逃脱。

Furthermore , in a simple test , irrespective of weather the above listed special characters are escaped or not , MySQL yielded same results . In other words MySQL did not even mind :

此外，在一个简单的测试中，无论天气如何，上面列出的特殊字符是否被转义，MySQL 都产生了相同的结果。换句话说，MySQL 甚至不介意：

$query_sql = "SELECT * FROM `user` WHERE user = '$user'";

The above query worked similarly for non-escaped and escaped versions of those above listed characters as put below :

上面的查询对于上面列出的字符的非转义和转义版本的工作方式类似，如下所示：

$user = chr(8);     // Back Space
$user = chr(0);     // Null char
$user = chr(13);    // Carriage Return
$user = chr(9);     // Horizontal Tab
$user = chr(26);    // Substitute
$user = chr(92) .chr(8);    // Escaped Back Space
$user = chr(92) .chr(0);    // Escaped Null char
$user = chr(92) .chr(13);   // Escaped Carriage Return
$user = chr(92) .chr(9);    // Escaped Horizontal Tab
$user = chr(92) .chr(26);   // Escaped Substitute

Test table and data used in the simple test :

简单测试中用到的测试表和数据：

-- Table Structure

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(10) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- Table Data

INSERT INTO `user` ( `user` ) VALUES
( char( '8' ) ),
( char( '0' ) ),
( char( '10' ) ),
( char( '13' ) ),
( char( '9' ) ),
( char( '26' ) );

回答by Your Common Sense

There are no such characters.

没有这样的字符。

There are two wrong statements in your question that lead you to confuse:

你的问题中有两个错误的陈述让你感到困惑：

1. We all know that we should use ... the appropriate replacement rules in order to prevent sql injection in our applications.

1. 我们都知道我们应该使用......适当的替换规则，以防止我们的应用程序中的sql注入。

This is wrong statement. Not replacement but formatting. It's essential thing. Replacements do not protect against injections, while formatting does. Note that every distinct part of the query require differentformatting which being useless for any other part. Say, there is another character, essential for injection protection - a backtick (`). But you didn't list it because it has nothing to do with string literals.

这是错误的说法。不是替换而是格式化。这是必不可少的东西。替换不能防止注入，而格式化可以。请注意，查询的每个不同部分都需要不同的格式，这对任何其他部分都是无用的。比如说，还有另一个字符，对于注入保护必不可少 - 反引号 (`)。但是您没有列出它，因为它与字符串文字无关。

1. the ' (single quote), \ (backslash), and " (double quote) all need to be escaped in order to prevent injection

1. '（单引号）、\（反斜杠）和 "（双引号）都需要转义以防止注入

This is awfully wrong statement. Escaping do not prevent injections!These characters need to be escaped in order to format stringsand has absolutely nothing to do with injections. While it is true that properly formatted query part is invulnerable against injection. But the truth is - you have to format dynamical query parts just for sake of it, to follow syntax rules - not because of whatever injections. And you will have your query impenetrable just as a consequence.

这是非常错误的说法。逃避不妨碍注射！这些字符需要被转义以格式化字符串并且与注入完全无关。虽然格式正确的查询部分确实对注入是无懈可击的。但事实是——你必须格式化动态查询部分只是为了它，遵循语法规则——而不是因为任何注入。因此，您的查询将变得难以理解。

Now you can see why your last statement,

现在你可以明白为什么你的最后一句话了，

why all of these other characters are vulnerable enough to be escaped via mysql_real_escape_string, as it is not immediately obvious to me.

为什么所有这些其他字符都足够脆弱，可以通过 mysql_real_escape_string 进行转义，因为这对我来说并不是很明显。

is wrongly put:
It is string formatting rules require these characters, not whatever "vulnerability". Some of them are escaped just for convenience, some for readability, some for obvious reason of escaping a delimiter. That's all.

错误地提出：
字符串格式规则需要这些字符，而不是任何“漏洞”。其中一些只是为了方便而被转义，一些是为了可读性，一些是出于明显的转义分隔符的原因。就这样。

To answer recent questions from comments:

从评论中回答最近的问题：

I really want an answer to this, as PHP's mysql_real_escape_string does not quote these literals either.

我真的很想回答这个问题，因为 PHP 的 mysql_real_escape_string 也没有引用这些文字。

Again: although in the mind of average PHP user mysql_real_escape_string()is strongly connected to whatever scaring injection, in reality it doesn't.There are no "dangerous" characters ever. Not a single one. There are some service characters with special meaning. They have to be escaped in some circumstances, depends on the context.

再说一遍：虽然在普通 PHP 用户的心目中，mysql_real_escape_string()与任何可怕的注入密切相关，但实际上并非如此。从来没有“危险”的角色。一个都没有。有一些具有特殊含义的服务字符。在某些情况下，它们必须被转义，这取决于上下文。

Thus, there is no connection between characters escaped by this function, and whatever "danger". The moment you start thinking that mysql_real_escape_string()'s purpose is to escape "dangerous" characters, you are indeed become in danger. While long as you are using this function only to escape strings (and doing it unconditionally) - you may consider yourself safe (of course if you don't forget to format all other literalstoo, using their respective formatting rules)

因此，此函数转义的字符与任何“危险”之间没有联系。当您开始认为其mysql_real_escape_string()目的是逃避“危险”角色时，您确实处于危险之中。只要您仅使用此函数来转义字符串（并无条件地执行此操作）-您可能会认为自己是安全的（当然，如果您也没有忘记使用它们各自的格式规则来格式化所有其他文字）

I want to know if the "%" character can lead to anything more than extra results in a LIKE clause.

我想知道“%”字符是否会导致 LIKE 子句中的额外结果。

No.

不。