土豆丝不辣

最近搞了个JasperServer7.8个性化报表服务器,默认功能对于复杂with as select不支持，进行个性化设置

 2021-4-19 /  0评 / SQL

 赏

• 
• 

 码 移动设备上继续阅读

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文件夹

本文共计 4786 字，感谢您的耐心浏览与评论。