哪些字符实际上可以在mysql中引起SQL注入 [英] Which characters are actually capable of causing SQL injection in mysql

问题描述

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

但是，当看看在mysql的字符文字列表中，我注意到它包含以下字符：

• \0 ASCII NUL（0x00） / li>
  
• \'单引号（'）字符。


• \双引号（）字符。 >
  
• \b退格字符。


• \\\
  换行符（换行符）。


• \r一个回车符。


• \t一个制表符。


• \Z ASCII 26（Control + Z）。


• \\一个反斜杠（\）。


• \％A％字符


• \__字符。

现在， _字符需要转义，以防止将不想要的通配符注入到LIKE语句中，而'（单引号），\（反斜杠）和（双引号）都需要转义，以防止注入任意的SQL - 可能会有任何其他这些其他字符未转义直接导致SQL注入漏洞，否则将不存在？有没有人有这样的漏洞利用的真实世界的例子？

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

  SELECT * FROM users WHERE username ='$ user'
  

$ user有什么价值，唯一未转义的字符文字是\b（backspace），\\ \\ 0（NUL），\\\
（newlin e），\r（换行），\t（选项卡）或\Z（Ctrl + Z），允许在此查询中注入任意SQL？

解决方案

考虑以下代码 mysql_real_escape_string（）手册：

MySQL只需要反斜杠和引用字符引用查询中的字符串进行转义。 mysql_real_escape_string（）引用其他字符以便使它们更容易阅读日志文件。

MySQL中的SQL注入不应该仅仅是使用这些特殊字符： code> \b \0 \\\
\r \t \Z 。

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

如果要将二进制数据插入字符串列（例如 BLOB 列），您应该通过转义序列来表示某些字符。反斜杠（\）和用于引用字符串的引号必须被转义。在某些客户端环境中，也可能需要逃避NUL或Control + Z。 mysql 客户端截断包含NUL个字符的引用字符串（如果它们不包含）如果没有转义，Control + Z可能会在Windows上被采用为END-OF-FILE。

此外，在一个简单的测试不管天气如何，上面列出的特殊字符都不会被转义，MySQL产生了相同的结果。换句话说，MySQL甚至没有想到：

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

上面的查询与上面列出的字符的非转义和转义版本类似，如下所示：

  $ user = chr（8）; // Back Space 
 $ user = chr（0）; // Null char 
 $ user = chr（13）; // Carriage Return 
 $ user = chr（9）; // Horizo​​ntal Tab 
 $ user = chr（26）; //替换
 $ user = chr（92）.chr（8）; // Escaped Back Space 
 $ user = chr（92）.chr（0）; // Escaped Null char 
 $ user = chr（92）.chr（13）; //逃逸回车
 $ user = chr（92）.chr（9）; // Escaped Horizo​​ntal Tab 
 $ user = chr（92）.chr（26）; //转换替代
  

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

   - 表结构
 
 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; 
 
  - 表数据
 
 INSERT INTO`user`（`user`）VALUES 
（char（'8'）），
（char （'0'）），
（char（'10'）），
（char（'13'）），
（char（'9'）），
 （char（'26'））; 
  

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

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

• \0 An ASCII NUL (0x00) character.
• \' A single quote ("'") character.
• \" A double quote (""") character.
• \b A backspace character.
• \n A newline (linefeed) character.
• \r A carriage return character.
• \t A tab character.
• \Z ASCII 26 (Control+Z). See note following the table.
• \\ A backslash ("\") character.
• \% A "%" character.
• \_ A "_" character.

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?

Let's assume we are building our query like:

SELECT * FROM users WHERE username='$user'

Is there any value for $user where 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?

解决方案

Considering the below lines from mysql_real_escape_string() manual :

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.

SQL injection in MySQL should not be possible with these special characters alone by themselves : \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 :

If you want to insert binary data into a string column (such as a BLOB column), 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 mysql client 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.

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 :

$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' ) );

这篇关于哪些字符实际上可以在mysql中引起SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！