提问
一个粗心,括号不小心打错了地方,猜猜会是什么运行结果?
t_xxx表order_no为varchar类型。
select * from t_xxx where order_no in ('U123','U234' AND type in (10,11));
语法错误?
row 0?
答案
实际情况是,这句sql并没有语法错误,而是将全部数据返回出来。
本以为这句sql会报语法错误,但万万没想到竟然可以运行成功,而且会导致where子句的失效,让我们一起盘根问底一下。
首先是词法分析
MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。https://segmentfault.com/a/1190000023554495?utm_source=tag-newest
1. 本例中,in将被分解为【'U123'】、【'U234' AND type in (10,11)】两部分,也同时等效于order_no=('U123') or order_no=('U234' AND type in (10,11)),前一段必定是false,出问题的部分其实就在后面这段。
2. 猜想下('U234' AND type in (10,11))的结果是什么?因为中间有and逻辑运算符,其结果将为布尔型(底层为tinyint(1)),0/1这样的值
那为什么后面一段会被认为是true呢?这就要从varchar与int的比较说起,这也是一个会隐藏大坑的地方《数据类型隐式转换》
数据类型隐式转换分析
文档我们只看原版:https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html?spm=5176.100239.blogcont47339.5.1FTben
首先我们列举下已知的隐式转换规则:
- 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
- 两个参数都是字符串,会按照字符串来比较,不做类型转换
- 两个参数都是整数,按照整数来比较,不做类型转换
- 十六进制的值和非数字做比较时,会被当做二进制串
- 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
- 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
- 所有其他情况下,两个参数都会被转换为浮点数再进行比较
【order_no=('U234' AND type in (10,11))】出问题的条件表达式,order_no是字符串、后面的是tinyint(1),等价于order_no=0这个条件表达式,邪恶了,命中了7号策略
我们又要细说,字符转数字的过程
字符串转为数字
一个字符串是会与int 0匹配上的,这也是一个经典的安全问题,那为什么字符串或者明确说首字母非数字的字符串会与int 0匹配上呢?
再来猜测一下,这些表达式的结果会是什么?
SELECT '123'=123,'123a'=123,'a123'=123---------------------1 1 0---------------------
当字符串转为数字的时候,原则是从左边开始处理:
- 如果字符串的第一个字符就是非数字的字符,那么转换为数字就是0
- 如果字符串以数字开头
- 如果字符串中都是数字,那么转换为数字就是整个字符串对应的数字
- 如果字符串中存在非数字,那么转换为的数字就是开头的那些数字对应的值
所以order_no变成了0,与右侧的0匹配上了
这就是这个问题的分析过程