search

alibaba / druid

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

Druid1.2.5版本对clickhouse的部分SQL语法不支持，WallFilter检测报错 #4235

Open huangjiabin2020 opened 3 years ago

huangjiabin2020 commented 3 years ago

我要执行的SQL如下： ALTER TABLE t_test1 UPDATE name = '张三' WHERE ck_f_id = '1' 在clickHouse上执行 完全正常，如下： image 但是，一旦我试图在项目中运行这条SQL，就报错，打断点发现在 WallProvider.class的630行，这条SQL被解析异常，直接被ParserException捕获，最后报错，如下： image

Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ? at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 89 common frames omitted Caused by: com.alibaba.druid.sql.parser.ParserException: Invalid from clause : name = ? at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSourceQueryTableExpr(SQLSelectParser.java:1124) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1098) at com.alibaba.druid.sql.parser.SQLStatementParser.parseUpdateStatement(SQLStatementParser.java:4208) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:243) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:124) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:630) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:584) at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:826) ... 104 common frames omitted

stone5751 commented 3 years ago

我要执行的SQL如下： ALTER TABLE t_test1 UPDATE name = '张三' WHERE ck_f_id = '1' 在clickHouse上执行 完全正常，如下： image 但是，一旦我试图在项目中运行这条SQL，就报错，打断点发现在 WallProvider.class的630行，这条SQL被解析异常，直接被ParserException捕获，最后报错，如下： image

Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ? at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 89 common frames omitted Caused by: com.alibaba.druid.sql.parser.ParserException: Invalid from clause : name = ? at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSourceQueryTableExpr(SQLSelectParser.java:1124) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1098) at com.alibaba.druid.sql.parser.SQLStatementParser.parseUpdateStatement(SQLStatementParser.java:4208) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:243) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:124) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:630) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:584) at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:826) ... 104 common frames omitted

name改成`name`试试，这个是关键字

huangjiabin2020 commented 3 years ago

我要执行的SQL如下： ALTER TABLE t_test1 UPDATE name = '张三' WHERE ck_f_id = '1' 在clickHouse上执行 完全正常，如下： image 但是，一旦我试图在项目中运行这条SQL，就报错，打断点发现在 WallProvider.class的630行，这条SQL被解析异常，直接被ParserException捕获，最后报错，如下： image Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ? at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 89 common frames omitted Caused by: com.alibaba.druid.sql.parser.ParserException: Invalid from clause : name = ? at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSourceQueryTableExpr(SQLSelectParser.java:1124) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1098) at com.alibaba.druid.sql.parser.SQLStatementParser.parseUpdateStatement(SQLStatementParser.java:4208) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:243) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:124) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:630) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:584) at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:826) ... 104 common frames omitted

name改成name试试，这个是关键字

试过了，首先 name不能改成 'name'，运行就会报错，如下： image

其次，就算在项目里改成 'name'，报错如下： Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'DATE 'name' = ? WHERE ck_f_id = ?', expect SET, actual =, pos 36, line 1, column 35, token =

这显然是druid解析不了ck的修改字段值SQL语句，不支持这种语法

stone5751 commented 3 years ago

我要执行的SQL如下： ALTER TABLE t_test1 UPDATE name = '张三' WHERE ck_f_id = '1' 在clickHouse上执行 完全正常，如下： image 但是，一旦我试图在项目中运行这条SQL，就报错，打断点发现在 WallProvider.class的630行，这条SQL被解析异常，直接被ParserException捕获，最后报错，如下： image Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ? at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 89 common frames omitted Caused by: com.alibaba.druid.sql.parser.ParserException: Invalid from clause : name = ? at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSourceQueryTableExpr(SQLSelectParser.java:1124) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1098) at com.alibaba.druid.sql.parser.SQLStatementParser.parseUpdateStatement(SQLStatementParser.java:4208) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:243) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:124) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:630) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:584) at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:826) ... 104 common frames omitted

name改成name试试，这个是关键字

试过了，首先 name不能改成 'name'，运行就会报错，如下： image

其次，就算在项目里改成 'name'，报错如下： Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'DATE 'name' = ? WHERE ck_f_id = ?', expect SET, actual =, pos 36, line 1, column 35, token =

这显然是druid解析不了ck的修改字段值SQL语句，不支持这种语法

是斜撇，不是引号，数字1前面的那个键

huangjiabin2020 commented 3 years ago

我要执行的SQL如下： ALTER TABLE t_test1 UPDATE name = '张三' WHERE ck_f_id = '1' 在clickHouse上执行 完全正常，如下： image 但是，一旦我试图在项目中运行这条SQL，就报错，打断点发现在 WallProvider.class的630行，这条SQL被解析异常，直接被ParserException捕获，最后报错，如下： image Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ? at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 89 common frames omitted Caused by: com.alibaba.druid.sql.parser.ParserException: Invalid from clause : name = ? at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSourceQueryTableExpr(SQLSelectParser.java:1124) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1098) at com.alibaba.druid.sql.parser.SQLStatementParser.parseUpdateStatement(SQLStatementParser.java:4208) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:243) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:124) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:630) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:584) at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:826) ... 104 common frames omitted

name改成name试试，这个是关键字

试过了，首先 name不能改成 'name'，运行就会报错，如下： image 其次，就算在项目里改成 'name'，报错如下： Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'DATE 'name' = ? WHERE ck_f_id = ?', expect SET, actual =, pos 36, line 1, column 35, token = 这显然是druid解析不了ck的修改字段值SQL语句，不支持这种语法

是斜撇，不是引号，数字1前面的那个键

我试过了，在ck是可以运行的，如下： image dan's 但是在项目里，还是报错，如下： image

Cause: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ?

zhouleijie commented 2 years ago

我要执行的SQL如下： ALTER TABLE t_test1 UPDATE name = '张三' WHERE ck_f_id = '1' 在clickHouse上执行 完全正常，如下： image 但是，一旦我试图在项目中运行这条SQL，就报错，打断点发现在 WallProvider.class的630行，这条SQL被解析异常，直接被ParserException捕获，最后报错，如下： image Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.5, syntax error: Invalid from clause : name = ? : ALTER TABLE t_test1 UPDATE name = ? WHERE ck_f_id = ? at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849) at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351) at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:53) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 89 common frames omitted Caused by: com.alibaba.druid.sql.parser.ParserException: Invalid from clause : name = ? at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSourceQueryTableExpr(SQLSelectParser.java:1124) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1098) at com.alibaba.druid.sql.parser.SQLStatementParser.parseUpdateStatement(SQLStatementParser.java:4208) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:243) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:124) at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:630) at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:584) at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:826) ... 104 common frames omitted

name改成name试试，这个是关键字

试过了，首先 name不能改成 'name'，运行就会报错，如下： image 其次，就算在项目里改成 'name'，报错如下： Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'DATE 'name' = ? WHERE ck_f_id = ?', expect SET, actual =, pos 36, line 1, column 35, token = 这显然是druid解析不了ck的修改字段值SQL语句，不支持这种语法

是斜撇，不是引号，数字1前面的那个键

请问这个是怎么解决的啊，ch的版本是21.10.2.15，druid的是1.2.5

zhouleijie commented 2 years ago

大佬

weiqiangma commented 2 years ago

多数据源的情况下依然会报错，可以尝试配置strict-syntax-check

ZhangY18 commented 1 year ago

你好 问题有得到解决或者planB 么

adminkk commented 1 year ago

配置strict-syntax-check可以的

adminkk commented 1 year ago

官方大佬有时间，麻烦兼容下clickhouse的update语法

simory commented 3 months ago

v1.2.8仍然没解决clickhouse update语法检查失败的问题。除了配置strict-syntax-check 如果是全yml配置的，通过spring.datasource.dynamic.druid.filters=stat 配置去除wall的WallFilter防火墙配置，避开clickhouse语法检查也可以达到效果。默认的配置是spring.datasource.dynamic.druid.filters=stat,wall,log4j