MySQL一隐式转换

柔光的暖阳◎ 2024-03-26 12:39 84阅读 0赞

>   我相信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语句][SQL]中隐式转换的坑 ###

先看一下官方的隐试转换说明：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MzkwNTQ1_size_16_color_FFFFFF_t_70_pic_center]

翻译成人话：

1.  两个参数至少有一个是 NULL 时，比较的结果也是 NULL，[例外是使用 <=> 对两个 NULL 做比较时会返回 1][_ _ 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`

现在我们就明白为什么能匹配到`提莫`了。因为在不同类型转换时"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

[SQL]: https://so.csdn.net/so/search?q=SQL%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MzkwNTQ1_size_16_color_FFFFFF_t_70_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/eb95632e690c401f84cfd5d5353712fa.png
[_ _ NULL _ 1]: https://chensj.blog.csdn.net/article/details/115331735