mybatis自定义sql非法字符拦截器

1、自定义拦截器

/**
* 自定义sql非法字符拦截器
*/
@Component
@Intercepts({
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
    @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 MySqlInterceptor implements Interceptor {
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
        Object parameter = null;
        if(invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        if(isLegalSql(boundSql)){
            // 如果sql不包含非法字符则放行
            return invocation.proceed();
        } else {
            Configuration configuration = mappedStatement.getConfiguration();
            String sql = showSql(configuration, boundSql);
            throw new Exception("sql语句:[" + sql + "]存在安全隐患，拒绝执行");
        }
    }

    /**
    * 检查sql是否合法
    */
    private boolean isLegalSql(BoundSql boundSql) {
        String sql = boundSql.getSql();
        if(StringUtil.isEmpty(sql) || (!sql.toLowerCase().contains("where"))) return false;
        sql = org.springframework.util.StringUtils.trimAllWhitespace(sql);
        if(s.contains("1=1") && !s.toLowerCase().contains("1=1and")) return false;

        return true;
    }

    private String showSql(Configuration configuration, BoundSql boundSql){
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if(parameterMappings.size() > 0 && parameterObject != null){
            TypeHandlerRegistry thr = configuration.getTypeHandlerRegistry();
            if(thr.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for(ParameterMapping parameterMapping : parameterMappings){
                    String propertyName = parameterMapping.getProperty();
                    if(metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    } else if(boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    }
                }
            }
        }
        return sql;
    }

    private String getParameterValue(Object obj) {
        String value = null;
        if(obj instanceof String){
            // 已经包含引号
            String tmp = obj.toString();
            value = tmp.contains("'") ? tmp : "'" + tmp + "'";
        } else if(obj instanceof Date){
            FastDateFormat dateFormat = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss");
            value = "'" + dateFormat.format(obj) + "'";
        } else {
            value = obj != null ? obj.toString() : "'" + " " + "'";
        }
        return value;
    }
}

2、注册拦截器bean

@Configuration
public class MybatisSqlInterceptConfig {
    
    @Bean
    public MySqlInterceptor sqlInterceptor() {
        return new MySqlInterceptor();
    }
}