sql injection violation, syntax error:token LPAREN

created at 07-05-2021 views: 1

description

Use druid to query MySql with as and report the following error:

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 abnormal information, it is the error reported by druid parsing SQL. 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)

solution

This SQL can be executed normally in the MySql workbeach tool. Finally, when druid is parsing the SQL, it seems that the SQL cannot end with a closing parenthesis. The final modification 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 "select * from" part of the two selects in the last union all is newly added, so that the problem can be solved by letting SQL not end with a right parenthesis.

Please log in to leave a comment.