A detailed analysis of the murder caused by a misplaced double quote in MySQL

1. Introduction

Recently, I often encounter developers accidentally deleting or updating data. Now, they have caused me trouble again. Let’s take a look at the whole process.

2. Process

Since development needs to repair data in the production phase, 120 SQL statements need to be executed and the data needs to be updated

So the developer connected to the production database and executed the first SQL

update tablename set source_name = "bj1062-Beichen Fudi, Changying, Chaoyang District, Beijing"
where source_name = "-Beichen Fudi, Changying, Chaoyang District, Beijing"

We took a closer look at this SQL and found that there is indeed no problem. The where condition is also normal. The general idea is to add the string bj1062 in front of the address. Is there really no error? Yes there is no error. After the development was completed, the results were indeed in line with expectations.

Then the developer executed the rest of the SQL, which is the same as the above SQL, to update the address. After the execution was completed, the developer was confused and found that source_name had become 0. The developer quickly called me and said:

Harvey, I executed the update, the where conditions are correct, and the set values ​​are also correct, but all the fields after the set have become 0. Can you help me check and see if the data can be restored?

I quickly logged into the server and checked the binlog during this period. I found a large number of update tablename set source_name=0 statements. I used binlog2sql to parse them. Project address: binlog2sql

We quickly determined the time of the operation with the developer, generated the flashback SQL, recovered the data, and preserved the on-site evidence.

Then I checked the SQL executed by the development and found several very strange SQLs:

The quotation marks in these SQL statements are placed after the where field name. The simplified SQL statement becomes:

update tbl_name set str_col="xxx" = "yyy"

So how does this SQL perform semantic conversion in MySQL?

Could it be something like this?

update tbl_name set (str_col="xxx" ) = "yyy"

This is a grammatical error, and it will only be in the following form:

update tbl_name set str_col=("xxx" = "yyy")

and

select "xxx" = "yyy"

The value of is 0, so

update tbl_name set str_col="xxx" = "yyy"

Equivalent to

update tbl_name set str_col=0

Therefore, all source_name fields are updated to 0.

Let's study what happens to this kind of statement in select form.

mysql [localhost] {msandbox} (test) > select id,str_col from tbl_name where str_col="xxx" = "yyy";
+----+---------+
| id | str_col |
| 1 | aaa |
+----+---------+ | 2 | aaa |
+----+---------+
| 3 | aaa |
| 4 | aaa |

We found that this SQL also found the records with str_col='aaa' . Why?

mysql [localhost] {msandbox} (test) > warnings
Show warnings enabled.
mysql [localhost] {msandbox} (test) > explain extended select id,str_col from tbl_name where str_col="xxx" = "yyy"\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE table: tbl_name type: index
1 row in set, 1 warning (0.00 sec)
possible_keys: NULL
key: idx_str
ref: NULL
key_len: 33 rows: 4
Extra: Using where; Using index
filtered: 100.00
Note (Code 1003): /* select#1 */ select `test`.`tbl_name`.`id` AS `id`,`test`.`tbl_name`.`str_col` AS `str_col` from `test`.`tbl_name` where ((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')

Here he transforms the where condition into

((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')

This condition first determines whether str_col and 'xxx' are equal. If they are equal, the value in the brackets is 1. If they are not equal, it is 0.
Then 0 or 1 is compared with 'yyy'.

Since one side of the equal sign is an int and the other side is a string, both sides are converted to float for comparison.

Case analysis of query result errors caused by implicit conversion in MySQL

'yyy' is converted to a floating point type and is equal to 0. 0 is always equal to 1 when compared with 0.

mysql [localhost] {msandbox} (test) > select 'yyy'+0.0;
+-----------+
| 'yyy'+0.0 |
| 0 |
+-----------+
mysql [localhost] {msandbox} (test) > select 0=0;
+-----------+
1 row in set, 1 warning (0.00 sec)
+-----+
1 row in set (0.00 sec)
| 0=0 |
+-----+
+-----+
| 1 |

This results in the result being always true, that is, the select statement is equivalent to the following SQL

select id,str_col from tbl_name where 1=1;

All records will be queried.

3. Summary

When writing SQL, you must be careful about whether the quotation marks are in the correct position. Sometimes, if the quotation marks are in the wrong position, the SQL is still normal, but it will cause all the execution results to be wrong. Before execution, you must run the test in the test environment and use the IDE's syntax highlighting to identify corresponding problems.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.