Spring Boot 项目中使用 MyBatis 的 @SelectProvider 注解并解决 SQL 注入的问题

一、背景

公司做了一个指标DashBoard项目，展示营业额、成交率、访客率、购买率等。这些指标需要从数仓的宽表中通过 SQL 计算捞取数据并且然后展示出来。同时每个指标都需要不同的维度，比如时间维度、品牌维度、地区维度，计算同期、同比、环比等等。
这个项目的核心点就是动态 SQL 的编写有些 SQL 可能上百行。因此要保证易维护、易排查、可读性、灵活性。第一版的 SQL 写在了 XML 文件中，因为有大量动态 SQL 发现可读性和维护性并不高，因此尝试使用 @SelectProvider 注解的方式生成执行 SQL 。

二、思路

查询 MyBatis 的官网发现可以使用 @SelectProvider + SQL 语句构建器的写法。具体用法可以参考官网（mybatis.org/mybatis-3/z… ； mybatis.org/mybatis-3/z… ）
这种方法的本质还是在代码中拼接完整的 SQL 字符串，只不过可以使用#{}的格式来防止 SQL 注入的问题，并且可以灵活的使用 java 代码中的工具。

三、代码

SelectProviderController.java 接口入口类

@RestController
@RequestMapping(value = "/select")
@Slf4j
public class SelectProviderController {

    @Resource
    public PerDepartService perDepartService;
    @Resource
    public PerDepartMapper perDepartMapper;

    @GetMapping(value = "get")
    public String add() {
        PerDepartPageCriteria criteria = new PerDepartPageCriteria();
        criteria.setId(0L);
        criteria.setPersonnelId("222");
        criteria.setPersonnelName("333");
        criteria.setDeptId("444");
        criteria.setWorkTypeCd("555");
        criteria.setPageSize(0);
        criteria.setPageNum(0);
        List<PerDepartEntity> perDepart = perDepartMapper.getPerDepart(criteria);
        return JSON.toJSONString(perDepart);
    }
}

PerDepartMapper.java

@Mapper
public interface PerDepartMapper extends BaseMapper<PerDepartEntity> {
    @SelectProvider(type = PerDepartBuild.class, method = "buildCommonSql")
    List<PerDepartEntity> getPerDepart(@Param("criteria") PerDepartPageCriteria criteria);
}

PerDepartBuild.java 生成可执行 SQL 的类

public class PerDepartBuild {

    public static String buildCommonSql(final PerDepartPageCriteria criteria) {
        SQL sql = new SQL();
        // 必须有select，否则后面的SQL语句不会拼接
        sql.SELECT("*");
        sql.FROM("per_depart as a");
        if (criteria.getDeptId() != null) {
            sql.INNER_JOIN("depart_info as b on a.depart_id = b.depart_id");
        }
        // 判空可以使用java中的方法
        if (StringUtils.isNotBlank(criteria.getPersonnelName())){
            sql.WHERE("b.name >= #{criteria.personnelName}");
        }
        sql.WHERE("a.data_type = 'RGY'");
        // #{criteria.workTypeCd} 格式可以防止SQL注入。criteria为本方法中入参；workTypeCd为方法入参中的属性字段
        sql.WHERE("c.work_type_cd <= #{criteria.workTypeCd}");
        // SQL中复杂的部分可以单独编写，最后拼接
        sql.WHERE(commonConditionsSql(criteria));
        return sql.toString();
    }

    // 复杂where条件拼接，这里生成一个SQL字符串
    public static String commonConditionsSql(PerDepartPageCriteria criteria) {
        StringBuilder conditions = new StringBuilder();

        // 格式需要，不可删除
        conditions.append(" 1 = 1 ");

        // 如果条件中包含 in 语句不能使用直接拼接的形式可能会有SQL注入问题。
//        if (criteria.getDataScope() != null && !criteria.getDataScope().isEmpty()) {
//            conditions.append(" AND a.data_scope IN (")
//                    .append(criteria.getDataScope().stream().collect(Collectors.joining("','", "'", "'")))
//                    .append(")");
//        }

        // 使用#{}格式可以防止SQL注入。但是List列表中的数据无法直接拼接成#{}格式。因此手动处理。
        if (criteria.getDataScope() != null && !criteria.getDataScope().isEmpty()) {
            HashMap<String, String> hashMap = new HashMap<>();
            int suffix = 1;
            conditions.append(" AND a.data_scope IN (");
            for (String  dataScope : criteria.getDataScope()) {
                // 将列表中的数据转为map，方便mybatis框架获取map中的数据进行替换。
                // key可以自定义但是不能重复，保证下面可以拼接成完整的#{}格式
                hashMap.put("DataScope" + suffix, dataScope);
                // 拼接成#{}格式，该格式不会有SQL注入问题
                conditions.append(" #{criteria.dataScopeMap.DataScope" + suffix + "}, ");
                suffix++;
            }
            // 删除最后一个逗号
            conditions.deleteCharAt(conditions.lastIndexOf(","));
            conditions.append(")");
            // 将转换后的map保存到条件中，方便mybatis框架自动获取。与#{criteria.dataScopeMap.DataScope" + suffix + "}格式对应
            criteria.setDataScopeMap(hashMap);
        }

        return conditions.toString();
    }

    // 可以使用main函数，直接生成可执行的SQL进行检查
    public static void main(String[] args) {
        PerDepartPageCriteria criteria = new PerDepartPageCriteria();
        criteria.setId(0L);
        criteria.setPersonnelId("222");
        criteria.setPersonnelName("333");
        criteria.setDeptId("444");
        criteria.setWorkTypeCd("555");
        criteria.setPageSize(0);
        criteria.setPageNum(0);
        String sql = buildCommonSql(criteria)
                .replace("#{criteria.personnelName}",criteria.getPersonnelName())
                .replace("#{criteria.deptId}",criteria.getDeptId())
                .replace("#{criteria.workTypeCd}",criteria.getWorkTypeCd());
        System.out.println(sql);
    }
}

PerDepartPageCriteria.java 条件类

@Data
@NoArgsConstructor
public class PerDepartPageCriteria extends Page implements Serializable {
    private static final long serialVersionUID = 756376742909714318L;

    private String personnelId;

    private String personnelName;

    private String deptId;

    private String workTypeCd;

    private List<String> dataScope;

    // 防止 SQL 注入所需要的属性
    private Map<String,String> dataScopeMap;
}

四、最后

普通的拼接直接使用 #{} 格式即可。针对于 in 语句不能直接拼接 List 列表中的结果，需要把 List 中的每个值都拼接一个 #{} 格式，让 MyBatis 框架自动替换。
两种写法都有各自的优缺点，可以项目需求自行选择。