MySql+Mybatis+Druid之SqlException:sql injection violation, multi-statement not allow
来源:互联网 发布:网络火的歌曲有哪些 编辑:程序博客网 时间:2024/05/29 14:15
接上一篇博客:《数据库优化之MyBatis批量删除、更新》
Druid是阿里巴巴,开发的一个数据库连接池工具,经历过多次双十一的洗礼,它的性能已经能够满足国内大多数项目的需求。
异常一:
项目中启用Druid的统计管理,在执行批量修改时:提示Error updating database. Cause: java.sql.SQLException: sql injection violation, multi-statement not allow 。
提示:违反sql注入:多声明不被允许
以下是栈异常输出:
Caused by: java.sql.SQLException: sql injection violation, multi-statement not allow : UPDATE t_single_project_score SET update_time=now() , is_delete = 1 , operator =? WHERE student_id IN (?) AND school_year IN( ? ) AND is_delete = 0 ; UPDATE t_single_project_score SET update_time=now() , is_delete = 1 , operator =? WHERE student_id IN (?) AND school_year IN( ? ) AND is_delete = 0 at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:800)at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:251)at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:473)at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:929)at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:473)at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:929)at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:473)at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:349)at com.p6spy.engine.wrapper.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:119)at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:483)at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)at com.sun.proxy.$Proxy44.update(Unknown Source)at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:213)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:483)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)... 52 more
通过异常栈输出出现异常如何分析解决?
at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:800)
这是在spring-db.xml的wall-filter这个 bean中报出的异常:
在这个WallFilter找到checkInternal方法,就会看到提示的错误信息前半部分:sql injection violation
查看check()方法-->checkInternal()方法
找到异常后半段:multi-statement not allow。造成打印这个异常消息的原因是config.ismultiStatementAllow()为false
解决方法:把multiStatementAllow修改成true即可
<!--在spring-db.xml的wall-filter中添加config,修改后如下--> <bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter"> <property name="dbType" value="mysql"/> <!--<property name="config" ref="wall-config"/>--> </bean> <!--解决mybatis与druid集成后,wallFilter sql注入异常--> <bean id="wall-config" class="com.alibaba.druid.wall.WallConfig"> <property name="multiStatementAllow" value="true"/> </bean>
了解wall-filter:通过官方文档配置
异常二:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE t_single_project_score SET update_time=now() , '
参考:
【sql关键字冲突】https://www.cnblogs.com/zzxbest/archive/2011/09/22/2185029.html
【sql关键字冲突】http://blog.csdn.net/qq_34698126/article/details/53128746
解决方法:这个问题一直报sql语法问题,删除wall-filter的bean和wall-config的bean,同时在jdbc上加上allowMultiQueries=true&,问题得到了解决。
分析:wall-filter会拦截多次声明请求的循环sql语句,即使设置为true,还会检测到sql语句间的';'分号会视为sql已经结束,所以有sql循环,第二个sql语句就会报出异常。
以上不是最终的解决方案,希望遇到过这个问题的亲们,可以多多留言与小编交流。
祝好运
- MySql+Mybatis+Druid之SqlException:sql injection violation, multi-statement not allow
- MySql+Mybatis+Druid:sql injection violation, multi-statement not allow
- MySql+Mybatis+Druid:sql injection violation, multi-statement not allow
- druid sql黑名单 报异常 sql injection violation, part alway true condition not allow
- MyBatis(7)--spring boot集成MyBatis,集成Druid批量更新报错multi-statement not allow
- springboot druid 数据库批量更新错误 multi-statement not allow
- springboot druid 数据库批量更新错误 multi-statement not allow
- 【Mybatis】Cause: java.sql.SQLException: sql injection violation, syntax error: ERROR. token : WHERE,
- Caused by: java.sql.SQLException: sql injection violation, syntax error: ERROR. token : DESC
- java.sql.SQLException: Protocol violation
- java.sql.SQLException: statement is closed语句被关闭 druid连接池报错
- java.sql.SQLException: !Statement.GeneratedKeysNotRequested!
- SQL java.sql.SQLException: Parameter metadata not available for the given statement
- MySql+Mybatis+Druid 优化之MyBatis批量删除、更新
- java.sql.SQLException: Statement parameter 1 not set.问题的解决
- java.sql.SQLException: Parameter metadata not available for the given statement
- java.sql.SQLException: Parameter metadata not available for the given statement
- SQL Injection with MySQL
- PHP网站——返回上一页(登陆后自动到登陆前的界面)
- 卡内基梅隆大学提出基于学习的动作捕捉模型,用自监督学习实现人类3D动作追踪
- 机器人slam算法资料整理
- 基础python的手机号随机生成
- 超美国11倍!麦肯锡176页报告解读数字中国领先全球的秘密
- MySql+Mybatis+Druid之SqlException:sql injection violation, multi-statement not allow
- 大数据时代的伦理道德
- 寒武纪芯片创始人:要让AI芯片计算效率提高一万倍
- Unity3d实现画笔
- 理解matplotlib、pylab与pyplot之间的关系
- 学界 | OpenAI 发布稀疏计算内核,更宽更深的网络,一样的计算开销
- Opencv3中FeatureDetector未定义标识符,不在cv命名空间下的解决办法
- CCF大数据专家委:2018年大数据发展趋势预测
- 普及练习场 简单的模拟 笨小猴