mysql search string contains apostrophe

[JackAlbright]

I'm having a hard time creating a query that searches for database content that contains single quotes, or apostrophe characters.

I use mysql_real_escape_string on the data prior to putting it into the db. Then I have a search form where the user can enter a word or phrase.

If the user searches for "Mayor's" it is not found because the apostrophe is escaped in the db. (When I look directly in the database using phpMyAdmin, I can see that the value is Mayor\'s)

So, the question in a nutshell is: what's the best way to allow a user to search the database for values that contain an apostrophe, or a quotation mark, or any other character that would have been escaped by mysql_real_escape_string?

Incidentally, the user's search string also is cleaned with mysql_real_escape_string, just to further complicate things.

Thanks,
Jack

[guido2004]

Using mysql_real_escape_string is the correct thing to do.
If you see an escaped ' in the database, then maybe magic_quotes is 'on' on your server? Did you check the value of the data prior to passing it through mysql_real_escape_string?

Did you try your query in phpMyAdmin?

[JackAlbright]

Yes, Magic Quotes are ON.

So, if Magic Quotes are not ON, then the apostrophe should not appear to be escaped in the database?

What's the recommendation here? Turn off magic quotes? What are the possible repercussions?

Thanks

[JackAlbright]

did I check the value in phpMyAdmin? Yes I did, and the apostrophe is escaped in the database

did I check the value prior to passing it through mysql_real_escape_string? Yes, this has been a controlled experiment.

The input value was "mayor's" (without double-quotes)

The search string was mayor's

The query failed to yield a result.

Thanks

[guido2004]

What's the recommendation here? Turn off magic quotes? What are the possible repercussions?

From the manual

Warning

This feature has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.

It's preferred to code with magic quotes off and to instead escape the data at runtime, as needed.

There are no repercussions. You always have to sanitize user input before using it in a query anyways.