jfinal查询pivot时出错

Question

@JFinal

有人遇到过吗？ 用jfinal的Db.find查询行转列数据，用了pivot，查询报错

String sql = " select * from dbo.Mul_Cause_tb pivot( max(event) for [level] in ([1],[2],[3]))a  order by id";
List<Record> lc = Db.find(sql);

运行时，报如下错误：

[WARN]-[Thread: main]-[org.eclipse.jetty.util.component.AbstractLifeCycle.setFailed()]: FAILED jfinal: com.jfinal.plugin.activerecord.ActiveRecordException: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'vot( max(event) for [level] in ([1]',expect IDENTIFIER, actual IDENTIFIER max :  select * from dbo.Mul_Cause_tb pivot( max(event) for [level] in ([1],[2],[3]))a  order by id
com.jfinal.plugin.activerecord.ActiveRecordException: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'vot( max(event) for [level] in ([1]',expect IDENTIFIER, actual IDENTIFIER max :  select * from dbo.Mul_Cause_tb pivot( max(event) for [level] in ([1],[2],[3]))a  order by id
	at com.jfinal.plugin.activerecord.DbPro.query(DbPro.java:98)
	at com.jfinal.plugin.activerecord.DbPro.query(DbPro.java:109)
	at com.jfinal.plugin.activerecord.Db.query(Db.java:55)
	at com.service.MCSService.calc(MCSService.java:40)
	at com.RMConfig.afterJFinalStart(RMConfig.java:117)
	at com.jfinal.core.JFinalFilter.init(JFinalFilter.java:55)
	at org.eclipse.jetty.servlet.FilterHolder.doStart(FilterHolder.java:119)
	at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:64)
	at org.eclipse.jetty.servlet.ServletHandler.initialize(ServletHandler.java:724)
	at org.eclipse.jetty.servlet.ServletContextHandler.startContext(ServletContextHandler.java:265)
	at org.eclipse.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1250)
	at org.eclipse.jetty.server.handler.ContextHandler.doStart(ContextHandler.java:706)
	at org.eclipse.jetty.webapp.WebAppContext.doStart(WebAppContext.java:492)
	at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:64)
	at org.eclipse.jetty.server.handler.HandlerWrapper.doStart(HandlerWrapper.java:95)
	at org.eclipse.jetty.server.Server.doStart(Server.java:277)
	at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:64)
	at com.jfinal.server.JettyServer.doStart(JettyServer.java:120)
	at com.jfinal.server.JettyServer.start(JettyServer.java:64)
	at com.jfinal.core.JFinal.start(JFinal.java:159)
	at com.Config.main(RMConfig.java:124)
Caused by: java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'vot( max(event) for [level] in ([1]',expect IDENTIFIER, actual IDENTIFIER max :  select * from dbo.Mul_Cause_tb pivot( max(event) for [level] in ([1],[2],[3]))a  order by id
	at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:711)
	at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:240)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
	at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:928)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:318)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at com.jfinal.plugin.activerecord.SqlReporter.invoke(SqlReporter.java:58)
	at com.sun.proxy.$Proxy8.prepareStatement(Unknown Source)
	at com.jfinal.plugin.activerecord.DbPro.query(DbPro.java:67)
	at com.jfinal.plugin.activerecord.DbPro.query(DbPro.java:96)
	... 20 more
Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'vot( max(event) for [level] in ([1]',expect IDENTIFIER, actual IDENTIFIER max
	at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:229)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:325)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:121)
	at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:620)
	at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:574)
	at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:699)
	... 35 more

Answer 1

一样的脚本

select * from dbo.Mul_Cause_tb pivot( max(event) for level in ([1],[2],[3]))a  order by id

Answer 2

Answer 3

在sqlserver里运行正常的

Answer 4

是啊，明显的SQL有问题啊

Answer 5

翻看一下Druid的官方文档，看WallFilter是否有配置项可能放行指定的sql，或者可以改造一下现有的sql，让其可以正确执行，例如将sql拆分成多条分别执行

Answer 6

把WallFilter配置去掉就可以了，怎么设置能让这样的脚本通过呢？

@JFinal

//WallFilter wall = new WallFilter();
//wall.setDbType("sqlserver");        
//dl.addFilter(wall);

像这样注释掉就可以了

Answer 7

可能是使用DruidPlugin时配置了
WallFilter，注释掉配置试试。