若依数据隔离 ${params.dataScope} 替换 优化为sql 替换

若依数据隔离 ${params.dataScope} 替换 优化为sql 替换

安全问题:有风险的SQL查询：MyBatis解决

若依框架的数据隔离是通过 ${params.dataScope} 实现的 
但是在代码安全扫描的时候$ 符会提示有风险的SQL查询：MyBatis
所以我们这里需要进行优化

参考:
MyBatis-Plus实现动态表名
MyBatis-Plus实现动态表名只能实现表名替换 也就是除了from 后面的$符号都替换不了
所以我们需要进行改造

导入依赖

 <dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.1</version></dependency>

RequestDataHelper

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;import java.util.Map;public class RequestDataHelper {/* 请求参数存取*/private static final ThreadLocal<Map<String, Object>> REQUEST_DATA = new ThreadLocal<>();/* 设置请求参数 @param requestData 请求参数 MAP 对象*/public static void setRequestData(Map<String, Object> requestData) {REQUEST_DATA.set(requestData);}/* 获取请求参数 @param param 请求参数* @return 请求参数 MAP 对象*/public static <T> T getRequestData(String param) {Map<String, Object> dataMap = getRequestData();if (CollectionUtils.isNotEmpty(dataMap)) {return (T) dataMap.get(param);}return null;}/* 获取请求参数 @return 请求参数 MAP 对象*/public static Map<String, Object> getRequestData() {return REQUEST_DATA.get();}
}

MybatisPlusConfig

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.annotation.PostConstruct;
import java.util.ArrayList;
import java.util.List;@Configuration
public class MybatisPlusConfig {static List<String> tableList(){List<String> tables = new ArrayList<>();//表名 C55EA8171877E962E08DFF63AA367884123 可以为任意字符  建议复杂度高点 如果重复 会造成替换bugtables.add("C55EA8171877E962E08DFF63AA367884123");return tables;}@Autowiredprivate List<SqlSessionFactory> sqlSessionFactoryList;@PostConstructpublic void addMyInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// mybatis-plus DynamicTableNameInnerInterceptor 只能实现表名替换 所以这里我们优化了使用我们自己的拦截器LizzMybatisIntercepts
//        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
//        dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
//            String newTable = null;
//            for (String table : tableList()) {
//                newTable = RequestDataHelper.getRequestData(table);
//                if (table.equals(tableName) && newTable!=null){
//                    tableName = newTable;
//                    break;
//                }
//            }
//            return tableName;
//        });// mybatis-plus DynamicTableNameInnerInterceptor 只能实现表名替换 所以这里我们优化了使用我们自己的拦截器LizzMybatisInterceptsLizzMybatisIntercepts lizzMybatisIntercepts = new LizzMybatisIntercepts();interceptor.addInnerInterceptor(lizzMybatisIntercepts);for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {sqlSessionFactory.getConfiguration().addInterceptor(interceptor);}}
}

LizzMybatisIntercepts

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;import java.sql.SQLException;@Slf4j
public class LizzMybatisIntercepts implements InnerInterceptor {@Overridepublic void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {log.info("##beforeQuery");String sql = boundSql.getSql();// 这里是获取到需要替换到的sql 通过 PluginUtils.mpBoundSql(boundSql); 替换   mybatis-plus表名替换源码的原理也是这个String params = RequestDataHelper.getRequestData("C55EA8171877E962E08DFF63AA367884123");String param = sql.replaceAll("C55EA8171877E962E08DFF63AA367884123", params);PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);mpBs.sql(param);}}

测试

mapper

    <select id="getList" resultType="com.wys.entity.User">SELECT *FROM user where name=#{name} and C55EA8171877E962E08DFF63AA367884123</select>

UserController

    @GetMapping("/listUser")public List listUser(){RequestDataHelper.setRequestData(new HashMap<String, Object>() {{put("C55EA8171877E962E08DFF63AA367884123", "1=1");}});User user=new User();user.setName("111111");List list = userMapper.getList(user);return list;}

若依代码替换如下

相当于我们手动从实体类拿出来需要替换的sql 然后塞到我们的拦截器里面 有拦截器去替换

 // 若依数据隔离 ${params.dataScope} 替换 优化sql 替换String dataScope="";Map<String, Object> params = role.getParams();if (params!=null && params.size()>0){dataScope = params.get("dataScope").toString();}String finalDataScope = dataScope;RequestDataHelper.setRequestData(new HashMap<String, Object>() {{put("C55EA8171877E962E08DFF63AA367884123", finalDataScope);}});

修改前后对比图

执行sql打印

可以看到 我们的 sql可以正确替换