how to escape special character in mysql

Question

eg: 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

please help me how to build this query

Answer 1

See http://dev.mysql.com/doc/refman/5.0/en/string-syntax.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" %';

Answer 2

+1 to Paul's answer. If you are building this string in PHP, you could use the function mysql_real_escape_string() which will escape this and other characters for you.

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

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