How do I escape special characters in MySQL?

Question

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

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

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

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