The custom report execution fails with Error "Validation Unsuccessful"

Products

• Clarity PPM On Premise

Issue/Introduction

We upgrade Jaspersoft to 8.1.1 from 7.9 and post that while trying to execute one of the custom report, fails with below error

 

2024-07-16T07:59:19,566 ERROR Validator,MEReport subreports #1:508 [] - Validation unsuccessful. Failed evaluating rule 'ValidSQL'

2024-07-16T07:59:19,568 ERROR JRFillSubreport,pool-9-thread-31:961 [] - Fill 1: exception

 

We tried to republish the report still the issue persist.

Environment

Component: Clarity Jaspersoft

Release: 8.1.1 SQL Injection Protection > Query Validation

Cause

The above error w.r.t. custom report shows that there is some invalid query formation which is resulting in Validation unsuccessful. In the specific case it was observed that you have sub-reports where multiple queries are separated by semi-colons (;)

The default validation expression(regular expression), enforces below rules

•    Queries may only use the SELECT statement, which is read-only. The following write statements are forbidden: DROP, INSERT, UPDATE, DELETE
•    SELECT statements may not use the INTO clause that could copy data.
•    CALL statements for stored procedures are allowed.
•    Multiple queries separated by semi-colons (;) will be rejected. The following example causes a security error: SELECT f1,f2 FROM tbl_1; SELECT f3 from tbl_2;

Resolution

Modify the query as per permissible characters set used by Jaspersoft for query validation and publish the report. 

Please refer to configuration of Jaspersoft around protecting SQL injection and rules used to validate SQL queries in the Jaspersoft community Understanding Query Validation for more details.