A very odd Apostrophes MySQL PHP error

Question

Hi Guys, another brain teaser.

This isn't the usual "but Apostrophes are breaking my Queries" as I know all about escaping and sanitising so I'll get right too it.

Many many things are stored in a db with names like "Assassin's" and "Babe's" which end up "Assassin\'s" and "Babe\'s", recovering is good but searching via text is... painful.

LIKE "%Babe\'s%" has no results, "%Babe\\'s" has no results, "%Babe\''s" has no results.

BUT if I go to the server directly then they will all produce results.

In other words, the SAME query totally unedited will work directly in the MySQL engine but sent via php's mysql api it produces no matched results.

Any ideas what could cause this? (I've checked 100 times the amount of slashes etc, is there a character set issue?"

Many many many many thanks in advance.

edit:

I think i better make myself more clear:

"SELECT title FROM games WHERE title LIKE "%assassin\\\'s%"; (Since SQL should escape the apostrophe and one of the slashes will turn into the slash that was stored, since we're looking for "Assassin\'s Creed" in this example)

edit2: Sow we've figured out this is caused by having escaped escapes in the actual db caused by poor sanitising. Currently I'm in the process of trying to cleanup the db and the input method.

edit3: It seems like magic quotes were on somehow... I swear that was turned off! However it wasn't just that. The DB wrapper which is in place for this site has a clean event and also a pre-clean which caused the issue. That has been fixed now and now I'm running a script to (hopefully) cleanse the db...

Answer 1

Did you try to use mysql_real_escape_string, also check if magic_quotes is enabled and messing with your strings

Answer 2

When you escape an apostrophe so that you can store it in MySQL, the back slash isn't stored.

So try this:

SELECT title FROM games WHERE title LIKE "%assassin\'s%";

Instead of:

SELECT title FROM games WHERE title LIKE "%assassin\\\'s%";

Answer 3

SELECT title FROM games WHERE title REGEXP "assassin\'s";

Think of REGEXP as a more powerful, less picky "LIKE" in MySql....Not the be-all, end-all, but another cool tool in the shop.

Answer 4

I would seriously consider fixing the data in your database.

Now, having said that, MySQL recognizes both \' and '' as an escaped apostrophe. That is, unless the server mode is set to use strict SQL, in which case only '' is recognized.

The data is most certainly getting double-escaped on entry, most likely once when the user enters it (by magic_quotes_gpc, which uses addslashes) and again by mysql_real_escape_string when you call it.

This would turn Assassin's Creed into Assassin\\\'s Creed, which would ultimately get stored as Assassin\'s Creed.

I highly recommend disabling magic_quotes_gpc if you can, as it causes more problems than it fixes.

Answer 5

As documentation says, slashes in LIKE and REGEXP clauses must be doubled

Edit: wrong link corrected