MySQL查询整型ID传入带后缀字符串仍返回结果的异常问题

问题复现 #

执行查询时，传入的ID值为带随机后缀的字符串'1abcd'，按预期格式不匹配应返回空结果集，实际命中了id=1的记录：

mysql> select * from pricelists where id = '1abcd';
+----+---------+--------+--------------+--------------+---------------------+-----+-------------------+--------+-------------+--------------------+----------------+------------+---------------------+---------------------+---------------+
| id | name    | markup | routing_type | quality_base | initially_increment | inc | shadow_billing_id | status | reseller_id | pricelist_id_admin | routing_prefix | call_count | creation_date       | last_modified_date  | decimal_value |
+----+---------+--------+--------------+--------------+---------------------+-----+-------------------+--------+-------------+--------------------+----------------+------------+---------------------+---------------------+---------------+
|  1 | default | 0      |            0 |            1 |                   0 |  60 |                 0 |      0 |           0 |                  0 |                |          0 | 2016-07-25 00:00:00 | 2022-07-06 10:36:31 |             4 |
+----+---------+--------+--------------+--------------+---------------------+-----+-------------------+--------+-------------+--------------------+----------------+------------+---------------------+---------------------+---------------+
1 row in set, 1 warning (0.00 sec)

返回结果末尾已经提示存在1个执行告警。

根本原因 #

这个现象是MySQL的隐式类型转换规则导致的：

• 表中id字段是整数类数值类型，传入的匹配值是字符串类型，两边数据类型不一致时，MySQL会自动做类型转换后再做比较
• 字符串转数值的逻辑为：从字符串起始位置开始截取连续的数字字符，遇到第一个非数字字符就停止截断，将截取到的内容转为数值。因此'1abcd'会被转换为整数1，自然匹配到id=1的记录
• 执行show warnings;就能看到对应告警：Truncated incorrect INTEGER value: '1abcd'，提示字符串转整数时发生了非预期截断。

修复方案 #

• 对齐参数类型：如果字段是数值类型，查询时直接传数值类型参数，不要用字符串包裹，更不要在参数中拼接非数字内容
• 前置参数校验：业务逻辑层先校验ID参数格式，不符合纯数字规则的请求直接拦截，无需下发到数据库执行
• 开启MySQL严格模式：开启STRICT_TRANS_TABLES等严格SQL模式后，这类隐式截断转换的场景会直接抛出错误，不会静默返回非预期结果

内容的提问来源于stack exchange，提问作者HP371