6632 error reports running in Jaspersoft 6.4.2

[Sreerambabu Palaniswamy]

Hi all,

 

Recently I migrated the Jaspersfot report from 5.6.1 to 6.4.2, the reports worked fine in older version. When I exported the same to 6.4.2, it gives me below error. I even checked the webapps\Jaspersoft\WEB-INF\classes\esapi\Validation.propertis, the following line looks good

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

Please do advise, how to solve the issues. FYI, I'm using the subreport of HTML chart (Spider Column) for which the below query is used.

2018-06-11 18:32:03,361 ERROR Validator,MS_04_Main subreports #2:499 [root|superuser] - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: (SELECT
PK_ID INVID,
CATEGORY RADARCATEGORY,
MAX(NVL(CRITICALITY,0)) RADARCRIT
FROM
(SELECT RIM.PK_ID,
PLANG.NAME CATEGORY,
CASE WHEN COUNT(PLANG.NAME) = 0 THEN 0 ELSE SUM(RIM.PROBABILITY_ENUM*RIM.IMPACT_ENUM)/COUNT(PLANG.NAME) END CRITICALITY
FROM
RIM_RISKS_AND_ISSUES RIM
INNER JOIN ODF_CA_RISK RISK ON RIM.ID = RISK.ID
LEFT JOIN CMN_LOOKUPS_V PLANG ON PLANG.LOOKUP_CODE = RISK.WM_CATEGORY AND PLANG.LOOKUP_TYPE = 'WM_CATEGORY' AND PLANG.LANGUAGE_CODE = 'en'
GROUP BY RIM.PK_ID, PLANG.NAME
UNION
select
DISTINCT
RIM.PK_ID,
CATEGORY,
NULL CRITICALITY
FROM
(SELECT 'Resources' CATEGORY FROM DUAL
UNION
SELECT 'Delivery Quality' CATEGORY FROM DUAL
UNION
SELECT 'Scope' CATEGORY FROM DUAL
UNION
SELECT 'WMIS Dependencies' CATEGORY FROM DUAL
UNION
SELECT 'External Constraints' CATEGORY FROM DUAL
union
SELECT 'Sponsors/ Users Involvement' CATEGORY FROM DUAL) A
INNER JOIN RIM_RISKS_AND_ISSUES RIM ON 1=1) WHERE CATEGORY IS NOT NULL and  PK_ID IN ?
GROUP BY
PK_ID,
CATEGORY
)ORDER BY PK_ID,CATEGORY
2018-06-11 18:32:03,362 ERROR JRFillSubreport,MS_04_Main subreports #1:866 [root|superuser] - Fill 2: exception
com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (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 com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
 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:440)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
 at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
 at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
 at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
2018-06-11 18:32:03,365 ERROR JRFillSubreport,pool-6-thread-15:866 [root|superuser] - Fill 1: exception
com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (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 com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
 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:440)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
 at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
 at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
 at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
2018-06-11 18:32:03,368 ERROR AsyncJasperPrintAccessor,pool-6-thread-15:321 [root|superuser] - 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.validateSQL(Validator.java:500)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168)
 at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
 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:440)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
 at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
 at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
 at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)

[Suman Pramanik]

Hi Sreeram

 

I see you are trying to run the report with superuser "root|superuser", as per PPM integration with Jaspersoft you need to a tenant user as well as a PPM user in order to run the report from PPM. Also were you able to run the report from Jaspersoft Studio? 

 

If you take the query and execute directly in database does that return results. Also in Jaspersoft 5.6.1 did you try to disable the SQL validation rule.

 

See this thread which can help you 

 

Getting Error Code 6632 When Running a Report Using Stored Procedure in JapserReports Server | Jaspersoft Community 

 

Error (6632) while running a Report | Jaspersoft Community 

 

Regards

Suman Pramanik 

[Sreerambabu Palaniswamy]

Thanks suman for your reply.

 

I ran the query in new Clarity DB, it worked there. Also why do i have to

set the Validation SQL properties in 5.6.1?

 

I'm trying to export the production existing 5.6.2 report(which has HTML

chart in sub report) and then import in Development new 6.4.2 server.

Hence I need to check the 6.4.2 set properties, right? or should I check

5.6.2 properties before I import?

 

As it was running fine in 5.6.1, hence i am asking this question

 

Please advise.

 

Thanks & Regards

Sreerambabu

WMIS Hub Enabling Team| BNP Wealth Management

Tel: [phone]

Mob:[phone]

 

 

 

 

 

Re:  - Re: 6632 error reports running in Jaspersoft 6.4.2

 

(Internet)

communityadmin

 

 

To:

Sreerambabu PALANISWAMY

 

12/06/2018 14:00

 

Please respond to jive-1704483783-1zz5v9-2-405hky

 

 

 

 

 

 

 

CA Communities

 

Re: 6632 error reports running in Jaspersoft 6.4.2

reply from Suman Pramanik in CA PPM - View the full discussion

 

Hi Sreeram

 

I see you are trying to run the report with superuser "root|superuser", as

per PPM integration with Jaspersoft you need to a tenant user as well as a

PPM user in order to run the report from PPM. Also were you able to run

the report from Jaspersoft Studio?

 

If you take the query and execute directly in database does that return

results. Also in Jaspersoft 5.6.1 did you try to disable the SQL

validation rule.

 

See this thread which can help you

 

Getting Error Code 6632 When Running a Report Using Stored Procedure in

JapserReports Server | Jaspersoft Community

 

Error (6632) while running a Report | Jaspersoft Community

 

Regards

Suman Pramanik

 

 

Reply to this message by replying to this email, or go to the message on

CA Communities

Please remember to Mark the Correct Answer go to the message on CA

Communities

Start a new discussion in CA PPM by email or at CA Communities

Following Re: 6632 error reports running in Jaspersoft 6.4.2 in these

streams: Inbox

You are receiving this email because you are a member of the CA

Communities.

If you'd like to change your email preferences, click here. If you want

your communities account to be deactivated (opt out), please send an email

to [e-mail].

Additionally, if you wish to opt out of all unsolicited commercial

communications from CA Technologies, click here.

 

 

 

 

This message and any attachments (the "message") is

intended solely for the intended addressees and is confidential.

If you receive this message in error,or are not the intended recipient(s),

please delete it and any copies from your systems and immediately notify

the sender. Any unauthorized view, use that does not comply with its purpose,

dissemination or disclosure, either whole or partial, is prohibited. Since the internet

cannot guarantee the integrity of this message which may not be reliable, BNP PARIBAS

(and its subsidiaries) shall not be liable for the message if modified, changed or falsified.

Do not print this message unless it is necessary,consider the environment.

[Suman Pramanik]

Hi Sreeram,

 

Good question, when you export reports it's brings all report related dependencies and not server settings. The error relates to SQL injection which is blocked by the server side.

 

You may like to check if you had disabled the same in Jaspersoft 5.6.1 and compare the settings in your Jaspersoft 6.4.2

 

Regards

Suman Pramanik

[Sreerambabu Palaniswamy]

Hi Suman,

 

I compare the files from the following location \\webapps\Jaspersoft\WEB-INF\classes\esapi

 

Validation.properties

 

5.6.1

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

6.4.2

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

 

Security-Config.properties:

 

5.6.1

# Turns request parameter validation on or off.
security.validation.input.on=false
# Turns sql validation on or off.
security.validation.sql.on=true

 

6.4.2

# Turns request parameter validation on or off.
security.validation.input.on=true
# Turns CSRF attack guard on or off.
security.validation.csrf.on=true
# Turns sql validation on or off.
security.validation.sql.on=true

 

Apart from it, all are same.. do I need to update as such in 5.6.1? Please advise

 

Thanks

 

Sreeram

[Suman Pramanik]

Hi Sreeram

 

In 5.6.1 you have disabled the same and in 6.4.2 by default its enabled which you haven't changed. This change exposed system to SQL injection and I will not recommend the change rather I would recommend to modify the query. 

However before changing the query change the parameter and test and then proceed accordingly.

 

 

5.6.1

# Turns request parameter validation on or off.
security.validation.input.on=false
# Turns sql validation on or off.
security.validation.sql.on=true

 

6.4.2

# Turns request parameter validation on or off.
security.validation.input.on=true
# Turns CSRF attack guard on or off.
security.validation.csrf.on=true
# Turns sql validation on or off.
security.validation.sql.on=true

 

Regards

Suman Pramanik 

[Sreerambabu Palaniswamy]

hi suman,

 

my mistake, read it as

6.4.2

# Turns request parameter validation on or off.
security.validation.input.on=false
# Turns sql validation on or off.
security.validation.sql.on=true

 

5.6.1

# Turns request parameter validation on or off.
security.validation.input.on=true
# Turns CSRF attack guard on or off.
security.validation.csrf.on=true
# Turns sql validation on or off.
security.validation.sql.on=true

 

So what do I do now? Also what do you mean by :"modify the query" and "before changing the query change the parameter and test and then proceed accordingly"

 

Thanks

[Suman Pramanik]

Hi Sreeram

 

Can you turn off security.validation.sql.on=false and try but this is not recommended though. Also did this report run from Jaspersoft Studio

 

Regards

Suman Pramanik 

[Sreerambabu Palaniswamy]

Yes, this worked. As usual, thanks Suman.. Cheers

[Suman Pramanik]

Hi Sreeram

 

Doing that change makes your system vulnerable to SQL injection. There were few added security in 6.4.2 so i would recommend to re write the query.

 

Regards

Suman Pramanik