[BUG] AS table_name(column_name) #6572
Description
Database Type
postgresql
Database Version
17
Druid Version
1.2.23
JDK Version
23
Error SQL
select *
from (select 1
union all
select 2)
AS serise_table(time),,,
Testcase Code
sql injection violation, dbType postgresql, , druid-version 1.2.23, syntax error: TODO pos 85, line 5, column 26, token IDENTIFIER time : select *
from (select 1
union all
select 2)
AS serise_table(time),,,
Stacktrace Info
org.springframework.jdbc.UncategorizedSQLException:
Error querying database. Cause: java.sql.SQLException: sql injection violation, dbType postgresql, , druid-version 1.2.23, syntax error: TODO pos 85, line 5, column 26, token IDENTIFIER time : select *
from (select 1
union all
select 2)
AS serise_table(time)
The error may exist in com/angryecho/web/common/mybatis/mysql/mapper/maunal/PostgresqlManualMapper.java (best guess)
The error may involve com.angryecho.web.common.mybatis.mysql.mapper.maunal.PostgresqlManualMapper.testTableWithColumnName
The error occurred while executing a query
SQL: select * from (select 1 union all select 2) AS serise_table(time)
Cause: java.sql.SQLException: sql injection violation, dbType postgresql, , druid-version 1.2.23, syntax error: TODO pos 85, line 5, column 26, token IDENTIFIER time : select *
from (select 1
union all
select 2)
AS serise_table(time)
; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, dbType postgresql, , druid-version 1.2.23, syntax error: TODO pos 85, line 5, column 26, token IDENTIFIER time : select *
from (select 1
union all
select 2)
AS serise_table(time)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
at jdk.proxy1/jdk.proxy1.$Proxy114.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
at jdk.proxy1/jdk.proxy1.$Proxy205.testTableWithColumnName(Unknown Source)
at com.angryecho.web.common.mybatis.PostgresqlTest.testTableWithColumnName(PostgresqlTest.java:126)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1597)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1597)
Caused by: java.sql.SQLException: sql injection violation, dbType postgresql, , druid-version 1.2.23, syntax error: TODO pos 85, line 5, column 26, token IDENTIFIER time : select *
from (select 1
union all
select 2)
AS serise_table(time)
at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:876)
at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:318)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:547)
at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:908)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:116)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:547)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:328)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:369)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:88)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:90)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:60)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:90)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:64)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
at com.angryecho.web.common.mybatis.MybatisInterceptor.intercept(MybatisInterceptor.java:40)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
at jdk.proxy1/jdk.proxy1.$Proxy263.query(Unknown Source)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:169)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
at jdk.proxy1/jdk.proxy1.$Proxy263.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:75)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425)
... 10 more
Caused by: com.alibaba.druid.sql.parser.ParserException: TODO pos 85, line 5, column 26, token IDENTIFIER time
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:617)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:117)
at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:494)
at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:446)
at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:853)
... 41 more
Error Info
原因分析：
你在 PostgresqlManualMapper 中执行的 SQL 语句 select * from (select 1 union all select 2) AS serise_table(time)，被 Druid 的 SQL 防火墙（WallFilter）拦截，认为存在 SQL 注入风险或语法不被支持。
Druid 1.2.23 版本对 PostgreSQL 的 AS table_name(column_name) 语法支持不完善，导致解析报错。
解决办法：
临时解决：可在 Druid 配置中关闭 WallFilter 检查（不推荐生产环境）。
推荐做法：避免使用 AS serise_table(time) 这种写法，改为标准 SQL 或升级 Druid 版本。
升级 Druid：尝试升级到最新的 Druid 版本，部分新版本已修复此类兼容性问题。
SQL调整：如无特殊需求，直接写 select * from (select 1 as time union all select 2 as time) as serise_table。
,这种语法会支持吗