如何确定jdbc预编译sql中的?位置

前景介绍

最近接到个新任务，需要利用mybatis拦截器对sql语句中的limit ?,xxx中的？进行拦截，数值过大则拒绝此sql执行

思考

之前想到了，利用sql字段中的？字段进行分组，再遍历 确定limit后问号的位置，再取其值判断大小

问题

由于sql的不确定性，该想法暴露了很多问题，如select * from xx where xx like ‘xxx?xxx’ and xxx;甚至
select * from xx where xx like ‘xxx?xxx’xxx’ and xx导致问号分组无法继续。

解决

据对jdbc 的了解，我们经常写的一句预编译代码是

    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement(sql);

当我们debug到此时，发现返回的对象其tostring方法中不仅包含了对象地址，还包含了已经讲？替换为占位符的sql，
我们可以点开PreparedStatement可以看到，其为一个接口，其具体实现为ClientPreparedStatement，进入ClientPreparedStatement，找到其toString方法，可以看到其后拼接了一个assql的方法

一路进入assql方法，即为jdbc对占位符的处理，感兴趣的可以自己去研究一下

此为真正将？换为占位符方法

以下为完整代码，防止遗忘

拦截limit 第一个？值

@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
public class SelectLimitInterceptor implements Interceptor {
    private Logger logger = LoggerFactory.getLogger(getClass());
    private final PropertiesConfig config;

    public SelectLimitInterceptor(PropertiesConfig config) {
        this.config = config;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        Statement proceed = (Statement) invocation.proceed();
        if (sql.contains(" limit ")) {
            Connection connection = proceed.getConnection();
            PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
            //拿到预编译sql
            String preSql = preparedStatement.toString();
            //按占位符分割
            String[] split = preSql.split("\\*\\* NOT SPECIFIED \\*\\*");
            for (int i = 0; i < split.length; i++) {
                if (split[i].matches("(|.*) limit\\s+$")) {
                    Map parameterObject = (Map) boundSql.getParameterObject();
                    Object o = parameterObject.get("param" + (i + 1));
                    //考虑分页参数类型 string integer biginteger
                    if (o != null && new BigInteger(o.toString()).compareTo(new BigInteger(config.getPageIndex().toString())) >= 0) {
                        logger.error("sql：{} limit超出限制行数：{}", sql, "100");
                        logger.error("预编译sql：{}", preSql);
                        throw new RuntimeException("sql分页查询太长，禁止查询");
                    }
                }
            }
        }
        return proceed;
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof StatementHandler ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {
    }
}

拦截in中？个数

@Intercepts({@Signature(
        type = StatementHandler.class,
        method = "prepare",
        args = {Connection.class, Integer.class}
)})
public class SelectInSizeInterceptor implements Interceptor {
    private Logger logger = LoggerFactory.getLogger(getClass());
    private final PropertiesConfig config;

    public SelectInSizeInterceptor(PropertiesConfig config) {
        this.config = config;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Statement proceed = (Statement) invocation.proceed();
        if (invocation.getTarget() instanceof StatementHandler) {
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            BoundSql boundSql = statementHandler.getBoundSql();
            String sql = boundSql.getSql();
            //判断sql中是否包含in并且括号中有？
            if (sql.matches("[\\s\\S]*in(|\\s+)\\([\\s\\S]*\\?[\\s\\S]*\\)[\\s\\S]*")) {
                Connection connection = proceed.getConnection();
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
                //拿到预编译sql
                String preSql = preparedStatement.toString();
                //按占位符分割
                String[] split = preSql.split("\\*\\* NOT SPECIFIED \\*\\*");
                int length = split.length;
                int j = 0;
                Boolean flag = false;
                if (length > 0) {
                    for (int i = 0; i < length; i++) {
                        //匹配 in( 或者 in('xxx',
                        if (split[i].matches("^[\\s\\S]*in(|\\s+)\\((|[\\s\\S]*,)$")) {
                            flag = true;
                            j++;
                        } else if (flag && split[i].contains(",")) {
                            j++;
                            //匹配)xxx或者   )xxx
                        } else if (flag && split[i].matches("(|\\s+)\\)[\\s\\S]*")) {
                            flag = false;
                        }
                    }
                }
                //考虑分页参数类型 string integer biginteger
                if (j >= config.getInSize()) {
                    logger.error("sql：{} in参数超出限制,限制个数为：{}", sql, config.getInSize());
                    logger.error("预编译sql：{}", preSql);
                    throw new RuntimeException("sql查询in条件太长，禁止查询");
                }
            }
        }
        return proceed;
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof StatementHandler ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {
    }
}

拦截执行结果，记录条数

@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 ResultInterceptor implements Interceptor {
    private Logger logger = LoggerFactory.getLogger(getClass());
    private final PropertiesConfig config;

    public ResultInterceptor(PropertiesConfig config) {
        this.config = config;
    }

    @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);
        Object result = invocation.proceed();
        if (result instanceof List){
            List lists=(List)result;
            int size = lists.size();
            if (size> config.getResultSize()){
                logger.error("当前sql：{}，返回结果超出限制行：{}行",boundSql.getSql(),config.getResultSize());
            }
        }
        return result;
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof Executor ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {
    }
}