漏洞扫描——使用boundSql解决sql注入问题解决方案

345 阅读2分钟

这是我参与更文挑战的第2天,活动详情查看: 更文挑战

背景

项目上线前经常会用来进行漏洞扫描,不可避免都会遇到,尤其是甲方爸爸都会强烈要求通过漏洞扫描结果才可以上线。针对本次上线项目遇到的漏洞扫描给予解决方案,针对解决sql注入问题。

扫描结果:SQL InjectIon:MyBatis Mapper

一、引入boundSql进行文本替换:

/**
 * 替换 mapper 中生成的 fields 、 order 占位符 (只为解决漏洞扫描问题)
 * 1、把以前用 ${orderField.fieldName} ${orderField.order} 设置值的代码 设置为 ORDER_FIELD_PLACE_HOLDER_STR
 * 2、插件检查 SQL 在最后执行时替换SQL语句中的 ORDER_FIELD_PLACE_HOLDER_STR 为 真实数据
 * 字段替换标准:
 * 排序字段:ORDER_FIELD_PLACE_HOLDER_STR
 * 排序方式:ORDER_TYPE_PLACE_HOLDER_STR
 * 排序字段和方式:ORDER_FIELD_TYPE_PLACE_HOLDER_STR
 * select字段:FIELDS_PLACE_HOLDER_STR
 */
 
@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
})

public class ReplaceFiledsAndOrderPlaceholderPlugin implements Interceptor {
    private static final Field ADDITIONAL_PARAMETERS_FIELD;
    private static final String ORDER_FIELD_TYPE_PLACE_HOLDER = "ORDER_FIELD_TYPE_PLACE_HOLDER_STR";
    private static final String ORDER_FIELD_PLACE_HOLDER = "ORDER_FIELD_PLACE_HOLDER_STR";
    private static final String ORDER_TYPE_PLACE_HOLDER = "ORDER_TYPE_PLACE_HOLDER_STR";
    private static final String FILEDS_PLACE_HOLDER = "FIELDS_PLACE_HOLDER_STR";
    private static final Pattern ORDER_FIELD_TYPE_PLACE_HOLDER_PATTERN = Pattern.compile(ORDER_FIELD_TYPE_PLACE_HOLDER + "|" + ORDER_FIELD_PLACE_HOLDER + "|" + ORDER_TYPE_PLACE_HOLDER);

    static {
        try {
            ADDITIONAL_PARAMETERS_FIELD = BoundSql.class.getDeclaredField("additionalParameters");
            ReflectionUtils.makeAccessible(ADDITIONAL_PARAMETERS_FIELD);
        } catch (NoSuchFieldException e) {
            throw new HippoRuntimeException("Cannot read the field of additionalParameters from BoundSql.");
        }
    }


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Executor executor = (Executor) invocation.getTarget();
        Object[] args = invocation.getArgs();
        MappedStatement statement = (MappedStatement) args[0];
        Object param = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler<?> resultHandler = (ResultHandler<?>) args[3];
        BoundSql boundSql = statement.getBoundSql(param);
        if (!needReplace(boundSql.getSql())) {
            return invocation.proceed();
        }
         // 创建新的 BoundSql 并设置里面的 sql 为替换后的SQL
        BoundSql newBoundSql = new BoundSql(statement.getConfiguration(), replaceSql(boundSql, param), boundSql.getParameterMappings(), param);

        for (Map.Entry<String, Object> entry : ((Map<String, Object>) ADDITIONAL_PARAMETERS_FIELD.get(boundSql)).entrySet()) {
            newBoundSql.setAdditionalParameter(entry.getKey(), entry.getValue());
        }

        CacheKey cacheKey = executor.createCacheKey(statement, param, rowBounds, newBoundSql);

        return executor.query(statement, param, rowBounds, resultHandler, cacheKey, newBoundSql);
    }

    private boolean needReplace(String sql) {
        return ORDER_FIELD_TYPE_PLACE_HOLDER_PATTERN.matcher(sql).find() || sql.contains(FILEDS_PLACE_HOLDER);
    }

    private String replaceSql(BoundSql boundSql, Object param) {
        String sql = boundSql.getSql();
        if (param instanceof BaseQuery) {
            sql = replaceSql(sql, (BaseQuery) param);
        }

        if (param instanceof List) {
            for (Object o : ((List<?>) param)) {
                if (o instanceof BaseQuery) {
                    sql = replaceSql(sql, (BaseQuery) o);
                    break;
                }
            }
        }
        if (param instanceof MapperMethod.ParamMap) {
            for (Object value : ((MapperMethod.ParamMap) param).values()) {
                if (value instanceof BaseQuery) {
                    sql = replaceSql(sql, (BaseQuery) value);
                    break;
                }
            }
        }
        return sql;
    }
    
    private String replaceSql(String sql, BaseQuery query) { String resultSql = replaceFieldsSql(sql, query.getFields()); List<Query.OrderField> orderFields = query.getOrderFields(); return replaceOrderSql(resultSql, orderFields); }
    
    private String replaceOrderSql(String sql, List<Query.OrderField> orderFields) {   Matcher matcher = ORDER_FIELD_TYPE_PLACE_HOLDER_PATTERN.matcher(sql); 
    int index = 0, preIndex = index;
    StringBuffer buffer = new StringBuffer();
    while (matcher.find()) { String group = matcher.group(); switch (group) { case ORDER_FIELD_TYPE_PLACE_HOLDER: matcher.appendReplacement(buffer, orderFields.get(index).getFieldName() + " " + orderFields.get(index).getOrder()); index++; break;
    case ORDER_FIELD_PLACE_HOLDER: matcher.appendReplacement(buffer, orderFields.get(index).getFieldName()); preIndex = index; index++; break;
    case ORDER_TYPE_PLACE_HOLDER: matcher.appendReplacement(buffer, orderFields.get(preIndex).getOrder()); default: break; }
    
    // 如果多个循环时 直接重置下标
            if (index >= orderFields.size()) {
                index = 0;
            }

        }
        matcher.appendTail(buffer);
        return buffer.toString();
    }
    
    private String replaceFieldsSql(String sql, String fields) { 
        if (sql.contains(FILEDS_PLACE_HOLDER)) { 
            return sql.replace(FILEDS_PLACE_HOLDER, fields == null ? "*" : fields); 
        }
        return sql; 
    }
    
    @Override 
    public Object plugin(Object target) { 
        return Plugin.wrap(target, this); 
    } 
    
    @Override 
    public void setProperties(Properties properties) {
    
    } 
}
    

二、在mybatis配置文件中引入插件

<plugin interceptor="xxx.xxx..ReplaceFiledsAndOrderPlaceholderPlugin"> </plugin>

三、sql中替换对应的字段

<sql id="listOrder"> <if test='orderFields != null and orderFields.size >0'> order by <foreach collection="orderFields" separator="," item="orderField"> <!-- ${orderField.fieldName} ${orderField.order} --> ORDER_FIELD_TYPE_PLACE_HOLDER_STR </foreach> </if> </sql>

总结

大家都熟知的sql注入问题,项目交付时总需要进行安全漏洞的扫描,头疼。除了我们熟知的${} 替换成#{},但有时候并不能满足,比如查询该字段的时候会注入不成功。所以使用了另外一种方式来实现某些不能使用#{}来替换字段的情形。所以遇到这类的问题,我们可以通过boundSql的方式进行替换。