如何在 MySQL 中转义特殊字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/881194/
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
How do I escape special characters in MySQL?
提问by Paul Dixon
For example:
例如：
select * from tablename where fields like "%string "hi" %";
Error:
错误：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hi" "' at line 1
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“hi”“”附近使用的正确语法
How do I build this query?
如何构建此查询？
回答by Paul Dixon
The information provided in this answer can lead to insecure programming practices.
The information provided here depends highly on MySQL configuration, including (but not limited to) the program version, the database client and character-encoding used.
此答案中提供的信息可能会导致不安全的编程实践。
此处提供的信息高度依赖于 MySQL 配置，包括（但不限于）程序版本、数据库客户端和使用的字符编码。
See http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
见http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
MySQL recognizes the following escape sequences.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.select * from tablename where fields like "%string \"hi\" %";
So you need
所以你需要
select * from tablename where fields like '%string "hi" %';
Although as Bill Karwin notes below, using double quotes for string delimiters isn't standard SQL, so it's good practice to use single quotes. This simplifies things:
尽管正如Bill Karwin 在下面指出的那样，对字符串分隔符使用双引号并不是标准的 SQL，因此使用单引号是一种很好的做法。这简化了事情：
private static final HashMap<String,String> sqlTokens;
private static Pattern sqlTokenPattern;
static
{
//MySQL escape sequences: http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html
String[][] search_regex_replacement = new String[][]
{
//search string search regex sql replacement regex
{ "\u0000" , "\x00" , "\\0" },
{ "'" , "'" , "\\'" },
{ "\"" , "\"" , "\\\"" },
{ "\b" , "\x08" , "\\b" },
{ "\n" , "\n" , "\\n" },
{ "\r" , "\r" , "\\r" },
{ "\t" , "\t" , "\\t" },
{ "\u001A" , "\x1A" , "\\Z" },
{ "\" , "\\" , "\\\\" }
};
sqlTokens = new HashMap<String,String>();
String patternStr = "";
for (String[] srr : search_regex_replacement)
{
sqlTokens.put(srr[0], srr[2]);
patternStr += (patternStr.isEmpty() ? "" : "|") + srr[1];
}
sqlTokenPattern = Pattern.compile('(' + patternStr + ')');
}
public static String escape(String s)
{
Matcher matcher = sqlTokenPattern.matcher(s);
StringBuffer sb = new StringBuffer();
while(matcher.find())
{
matcher.appendReplacement(sb, sqlTokens.get(matcher.group(1)));
}
matcher.appendTail(sb);
return sb.toString();
}
回答by Walid
I've developed my own MySQL escape method in Java (if useful for anyone).
我已经用 Java 开发了我自己的 MySQL 转义方法（如果对任何人有用的话）。
See class code below.
请参阅下面的类代码。
Warning: wrong if NO_BACKSLASH_ESCAPES SQL mode is enabled.
警告：如果启用了 NO_BACKSLASH_ESCAPES SQL 模式，则错误。
select * from tablename where fields like '%string "hi" %';
回答by Bill Karwin
You should use single-quotes for string delimiters. The single-quote is the standard SQL string delimiter, and double-quotes are identifier delimiters (so you can use special words or characters in the names of tables or columns).
您应该对字符串分隔符使用单引号。单引号是标准的 SQL 字符串分隔符，双引号是标识符分隔符（因此您可以在表或列的名称中使用特殊的词或字符）。
In MySQL, double-quotes work (nonstandardly) as a string delimiter by default (unless you set ANSISQL mode). If you ever use another brand of SQL database, you'll benefit from getting into the habit of using quotes standardly.
在 MySQL 中，默认情况下双引号（非标准地）用作字符串分隔符（除非您设置ANSISQL 模式）。如果您曾经使用过其他品牌的 SQL 数据库，您将从养成使用标准引号的习惯中受益。
Another handy benefit of using single-quotes is that the literal double-quote characters within your string don't need to be escaped:
使用单引号的另一个方便的好处是不需要对字符串中的文字双引号字符进行转义：
A “'” inside a string quoted with “'” may be written as “''”.
A “"” inside a string quoted with “"” may be written as “""”.
Precede the quote character by an escape character (“\”).
A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a
回答by Hadi Sadeqi
回答by nickf
You can use mysql_real_escape_string. mysql_real_escape_string()does not escape %and _, so you should escape MySQL wildcards (%and _) separately.
您可以使用mysql_real_escape_string。mysql_real_escape_string()不转义%and _，因此您应该单独转义 MySQL 通配符 ( %and _)。
回答by Raúl Moreno
For strings like that, for me the most comfortable way to do it is doubling the ' or ", as explained in the MySQL manual:
对于这样的字符串，对我来说，最舒适的方法是将 ' 或 " 加倍，如 MySQL 手册中所述：
There are several ways to include quote characters within a string:
A “'” inside a string quoted with “'” may be written as “''”. A “"” inside a string quoted with “"” may be written as “""”. Precede the quote character by an escape character (“\”). A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside aStrings quoted with “'” need no special treatment.
有几种方法可以在字符串中包含引号字符：
update table TableName replace(Dstring, "QQ", "\"")用“'”引用的字符串不需要特殊处理。
It is from http://dev.mysql.com/doc/refman/5.0/en/string-literals.html.
它来自http://dev.mysql.com/doc/refman/5.0/en/string-literals.html。
回答by Vishnu Prasanth G
For testing how to insert the double quotes in MySQL using the terminal, you can use the following way:
为了测试如何使用终端在MySQL中插入双引号，您可以使用以下方式：
TableName(Name,DString) - > Schema
insert into TableName values("Name","My QQDoubleQuotedStringQQ")
TableName(Name,DString) -> Schema
插入到 TableName values("Name","My QQDoubleQuotedStringQQ")
After inserting the value you can update the value in the database with double quotes or single quotes:
插入值后，您可以使用双引号或单引号更新数据库中的值：
$char = "and way's 'hihi'";
$myvar = mysql_real_escape_string($char);
select * from tablename where fields like "%string $myvar %";
回答by YeHtunZ
If you're using a variable when searching in a string, mysql_real_escape_string()is good for you. Just my suggestion:
如果您在搜索字符串时使用变量，mysql_real_escape_string()这对您有好处。只是我的建议：
