Failed evaluating default rule 'Validator.SQL_VALIDATION_PATTERN' - how do I fix this, and avoid it in the future?

[ddelsignore]

Hi,

We're seeing this error on a report deployed to JasperReports Server v9.0.  The report works in the designer, so it seems likely there's a problem with how the query is constructed. 

Can someone point me to the validation rules so I can figure out what's wrong with this report, and what to avoid in the future?

Thanks!

Stack Trace:

2024-09-06T10:15:40,307 ERROR Validator,pool-6-thread-10724:541 - Validation unsuccessful. Failed evaluating default rule 'Validator.SQL_VALIDATION_PATTERN', no matches found
2024-09-06T10:15:40,307 ERROR AsyncJasperPrintAccessor,pool-6-thread-10724:321 - Error during report execution com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)
Arguments:
        at com.jaspersoft.jasperserver.api.security.validators.Validator.newSecurityException(Validator.java:553)
        at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:542)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.validateSQL(JRTimezoneJdbcQueryExecuter.java:211)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:157)
        at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:112)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1286)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:736)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:465)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:591)
        at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:141)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:988)
        at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:192)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:939)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1928)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1205)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1142)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:1013)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)

 

[LMBadEms]

just set the parameter security.validation.sql in WEB-INF/classes/esapi/security-config.properties to false.... 

[kennetht]

Customizable validation string in WEB-INF/classes/esapi/validation.properties

Default validation string is Validator.ValidSQL=(?is)^\\s*(select|call)\\b((?!\\b(into|delete|update|drop)\\b)[^;])*;?\\s*$

The JasperReports Server v9.0.0 added additional constraints to reject SQL containing delete|update|drop (with good intention) but now we're being tripped up with queries like:

SELECT id
FROM <table>
WHERE <field> = 'Update';

[ddelsignore]

Thank you - the problem is as Kenneth described; we have a criterion

where field = 'update'

Solution (works for MySQL) is to escape one of the characters in the string literal:

where field = 'u\pdate'

Be sure to escape a character that has no special meaning (i.e. not \t, \n, etc.) You can also use concat() for a slightly more verbose but more general solution:

where field = CONCAT('up','date')

 

Edited September 23 by ddelsignore
additional content