1、业务背景
我需要检索出在某些部门下的所以员工信息。其中传入的参数是部门id列表。
select * from x_table where y_file in ();
对于mybatis来说,部门id列表需要mybatis中的foreach迭代器去迭代该参数。
我认为foreach遍历也是将id列表拼接为以英文逗号(,)隔开的字符串,所以我就在程序中多做了一层转换,直接在mybatis中的sql为:
select * from x_table where y_file in (#{string});
string即是我把id列表手动转化的以逗号隔开的字符串。
大家觉得我这样做有什么问题吗?该sql是否可以执行?如果可以,查询的数据是否正确呢?
2、出现的问题
在测试接口的阶段,发现该sql是可以执行的,但是查询的结果却不正确!于是我在控制台打印出mybatis执行的sql语句,我将该sql拷贝出来后,在数据库执行一次,验证该sql是否有误,令人莫名其妙的是数据库执行后的结果是正确。
在反复定位问题的过程中,发现了一个令我很费解的规律。这条sql只能检索出in条件中的第一个参数的数据。例如我的sql是select * from x_table where y_file in (1,2,3);查询结果中只能检索到 y_file=1的数据。
3、揭晓答案
我相信有的小伙伴已经注意到我在描述背景的时候,我在mybatis中写的sql时用的#{}字符,那么你们是否了解#字符和$字符有什么区别呢?
#{}符号和${}符号的区别:
#实现的是sql语句的预处理参数、之后执行sql中用?号代替、使用时不需要关注数据类型、mybatis自动实现数据转换,优点就是可以防止SQL注入。$实现是sql语句的直接拼接、不做数据类型转换。需要自行判断数据类型,缺点不能防止sql注入(ps 使用order by时,就要用${})
所以mybatis中执行的sql是:select * from x_table where y_file in ('1,2,3');
哦~它把我传入的参数添加了引号,所以才导致查询数据错误!但是为什么我可以检索到 y_file=1的数据呢?真让人头大!
4、解决方法
尽然是引号"惹来的问题,那就让它原样填充到sql里就行啦~
最简单粗暴的解决方案:把#号字符改成$符号就o了!
而最标准的做法还是用mybatis中的foreach迭代器让它自己遍历填入参数把~毕竟$符号不能防止恶意sql注入的问题
5、能够检索出部分数据的原因:
下面我将以举案例的形式为大家讲解为什么能查询出部分数据的原因
表结构如下:
表内数据如下:
执行SQL1如下:
有的小伙伴可能要抓狂了,可能都看不下去了,内心ps(你上面都说了数据库存的是字符串,你为啥还要拿一个整数类型的去比较。。。没事找事吧)先别急,咱们猜猜这个sql可以执行成功吗?执行结果是什么?
what?此处假装有一个黑人问号脸。
执行SQL2如下:
我们再猜一猜执行结果
数据库中比较整数和字符串的(粗略)规则:
挨个的拿字符串进行转换为整数类型后,逐个与整数类型进行比较,直至遇到第一个为非数字的字符为止。
比如案例1中,首先筛选出string_x为89abc的字符串和整数类型的89比较,遍历字符串的第一个字符8,将其转换成整数类型后,将其与整数类型的第一个字符8进行比较,通过后,比较字符串的第二个字符9...通过后,比较第三个字符a,发现其不是数字类型,就停止比较了。
那它是mysql的一个bug吗?
mysql说:这个锅我不背!让不同类型进行比较本来就是一种“冒险”的做法。你在不了其比较规则的情况下,为什么要瞎用别的类型。
6、总结
1、#符号和$符号的区别,#符号是执行的sql预编译处理参数,能够防止sql注入,而$符号是动态拼装参数,将参数原样填充到sql语句里。
2、mysql中字符串和整数的比较规则是将字符串转化为整数类型后,进行比较,直到遇到第一个非数字的字符为止。