MySQL 必须转义哪些字符以防止 (My)SQL 注入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1086918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What characters have to be escaped to prevent (My)SQL injections?
提问by Tower
I'm using MySQL API's function
我正在使用 MySQL API 的功能
mysql_real_escape_string()
Based on the documentation, it escapes the following characters:
根据文档，它转义了以下字符：
"""
Encodes a character for MySQL.
"""
lookup = {
0x00 : "\0",
0x08 : "\b",
0x09 : "\t",
0x0a : "\n",
0x0d : "\r",
0x1a : "\Z",
0x22 : '\"',
0x25 : "\%",
0x27 : "\'",
0x5c : "\\",
0x5f : "\_",
}
\n
\r
\
'
"
\Z
Now, I looked into OWASP.org's ESAPI security library and in the Python port it had the following code (http://code.google.com/p/owasp-esapi-python/source/browse/esapi/codecs/mysql.py):
现在，我查看了 OWASP.org 的 ESAPI 安全库，在 Python 端口中它有以下代码（http://code.google.com/p/owasp-esapi-python/source/browse/esapi/codecs/mysql. py):
SELECT a FROM b WHERE c = '...user input ...';
Now, I'm wondering whether all those characters are really needed to be escaped. I understand why % and _ are there, they are meta characters in LIKE operator, but I can't simply understand why did they add backspace and tabulator characters (\b \t)? Is there a security issue if you do a query:
现在，我想知道是否真的需要转义所有这些字符。我理解为什么 % 和 _ 在那里，它们是 LIKE 运算符中的元字符，但我不能简单地理解它们为什么要添加退格符和制表符 (\b \t)？如果您进行查询，是否存在安全问题：
INSERT INTO students VALUES ("Bobby Tables",12,"abc",3.6);
Where user input contains tabulators or backspace characters?
用户输入在哪里包含制表符或退格字符？
My question is here: Why did they include \b \t in the ESAPI security library? Are there anysituations where you might need to escape those characters?
我的问题在这里：为什么他们在 ESAPI 安全库中包含 \b \t？在任何情况下您可能需要转义这些字符吗？
回答by balpha
A guess concerning the backspace character: Imagine I send you an email "Hi, here's the query to update your DB as you wanted" and an attached textfile with
关于退格字符的猜测：想象一下，我向您发送了一封电子邮件“嗨，这是根据需要更新数据库的查询”和一个附加的文本文件
DROP TABLE students;\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b
You cat the file, see it's okay, and just pipe the file to MySQL. What you didn't know, however, was that I put
你 cat 文件，看到它没问题，然后将文件通过管道传输到 MySQL。然而，你不知道的是，我把
public static String filter( String s ) {
StringBuffer buffer = new StringBuffer();
int i;
for( byte b : s.getBytes() ) {
i = (int) b;
switch( i ) {
case 9 : buffer.append( " " ); break;
case 10 : buffer.append( "\n" ); break;
case 13 : buffer.append( "\r" ); break;
case 34 : buffer.append( "\\"" ); break;
case 39 : buffer.append( "\'" ); break;
case 92 : buffer.append( "\" );
if( i > 31 && i < 127 ) buffer.append( new String( new byte[] { b } ) );
}
}
return buffer.toString();
}
before the INSERT STATEMENT which you didn't see because on console output the backspaces overwrote it. Bamm!
在您没有看到的 INSERT STATEMENT 之前，因为在控制台输出上退格键覆盖了它。砰！
Just a guess, though.
不过只是猜测。
Edit (couldn't resist):
编辑（无法抗拒）：
回答by Gumbo
The MySQL manual page for stringssays:
\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. See note following the table.\_???A “_” character. See note following the table.
\0???一个 ASCII NUL (0x00) 字符。\'???单引号 (“'”) 字符。\"???双引号 (“"”) 字符。\b???退格字符。\n???换行（换行）字符。\r???一个回车符。\t???制表符。\Z???ASCII 26 (Control-Z)。请参阅表格后面的注释。\\???反斜杠（“\”）字符。\%???一个“%”字符。请参阅表格后面的注释。\_???一个“_”字符。请参阅表格后面的注释。
回答by Cheekysoft
Blacklisting (identifying bad characters) is never the way to go, if you have any other options.
如果您有任何其他选择，将黑名单（识别坏字符）列入黑名单绝不是可行的方法。
You need to use a conbination of whitelisting, and more importantly, bound-parameter approaches.
您需要结合使用白名单，更重要的是使用绑定参数方法。
Whilst this particular answer has a PHP focus, it still helps plenty and will help explain that just running a string through a char filter doesn't work in many cases. Please, please see Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?
虽然这个特定的答案侧重于 PHP，但它仍然有很多帮助，并且有助于解释仅通过字符过滤器运行字符串在许多情况下不起作用。请，请参阅htmlspecialchars 和 mysql_real_escape_string 是否可以防止我的 PHP 代码被注入？
回答by Your Common Sense
Where user input contains tabulators or backspace characters?
用户输入在哪里包含制表符或退格字符？
It's quite remarkable a fact that up to this day most users do believe that it's user inputhave to be escaped, and such escaping "prevents injections".
一个非常了不起的事实是，直到今天，大多数用户确实认为必须对用户输入进行转义，而这种转义“可以防止注入”。
回答by crae
Java solution:
Java解决方案：
##代码##回答by Jarett L
couldn't one just delete the single quote(s) from user input?
不能从用户输入中删除单引号吗？
eg: $input =~ s/\'|\"//g;
例如： $input =~ s/\'|\"//g;
