jsqlparser无法解析带if语句问题

最新推荐文章于 2024-08-08 07:18:59 发布

起风哥

最新推荐文章于 2024-08-08 07:18:59 发布

阅读量2.1k

点赞数

文章标签： mybatis java sql

本文链接：https://blog.csdn.net/a807719447/article/details/128732926

版权

项目中引入了sharding-sphere encry 包做数据脱敏版本 4.0.1
mybatis-plus版本用的是3.1.0使用内置分页插件，默认引用的jsqlparser版本是1.4，maven中央库的版本范围是1.4-4.5，实际上仅支持到1.4

业务上报了一个奇怪的错误，一下sql语句数组越界

SELECT COUNT(1) FROM ( select u.name,u.telephone,if(u.age>18,'成年','未成年') as ageType,
        from user u
        where u.deleted = 0
            AND u.telephone = ?)

这个语句是由mybatis-plus生成的sql，理论上生成出来的应该是优化过的sql，如下

SELECT COUNT(1) FROM 
        from user u
        where u.deleted = 0
            AND u.telephone = ?

但是为什么用了源sql，这是第一个问题，sql优化使用的是jsqlparser-1.4版本，如果能优化就会生成上面的sql，如果抛异常使用原sql。为什么如此简单的sql会抛异常呢？

于是写了个测试用例，新建一个空项目引入对于版本包

<dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>1.4</version>
        </dependency>

用例

public class JsqlparserTest {
    public static final Logger logger= LoggerFactory.getLogger(JsqlparserTest.class);
    
    public static void main(String[] args) throws JSQLParserException {
        String sql ="SELECT COUNT(1) FROM ( select u.name,u.telephone,if(u.age>18,'成年','未成年') as ageType,\n"
            + "        from user u\n"
            + "        where u.deleted = 0\n"
            + "            AND u.telephone = ?)";
        Select selectStatement = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
        SqlInfo parser = parser(sql);
    
        System.out.println("xxx");
    }
    
    public static SqlInfo parser(String sql) {
        if (logger.isDebugEnabled()) {
            logger.debug(" JsqlParserCountOptimize sql=" + sql);
        }
        SqlInfo sqlInfo = SqlInfo.newInstance();
        try {
            Select selectStatement = (Select) CCJSqlParserUtil.parse(sql);
            PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();
            Distinct distinct = plainSelect.getDistinct();
            List<OrderByElement> orderBy = plainSelect.getOrderByElements();
            List<Expression> groupBy = plainSelect.getGroupByColumnReferences();
            // 优化 SQL
            plainSelect.setSelectItems(countSelectItem());
            sqlInfo.setSql(selectStatement.toString());
            return sqlInfo;
        } catch (Throwable e) {
            // 无法优化使用原 SQL
            return null;
        }
    }
    private static List<SelectItem> countSelectItem() {
        Function function = new Function();
        function.setName("COUNT");
        List<Expression> expressions = new ArrayList<>();
        LongValue longValue = new LongValue(1);
        ExpressionList expressionList = new ExpressionList();
        expressions.add(longValue);
        expressionList.setExpressions(expressions);
        function.setParameters(expressionList);
        List<SelectItem> selectItems = new ArrayList<>();
        SelectExpressionItem selectExpressionItem = new SelectExpressionItem(function);
        selectItems.add(selectExpressionItem);
        return selectItems;
    }

}

执行报错如下

Exception in thread "main" net.sf.jsqlparser.JSQLParserException
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:55)
	at com.example.democ.JsqlparserTest.main(JsqlparserTest.java:34)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "if" "IF"
    at line 1, column 50.

Was expecting one of:
...此处省略
Process finished with exit code 1

异常说的是不支持if语句，这个也好办，使用case when 代替就好了。
第二个问题，即使这里优化不成功，使用原sql为什么sharding-sphere会报数组越界呢？
接着看数组越界的位置

@RequiredArgsConstructor
@Getter
public abstract class AbstractSQLBuilder implements SQLBuilder {
    
    private final SQLRewriteContext context;
    
    @Override
    public final String toSQL() {
        if (context.getSqlTokens().isEmpty()) {
            return context.getSql();
        }
        Collections.sort(context.getSqlTokens());
        StringBuilder result = new StringBuilder();
        result.append(context.getSql().substring(0, context.getSqlTokens().get(0).getStartIndex()));
        for (SQLToken each : context.getSqlTokens()) {
            result.append(getSQLTokenText(each));
            result.append(getConjunctionText(each));
        }
        return result.toString();
    }
    
    protected abstract String getSQLTokenText(SQLToken sqlToken);
    
    private String getConjunctionText(final SQLToken sqlToken) {
        return context.getSql().substring(getStartIndex(sqlToken), getStopIndex(sqlToken));
    }
    
    private int getStartIndex(final SQLToken sqlToken) {
        int startIndex = sqlToken instanceof Substitutable ? ((Substitutable) sqlToken).getStopIndex() + 1 : sqlToken.getStartIndex();
        return Math.min(startIndex, context.getSql().length());
    }
    
    private int getStopIndex(final SQLToken sqlToken) {
        int currentSQLTokenIndex = context.getSqlTokens().indexOf(sqlToken);
        return context.getSqlTokens().size() - 1 == currentSQLTokenIndex ? context.getSql().length() : context.getSqlTokens().get(currentSQLTokenIndex + 1).getStartIndex();
    }
}

我们配置了telephone 字段加密，此时发现sql token集合中出现了4个元素两个telephone 两个？意味着此处投影中的 telephone片段也被作为条件恶意取了个问号填冲进去，原因是它在子查询中。详见EncryptProjectionTokenGenerator
然后升级了个版本4.1.1之后发现，这个类做了一个更新
片段如下

    private SubstitutableColumnNameToken generateSQLToken(ColumnProjectionSegment segment, String tableName) {
        String encryptColumnName = this.getEncryptColumnName(tableName, segment.getColumn().getIdentifier().getValue());
        if (!segment.getAlias().isPresent()) {
            encryptColumnName = encryptColumnName + " AS " + segment.getColumn().getIdentifier().getValue();
        }

        return segment.getColumn().getOwner().isPresent() ? new SubstitutableColumnNameToken(((OwnerSegment)segment.getColumn().getOwner().get()).getStopIndex() + 2, segment.getStopIndex(), encryptColumnName) : new SubstitutableColumnNameToken(segment.getStartIndex(), segment.getStopIndex(), encryptColumnName);
    }