在使用druid的时候很简单的一条sql报了错,如下
select
<include refid="Base_Column_List"/>
from sys_user_identity where user_id = #{userId} and is_deleted = '0' and 'identity' = 274
报错信息如下
### Error querying database. Cause: java.sql.SQLException: sql injection violation, part alway false condition not allow : select
id, user_id, identity, identity_name, company_id, is_deleted, create_user_id, create_time,
update_user_id, update_time
from sys_user_identity where user_Id = ? and is_deleted = '0' and 'identity' = 274
### The error may exist in URL [jar:file:/tomcat/app.jar!/BOOT-INF/lib/member-dao-5.0.0-SNAPSHOT.jar!/cn/com/ebidding/member/mapper/mysql/SysUserIdentityMapper.xml]
### The error may involve cn.com.ebidding.member.dao.mapper.SysUserIdentityMapper.hasTenderIdentity
### The error occurred while executing a query
### SQL: select id, user_id, identity, identity_name, company_id, is_deleted, create_user_id, create_time, update_user_id, update_time from sys_user_identity where user_Id = ? and is_deleted = '0' and 'identity' = 274
### Cause: java.sql.SQLException: sql injection violation, part alway false condition not allow : select
id, user_id, identity, identity_name, company_id, is_deleted, create_user_id, create_time,
update_user_id, update_time
from sys_user_identity where user_Id = ? and is_deleted = '0' and 'identity' = 274
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; sql injection violation, part alway false condition not allow : select
id, user_id, identity, identity_name, company_id, is_deleted, create_user_id, create_time,
update_user_id, update_time
from sys_user_identity where user_Id = ? and is_deleted = '0' and 'identity' = 274; nested exception is java.sql.SQLException: sql injection violation, part alway false condition not allow : select
id, user_id, identity, identity_name, company_id, is_deleted, create_user_id, create_time,
update_user_id, update_time
from sys_user_identity where user_Id = ? and is_deleted = '0' and 'identity' = 274
搜了下,有些是关于druid的wall配置的,我们使用wall来应对批量插入的情况,和我的情况不符。
部分使用函数的情况,也是和我的不符合的。
检查sql,放在数据库中执行也是没有问题的。
最后想到因为mysql关键字 identity引起的问题,对于关键字应该使用`
进行转义。
实践了一把发现不转义和用`
转义都是可以成功的,即druid是对这种情况有考虑的。
使用’在navicat中是无问题的,而在druid中是有问题的。所以druid对’的处理方式是任务这个就是标志一个常量,对于’identity’ = 2的判断是字符串与数值进行对比是恒不等。因而报出 part alway false condition not allow。
应该所有的数据源相关框架对mysql的关键字都是用 `进行转义的,以后还是要跟着规范来。