When Java queries Mysql database, the query conditions with special characters (\% '* _) are handled

Post the public method first

    public static String replaceStr(String str) {
        if(str != null && !"".equals(str)) {
            str = str.replaceAll("\\\\", "\\\\\\\\")
                    .replace("_", "\\_").replace("\'", "\\'")
                    .replace("%", "\\%").replace("*", "\\*");

        }
        return str;
    }

For the special characters _ '% *, it is easy to understand, so why is \ different?

1. Knowledge Reserve

1. Use backslashes in Java\

If you want to use a string to represent a backslash, you need to use "\". If you use "" directly, an error will be reported.

Insert picture description here


The reason is: The java compiler will mistakenly think that \ is used to escape the "symbol that follows.

2. Use backslashes in regular expressions\

First, the "\\" in the target string will be parsed into "\" by the compiler, and then, as a regular expression, "\" will be used as an escape character to escape the "\\" in the expression to make"\".

So if you want to match "\" (a backslash) in the regular expression, you must use "\\\\" (four backslashes).

Conversion process: "\\\\" -> "\\" -> "\"

3. Query string with backslash \ in Mysql

In Mysql, the NO_BACKSLASH_ESCAPES mode is not enabled by default , that is, "" will be parsed as an escape character instead of a normal character.
The following is the reference document of Mysql8.0 : Mysql8.0 doc

Insert picture description here


officially tells us that "\" will escape the characters that appear in the escape table, but for those characters that are not in the escape table, the backslash will be parsed Is ignored. (I ticked it out with a red line)

Looking back at "\", the first two "\" backslashes will be parsed into "", but everyone noticed that there is still one "" backslash left, which is not followed by any characters. So it will be ignored when parsing!
Let's take a look at Mysql's analysis of "" in the query. There is a slight difference between the exact query and fuzzy query of "" in Mysql.

For example, there is now a table country with fields id and name

Insert picture description here


1. Precise query

It only needs to parse the "\" in the target string as an escape character once.

Query statement 1: select * from country where name="中\国";

Since the characters connected with "" are not in the escape table, "" will be ignored, and the actual query result is "China".

Insert picture description here


Query sentence 2: select * from country where name="中\\国";

Since "\\" can be parsed into "\" in the escape table, the actual query result is "中\国".

Insert picture description here


Query statement 3: select * from country where name="中\\\国";

Since "\\" is in the escape table, the first two right slashes can be parsed into "\", but the third right slash is alone, without any characters after it, and will be ignored if it is not in the escape table . Therefore, the actual query result is "China\Country".

Insert picture description here


2. Fuzzy query
has gone through two parsing processes, "compiler parsing" and "regular expression parsing".

Query statement 1: select * from country where name like'中\\国';

After "compiler analysis", "中\\国" is parsed as "中\国", and then after "regular expression analysis", "中\国" is parsed as "China".

Insert picture description here

Query statement 2: select * from country where name like'中\\\国';

After "compiler analysis", "中\\\国" is parsed as "中\国", and then after "regular expression analysis", "中\国" is parsed as "China".

Insert picture description here


Query statement 3: select * from country where name like'中\\\国';

After "compiler analysis", "中\\\国" is parsed as "中\\国", and then after "regular expression analysis", "中\\" is parsed as "中\国". So the actual fuzzy query result is "China\Country".

Insert picture description here


Insert picture description here


Query statement 4: select * from country where name like'中\\\\\国';

After parsing by the compiler, "中\\\\\国" is parsed into "中\\国", and then after "regular expression analysis", "中\\国" is parsed as "中\国". So the actual fuzzy query result is "China\Country".

Insert picture description here


Insert picture description here

2. Application

Taking Spring boot starter web as an example, when the controller layer receives the attribute (or string) parameter with a backslash, it will look like the following figure:

Insert picture description here


but in fact, if we write a test case simulation, this is equivalent in

Insert picture description here

Here, I did a fuzzy query on the parameter, so I write it as follows:

scheduleEventVO.setDescription(scheduleEventVO.getDescription().replaceAll("\\\\", "\\\\\\\\"));
                criteria.andLike("description", StringUtils.stringLike(scheduleEventVO.getDescription()));