Im having a small problem with storage of special characters like quotes, double quotes and ampersands. I put every POST request through mysql_real_escape_string(), and when I add a string like "That '70s Show" it gets stored as "That '70s Show" in the mysql DB. When I echo it out, it works fine... but when I try to run a % $string % search for "That '70s Show", it will not find the record. I have magic_quotes disabled.
How can I get around this?
It looks like the problem is that you're not just running the incoming data that you store in the database through mysql_real_escape_string(), but also htmlentities() or a relative. Is that the case? If so, quit. :)
Quoted from Mysql reference doc
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 string quoted with “'” needs no special treatment.
The following SELECT statements demonstrate how quoting and escaping work:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
Refer : http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html