CODEWITHSUNDEEP
mysqlescaping
Industrial Themes
Industrial Themes

Reputation: 567

How do I select a value with an escaped apostrophe in MySql?

I have the following value in my table:

When I Don\'t Know What To Do

Notice the apostrophe is already escaped. Now I want to select this value from this table using the following SQL string:

SELECT * FROM wp_wpsc_productmeta WHERE meta_value = 'When I Don\'t Know What To Do'

I am getting 0 rows returned. When I change the SQL string to this:

SELECT * FROM wp_wpsc_productmeta WHERE meta_value LIKE 'When I Don%'

I get my result set correctly. So, my question is, how do I write the SQL string so that I can select a value from a table when that value has an already-escaped apostrophe in it?

Please do not answer that I should use parameters. I'd very much like to figure out the answer using the method that I'm trying. Thanks for your help!

Upvotes: 1

Views: 2392

Answers (3)

Evanesco
Evanesco

Reputation: 897

You'll need to escape both the backslash and the quote:

'I don\\\'t know';

Why?

The problem is that the slash and the quote are special characters in your programming language as well. So when you're typing:

'I don\'t know';

What the database sees is 'I don't know', which does not exist. If on the other hand you double-escape your string like so:

'I don\\\'t know';

...the result sent to the database is 'I don\'t know', which is what you want.

Upvotes: 2

gub
gub

Reputation: 5239

What language are you using? I think the language is probably interpreting the escape backslash as an escape for its benefit, so you may need to do it twice to ensure the database sees it too:

'I don\\\'t know';

Upvotes: 1

anothershrubery
anothershrubery

Reputation: 21023

If the string in the database is actually "When I Don\'t Know What To Do", with the backslash included then to search for it you would need to use the statement.

SELECT * FROM wp_wpsc_productmeta WHERE meta_value = 'When I Don\\''t Know What To Do'
Or
SELECT * FROM wp_wpsc_productmeta WHERE meta_value = 'When I Don\\\'t Know What To Do'

Upvotes: 3

Related Questions