How do I escape special characters in MySQL?

Question

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

How do I build this query?

This may be dupe hammered into *[How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174)*... (I am not saying it should or is correct to do - just what may happen.) — Peter Mortensen, Jul 15 '19 at 15:30

score 132 · Answer 1 · edited Jun 20 '20 at 09:12

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.

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:

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

@PeterMortensen Because you can't rely on it entirely to sanitize untrusted input. That relies on you escaping everything the server interprets differently, and as the remark says, depends on configuration, versions and encodings used. A new version can easily introduce something new that you didn't consider, or you may easily forget some characters or attackers can find some edge cases. Finally, escapng drives you away from the good practice: use parameters for everything untrusted, which are guarranted to work as expected by the DB engine. — Alejandro, Aug 24 '20 at 18:13

score 38 · Answer 2 · answered Jun 25 '11 at 15:11

I've developed my own MySQL escape method in Java (if useful for anyone).

See class code below.

Warning: wrong if NO_BACKSLASH_ESCAPES SQL mode is enabled.

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

Used your search replace table in a mysql procedure where I need to escape a tab delimiter to be correctly inserted in the db like '\t'. thx — Erik Čerpnjak, Feb 20 '17 at 13:28

score 27 · Answer 3 · answered May 19 '09 at 06:34

27

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).

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.

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" %';

answered May 19 '09 at 06:34

Bill Karwin

538,548
86
673
828

I concur with the single-quote idea because if you switch to, say, PostgresSQL it will stubbornly refuse your queries and you must use single quotes. It is a good practice. – kovacsbv Jun 10 '16 at 15:25
That handles the example, but what about the more general case? E.g. for a backslash, `\\`. – Peter Mortensen Dec 12 '19 at 11:17
@PeterMortensen Use a double-backslash to get one literal backslash character. This is the same it is in virtually every programming language. See also: https://dev.mysql.com/doc/refman/8.0/en/string-literals.html – Bill Karwin Dec 12 '19 at 17:32

Hadi Sadeqi · Answer 4 · 2021-12-08T09:46:57.410

17

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

edited Dec 08 '21 at 09:46

answered Nov 09 '13 at 14:08

Hadi Sadeqi

196
1
5

1

Except it doesn't quote `LIKE`'s `%` character, so it is pretty useless – Peter V. Mørch May 21 '19 at 13:59
2

Also pay attention, that `QUOTE` adds surrounding single qoutes for string values – FlameStorm Nov 22 '19 at 13:06

score 9 · Answer 5 · edited Mar 18 '16 at 17:38

For strings like that, for me the most comfortable way to do it is doubling the ' or ", as explained in the MySQL manual:

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.

score 8 · Answer 6 · edited Mar 18 '16 at 17:37

8

You can use mysql_real_escape_string. mysql_real_escape_string() does not escape % and _, so you should escape MySQL wildcards (% and _) separately.

edited Mar 18 '16 at 17:37

Peter Mortensen

30,738
21
105
131

answered May 19 '09 at 06:21

nickf

537,072
198
649
721

4

[Function *mysql_real_escape_string()* was deprecated in PHP 5.5.0 and was removed in PHP 7.0.0](https://www.php.net/manual/en/function.mysql-real-escape-string.php). – Peter Mortensen Jul 15 '19 at 15:36
You could only use this if you are using PHP. For any other application, this answer is pretty useless – Nico Haase Sep 09 '22 at 11:08

score 1 · Answer 7 · edited Jul 15 '19 at 15:39

1

For testing how to insert the double quotes in MySQL using the terminal, you can use the following way:

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

After inserting the value you can update the value in the database with double quotes or single quotes:

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

edited Jul 15 '19 at 15:39

Peter Mortensen

30,738
21
105
131

answered Aug 18 '16 at 06:37

Vishnu Prasanth G

1,133
12
12

score 0 · Answer 8 · edited Mar 18 '16 at 17:40

0

If you're using a variable when searching in a string, mysql_real_escape_string() is good for you. Just my suggestion:

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

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

edited Mar 18 '16 at 17:40

Peter Mortensen

30,738
21
105
131

answered Dec 23 '15 at 08:35

Ye Htun Z

2,079
4
20
31

6

This function is depreciated in PHP 5.5 and removed from 7.0 – Phil M Dec 27 '16 at 18:05

How do I escape special characters in MySQL?

8 Answers8

Linked

Related