Getting a JSSSecurityException when running a report on the server

[andrew_50]

I get a one-two set of errors from jaspererver.

They are:

• There was an error on the server. Try again or contact site administrators. (Error UID: 8bc53b8a-57fd-4e05-9baf-c74ff6ba2115)
• There was an error on the server. Try again or contact site administrators. (Error UID: 41b2aa5c-34c6-47d2-a873-b18d45010187)

Looking at the jasperserver log these errors show:

2020-06-19 15:02:21,749 ERROR AsyncJasperPrintAccessor,pool-4-thread-5: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.createDatasource(JRTimezoneJdbcQueryExecuter.java:143)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578)
        at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:964)
        at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:181)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:915)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1846)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1167)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1108)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:986)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
2020-06-19 15:02:21,753 ERROR SecureExceptionHandlerImpl,http-nio-8080-exec-1:116 - There was an error on the server. Try again or contact site administrators. (Error UID: 41b2aa5c-34c6-47d2-a873-b18d45010187)
2020-06-19 15:02:21,753 ERROR ErrorPageHandlerAction,http-nio-8080-exec-1:118 - Error UID 41b2aa5c-34c6-47d2-a873-b18d45010187
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.createDatasource(JRTimezoneJdbcQueryExecuter.java:143)
        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257)
        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726)
        at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578)
        at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:964)
        at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:181)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:915)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1846)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1167)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1108)
        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:986)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
 

The thing is that I'm using a fairly "fun" query.  This the query...

SELECT @row := @row + 1 as the_row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4) t4,
(select @row := (SELECT DISTINCT last_printed from qr_code_ctr where id = 1 LIMIT 1)) as r
where @row < ((SELECT DISTINCT last_printed from qr_code_ctr where id = 1 LIMIT 1) +  $P{pQRCodeCount} );

Where the parameter $P{pQRCodeCount} is an Integer.

Now the fun.

The report runs fine when run from JasperStudio. It WILL NOT RUN on JasperServer.

Not sure what I am doing wrong.

Is this query too weird? If so, why does JasperStudio not skip a beat?

[dobryxa]

Look here - https://community.jaspersoft.com/questions/541509/error-has-occurred-6632-sql-validation-stored-procedure-45-breaks-all-report

[dobryxa]

del

[andrew_50]

I suspect the answer given is along the right lines. I rewrote my query and it now works on both Studio and Server. 
my issue is now this... Why should Studio be "more forgiving" and Server not be? That and as this seems to come down to how queries are written, so the error could be a whole lot more in one's face than the amazing catch all of "Server Error"

[andrew_50]

The regex thing gave me an idea that Jasper Server just doesn't like queries that are too sneaky (as mine was). Variables actually.

The answer was to beat about the bush, smack myself (metaphorically) upside the head with a brick. That led me to completely rewriting the query.

This version of my query is a little more annoying, but perhaps more understandable, and most importantly, it works...

SELECT    ( ( SELECT last_printed FROM qr_code_ctr WHERE id = 1 ) + SEQ.SeqValue ) QRFROM(SELECT    ( THOUSANDS.SeqValue + HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue ) SeqValueFROM    (    SELECT 0  SeqValue    UNION ALL    SELECT 1 SeqValue    UNION ALL    SELECT 2 SeqValue    UNION ALL    SELECT 3 SeqValue    UNION ALL    SELECT 4 SeqValue    UNION ALL    SELECT 5 SeqValue    UNION ALL    SELECT 6 SeqValue    UNION ALL    SELECT 7 SeqValue    UNION ALL    SELECT 8 SeqValue    UNION ALL    SELECT 9 SeqValue    ) ONES    CROSS JOIN    (    SELECT 0 SeqValue    UNION ALL    SELECT 10 SeqValue    UNION ALL    SELECT 20 SeqValue    UNION ALL    SELECT 30 SeqValue    UNION ALL    SELECT 40 SeqValue    UNION ALL    SELECT 50 SeqValue    UNION ALL    SELECT 60 SeqValue    UNION ALL    SELECT 70 SeqValue    UNION ALL    SELECT 80 SeqValue    UNION ALL    SELECT 90 SeqValue    ) TENS    CROSS JOIN    (    SELECT 0 SeqValue    UNION ALL    SELECT 100 SeqValue    UNION ALL    SELECT 200 SeqValue    UNION ALL    SELECT 300 SeqValue    UNION ALL    SELECT 400 SeqValue    UNION ALL    SELECT 500 SeqValue    UNION ALL    SELECT 600 SeqValue    UNION ALL    SELECT 700 SeqValue    UNION ALL    SELECT 800 SeqValue    UNION ALL    SELECT 900 SeqValue    ) HUNDREDS    CROSS JOIN    (    SELECT 0 SeqValue    UNION ALL    SELECT 1000 SeqValue    UNION ALL    SELECT 2000 SeqValue    UNION ALL    SELECT 3000 SeqValue    UNION ALL    SELECT 4000 SeqValue    UNION ALL    SELECT 5000 SeqValue    UNION ALL    SELECT 6000 SeqValue    UNION ALL    SELECT 7000 SeqValue    UNION ALL    SELECT 8000 SeqValue    UNION ALL    SELECT 9000 SeqValue    ) THOUSANDS) SEQWHERE    SEQ.SeqValue BETWEEN 1 AND $P{pQRCodeCount} ORDER BY    SeqValue