springboot druid连接池多数据源SQL错误sql injection violation, multi-statement not

小鱼儿 2023-01-16 11:14 977阅读 0赞

> 一个小错误而已，为嘛专门写一个博客  
> 主要是这个问题还不太好解决，一开始直接在sql连接参数常规性加上允许多条sql的参数，结果还是不好使，面向谷歌编程后改了yml配置依然不生效。  
> 直接上github看issue的时候发现别人的解决办法，但是并不适用我，俺用的dynamic-datasource-spring-boot-starter多数据源，其他人的话下列链接应该大部分都能解决问题了。  
> 相似问题的github解决办法：[https://github.com/alibaba/druid/issues/980][https_github.com_alibaba_druid_issues_980]

**解决方式**

1.加上连接参数

jdbc:mysql://127.0.0.1:3306/tableName?allowMultiQueries=true

2.自定义初始化数据源实现

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.wall.WallConfig;
    import com.alibaba.druid.wall.WallFilter;
    import com.baomidou.dynamic.datasource.creator.DataSourceCreator;
    import com.baomidou.dynamic.datasource.ds.ItemDataSource;
    import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
    import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
    import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Primary;
    import org.springframework.stereotype.Service;
    
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    
    /** * @author 954L * @create 2021/4/23 14:13 */
    @Service
    @Primary
    public class MyDynamicDataSourceProvider implements DynamicDataSourceProvider{ 
    
        @Autowired
        private DataSourceCreator dataSourceCreator;
    
        @Autowired
        private DynamicDataSourceProperties properties;
    
        @Override
        public Map<String, DataSource> loadDataSources() { 
            Map<String, DataSourceProperty> dataSourcePropertiesMap = properties.getDatasource();
            Map<String, DataSource> dataSourceMap = new HashMap(dataSourcePropertiesMap.size() * 2);
            Iterator var3 = dataSourcePropertiesMap.entrySet().iterator();
    
            while(var3.hasNext()) { 
                Map.Entry<String, DataSourceProperty> item = (Map.Entry)var3.next();
                DataSourceProperty dataSourceProperty = (DataSourceProperty)item.getValue();
                String poolName = dataSourceProperty.getPoolName();
                if (poolName == null || "".equals(poolName)) { 
                    poolName = (String)item.getKey();
                }
                dataSourceProperty.setPoolName(poolName);
                DataSource dataSource = this.dataSourceCreator.createDataSource(dataSourceProperty);
                DruidDataSource druidDataSource = (DruidDataSource) ((ItemDataSource) dataSource).getDataSource();
                druidDataSource.getProxyFilters().forEach(x -> { 
                    if (x instanceof WallFilter){ 
                        WallFilter wallFilter = ((WallFilter)x);
                        WallConfig wallConfig = wallFilter.getConfig();
                        wallConfig.setMultiStatementAllow(true);
                        wallConfig.setNoneBaseStatementAllow(true);
                    }
                });
                dataSourceMap.put(poolName, dataSource);
            }
            return dataSourceMap;
        }
    
    }

注：yml配置不变，该配置数据库url、password什么得还是一样不变

**大致解释下**  
![20210425164828482.png][]  
**该多数据源的默认实现是YmlDynamicDataSourceProvider**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3draF9fXw_size_16_color_FFFFFF_t_70][]

> YmlDynamicDataSourceProvider实例化的时候是调用它爹AbstractDataSourceProvider去加载datasource返回多数据源。所以思路是在创建datasource的时候把wallFilter的配置改了即可

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3draF9fXw_size_16_color_FFFFFF_t_70 1][]

> 可以看一下它爹的代码，我自定义的实现基本一致，可以跟上文对比一下，俺主要就是修改一下创建后的datasource然后返回即可。这么简单的一个配置在yml里配置不生效…头疼，然后就得自定义一个实现。

对你有帮助的话，给个赞呗~

[https_github.com_alibaba_druid_issues_980]: https://github.com/alibaba/druid/issues/980
[20210425164828482.png]: /images/20221022/d4922048b37a417a91055a52321df6ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3draF9fXw_size_16_color_FFFFFF_t_70]: /images/20221022/f6a24983f5314bac9fe7b6f586720d7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3draF9fXw_size_16_color_FFFFFF_t_70 1]: /images/20221022/bba705d37f9c47d99f85d0ffe1080a10.png