JasperServer and SQL Query Using With As clause
Validation unsuccessful. Failed evaluating rule 'ValidSQL' 或者 Validator.SQL_VALIDATION_PATTERN 异常。
--下面是解决该问题的方案，测试环境自测通过，
Change SQL Security Validation Settings：
1.系统安装环境切换到如下路径
~#cd jasperreports-server-cp-7.8.0/apache-tomcat/webapps/jasperserver/WEB-INF/classes/esapi
进入下面文件:
vim security.properties
具体位置在460行左右:
459 # Validator.ValidSQL property would need to be uncommented & customized in validation.properties
460 # sqlQueryExecutor=Alpha,ValidSQL,500000,true,SQL_Query_Executor_context
被注释掉了，放开该行配置
sqlQueryExecutor=Alpha,ValidSQL,500000,true,SQL_Query_Executor_context
2.上面放开还是不行，需要修改如下2个设置:
You are getting this error because of security validator. Security validator let you start a query with 'Select' only. You could set the SQL validation off or add 'WITH' to validator.
2.1. Set SQL validation off
~# vim jasperreports-server-cp-7.8.0/apache-tomcat/webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties
设置该配置为 false 如下
security.validation.sql.on=false
2.2. Adding WITH to the security validator
Open jasperreports-server-cp-7.8.0/apache-tomcat/webapps/jasperserver/WEB-INF/classes/esapi/validation.properties
Add the following to the ValidSQL rule:
Validator.ValidSQL=(?is)^\s*(select|with)\s+^;+$
Close the file and restart the server. The ValidSQL rule will now allow for the words "select" or "with" to be at the beginning of a query.
官方推荐使用2.1，如果使用2.2的化，有可能不安全，但是报表数据，仅仅展示，用。不回对数据进行修改，不用担心安全问题，怕SQL注入。
I recommend you to use second one. Because disabling the validator causes lack of security.
注意: 重启Jasperserver Tomcat服务时记得删除Tomcat临时temp文件夹
评论一下？