解决——》java.sql.SQLException: sql injection violation, multi-statement not allow

叁歲伎倆 2024-02-19 13:22 8阅读 0赞

版权声明：本文为博主原创文章，无需授权即可转载，甚至无需保留以上版权声明，转载时请务必注明作者。  
[https://blog.csdn.net/weixin\_43453386/article/details/84064498][https_blog.csdn.net_weixin_43453386_article_details_84064498]

#### 解决——》java.sql.SQLException: sql injection violation, multi-statement not allow ####

*   *  1、操作
     *  2、现象（错误信息）
     *  3、原因
     *  4、解决
     *  5、参考

violation, multi-statement not allow)

### 1、操作 ###

Spring Boot配置druid连接池，进行批量插入

### 2、现象（错误信息） ###

Caused by: java.sql.SQLException: sql injection violation, multi-statement not allow : 
    insert into public_serve(`type`,  community_id, result, create_time ) 
    values (?,  ?,  ?, ? ) ; 
    insert into public_serve(`type`,  community_id, result, create_time ) 
    values (?,  ?,  ?, ? ) ; 
    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:568)
    	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:568)
    	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
    	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:349)
    	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:498)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    	at com.sun.proxy.$Proxy214.update(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
    	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:498)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    	... 56 more

### 3、原因 ###

需要设置过滤器 **WallFilter** 的配置： **WallConfig** 的参数 **multiStatementAllow** 为true，默认情况下false不允许批量操作

### 4、解决 ###

package com.ejudata.platform.config;
    
    import com.alibaba.druid.filter.Filter;
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.wall.WallConfig;
    import com.alibaba.druid.wall.WallFilter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    import java.util.ArrayList;
    import java.util.List;
    
    
    @Configuration
    public class DruidConfig {
        
    
    	//使用连接池dataSource
        @Bean
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource druidDataSource() {
        
            DruidDataSource druidDataSource = new DruidDataSource();
            List<Filter> filterList = new ArrayList<>();
            filterList.add(wallFilter());
            druidDataSource.setProxyFilters(filterList);
            return druidDataSource;
    
        }
    
        @Bean
        public WallFilter wallFilter() {
        
            WallFilter wallFilter = new WallFilter();
            wallFilter.setConfig(wallConfig());
            return wallFilter;
        }
    
        @Bean
        public WallConfig wallConfig() {
        
            WallConfig config = new WallConfig();
            config.setMultiStatementAllow(true);//允许一次执行多条语句
            config.setNoneBaseStatementAllow(true);//允许非基本语句的其他语句
            return config;
        }
    }

### 5、参考 ###

[https://github.com/alibaba/druid/wiki/配置-wallfilter][https_github.com_alibaba_druid_wiki_-wallfilter]

[https_blog.csdn.net_weixin_43453386_article_details_84064498]: https://blog.csdn.net/weixin_43453386/article/details/84064498
[https_github.com_alibaba_druid_wiki_-wallfilter]: https://github.com/alibaba/druid/wiki/%E9%85%8D%E7%BD%AE-wallfilter