How to Search for Slash (\) in MySQL and Why Escaping (\) Not Required for Where (=) But for Like Is Required

How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?

\ functions as an escape character in LIKE by default.

From the manual for LIKE:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

You can change this by specifying another escape character, as in:

SELECT * FROM `titles` WHERE title LIKE 'test\\' ESCAPE '|'

PHP mysqli query searching for backslashes

It's because php escape character is backslash, you need to escape them too like.

$mysqli->query('SELECT id, name FROM `posts` WHERE content LIKE \'%[insert=\\\\"userform\\\\",id=\\\\"1\\\\"]%\' ESCAPE "|"');

A good solution to check it's to echo the query before execute to check if the final result is good.

MySQL returning nothing on a very simple query

what about:

select * from groups_pivot
where model = 'App\\Models\\User';

Backslash is often an escape character and thus requires to be escaped itself to be used.

Incomprehensible behavior: MySQL not matching string. String is completely clean and hex-verified. Still no matches

Solved! The database contents were correct: the file name didn't contain any strange characters.

The query however wasn't correct. That query pulled the file name from the file being read, and that part of the code apparently did not strip all characters. This explains the inconsistent results.

When I wrote the actual queries to a separate text file and analyzed them in 010 Editor, this is what I got:

Apparently the code neatly stripped these before putting the file names in the database, but for this particular query, the file name wasn't stripped. I.e. the database was correct, but this particular query wasn't.

I solved this by using the following before querying any file names, ever, in the database:

function escapeFileStringToSQL($string)
{
    $string = str_replace('\\', '\\\\', $string);
    $string = str_replace("'", "\'", $string);
    $string = str_replace('"', '\"', $string);
    // Never ever search the database for file names containing non-breaking spaces, as these have been stripped in the database
    $string = preg_replace("/(?:\xc2\xa0)/", "\x20", $string);
    return $string;
}

I wish I could vote for everyone who commented, but unless I'm mistaken, you can only vote for actual Answers. If I'm wrong, please let me know and I will correct. Thank you all very much for taking you time to look into this.

Everybody thought about the database, but no one thought about the query itself. PhPMyAdmin not rendering non-breaking spaces differently didn't really help either. Maybe some of the info in the original question was wrong, which may have been misleading... then again, once you start making mistakes during the debugging itself, thinks become very complicated.

MySQL LIKE Operator with Special Characters Confusion

Maybe this help you understand the usage of escape chars in mySQL

https://stackoverflow.com/a/27061961/634698

Why doesn't Like match a query when equal does?

I think the reason is because you need to slash every slash when writing an expression try something like this

SELECT * FROM images WHERE originalpath Like "d:\\\\pic16\\\\160623 bugs\\\\RW2-jpg\\\\P1280627.jpg";

While writing an expression then because certain characters can have a different meaning they need to be escaped in order to show that they are literal.

---

Related Topics