sql injection violation, syntax error:token LPAREN

Time:2021-4-16

Using druid to query MySQL with as, the following error is reported:
Error querying database. Cause: java.sql.SQLException: sql injection violation, syntax error: TODO. pos 782, line 25, column 5, token LPAREN : with g as ( 。。。
uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, syntax error: TODO. pos 782, line 25, column 5, token LPAREN : with g as (。。。
Caused by: com.alibaba.druid.sql.parser.ParserException: TODO. pos 782, line 25, column 5, token LPAREN

at com.alibaba.druid.sql.parser.SQLStatementParser.parseWith(SQLStatementParser.java:3336)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:242)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:182)
at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:624)
at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:578)
at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:793)
... 114 common frames omitted

From the above caused by: part of the exception information, it is the error of Druid parsing SQL report. The complete SQL is as follows:
with g as (

        select
            COUNT(IF(DATE_FORMAT(t.so_order_date, '%Y') = '2020', 1, NULL)) as c2020,
            COUNT(IF(DATE_FORMAT(t.so_order_date, '%Y') = '2019', 1, NULL)) as c2019,
            t.so_city_code
        from (
            SELECT
                o.so_order_date,o.so_city_code,o.so_id
            FROM
                cor_service_order o
            WHERE
                o.so_order_date <= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
                AND o.so_order_date >= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
            UNION ALL
            SELECT
                d.so_order_date,d.so_city_code,d.so_id
            FROM
                cor_delete_order d
            WHERE
                d.robber_del_date IS NOT NULL
                AND d.issuer_del_date IS NOT NULL
                and d.so_order_date <= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
                AND d.so_order_date >= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
        ) t group by t.so_city_code
    )
    (select so_city_code,c2020,-1 c2019 from g order by g.c2020 desc limit 6)
    union all
    (select so_city_code,-1 c2020,c2019 from g order by g.c2019 desc limit 6)

This SQL can be executed normally in MySQL workbench tool. At last, when Druid parses SQL, it seems that SQL cannot end with right bracket. Finally, the transformation is as follows:
with g as (

        select
            COUNT(IF(DATE_FORMAT(t.so_order_date, '%Y') = '2020', 1, NULL)) as c2020,
            COUNT(IF(DATE_FORMAT(t.so_order_date, '%Y') = '2019', 1, NULL)) as c2019,
            t.so_city_code
        from (
            SELECT
                o.so_order_date,o.so_city_code,o.so_id
            FROM
                cor_service_order o
            WHERE
                o.so_order_date <= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
                AND o.so_order_date >= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
            UNION ALL
            SELECT
                d.so_order_date,d.so_city_code,d.so_id
            FROM
                cor_delete_order d
            WHERE
                d.robber_del_date IS NOT NULL
                AND d.issuer_del_date IS NOT NULL
                and d.so_order_date <= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
                AND d.so_order_date >= STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')
        ) t group by t.so_city_code
    )
    select * from (select so_city_code,c2020,-1 c2019 from g order by g.c2020 desc limit 6) g1
    union all
    select * from (select so_city_code,-1 c2020,c2019 from g order by g.c2019 desc limit 6) g2

Look at the last union all. The “select * from” part of the two select items is newly added, so that SQL can solve the problem without ending with right brackets.