好心办坏事小心隐式类型转换

2015-05-30

数据库为了保证SQL能够正常执行，总会提供一些友好性来满足那些看起来对的SQL正常执行，如隐式类型转换的使用，万字不如code，来看个具体的问题

创建表

create table  CONVERT_SHOW   
(  
  USER_ID              bigint(8),  
  NAME                 varchar(8),  
  OP_TIME              timestamp default CURRENT_TIMESTAMP
);

插入模拟数据

1
2
3

insert into CONVERT_SHOW(user_id,name)values(0,'cast');  
insert into CONVERT_SHOW(user_id,name)values(1,'cast2');  
insert into CONVERT_SHOW(user_id,name)values(2,'cast3');

前台读取表中的数据一般使用的查询SQL

1	select USER_ID,NAME,OP_TIME from CONVERT_SHOW where USER_ID= ?;

但会有去省事的直接以'参数值' 来替换"?"，替换后的SQL可能是这样的（参数有点夸张，但传递的查询值真可能什么都有）

1	select USER_ID,NAME,OP_TIME from CONVERT_SHOW where USER_ID= '查询参数';

这时，隐藏的一个陷阱就可能出现：数据库为了SQL正常工作，会使用隐式类型转换使得参数变成bigint类型，如果传递的参数不是整数会出现什么样的情况？

1	select cast('查询参数' as UNSIGNED ) ;

结果是：0

如果恰好表里存在一条userid为0的数据（真遇到了，不然也不会有这篇文章），就会被返回，接着就是让人的各种着急，连在Google上使用什么样的关键字来描述问题都非常困难^^

如果数据访问层能够做到对参数进行类型校验，肯定可以避免问题出现，但大多时候都是无心挖出来的坑，那真是满头的黑线啊！！！