There are a lot of special characters in MySQL that are quite disgusting, such as single quotation marks (‘), double quotation marks (“), backslash (\), etc.

In SQL, if you don’t pay attention to these symbols, you can get into trouble.

You still don’t believe it, listen to uncle’s advice, the water here is very deep, some things, you can not grasp… “Panga’s Friend” well, today let’s go to the most difficult person’s backslash (\), to see what pits.

INSERT statement with backslash (\)

1. Practical testing

Let’s test what a backslash (\) looks like in an INSERT statement with the following SQL statement.

INSERT INTO 'demo0526' (' id ', 'text ') VALUES (null, 'D: demo0526 '); INSERT INTO 'demo0526' (' id ', 'text ') VALUES (null, 'D: demo0526 '); INSERT INTO ` demo0526 ` (` id `, ` text `) VALUES (null, 'D: \ \ \ ha ha Chen \ \ \' overtime). INSERT INTO ` demo0526 ` (` id `, ` text `) VALUES (null, 'D: \ \ \ \ ha ha Chen \ \ \ \' overtime). INSERT INTO ` demo0526 ` (` id `, ` text `) VALUES (null, 'D: \ \ \ \ \ ha ha Chen \ \ \ \ \' overtime).Copy the code

Result after insertion:

mysql> select * from demo0526; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id text | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 D: ha ha Chen overtime | | 2 | D: \ ha ha Chen work overtime \ | | 3 | D: \ ha ha Chen work overtime \ | | | D: \ \ ha ha Chen work overtime \ \ | | | D: \ \ ha ha Chen \ \ | + overtime + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the code

Here’s what we found:

When there is one backslash in the string, zero after insertion.

When there are two backslashes in the string, one is counted after insertion.

When there are three backslashes in the string, one is counted after insertion.

When there are four backslashes in the string, two count after insertion.

When there are five backslashes in the string, two count after insertion.

2. What is the principle?

In MySQL, backslash (\) is an escaped character in a string, so when we insert a backslash (\) character, such as insert “\”, the database will only store “”. The first backslash (\) is treated as an escape character.

Similarly, a string like D: chen-ha-ha-overtime is treated as the next escape character by the parser when it gets to the third backslash (\), so it becomes D: chen-ha-ha-overtime when stored.

So when we insert a backslash in our code, pay attention to whether \ has been changed to \, otherwise the string will be inconsistent after being stored in the library.

SELECT backslash (\)

1. Practical testing

We still use the above table data, directly use like fuzzy matching to test.

mysql> select * from demo0526; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id text | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 D: ha ha Chen overtime | | 2 | D: \ ha ha Chen work overtime \ | | 3 | D: \ ha ha Chen work overtime \ | | | D: \ \ ha ha Chen work overtime \ \ | | | D: \ \ ha ha Chen \ \ | + overtime + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Let’s start with a single backslash and two backslashes and see what we can find

mysql> SELECT * from demo0526 where text like '%\%'; Empty set (0.00 SEC) mysql> SELECT * from demo0526 where text like '%\\%'; The Empty set (0.00 SEC)Copy the code

Ah!!!!! Select * from ‘%%’ where ‘%%’ = ‘%%’; Did I learn all that for nothing?

Don’t worry, I’ll tell you if four backslashes (\) represent one in the SELECT statement. Oh, I’ll try it quickly with like ‘%\%’.

mysql> SELECT * from demo0526 where text like '%\\\\%'; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id text | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 2 | D: \ ha ha Chen work overtime \ | | 3 | D: \ ha ha Chen work overtime \ | | | 4 D: \ \ ha ha Chen work overtime \ \ | | | D: \ \ ha ha Chen \ \ | + overtime + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

Oh? If I query a table with two backslashes (\), I would like eight… Don’t stop me. I’ll see who the fuck designed this rule.

mysql> SELECT * from demo0526 where text like '%\\\\\\\\%'; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id text | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | D: \ \ ha ha Chen work overtime \ \ | | | 5 D: \ \ ha ha \ \ | overtime Chen + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

2. What is the principle?

In mysql’s like syntax, the string following like is escaped once during parsing and a second time during re matching. So if you expect a final match to “”, you have to escape it twice, from “\” to “” and then to “”.

If it is a normal exact query (=), a second regular escape is not required, as is the case with INSERT statements.

mysql> SELECT * from demo0526 where text = '\\\\'; + - + -- -- -- -- -- -- + | | id text | + - + -- -- -- -- -- - + 7 | | \ \ | + + -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

conclusion

All right, how do you feel when you see this? There are still a lot of inconvenient syntax in MySQL. There is still a lot of room for improvement

In daily work, we often encounter problems caused by such symbols, especially when users fill in data on the interface. It is recommended to make relevant restrictions and clarify which symbols are not allowed.

Author: Chen Ha Ha original link: blog.csdn.net/qq_39390545…