[mysql] How do I escape special characters in 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

How do I build this query?

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

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

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

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

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

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.

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

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", "\"")