当前位置：首页 > article >正文

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

article 2024/11/13 8:55:53

安全问题:有风险的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 可以为任意字符  建议复杂度高点 如果重复 会造成替换bug
        tables.add("C55EA8171877E962E08DFF63AA367884123");
        return tables;
    }

    @Autowired
    private List<SqlSessionFactory> sqlSessionFactoryList;

    @PostConstruct
    public 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 只能实现表名替换 所以这里我们优化了使用我们自己的拦截器LizzMybatisIntercepts
        LizzMybatisIntercepts 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 {

    @Override
    public 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);

        }});