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

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?

如何构建此查询?

9 个解决方案

#1

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

MySQL recognizes the following escape sequences.\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.

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,因此使用单引号是一种好习惯。这简化了事情:

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

#2

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模式,则会出错。

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();}

#3

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 ANSI SQL mode). If you ever use another brand of SQL database, you'll benefit from getting into the habit of using quotes standardly.

在MySQL中,默认情况下双引号(非标准地)作为字符串分隔符工作(除非您设置ANSI SQL模式)。如果您使用其他品牌的SQL数据库,您将受益于养成使用标准报价的习惯。

Another handy benefit of using single-quotes is that the literal double-quote characters within your string don't need to be escaped:

使用单引号的另一个便利好处是,字符串中的文字双引号字符不需要转义:

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

#4

MySQL has the string function QUOTE, and it should solve this problem:

MySQL有字符串函数QUOTE,它应该解决这个问题:

#5

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()不会转义%和_,所以你应该分别转义MySQL通配符(%和_)。

#6

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 a

Strings quoted with “'” need no special treatment.

用“'”引用的字符串不需要特殊处理。

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。

#7

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()对你有好处。只是我的建议:

$char = "and way's 'hihi'";$myvar = mysql_real_escape_string($char);select * from tablename where fields like "%string $myvar  %";

#8

For testing how to insert the double quotes in MYSQL using Terminal you can use following way.

为了测试如何使用Terminal在MYSQL中插入双引号,您可以使用以下方式。

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

TableName(Name,DString) - > Schema插入TableName值(“Name”,“My QQDoubleQuotedStringQQ”)

after inserting the value you can update the value in the db with double quotes or single quotes

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

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

更新表TableName替换(Dstring,“QQ”,“\”“)

#9

if you are using php then you can escape special characters as below:

如果您使用的是php,那么您可以转义特殊字符,如下所示:

since mysql_real_escape_string() is deprecated in PHP 5.5 and removed from PHP 7.0 , i recommend to you it's equivalent in mysqli mysqli_real_escape_string().

因为在PHP 5.5中不推荐使用mysql_real_escape_string()并从PHP 7.0中删除,所以我建议你在mysqli mysqli_real_escape_string()中使用它。

Syntax:

mysqli_real_escape_string(connection,escapestring);

Example:

$con=mysqli_connect("localhost","my_user","my_password","my_db");// Check connectionif (mysqli_connect_errno()) {  echo "Failed to connect to MySQL: " . mysqli_connect_error();}// escape variables for security$your_string = 'string "hi" ';$escaped_string = mysqli_real_escape_string($con, $your_string);$sql = 'select * from tablename where fields like "%'.$escaped_string. '%" ';$result = $conn->query($sql);//here you can iterate over result array for displaying result

you can also use addslashes() which Returns a string with backslashes added before characters like single quote ('), double quote ("), backslash (), NUL (the NUL byte) that need to be escaped, but it has vulnerabilities to sql injections see the answer of this question Examples of SQL Injections through addslashes(), so better to use mysqli_real_escape_string() function.

你也可以使用addslashes()返回一个带有反斜杠的字符串,在单引号('),双引号(“),反斜杠(),NUL(NUL字节)等字符之前添加反斜杠,但是它有漏洞sql注入看到这个问题的答案通过addslashes()进行SQL注入的例子,所以最好使用mysqli_real_escape_string()函数。

Or if you want to escape characters for regular expressions then you can use preg_quote ( string $str [, string $delimiter = NULL ] ), which puts a backslash in front of every character that is part of the regular expression syntax. regular expression characters are: . \ + * ? [ ^ ] $ ( ) { } = ! < > | : -

或者,如果要转义正则表达式的字符,则可以使用preg_quote(string $ str [,string $ delimiter = NULL]),它会在作为正则表达式语法一部分的每个字符前放置一个反斜杠。正则表达式字符是:。 \ + *? [^] $(){} =! <> | : -

But be careful preg_quote() will not escape single(') or double quote(").

但要小心preg_quote()不会转义单(')或双引号(“)。