Pfthb

I have table with this conent

ID|name
----------------
1 |My Shop No. 6

(Don't ask why the six is within the backslashes, I have no idea, I just have to work with it)

I try to select this specifc value from my table.
My query looks like this:

$sql = "SELECT `id` FROM shops WHERE `name` = :name";
$params = array(':name' => "My Shop No. 6");
$this->fetchOne($sql, $params); // the binding happens in my mysql class

This query does not return any data, it's stating that there is no entry.
When I monitor my SQL statements with the Neor Profiler SQL, I can see that the database is receiving this query:

SELECT `id` FROM shops WHERE `name` = 'My Shop No. \6\'

When I use this query directly in PHPMyAdmin, it also does not return any results.

How can I deal with this kind of case?

There is absolutly no option to change the data in the database.

Many products (PHP, MySQL, etc) use backslash as the escape character.

By itself, it is usually consumed by doing something to the character after it. For example, atb gives you the letters a and b separated by a tab (the usual interpretation of t).

Doubling up the backslash \xyz usually is intepreted thus: The first backslash escapes the second, so you get xyz.

If your string goes through multiple layers of code (eg, PDO plus MySQL), you might need twice as many backslashes, since each layer will turn \ into .

Another diagnostic tool is SELECT HEX(col) ... to see what was stored in the database. Examples:

610962 atb -- a=61, tab=09, b=62
615C7462 a\tb -- a=61, =5C, t=74, b=62 

Back to your code:

$params = array(':name' => "My Shop No. 6");

6 is probably treated as just "6" (not everything has some escaped meaning)
" possibly caused a syntax error. Normally this is now to get a " inside a string

'My Shop No. \6\'

Now the pairs of backslashes are turned into single backslashes, then the string is handed to whoever is next.

(In these contexts, single- and double-quotes work the same.)