Using LISTAGG WITHIN GROUP in Report (Oracle Statement)

1

Hi,

 

First of all, I am using JasperReports Server Version 6.4.0, Build: 20170611_1233.

i have Problems executing a Report containing the following SQL-Statement:

WITH t
     AS (SELECT LISTAGG (FIELD, '; ') WITHIN GROUP (ORDER BY id)
           FROM test_table)
SELECT *
  FROM t

The exception I get is the following:

ERROR AsyncJasperPrintAccessor,pool-4-thread-453:321 - Error during report execution
com.jaspersoft.jasperserver.api.JSSecurityException: Ein Fehler ist aufgetreten. Wenden Sie sich an Ihren Systemadministrator. (6632)
Arguments:
at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:500)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:932)
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:888)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1790)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1130)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1059)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:954)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

I know that JasperServer does not support WITH-Statements out of the box, so I already (a few months ago) changed Validator.ValidSQL in Security-Settings to the following:

Validator.ValidSQL=(?is)^\\s*(select|call|with)\\s+[^;]+;?\\s*$


I figured out, that the problem is the LISTAGG ... WITHIN GROUP Clause which causes the validation to fail.

I could not find any setting for allowing that? Is there any official solution for using Oracle Functions on JasperReports Server?

pf_1's picture
19
Joined: Jun 19 2018 - 3:14am
Last seen: 2 years 3 months ago

4 Answers:

-1

Did you give a try on below ? 

webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties  

security.validation.sql.on=false

https://community.jaspersoft.com/questions/541509/error-has-occurred-663...

sadakar's picture
677
Joined: Aug 14 2012 - 3:57am
Last seen: 5 days 17 hours ago
0

Disabling the SQL-Validation is not an option for me, as it causes security issues...

I don't want anyone to execute arbitrary statements in their reports (e.g. Updates or Deletes)!

pf_1's picture
19
Joined: Jun 19 2018 - 3:14am
Last seen: 2 years 3 months ago
0

UPDATE:
I figured out, that this is not only the case for LISTAGG Function, it seems that also Oracle's DECODE is seen as invalid SQL.

I turned off the Validation now, but definately need a better solution.

Any help or other suggestions concerning this issue? I can't be the only one using Oracle Functions in a JasperReport (-:

pf_1's picture
19
Joined: Jun 19 2018 - 3:14am
Last seen: 2 years 3 months ago
1

UPDATE:

I think I found the solution for these Problems.

In <queryString>-Tag in the Report, there is a property "language". When setting this property to "plsql" instead of "sql", the validation does not fail and JasperReport Server therefore accepts the oracle functions!

pf_1's picture
19
Joined: Jun 19 2018 - 3:14am
Last seen: 2 years 3 months ago
Feedback
randomness