Take the 2-minute tour ×

I use mysql_real_escape_string on stuff that gets put in my database, so something like Bob's Shop would turn into Bob\'s Shop. And, for example, if I try to select all the entries that contain Bob\'s, my select statement looks like:

SELECT * FROM tbl WHERE name LIKE '%Bob\\\'s%' 

and for whatever reason, it doesn't work, even though it makes complete sense that it would work.

share|improve this question
mysql_real_escape_string() escapes quotes to be inserted by the API but doesn't store them escaped. When you look in the database, you should not see escaped quotes!. Should be possible to just do '%Bob\'s%' –  Michael Berkowski Jul 28 '12 at 13:45
If you do see escaped quotes in your data, it is possible that you are also calling something like addslashes() in addition to mysql_real_escape_string(), or you have magic quotes turned on in PHP. –  Michael Berkowski Jul 28 '12 at 13:47
Turn magic quotes off? –  Esailija Jul 28 '12 at 13:50
Check get_magic_quotes_gpc() You should have magic quotes turned off... –  Michael Berkowski Jul 28 '12 at 13:52
@user1330341 You will need to go in then and fix all your existing data to get rid of the extra backslashes, I'm afraid. magic_quotes_gpc was a terrible idea in PHP, and unfortunately people are still paying for it many years after it was realized to be folly. –  Michael Berkowski Jul 28 '12 at 13:59

3 Answers 3

The value entered in DB wouldn't include the backslash if that apostrophe is escaped by the mysql_real_escape_string function, it is for security purposes only. So the final value in your DB should be Bob's. It's like escaping the same quote inside a string wrapped within quotes, like "double \"quotes\"" and 'single \'quotes\''.

Did you try querying %Bob\'s% as is? And what is the datatype of the name column? I can not replicate your issue, it's working with both your and my examples.

share|improve this answer
up vote 0 down vote accepted

I fixed it (thanks to Michael for showing me how). I'm posting what I did in case someone else has this problem.

Never figured out how to properly put escape characters in a LIKE, but the problem was that I had magic quotes turned on, so whenever I used mysql_real_escape_string, it added additional escape characters so that instead of being stored as Bob's, it was stored as Bob\'s. This is the documentation to disable magic quotes: http://www.php.net/manual/en/security.magicquotes.disabling.php For me, putting php_flag magic_quotes_gpc Off or php_value magic_quotes_gpc Off in the .htaccess didn't work because I'm using purchased hosting and the host doesn't allow settings like that or something. So instead I put magic_quotes_gpc = Off in a file called php.ini and put it in every directory that I needed it.

Since I already had a lot of rows in my MySQL database that contained the extra backslashes, I needed to get rid of them when I turned off magic quotes. You can do this easily by issuing a query like this: UPDATE tbl SET col=REPLACE(col,'\\\'','\'') WHERE col LIKE '%\'%' Replace tbl with your table name and col with any column you're working with. If there's other escape characters you need to fix, just change the REPLACE and LIKE to reflect that. There's probably better code to do all columns and all escape characters in one swoop, but for my purposes I didn't need that.

share|improve this answer

Hi I think this can help you for mySQL and Java ,

In Java

char quotes ='"';

String sql =" SELECT * FROM tb1 where name LIKE "+quotes+"%Bob's%"+quotes+" ;";

mySQL Query

SELECT * FROM tbl WHERE name LIKE "%Bob's%" 
share|improve this answer

Your Answer


