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 5 years 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 · 5 years ago
Thanks Reid. The 3 slashes in front seems to work.
A little str_replace does the trick -
$string = str_replace("'", "\\'", $string);
Phil · www · 5 years 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 · 5 years 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 · 5 years 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 · 5 years ago