MySQL SELECT * WHERE name = "something with a backslash \"

Question

I have a table

table_id | name   | amount
1        | Arby\'s| 12
2        | Wendy's| 8

I usually do a

SELECT * WHERE table_id = whatever

But I would like (instead) do:

SELECT * WHERE name = "Arby\'s";

However, I seem to be running into problems with the backslash. The result isn't showing up at all. I've also tried

SELECT * WHERE name = 'Arby's;

Without any luck.

Is there any way to search by name if the name contains apostrophes or other special characters (ampersands etc?)

`SELECT * WHERE name = 'Arby\'s';` - you're missing a closing `'` — DaveRandom, Aug 22 '11 at 17:23
Sorry that was more of a typo in my question...just edited it. — redconservatory, Aug 22 '11 at 17:30
You probably shouldn't have the backslash in your data to begin with. — Jacob, Aug 22 '11 at 17:30
By that, do you mean I should stripslashes before I do an insert? I'm using php. — redconservatory, Aug 22 '11 at 17:38

score 2 · Answer 1 · answered Aug 22 '11 at 17:24

2

SELECT * FROM table WHERE name = 'Arby\\\'s'

Escape the backslash with a backslash and escape the apostrophe with another backslash leads to 3 backslashes.

answered Aug 22 '11 at 17:24

Jacob

41,721
6
79
81

score 1 · Accepted Answer · edited May 23 '17 at 11:47

1

If you're on PHP, you can use mysql_real_escape_string()

You might also want to look at this How do I escape special characters in MySQL?

edited May 23 '17 at 11:47

Community

1
1

answered Aug 22 '11 at 17:22

Kemal Fadillah

9,760
3
45
63

["`Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. `"](http://php.net/mysql_real_escape_string) – Michel Ayres Apr 24 '14 at 18:29

DaveRandom · Answer 3 · 2011-08-22T17:34:13.063

1

 SELECT * WHERE name = 'Arby\'s';

You're missing a closing ' on the end of your string. That will match Arby's. If you wanted to match Arby\'s (which I don't think you do) it would be:

 SELECT * WHERE name = 'Arby\\\'s';

Back slashes need to be 'double escaped', as it were, because they are the escape character. So to match \\ your query needs to be \\\\. When they immediately precede another character that needs to be escaped, you end up with another one - so to match \\' you need \\\\\'

I suggest you read this.

edited Aug 22 '11 at 17:34

answered Aug 22 '11 at 17:24

DaveRandom

87,921
11
154
174

Well, he has the backslash in his sample data, so I guess he wants to match that, too. – Jacob Aug 22 '11 at 17:29
I know it's confusing, but I interpret that as an attempt at escaping the single quote but leaving off the string-closing quote. – DaveRandom Aug 22 '11 at 17:30

MySQL SELECT * WHERE name = "something with a backslash \"

3 Answers3