Change SQL Security Validation Settings

0

Hi!  I've got a query working in Oracle SQL Developer that uses a "WITH" clause and it runs perfectly.  However, the query editor in jaspersoft will not let the query start with anything exept select.  I read somewhere that I need to add "with" to the sql validation  file.  Where would I do this on my local machine's jasper studio install?  I can't find the appropriate config file.

Alternatively, if there is a way to perform the equivilent of  a oracle pivot in jasper I would not need to do this (the query editor doesn't seem to like valid oracle pivot syntax).

Thanks!

yus0359's picture
10
Joined: Feb 21 2018 - 11:41am
Last seen: 1 year 5 months ago

1 Answer:

0

Hello,

To set SQL validation to off, here is the full file path:

C:\Jaspersoft\jasperreports-server-6.4.2\apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes\esapi\security-config.properties

security.validation.sql.on=false

To add WITH to the security validator, go to path:

C:\Jaspersoft\jasperreports-server-6.4.2\apache-tomcat\webapps\jasperserver-pro\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.

-Paul

pjimenez_2's picture
2041
Joined: Oct 26 2016 - 11:39am
Last seen: 1 week 6 hours ago
Feedback
randomness