如何在MySQL中转义特殊字符？

Question

例如：

select * from tablename where fields like "%string "hi"  %";

错误：

您的SQL语法有误；请查阅与您的MySQL服务器版本相对应的手册，以获取正确使用语法的信息 near 'hi" "' at line 1

我该如何构建这个查询？

- rajaneesh

---

这可能会被重复锤入 如何在PHP中防止SQL注入？...（我不是说这样做是正确的，只是可能会发生。） - Peter Mortensen

Answer 1

本答案提供的信息可能会导致不安全的编程实践。

此处提供的信息高度依赖于MySQL配置，包括（但不限于）程序版本、数据库客户端和使用的字符编码。

请参见http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

MySQL识别以下转义序列。
\0     ASCII NUL（0x00）字符。
\'     单引号（“'”）字符。
\"     双引号（“"”）字符。
\b     退格字符。
\n     换行符（LF）。
\r     回车字符。
\t     制表符。
\Z     ASCII 26（Control-Z）。请参见表格后面的注释。
\\     反斜杠（“\”）字符。
\%     “%”字符。请参见表格后面的注释。
\_     “_”字符。请参见表格后面的注释。

所以你需要：

select * from tablename where fields like "%string \"hi\" %";

虽然正如Bill Karwin在下面指出的那样，使用双引号作为字符串分隔符并不是标准SQL，因此最好使用单引号。这样可以简化事情:

select * from tablename where fields like '%string "hi" %';

- Paul Dixon

---

为什么转义字符是不安全的？转义字符在计算机编程中用于将特殊字符转换为文本字符串中的普通字符。但是，如果恶意用户能够控制输入并使用转义字符来执行攻击，则可能导致安全漏洞。因此，转义字符被认为是不安全的。 - Peter Mortensen

因为您不能完全依赖它来清理不受信任的输入。这取决于您转义服务器以不同方式解释的所有内容，正如备注所说，这取决于使用的配置、版本和编码。新版本很容易引入一些您没有考虑到的新内容，或者您可能会忘记一些字符，攻击者可以找到一些边缘情况。最后，转义使您远离良好的实践：对于所有不受信任的内容，请使用参数，这些参数由DB引擎保证按预期工作。 - Alejandro

Answer 2

我已经在Java中开发了自己的MySQL转义方法（如果对任何人有用）。

请参见下面的类代码。

警告：如果启用了NO_BACKSLASH_ESCAPES 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();
}

- Walid

---

在MySQL存储过程中使用了搜索替换表，需要转义制表符才能正确插入数据库，例如'\t'。谢谢。 - Erik Čerpnjak

你有C#版本吗？ - Hien Nguyen

Answer 3

在字符串分隔符中应该使用单引号。 单引号是标准的 SQL 字符串分隔符，而双引号是标识符分隔符（因此您可以在表或列名称中使用特殊单词或字符）。

在MySQL中，默认情况下双引号作为字符串分隔符工作（非标准方式），除非您设置了 ANSI SQL 模式。 如果您曾经使用过其他品牌的 SQL 数据库，那么养成使用标准引号的习惯将对您有益。

使用单引号的另一个便捷之处在于您字符串内的双引号字符不需要转义：

select * from tablename where fields like '%string "hi" %';

- Bill Karwin

---

我同意使用单引号的想法，因为如果你切换到例如PostgresSQL，它会固执地拒绝你的查询，你必须使用单引号。这是一个好习惯。 - kovacsbv

这处理了例子，但更一般的情况呢？例如反斜杠 \\。 - Peter Mortensen

@PeterMortensen 使用双反斜杠来获取一个字面上的反斜杠字符。这在几乎所有编程语言中都是相同的。另请参阅：https://dev.mysql.com/doc/refman/8.0/en/string-literals.html - Bill Karwin

Answer 4

MySQL拥有字符串函数QUOTE，它应该可以解决这个问题。

- Hadi Sadeqi

---

除了它不引用LIKE的％字符，所以它几乎没什么用处。 - Peter V. Mørch

还要注意，QUOTE会为字符串值添加单引号。 - FlameStorm

Answer 5

对于这样的字符串，我个人觉得最舒适的方式是使用两个相同的 ' 或 "，如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 a

Strings quoted with “'” need no special treatment.

来自于 http://dev.mysql.com/doc/refman/5.0/en/string-literals.html。

- Raúl Moreno

Answer 6

您可以使用 mysql_real_escape_string 函数。mysql_real_escape_string() 函数不会转义 % 和 _，所以您需要单独转义 MySQL 通配符（% 和 _）。

- nickf

---

函数*mysql_real_escape_string()*在PHP 5.5.0中被弃用，在PHP 7.0.0中被移除。 - Peter Mortensen

如果您使用的是PHP，那么您才能使用它。对于任何其他应用程序，这个答案都没有什么用处。 - Nico Haase

Answer 7

为了测试如何在MySQL终端中插入双引号，您可以使用以下方法：

TableName（Name，DString） -> Schema
insert into TableName values("Name","My QQDoubleQuotedStringQQ")

插入值后，您可以使用双引号或单引号更新数据库中的值：

update table TableName replace(Dstring, "QQ", "\"")

- Vishnu Prasanth G

Answer 8

如果您在字符串搜索中使用变量，则mysql_real_escape_string（）非常适合您。只是我的建议：

$char = "and way's 'hihi'";
$myvar = mysql_real_escape_string($char);

select * from tablename where fields like "%string $myvar  %";

- Ye Htun Z

---

该函数在 PHP 5.5 中已被弃用，并在 7.0 版本中删除。 - Phil M