sql injection violation, syntax error:TODO IDENTIFIER

[yililiangbaikai]

• druid版本：1.0.27

• mysql版本：5.7.16

• 系统框架：Spring4+Hibernate4+JPA2.1+Flyway4

• mysql客户端：navicat premium 11.2.11

• 问题：

开启wallfilter，在执行创建视图语句的时候报异常，该语句由mysql客户端导出且在里面执行没问题。

• 语句如下：
  CREATE ALGORITHM=UNDEFINED DEFINER=root@localhostSQL SECURITY DEFINER VIEWview_audit_enroll AS SELECT a.enroll_id AS 'enrollId', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 1) > 0) then "县站已审核" else NULL end AS 'countyAudit', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 2) > 0) then "市馆已审核" else NULL end AS 'cityAudit', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 3) > 0) then "省馆已审核" else NULL end AS 'provinceAudit' FROM actvty_audit a GROUP BY a.enroll_id

• 错误堆栈：

Caused by: java.sql.SQLException: sql injection violation, syntax error: TODO IDENTIFIER : CREATE ALGORITHM=UNDEFINED DEFINER=root@localhostSQL SECURITY DEFINER VIEWview_audit_enrollAS selecta.enroll_idASenrollId,(case when ((select actvty_audit.auditfromactvty_audit where ((actvty_audit.enroll_id=a.enroll_id) and (actvty_audit.rankjurisdiction= 1))) > 0) then '县站已审核' else NULL end) AScountyAudit,(case when ((select actvty_audit.auditfromactvty_audit where ((actvty_audit.enroll_id=a.enroll_id) and (actvty_audit.rankjurisdiction= 2))) > 0) then '市馆已审核' else NULL end) AScityAudit,(case when ((select actvty_audit.auditfromactvty_audit where ((actvty_audit.enroll_id=a.enroll_id) and (actvty_audit.rankjurisdiction= 3))) > 0) then '省馆已审核' else NULL end) ASprovinceAuditfromactvty_audit agroup bya.enroll_id at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:725) at com.alibaba.druid.wall.WallFilter.statement_execute(WallFilter.java:397) at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487) at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:137) at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:418) at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238) at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114) ... 165 more Caused by: com.alibaba.druid.sql.parser.ParserException: TODO IDENTIFIER at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseCreate(MySqlStatementParser.java:401) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:125) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:79) 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:712) ... 171 more

[yililiangbaikai]

改用最简单的viewCREATE ALGORITHM=UNDEFINED DEFINER=root@localhostSQL SECURITY DEFINER VIEWview_test_druid AS SELECT a.enroll_id AS 'enrollId' FROM actvty_audit a GROUP BY a.enroll_id
一样报错，应该是wallfilter不支持ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER 这个语法。

[wenshao]

已经支持，将会在1.0.29版本中带上