Clarity

Expand all | Collapse all

6632 error reports running in Jaspersoft 6.4.2

Jump to Best Answer
  • 1.  6632 error reports running in Jaspersoft 6.4.2

    Posted 06-11-2018 10:06 PM

    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)



  • 2.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Broadcom Employee
    Posted 06-12-2018 01:59 AM

    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 



  • 3.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Posted 06-12-2018 03:14 AM

    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: +91 44 7114 9567

    Mob:+91 9840240482

     

     

     

     

     

    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 CustomerPrograms@ca.com.

    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.



  • 4.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Broadcom Employee
    Posted 06-12-2018 03:35 AM

    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



  • 5.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Posted 06-12-2018 03:51 AM

    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



  • 6.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Broadcom Employee
    Posted 06-12-2018 04:12 AM

    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 



  • 7.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Posted 06-12-2018 04:22 AM

    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



  • 8.  Re: 6632 error reports running in Jaspersoft 6.4.2
    Best Answer

    Broadcom Employee
    Posted 06-12-2018 05:27 AM

    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 



  • 9.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Posted 06-13-2018 12:08 AM

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



  • 10.  Re: 6632 error reports running in Jaspersoft 6.4.2

    Broadcom Employee
    Posted 06-13-2018 06:02 AM

    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