mybatis-plus分页插件排序sql注入处理

问题描述

在使用mybatis-plus进行数据查询中，对于简单分页查询可直接继承官方提供的分页类 com.baomidou.mybatisplus.extension.plugins.pagination.Page 作为接口参数实体类传入，传入参数例子如下：

{
    "size": 10,
    "cureent": 1,
    "orders": [
        {
            "column": "create_time",
            "asc": false
        }
    ]
}

可见在排序策略是可以通过参数动态传入的，但是此参数并未严格校验，例如可如下例子传入：

{
    "size": 10,
    "cureent": 1,
    "orders": [
        {
            "column": "1=cast(select version()::text as numric)",
            "asc": false
        }
    ]
}

执行肯定是会报错的，但是异常不处理的情况下直接暴露到接口返回结果中，则会暴露相关信息。

所以问题出现的条件如下：
1、使用Page 作为实体作为接口参数且未特殊处理
2、产生异常未统一处理

问题处理

这个问题很容易解决，只需要加入验证、使用自定义的分页实体或者在接口报错时对报错信息进行处理。

• 通过mybatis-plus分页插件拦截器处理
  1、自定义分页拦截器

public class CustomPaginationInnerInterceptor extends PaginationInnerInterceptor {

    /**
     * 特殊字符正则匹配.
     */
    String regEx = ".*[\\s`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？\\\\]+.*";
    /**
     * 关键字集合.
     */
    Set<String> keySet = new HashSet<>(Arrays.asList("select "," and ", " or ", " xor ", " where "));

    /**
     * Instantiates a new Custom pagination inner interceptor.
     *
     * @param dbType the db type
     */
    public CustomPaginationInnerInterceptor(DbType dbType) {
        super(dbType);
    }

    /**
     * Instantiates a new Custom pagination inner interceptor.
     *
     * @param dialect the dialect
     */
    public CustomPaginationInnerInterceptor(IDialect dialect) {
        super(dialect);
    }

    @Override
    protected List<OrderByElement> addOrderByElements(List<OrderItem> orderList, List<OrderByElement> orderByElements) {
        List<OrderByElement> additionalOrderBy = orderList.stream()
                .filter(item -> StringUtils.isNotBlank(item.getColumn()))
                .map(item -> {
                    OrderByElement element = new OrderByElement();
                    String column = item.getColumn();
                    // 正则匹配，抛出自定义异常
                    if (ReUtil.isMatch(regEx, column)) {
                        throw new MybatisPageException("条件异常，请检查筛选条件是否存在特殊字符");
                    }
                    // 关键字匹配，抛出自定义异常
                    String lowerCase = column.toLowerCase();
                    for (String key : keySet) {
                        if (lowerCase.contains(key)) {
                            throw new MybatisPageException("条件异常，请检查筛选条件是否存在特殊字符");
                        }
                    }
                    element.setExpression(new Column(column));
                    element.setAsc(item.isAsc());
                    element.setAscDescPresent(true);
                    return element;
                }).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(orderByElements)) {
            return additionalOrderBy;
        }
        orderByElements.addAll(additionalOrderBy);
        return orderByElements;
    }

    @Override
    public String concatOrderBy(String originalSql, List<OrderItem> orderList) {
        try {
            Select select = (Select) CCJSqlParserUtil.parse(originalSql);
            SelectBody selectBody = select.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
                List<OrderByElement> orderByElementsReturn = addOrderByElements(orderList, orderByElements);
                plainSelect.setOrderByElements(orderByElementsReturn);
                return select.toString();
            } else if (selectBody instanceof SetOperationList) {
                SetOperationList setOperationList = (SetOperationList) selectBody;
                List<OrderByElement> orderByElements = setOperationList.getOrderByElements();
                List<OrderByElement> orderByElementsReturn = addOrderByElements(orderList, orderByElements);
                setOperationList.setOrderByElements(orderByElementsReturn);
                return select.toString();
            } else if (selectBody instanceof WithItem) {
                // todo: don't known how to resole
                return originalSql;
            } else {
                return originalSql;
            }
        } catch (JSQLParserException e) {
            logger.warn("failed to concat orderBy from IPage, exception:\n" + e.getCause());
        } catch (MybatisPageException e) {
            throw new MybatisPageException(e.getMsg());
        } catch (Exception e) {
            logger.warn("failed to concat orderBy from IPage, exception:\n" + e);
        }
        return originalSql;
    }

2、注入到容器

@Configuration(proxyBeanMethods = false)
@ConditionalOnSingleCandidate(DataSource.class)
@ConditionalOnClass(MybatisConfiguration.class)
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    @ConditionalOnMissingBean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new CustomPaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return interceptor;
    }
}

3、统一异常捕获
可如下进行自定义处理报错信息

public class MybatisPageException  {

    /**
    * 报错信息
    */
    private final String msg;
    
    /**
     * Instantiates a new Custom exception.
     *
     * @param msg the msg
     */
    public MybatisPageException(String msg) {
        super(new RuntimeException(msg));
        this.msg = msg;
    }
    
    /**
     * Gets msg.
     *
     * @return the msg
     */
    public String getMsg() {
        return this.msg;
    }

@ControllerAdvice
@Slf4j
public class ExceptionHandle {

    @ExceptionHandler(value = Exception.class)
    @ResponseBody
    public Result<?> handle(Exception e) {
        log.error(e.getMessage());
        StringBuilder stringBuilder = new StringBuilder();
        if (e instanceof MyBatisSystemException) {
            MyBatisSystemException ex = (MyBatisSystemException) e;
            Throwable cause = ex.getCause();
            stringBuilder.append("系统发生运行时错误:");
            if (cause instanceof PersistenceException){
                PersistenceException  ex1 = (PersistenceException)cause;
                Throwable cause1 = ex1.getCause();
                if (cause1 instanceof MybatisPageException){
                    MybatisPageException ex2 = (MybatisPageException)cause1;
                    stringBuilder.append(ex2.getMsg());

                }
            }

        }  else {
            stringBuilder.append("未知错误:" + e.getMessage());
        }
        return Result.failed(stringBuilder.toString());
    }

}