Querying mySQL data containing apostrophes 1

[cmayo]

Apologies for the cross-post, but it seems this thread is better asked here than in the MySQL forum where it began.

My client's database has been escaping character fields using PHP's addslashes(), resulting in records with fields containing values like "O\'Henry"

Querying for that data is problematic as those fields aren't returned by LIKE "O'Henry%" or escaped, "O\\\'Henry%"

Hand-munging the escaping, I find that escaping the embedded backslash instead of the apostrophe, i.e. LIKE "O\\\\'Henry%" will fetch the desired records, as will escaping both the backslash and the apostrophe with LIKE "O\\\\\\\'Henry%"

So my problem, I guess, is how to have PHP do that munging on user-entered search strings, i.e. when the user enters "O'Henry" into a form field. If I use addslashes() or mysql_real_escape_string() on the user text, i.e.

name like '".trim(addslashes($_POST['name']))."%'

the resulting query will read "LIKE O\\\'Henry%" and fail to find the records. If I double-escape the search string, i.e.

name like '".trim(addslashes(addslashes($_POST['name'])))."%'

I'll get a query containing

name like 'Bob\\\\\\\'s Burgers%'

which does find the records, but that's really ugly, and there's no telling how many other queries such awkward double-escaping would break.

I do realize that the data has been improperly written to the database and that the apostrophes shouldn't have been escaped with backslashes, but again, I'm working with an existing database behind an existing CRM front end, and fixing the data and all the app's queries would be so involved that some kind of PHP workaround would be much preferable.

Any ideas?

 

[jpadie]

ok. i'll take a look later.

 

[jpadie]

this bit of $pattern

[a-z`]

should have the underscore included in the class

[a-z`_]

and i'm a bit confused about why you are setting the enging charset to latin and the client to utf-8