Comparing strings in MySQL that contain apostrophes

Sanity check required please! I have text strings in a MySQL table which contain apostrophes, but aren’t escaped. I want to match the string using a LIKE comparison, but, of course, any string used in a MySQL statement that contains an apostrophe has to be escaped. As a result, MySQL is not finding a match.

For example, SELECT id FROM table WHERE field LIKE ‘%Isn\’t this fun?%’ doesn’t match the row with the string ‘Isn’t this fun?’ in the field. It’s obviously because of the escaped apostrophe, but how do I get around this?...

Posted 3 years, 1 month ago

This might help you - http://www.weberdev.com/Manuals/MySQL3.X_4.X/functions.html. Unfortunately I don’t have a MySQL DB setup to test it myself.

Excerpt: 12.3.1. String Comparison Functions

Note: Because MySQL uses C escape syntax in strings (for example, ‘n’ to represent a newline character), you must double any ‘’ that you use in LIKE strings. For example, to search for ‘n’, specify it as ‘\n’. To search for ‘’, specify it as ‘\\’; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Reid · www · 3 years, 1 month ago

Thanks Reid. The 3 slashes in front seems to work.
A little str_replace does the trick -
$string = str_replace("'", "\\'", $string);

Phil · www · 3 years, 1 month ago

Aaagh! My comments form has stripped out some slashes from my last comment. I’m off to lie down in a dark room now.

Phil · www · 3 years, 1 month ago

Off topic I know, but i’m looking at this site in high resolution and its pretty much the most gorgeous, simple, functional site i’ve ever seen.

Damn you! When are you redesigning so I can steal the CSS? ;)

Steve Woods · www · 3 years, 1 month ago

Thanks Steve - kind words indeed. Bearing in mind the frequency of my posts, the chances of me re-designing anytime soon are pretty slim ;-)

Phil · www · 3 years, 1 month ago

Commenting on this post has been disabled.

My name is Phil Lindsay, a web guy from Scotland, now living in Newcastle upon Tyne in England. I also run a web design company called Presence. You can email me at phil@xlab.co.uk, read more ramblings on my Twitter stream and view my photos on Flickr. You can grab the RSS feed for this weblog here.