Don't understand the search behavior of MySQL backslash

user_19186312 0
Viewed 31

I am trying to search for backslashes and percentages in a select statement. I understand that I need six backslashes to search the following records

id name
1 \ 
select * from test_table where name like '%\\\\\\%'

However, to search for the following records requires six backslashes and four backslashes, not six and six.

id name
2 \a\ 
select * from test_table where name like '%\\\\\\a\\\\%';
-- select * from test_table where name like '%\\\\\\a\\\\\\%'; ←unsearchable

What is the reason that the number of backslashes to escape depends on their position?

  • MySQL: 5.7.34
  • table/column collation: utf8_unicode_ci
escaping mysql
1 Answers

First of all, your first example does not look correct (more on that later). You don't get any match with 6 slashes. You need as few as 3 slashes (more on that later too), as illustrated here.

with test_table (name) as (
    select '\\'
)
select name,
    name like '%\\\\\\%' as 6_slashes,
    name like '%\\\%' as 3_slashes
from test_table;
name 6_slashes 3_slashes
\ 0 1

The backslash character is used as escape character in several contexts, including:

That means that you need to escape backslash itself just to produce a raw backslash, and all this adds up if you nest contexts.

  • 2 symbols in a string literal represent 1 actual character

     select '\\'; -- \

    (Demo)

  • 2 characters in a LIKE pattern represent a literal backslash. However, if you're using a string literal to generate them, you need to escape every of them. You wouldn't need that double-escaping if you were using a different source; for instance, using the client language of your choice, you could read the string from a text file and feed it into SQL using prepared statements.

  • In many cases, it isn't mandatory to escape raw backslashes if there's no ambiguity.

    SELECT '\' -- Parse error because \' is a valid entity
SELECT '\%' -- OK because because \% is not a valid entity
SELECT '\\%' -- OK and identical to previous one

    Demo

    This covers the second "more on that later" and explains why you sometimes get the same result with a variable number of slashes.

  • If you use another language to generate your SQL, that language might also use backslash as escape character (in particular, PHP and JavaScript do), so there's another layer of escaping involved. This is the first "more on that later".

user_135080
Related