Search returns no rows in mysql query using LIKE and values having "\"

Question

I have some problem regarding the search in mysql. Below is my query.

SELECT * FROM table WHERE name LIKE "%admin\'s%";

When i am executing this query it will return zero data.

actually i have "admin\'s" stored in db. this "\" is to prevent sql injection. i have used mysql_real_escape_string to prevent the sql injection.

but when i use three times addslashes to my variable it works.

So my below query is working.

SELECT * FROM table WHERE name LIKE "%admin\\\\\\\'s%";

My above query will return the data with name like admin's.

I am not getting where i am wrong.

Answer 1

well for one you shouldnt have data like this in your DB admin\'s .. most likely you double escaped your string ( check if you don't have magic_quotes enabled on your server ).

If you only do

INSERT ... username = "admin\'s";

you will have in your db the username value admin's so my recomandation would be to go ahead and remove the slashes from your database and then your first query should work.

• Avinash: no i dont have magic_quote enabled. I am using mysql_real_escape_string to prevent sql injection. Whats wrong with this?
• Gabriel Solomon: nothing wrong with that .. but the data in mysql database should'nt be saved with slashes .. even if it was escaped. Escaping is only for the query to work.