Add a Jaspersoft Function to SQL Validator

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

SQL Validator in Jasper7.8 is blocking WITH or REGEXP_REPLACE function in sub-query. It works fine on commenting the function lines, what are your options if you don't want to disable the SQL Validator? 

Environment

Release : Any

Component : CLARITY JASPERSOFT

Resolution

If you don't wish to disable the SQL Validator but just allow for this function:

Allow the specific function in SQL Validator:

1. Open the file: 
   C:\Jaspersoft\apache-tomcat\webapps\reportservice\WEB-INF\classes\esapi\validation.properties
2. Add the function to the ValidSQL rule:
   Example : Add "WITH" to be allowed in the beginning of the query:
   Validator.ValidSQL=(?is)^\\s*(select|with)\\s+^;+$
3. Close the file and restart the Jaspersoft server.
4. The ValidSQL rule will now allow for the words "select" or "with" to be at the beginning of a query.