search

alibaba / druid

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

oracle 中JSON相关操作 JSON_PATH 包含$ druid不能解析$ #5812

Open jikangzy opened 5 months ago

jikangzy commented 5 months ago

dbtype: oracle dbversion: 19C druid verion: *error sql: SELECT JT.,JD.* FROM JSON_TEST, JSON_TABLE (JSON_DATA, '$' COLUMNS (

                         ruleId VARCHAR(20) PATH '$.ruleId'
           )) JT ,
                           JSON_TABLE (JSON_DATA, '$.constraints[*]' COLUMNS (
           classProperty VARCHAR(20) PATH '$.classProperty',
           val VARCHAR(20) PATH '$.val'
           )) Jd

;

**testcase code:

CREATE TABLE "JSON_TEST" ( "JSON_DATA" VARCHAR2(2048 BYTE) VISIBLE ) LOGGING NOCOMPRESS PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT ) PARALLEL 1 NOCACHE DISABLE ROW MOVEMENT ;

-- Records of JSON_TEST

INSERT INTO "JSON_TEST" VALUES ('{"constraints": [{"classProperty": "channelCode","oper": "IN","val": "0"},{"classProperty": "couponCode","oper": "EQUAL","val": "C202401088587401537024"}],"results": [{"classProperty": "ruleId","val": "446878587402846976"}],"ruleId": "446878587402846976","ruleType": "1","rulename": ""}');

stacktrace info: **error info: uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, syntax error: syntax error, expect RPAREN, actual IDENTIFIER p

jikangzy commented 5 months ago

Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual IDENTIFIER pos 1504, line 42, column 51, token IDENTIFIER COLUMNS at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:2860) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:1288) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.methodRest(OracleExprParser.java:554) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLExprParser.primaryRest(SQLExprParser.java:1049) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.primaryRest(OracleExprParser.java:722) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:885) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleExprParser.primary(OracleExprParser.java:516) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:157) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourceQueryTableExpr(OracleSelectParser.java:686) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourcePrimary(OracleSelectParser.java:678) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourceRest(OracleSelectParser.java:910) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSource(OracleSelectParser.java:627) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:957) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.query(OracleSelectParser.java:321) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.select(OracleSelectParser.java:92) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourcePrimary(OracleSelectParser.java:639) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourceRest(OracleSelectParser.java:910) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourceRest(OracleSelectParser.java:935) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourceRest(OracleSelectParser.java:935) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSource(OracleSelectParser.java:627) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:957) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.query(OracleSelectParser.java:321) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.select(OracleSelectParser.java:92) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSourcePrimary(OracleSelectParser.java:639) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.parseTableSource(OracleSelectParser.java:624) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:957) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.query(OracleSelectParser.java:321) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleSelectParser.select(OracleSelectParser.java:92) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser.parseStatementList(OracleStatementParser.java:200) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:182) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:624) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:578) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:793) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:259) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:350) ~[druid-1.1.21.jar:1.1.21] at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.6.jar:3.5.6] at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) ~[pagehelper-5.0.0.jar:?] at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar:3.5.6] at com.sun.proxy.$Proxy284.query(Unknown Source) ~[?:?] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.6.jar:3.5.6] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.6.jar:3.5.6] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_241] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_241] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_241] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_241] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6] ... 69 more