我的开发环境是JFinal+Druid,在同时执行2个更新操作的时候提示:multi-statement not allow
更具体的出错信息如下
Caused by: java.sql.SQLException: sql injection violation, multi-statement not allow : delete from os_cms_content where content_id = ?; delete from os_cms_content_field where content_id = ?; at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:709) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:234) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448) 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:448) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:311) at sun.reflect.GeneratedMethodAccessor25.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:239) at com.sun.proxy.$Proxy12.prepareStatement(Unknown Source) at sun.reflect.GeneratedMethodAccessor25.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.jfinal.plugin.activerecord.SqlReporter.invoke(SqlReporter.java:58) at com.sun.proxy.$Proxy13.prepareStatement(Unknown Source) at com.jfinal.plugin.activerecord.DbPro.update(DbPro.java:276) at com.jfinal.plugin.activerecord.DbPro.update(DbPro.java:295)
可以看到,我同时执行了2个update语句,要解决“multi-statement not allow”的问题,需要分2步。
1、修改数据库连接配置,加上参数allowMultiQueries
jdbc.url=jdbc:mysql://127.0.0.1:3306/ousensecms?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
做到第一步,我看网上很多文章说,如果是springboot+Druid,问题就解决了,但是我做了这步操作之后,问题依旧。
2、修改Druid的相关配置
DataSource节点下
<property name="proxyFilters"> <list> <ref bean="stat-filter" /> <ref bean="log-filter" /> <ref bean="wall-filter"/> </list> </property> <!-- 配置监控统计拦截的filters,去掉后监控界面sql无法统计 --> <property name="filters" value="stat,wall,log4j" />
<!--在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>
通过以上的步骤,就能解决Druid“multi-statement not allow”的问题了。
需要注意的一点是,“proxyFilters”和“filters”顺序如果搞反了,虽然应用不会出错,但是还是依旧会报“multi-statement not allow”的错误。