Shulou(Shulou.com)06/01 Report--

Source: For DBA

Www.fordba.com/mysql-double-quotation-marks-accident.html

I. Preface

Recently, I often encounter developers mistakenly deleting and updating data. No, they have found trouble for me again. Let's take a look at the whole process.

II. Process

Because the developer needs to repair the data in the production process, 120 SQL statements need to be executed and the data needs to be updated.

So the developer connects to the production database and first executes the first SQL

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

We took a closer look, this SQL, there is no problem, where conditions are normal, the main idea is to add the string bj1062 in front of this address, is there really no error? Yes, there are no mistakes. After the development execution is completed, the results are indeed in line with expectations.

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

Harvey, I implemented the update,where condition is correct, the value of set is also correct, but all the fields after set have become 0, please help me to see if you can recover the data.

I quickly logged on the server, checked the binlog during this period, found a large number of update tablename set source_name=0 statements, parsed with binlog2sql, the project address:

Binlog2sql https://github.com/danfengcao/binlog2sql

Quickly determine the time of operation with the developer, generate the SQL of flashback, restore the data, and keep the on-site evidence at the same time.

Then check the SQL executed by the development, and find several very strange SQL

The quotation marks of these SQL go after the name of the where field, and the simplified SQL becomes:

Update tbl_name set str_col= "xxx" = "yyy"

So how does the semantic transformation of this SQL in MySQL?

Could it be something like this?

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

If there is a grammatical error, it will only be in the following form

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

And

Select "xxx" = "yyy"

Is 0, so

Update tbl_name set str_col= "xxx" = "yyy"

Equivalent to

Update tbl_name set str_col=0

So this causes the source_name field to be updated to 0. 0.

Let's take a look at what happens to such a statement in the form of select.

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 looked up the records of 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

Possible_keys: NULL

Key: idx_str

Key_len: 33

Ref: NULL

Rows: 4

Filtered: 100.00

Extra: Using where; Using index

1 row in set, 1 warning (0.00 sec)

Note (Code 1003): / * select#1 * / select `test`.`tbl _ name`.`id`AS `id`, `test`.`tbl _ name`.`str _ col`AS `str_ col``test`.`tbl _ name`where ((`test`.`tbl _ name`.`str _ col` = 'xxx') =' yyy')

Here he transformed the where condition into

((`test`.`tbl _ name`.`str _ col` = 'xxx') =' yyy')

The first step of this condition is to determine whether str_col and 'xxx' are equal. If they are equal, then the value of parentheses is 1. If not, it is 0.

Then 0 or 1 is judged with 'yyy'. Because the equal sign is int on one side and a string on the other, both sides are converted to float for comparison. You can see my previous article: an error case Analysis of query results caused by implicit conversion in MySQL case Analysis of http://www.fordba.com/mysql-type-convert-analysis.html' yyy' converted to floating-point 0 and 0 is always equal to 1

Mysql [localhost] {msandbox} (test) > select 'yyy'+0.0

+-+

| | 'yyy'+0.0 |

+-+

| | 0 |

+-+

1 row in set, 1 warning (0.00 sec)

Mysql [localhost] {msandbox} (test) > select 0

+-+

| | 00.00 | |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

This results in a constant result, 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.

III. Summary

In the process of writing SQL, be careful whether the position of quotation marks is correct, sometimes the position of quotation marks is wrong, the SQL is still normal, but it will lead to all errors in the execution results. Before execution, the test must be executed in the test environment, combined with the syntax highlighting of IDE to find the corresponding problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.