sql injection violation, syntax error:token LPAREN

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《sql injection violation, syntax error:token LPAREN》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

使用druid在查询MySql with as 报如下错：
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

从上面的Caused by:部分异常信息来看，是druid解析SQL报的错，完整的SQL如下：
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')
            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')
        ) 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)

这条SQL在MySql workbeach工具里能正常执行，最后发现druid在解析SQL时，好像SQL不能以右括号结尾，最后改造如下：
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')
            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')
        ) 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

看最后的union all中两条select 中的“select * from ”部分是新添加的，让SQL不以右括号结尾即可解决问题。

文中关于mysql的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《sql injection violation, syntax error:token LPAREN》文章吧，也可关注golang学习网公众号了解相关技术文章。