jfinal里面是报错的,但是我把sql复制到PL/SQL里运行就OK,怎么回事的

Java_weber 发布于 2014/07/14 15:17
阅读 2K+
收藏 0
Sql: select count(*)  from ( select sum(pl.qty) discharge_amount,ph.created_by from bms.pallet_head ph, bms.pallet_line pl, bms.barcode bc, bms.item bi, eshop.item ei where ph.pallet_head_id = pl.pallet_head_id and pl.item_barcode = bc.bar_code(+) and bc.item_id = bi.item_id(+) and bi.item_code = ei.item_code(+) group by ph.created_by ) discharg full join (select sum(lt.qty) enter_warehouse, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and nvl(lt.bill_type, 0) = 4 and trim(lt.transaction_type) = 'I' group by lt.created_by ) enter on discharg.created_by = enter.created_by full join( select sum(lt.qty) picking_down_qty, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and trim(lt.transaction_type) = 'O' group by lt.created_by ) picking on discharg.created_by = picking.created_by full join( select sum(lt.qty) initialise_qty, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and nvl(lt.bill_type, 0) = 6 and trim(lt.transaction_type) = 'I' group by lt.created_by ) initialise on discharg.created_by = initialise.created_by full join( select SUM(PC.QTY) out_warehouse_qty, PC.CREATED_BY from BMS.PICKING_CONFIRM PC, bms.barcode bc, bms.item bi, eshop.item ei WHERE PC.BAR_CODE = BC.BAR_CODE(+) AND BC.ITEM_ID = BI.ITEM_ID(+) AND BI.ITEM_CODE = EI.ITEM_CODE(+) GROUP BY PC.CREATED_BY ) out_warehouse on discharg.created_by = out_warehouse.created_by
[ERROR] [15:03:34] com.jfinal.core.ActionHandler - /wms/pallet/employeeEfficiency
com.jfinal.plugin.activerecord.ActiveRecordException: java.sql.SQLException: sql injection violation, syntax error, expect FROM, actual RPAREN transaction_type : select count(*)  from ( select sum(pl.qty) discharge_amount,ph.created_by from bms.pallet_head ph, bms.pallet_line pl, bms.barcode bc, bms.item bi, eshop.item ei where ph.pallet_head_id = pl.pallet_head_id and pl.item_barcode = bc.bar_code(+) and bc.item_id = bi.item_id(+) and bi.item_code = ei.item_code(+) group by ph.created_by ) discharg full join (select sum(lt.qty) enter_warehouse, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and nvl(lt.bill_type, 0) = 4 and trim(lt.transaction_type) = 'I' group by lt.created_by ) enter on discharg.created_by = enter.created_by full join( select sum(lt.qty) picking_down_qty, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and trim(lt.transaction_type) = 'O' group by lt.created_by ) picking on discharg.created_by = picking.created_by full join( select sum(lt.qty) initialise_qty, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and nvl(lt.bill_type, 0) = 6 and trim(lt.transaction_type) = 'I' group by lt.created_by ) initialise on discharg.created_by = initialise.created_by full join( select SUM(PC.QTY) out_warehouse_qty, PC.CREATED_BY from BMS.PICKING_CONFIRM PC, bms.barcode bc, bms.item bi, eshop.item ei WHERE PC.BAR_CODE = BC.BAR_CODE(+) AND BC.ITEM_ID = BI.ITEM_ID(+) AND BI.ITEM_CODE = EI.ITEM_CODE(+) GROUP BY PC.CREATED_BY ) out_warehouse on discharg.created_by = out_warehouse.created_by
    at com.jfinal.plugin.activerecord.Model.paginate(Model.java:242)
    at com.jfinal.plugin.activerecord.Model.paginate(Model.java:252)
    at com.topteam.model.wms.Pallet_Head.employeeEfficiency(Pallet_Head.java:1128)
    at com.topteam.controller.wms.PalletController.employeeEfficiency(PalletController.java:151)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.jfinal.core.ActionInvocation.invoke(ActionInvocation.java:55)
    at com.jfinal.ext.plugin.shiro.ShiroInterceptor.intercept(ShiroInterceptor.java:50)
    at com.jfinal.core.ActionInvocation.invoke(ActionInvocation.java:51)
    at com.topteam.interceptor.system.LoginInterceptor.intercept(LoginInterceptor.java:29)
    at com.jfinal.core.ActionInvocation.invoke(ActionInvocation.java:51)
    at com.jfinal.ext.interceptor.SessionInViewInterceptor.intercept(SessionInViewInterceptor.java:44)
    at com.jfinal.core.ActionInvocation.invoke(ActionInvocation.java:51)
    at com.jfinal.core.ActionHandler.handle(ActionHandler.java:73)
    at com.jfinal.core.JFinalFilter.doFilter(JFinalFilter.java:72)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1307)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
    at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
    at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
    at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
    at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
    at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
    at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
    at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
    at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
    at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1307)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:453)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:560)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1072)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:382)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1006)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
    at org.eclipse.jetty.server.Server.handle(Server.java:365)
    at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:485)
    at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:937)
    at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:998)
    at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:856)
    at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240)
    at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
    at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628)
    at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
    at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: sql injection violation, syntax error, expect FROM, actual RPAREN transaction_type : select count(*)  from ( select sum(pl.qty) discharge_amount,ph.created_by from bms.pallet_head ph, bms.pallet_line pl, bms.barcode bc, bms.item bi, eshop.item ei where ph.pallet_head_id = pl.pallet_head_id and pl.item_barcode = bc.bar_code(+) and bc.item_id = bi.item_id(+) and bi.item_code = ei.item_code(+) group by ph.created_by ) discharg full join (select sum(lt.qty) enter_warehouse, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and nvl(lt.bill_type, 0) = 4 and trim(lt.transaction_type) = 'I' group by lt.created_by ) enter on discharg.created_by = enter.created_by full join( select sum(lt.qty) picking_down_qty, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and trim(lt.transaction_type) = 'O' group by lt.created_by ) picking on discharg.created_by = picking.created_by full join( select sum(lt.qty) initialise_qty, lt.created_by from bms.location_transaction lt, eshop.item ei where lt.item_code = ei.item_code(+) and nvl(lt.bill_type, 0) = 6 and trim(lt.transaction_type) = 'I' group by lt.created_by ) initialise on discharg.created_by = initialise.created_by full join( select SUM(PC.QTY) out_warehouse_qty, PC.CREATED_BY from BMS.PICKING_CONFIRM PC, bms.barcode bc, bms.item bi, eshop.item ei WHERE PC.BAR_CODE = BC.BAR_CODE(+) AND BC.ITEM_ID = BI.ITEM_ID(+) AND BI.ITEM_CODE = EI.ITEM_CODE(+) GROUP BY PC.CREATED_BY ) out_warehouse on discharg.created_by = out_warehouse.created_by
    at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:668)
    at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:214)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:446)
    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:446)
    at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
    at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:311)
    at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.jfinal.plugin.activerecord.SqlReporter.invoke(SqlReporter.java:58)
    at $Proxy22.prepareStatement(Unknown Source)
    at com.jfinal.plugin.activerecord.Db.query(Db.java:39)
    at com.jfinal.plugin.activerecord.Model.paginate(Model.java:222)
    ... 52 more
Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, expect FROM, actual RPAREN transaction_type
    at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:1377)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.methodRest(OracleExprParser.java:507)
    at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:600)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.primaryRest(OracleExprParser.java:677)
    at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:561)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.primary(OracleExprParser.java:477)
    at com.alibaba.druid.sql.parser.SQLExprParser.bitXor(SQLExprParser.java:135)
    at com.alibaba.druid.sql.parser.SQLExprParser.multiplicative(SQLExprParser.java:151)
    at com.alibaba.druid.sql.parser.SQLExprParser.additive(SQLExprParser.java:1033)
    at com.alibaba.druid.sql.parser.SQLExprParser.shift(SQLExprParser.java:1061)
    at com.alibaba.druid.sql.parser.SQLExprParser.bitAnd(SQLExprParser.java:940)
    at com.alibaba.druid.sql.parser.SQLExprParser.bitOr(SQLExprParser.java:954)
    at com.alibaba.druid.sql.parser.SQLExprParser.equality(SQLExprParser.java:969)
    at com.alibaba.druid.sql.parser.SQLExprParser.relational(SQLExprParser.java:1130)
    at com.alibaba.druid.sql.parser.SQLExprParser.andRest(SQLExprParser.java:1092)
    at com.alibaba.druid.sql.parser.SQLExprParser.exprRest(SQLExprParser.java:128)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.exprRest(OracleExprParser.java:1095)
    at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:115)
    at com.alibaba.druid.sql.parser.SQLSelectParser.expr(SQLSelectParser.java:429)
    at com.alibaba.druid.sql.parser.SQLSelectParser.parseWhere(SQLSelectParser.java:235)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.query(OracleSelectParser.java:274)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.select(OracleSelectParser.java:88)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSource(OracleSelectParser.java:711)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourceRest(OracleSelectParser.java:954)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSource(OracleSelectParser.java:721)
    at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:304)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.query(OracleSelectParser.java:272)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.select(OracleSelectParser.java:88)
    at com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser.parseStatementList(OracleStatementParser.java:166)
    at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:107)
    at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:627)
    at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:586)
    at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:656)
    ... 66 more

加载中
0
tinshen
tinshen

java.sql.SQLException: sql injectio

druid开启了严格过滤。

认为你的sql有注入的问题。

还有就是jfinal躺着中枪了。

你的sql太复杂了吧。。。


Java_weber
Java_weber
正在无解中,SQL是有点复杂,但是我直接用SQL去查询就没问题,都不知道从哪里下手找问题了,难道要写新的一个SQL吗,这样的话又得想很久了
0
都哑
都哑
要死要死。从来没有在sql里写过加号。。
Java_weber
Java_weber
....我经常这样写的说
0
tinshen
tinshen

还有个方案就是把druid里的wall策略关了。

但是缺点就是没办法防恶意的sql输入了。

Java_weber
Java_weber
这个涉及到安全的东西,不敢乱改公司的配置的说,后来我把where里面的trim函数去掉之后就可以用了,加上就报错。。。。
0
JFinal
JFinal
      将 Druid 的 wall 去掉再试试,从异常来看,除了有 sql 注入嫌疑被拦截以外,还有语法错误,如果是语法错误可能是由于手误输入错误
Java_weber
Java_weber
可以把控制台生成的sql拿去正常运行,说明sql没什么错的,后来我把where里面的trim函数去掉就可以正常使用了。
0
酷酷的就
酷酷的就
超过3个表关联的sql就说明你需要增加一张中间表了.
Java_weber
Java_weber
这个sql是别人给我的,一开始有五个sql,后来由于业务要求,所以我把它full join成一张表了
返回顶部
顶部