MySQL一隐式转换

  我相信90%以上的同学们在平时开发时，或多或少都被隐式转换（CONVERT_IMPLICIT）坑过，甚至测出bug前你都浑然不知。你还别不信，“无形之刃，最为致命！”

 

mysql> SELECT * from t_user;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  1 | 陈哈哈    | abcd1234 |
|  2 | 侨布斯    | 1234     |
|  3 | 提莫      | 1234abcd |
+----+-----------+----------+
3 rows in set (0.00 sec)

不能展示真实数据，见谅~~ 上面是这张用户表的原始数据，侨总用下面的SQL查询自己这行数据，大家先看看有没有问题？

SELECT * from t_user where `password`=1234;

感觉没有明显的毛病？来看看结果：

mysql> SELECT * from t_user where `password`=1234;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  2 | 侨布斯    | 1234     |
|  3 | 提莫      | 1234abcd |
+----+-----------+----------+
2 rows in set, 1 warning (0.00 sec)

???这怎么把提莫队长给打现行了？

好了，其实这算是MySQL给开发者留下的不友好的礼物（坑）。今天我们一起从以下三个角度去聊一聊MySQL的隐式转换。

索引

1、SQL语句中隐式转换的坑

先看一下官方的隐试转换说明：

翻译成人话：

1. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换。
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
6. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较。（这里所说的浮点数一般默认为double类型）

  可以看到，非前六种以外的类型转换都要转成浮点类型来处理，这意味着什么？意味着MySQL承认了隐式转换这个事儿，还表示不爱看官方文档的哥们儿出问题活该~~

  我们用一些具体示例来看一下，通过下述SQL可见，当1234没有引号也就是整数时，‘1234abcd’ = 1234 → true，说明MySQL对’1234abcd’做了转型，转成了浮点类型，结果是：1234abcd => 1234

# 0：false；1：true
mysql> SELECT '1234abcd' = '1234';
+---------------------+
| '1234abcd' = '1234' |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)# 0：false；1：true
mysql> SELECT '1234abcd' = 1234;
+-------------------+
| '1234abcd' = 1234 |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

  为啥1234abcd => 1234呢？ 其实’1234’和’abcd’都会转成浮点数，即：1234+0=1234，非数字类型的都被直接转成了 0

mysql> SELECT '1234' + 'abcd';
+-----------------+
| '1234' + 'abcd' |
+-----------------+
|            1234 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

  你发现了什么？原来字符串涉及到 +、=、-、/ 等等运算符时都会进行隐式转型，也就是转成double，那么字符串转double是怎么转的呢？

# 转成：'1aaaa' = 1
mysql> SELECT '1aaaa' = 1;
+-------------+
| '1aaaa' = 1 |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)# 转成：'a1111' = 0
mysql> SELECT 'a1111' = 1;
+-------------+
| 'a1111' = 1 |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)# 转成：0 + 0 =0
mysql> SELECT 'aa' + 'aa' = 1;
+-----------------+
| 'aa' + 'aa' = 1 |
+-----------------+
|               0 |
+-----------------+
1 row in set, 2 warnings (0.00 sec)# 转成：0 + 0 + 1 =1
mysql> SELECT 'aa' + 'aa' + '1' = 1;
+-----------------------+
| 'aa' + 'aa' + '1' = 1 |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set, 2 warnings (0.00 sec)

  可见，是以字符串从左向右取值的，且从非数字起后面的值都被转成 0，如a11111，第一位为a,则整体转为 0；1aaaa第一位为1,第二位为a，从第二位往后转成0，得a11111 → 0

mysql> SELECT * from t_user where `password`=1234;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  2 | 侨布斯    | 1234     |
|  3 | 提莫      | 1234abcd |
+----+-----------+----------+
2 rows in set, 1 warning (0.00 sec)

  现在我们就明白为什么能匹配到提莫了。因为在不同类型转换时"1234abcd"被转成了浮点类型，"abcd"转成浮点型后为0，因此MySQL判为：“1234abcd” = ‘1234’ + 0 。

2、黑客同学喜欢用隐式转换进行SQL注入攻击

通过第一部分隐式转换的了解，我们可以预测一些简单SQL注入的方式：

mysql> SELECT * from t_user where username='陈哈哈' and `password`=0;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  1 | 陈哈哈    | abcd1234 |
+----+-----------+----------+
1 row in set, 1 warning (0.00 sec)

果然，我账号的密码被毫无意外的攻破了

如果有些朋友公司的网站用的是下面的写法：

select * from t_user where username=${username} and `password`=${password};

  不然有些小伙伴友好的把请求参数构建成 username → a' OR 1='1 ，那么password是啥都无所谓了，是吧。

懂我意思吧，快改了。

  当然，其实很多注入攻击的真实目的，并不是用来破解用户账号的，而是破坏服务器。一般我们在页面F12发现有问题的接口后，通过脚本模拟请求参数（构造注入参数），去不断尝试自定义构造limit、order、where等条件，或许花不了多久就能通过一个不规范的请求入口，检索出该表甚至其他大表全量信息。导致公司服务器负载异常，连接数打满，CPU200%等有趣的情况。有兴趣的同学可以花几小时尝试破解自己公司的web~~

3、索引中隐式转换的坑

  同理，在MySQL根据索引进行查询时，如果你的username字段有索引且为varchar类型，且查询如下时：

select * from t_user where username=123;

该SQL会出现两个问题：

1、索引失效
  无法使用到索引查询，因为mysql会在引擎层进行类型隐式转换（CONVERT_IMPLICIT），会先把username隐式转换成浮点数，然后再跟你的123进行比较，然而你的索引是建在username上的，并不是在转换后的username上的，所以进行转换后的username相当于没有索引。会全表扫描，换做大表中，无法使用索引，你懂得。

2、查询结果不准确
  第一部分我们已经举例说明，MySQL在隐式转换时的varchar转double，会出现很多意想不到的情况，比如 “123”，" 123"，"123a"都会转成123，实际场景中都是不允许出现的。
 

总结

就是当sql中一个字符串和一个数字做+、=、-、/ 计算时,如"a1bc"+1,   "ccccd3"=0

这个字符串中的非数字的字符会被隐式转换成0并且再转换为空,再去做比较.只分以下两种情况:

1.前面的字符不是数字时,直接等于0,如"a1"=0,"abcd1"=0

2.前面的字符是数字时,从第一个开始截取,从左往右直到出现的第一个不是数字的字符为止,截取的字符串就是最终转换的值,如"1a"=1,"2a3b"=2,"55555h666666"=55555