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?

Answer 1

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.

Answer 2

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