MySQL 中一个双引号的错位引发的事故

Wesley13
• 阅读 503

点击上方 Java后端,选择 设为星标

优质文章,及时送达


原文:https://sourl.cn/pqLJz4

MySQL 中一个双引号的错位引发的血案!值得一看,避免自己踩坑!

一、前言

最近经常碰到开发误删除误更新数据,这不,他们又给我找了个麻烦,我们来看下整个过程。

二、过程

由于开发需要在生产环节中修复数据,需要执行 120 条 SQL 语句,需要将数据进行更新

于是开发连上了生产数据库,首先执行了第一条 SQL

update tablename set source_name = "bj1062-北京市朝阳区常营北辰福第" where source_name = "-北京市朝阳区常营北辰福第"

我们仔细看了下,这个 SQL,的确没有什么问题,where 条件也是正常的,大意就是将这个地址的前面加字符串bj1062,是真的没有错误么?是的没有错误。开发执行完成后,结果的确是符合预期。

然后开发执行了剩下的 SQL,都是和上面的 SQL 一样,将地址进行更新。执行完成后,开发懵逼了,发现 source_name 都变成了 0,开发赶紧给我打电话说:

Harvey,我执行了 update,where 条件都是对的,set 的值也是对的,但是 set 后的字段全部都变成了 0,你赶紧帮我看看,看看能不能恢复数据。

我赶紧登上服务器,查看了这段时间的 binlog,发现了大量的 update tablename set source_name=0 的语句,利用 binlog2sql 进行了解析,项目地址:binlog2sql[1]

MySQL 中一个双引号的错位引发的事故

赶紧和开发确定了操作的时间点,生成 flashback 的 SQL,进行了数据恢复,同时保留现场证据。

然后对开发执行的 SQL 进行了 check,发现了几条很诡异的 SQL:

MySQL 中一个双引号的错位引发的事故

这几条 SQL 的引号位置跑到了 where 字段名字后面,简化后的 SQL 变成了:

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

那么这个 SQL 在 MySQL 他是如何进行语义转化的呢?可能是下面这样的么?

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

这样就语法错误了,那么只会是下面这样的形式,

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

select "xxx" = "yyy"

的值是 0,所以

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

等价于

update tbl_name set str_col=0

所以就导致了 source_name 字段全部更新成了 0.

我们再研究下 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     | +----+---------+

我们发现,这个 SQL 将 str_col='aaa'的记录也查找出来了,为什么呢?

``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 from test.tbl_name where ((test.tbl_name.str_col = 'xxx') = 'yyy')
``

这里他把 where 条件转化成了

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

这个条件的首先判断 str_col 和'xxx'是否相等,如果相等,那么里面括号的值为 1,如果不相等,就是 0 然后 0 或者 1 再和和'yyy'进行判断, 由于等号一边是 int,另外一边是字符串,两边都转化为 float 进行比较,可以看我之前的一篇文章MySQL 中隐式转换导致的查询结果错误案例分析[2]'yyy'转化为浮点型为 0,0 和 0 比较恒等于 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=0;
+-----+
| 0=0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)
`

这样导致结果恒成立,也就是 select 语句等价于以下 SQL

select id,str_col from tbl_name where 1=1;

将查询出所有的记录。

三、小结

在写 SQL 的过程中,一定要小心引号的位置是否正确,有时候引号位置错误,SQL 依然是正常的,但是却会导致执行结果全部错误。在执行前必须在测试环境执行测试,结合 IDE 的语法高亮发现相应的问题。

参考资料

[1]

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

[2]

MySQL中隐式转换导致的查询结果错误案例分析: https://www.fordba.com/mysql-type-convert-analysis.html

        Java后端交流群已成立
   
       
       
       
  
      
      
      
 
     
     
     
 
     
     
     
  
      
      
      
   
       
       
       
    
        
        
        公众号运营至今,离不开小伙伴们的支持。为了给小伙伴们提供一个互相交流的平台,特地开通了官方交流群。扫描下方二维码备注 进群 或者关注公众号 Java后端 后获取进群通道。
   
       
       
       
   
       
       
       
    
        
        
        
   
       
       
       
  
      
      
      
 
     
     
     

    
    
    
 
     
     
     

    
    
    

    
    
    
 
     
     
     

    
    
    

    
    
    
 
     
     
       
      
      
      
   
       
       
       
    
        
        
        
     
         
         
         
      
          
          
                 
           
           
           
        
            
            
            推
        
            
            
            荐
        
            
            
            阅
        
            
            
            读 
       
           
           
           
       
           
           
           
        
            
            
            1. GitHub 上有哪些好玩的项目
       
           
           
           
       
           
           
           
        
            
            
            2. Nginx 为什么快到根本停不下来?
       
           
           
           
       
           
           
           
        
            
            
            3. 牛逼!Docker从入门到上瘾
       
           
           
           
       
           
           
           
        
            
            
            4. 连夜撸了一个简易聊天室
       
           
           
           
       
           
           
           
        
            
            
            5. 推荐一款 Java 对象映射神器
       
           
           
           
       
           
           
           
        
            
            
            
        
            
            
            
       
           
           
           
     
         
         
         
    
        
        
        
   
       
       
       
  
      
      
      

    
    
    

本文分享自微信公众号 - Java后端(web_resource)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Opencv中Mat矩阵相乘——点乘、dot、mul运算详解
Opencv中Mat矩阵相乘——点乘、dot、mul运算详解2016年09月02日00:00:36 \牧野(https://www.oschina.net/action/GoToLink?urlhttps%3A%2F%2Fme.csdn.net%2Fdcrmg) 阅读数:59593
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这