alibaba / druid

阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品,为监控而生的数据库连接池
https://github.com/alibaba/druid/wiki
Apache License 2.0
27.76k stars 8.55k forks source link

pgsql在等值查询条件下,已单反斜杠结尾会报语法错误 #5978

Open aofq opened 3 weeks ago

aofq commented 3 weeks ago
    pgsql 在9.1版本以后,默认开启了standard_conforming_strings,该开关简单来说,\失去了转义的意思,表示单反斜杠时,用\即可以表示,不需要用\\表示
            在使用sqlStatementParser.parseStatement查询时,以单反斜杠结尾的查询条件会报语法错误,但是这样的语句可以在pgsql正确执行并返回预期结果,比如
            select * from table where aa = '\';
            select * from table where aa = '张三\';
            select * from table where aa in ('张三\\');
    以上语句均不能通过语法检查,但是在pgsql能正确执行;

当以单反斜杠作为等值查询

` public static void main(String[] args) {

    // 单反斜杠在中间
    String sql1 = "select field from table where name = '张三\\张三'";
    // in查询,单反斜杠结尾
    String sql2 = "select field from table where name in ('张三','李四')";
    // 单反斜杠结尾
    String sql3 = "select abc from tables where name = '\\'";
    // 单反斜杠结尾
    String sql4 = "select field form tables where name = '张三\\'";
    System.out.println(parseSql("sql1:"+ sql1));
    System.out.println(parseSql("sql2:"+sql2));
    System.out.println(parseSql("sql3:"+sql3));
    System.out.println(parseSql("sql4:"+sql4));
}
static String parseSql(String sql){
    SQLStatementParser sqlStatementParser = SQLParserUtils.createSQLStatementParser(sql, DbType.postgresql);
    SQLStatement statement = sqlStatementParser.parseStatement();
    return statement.toString();
}`