An error has occurred. (6632) - SQL validation of stored procedure in 4.5 breaks all report queries?

[Guest]

Yeah the below worked for me, it was a sub select that causet he problem. However I think having this switched off will help, as I need stored procedures for storing certain information, based on specific reports.

webapps/jasperserver/WEB-INF/classes/esapi/security-config.propertiessecurity.validation.sql.on=false[/code]

[kcd]

Hey

I think I can interpret the problem but not sure where to start for a solution.

The problem is for all reports having upgrade from 4.2.1 to 4.5.0. The UI says "An error has occurred. Please contact your system administrator. (6632)" and the stack trace is below.

Key cause appears to be:

 

Caused by: org.owasp.esapi.errors.ValidationException: SQL_Query_Executor_context: Invalid input. Please conform to regex ^\s*((?i)select)\s+[^;]+$ with a maximum length of 50000

Because all my queries are in the form

CALL Up_Reporting_GetSomethingUseful($P{StartDate}, $P{EndDate});

 

So I think I need to disable org.owasp.esapi query validation but I cannot find any documentation about this sort of configuration. Where do I start?

Code:

2012-01-18 15:50:03,354 ERROR ManagementServiceImpl,http-9123-3:1259 - caught Throwable exception: An error has occurred. Please contact your system administrator. (6632)com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)        at com.jaspersoft.jasperserver.api.security.validators.ValidatorImpl.validateSQL(ValidatorImpl.java:394)        at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:166)        at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:731)        at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:629)        at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1159)        at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:802)        at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:118)        at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:435)        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:773)        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:731)        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:389)        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:890)        at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:57)        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:320)        at com.jaspersoft.jasperserver.ws.axis2.ManagementServiceImpl.runReport(ManagementServiceImpl.java:1167)        at com.jaspersoft.jasperserver.ws.axis2.ManagementService.runReport(ManagementService.java:240)        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)        at java.lang.reflect.Method.invoke(Method.java:616)        at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)        at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)        at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)        at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)        at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)        at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)        at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:453)        at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)        at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)        at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)        at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:399)        at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)        at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101)        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at org.springframework.security.providers.anonymous.AnonymousProcessingFilter.doFilterHttp(AnonymousProcessingFilter.java:105)        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:139)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at com.jaspersoft.jasperserver.api.metadata.user.service.impl.PasswordExpirationProcessingFilter.doFilter(PasswordExpirationProcessingFilter.java:85)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at org.springframework.security.ui.basicauth.BasicProcessingFilter.doFilterHttp(BasicProcessingFilter.java:174)        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at com.jaspersoft.jasperserver.api.metadata.user.service.impl.JIPortletAuthenticationProcessingFilter.doFilter(JIPortletAuthenticationProcessingFilter.java:81)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at com.jaspersoft.jasperserver.api.logging.filter.BasicLoggingFilter.doFilter(BasicLoggingFilter.java:53)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235)        at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)        at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:411)        at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:188)        at org.springframework.security.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:99)        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)        at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:67)        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:236)        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525)        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)        at java.lang.Thread.run(Thread.java:636)Caused by: org.owasp.esapi.errors.ValidationException: SQL_Query_Executor_context: Invalid input. Please conform to regex ^\s*((?i)select)\s+[^;]+$ with a maximum length of 50000        at org.owasp.esapi.reference.validation.StringValidationRule.checkWhitelist(StringValidationRule.java:144)        at org.owasp.esapi.reference.validation.StringValidationRule.checkWhitelist(StringValidationRule.java:160)        at org.owasp.esapi.reference.validation.StringValidationRule.getValid(StringValidationRule.java:284)        at org.owasp.esapi.reference.DefaultValidator.getValidInput(DefaultValidator.java:213)        at org.owasp.esapi.reference.DefaultValidator.getValidInput(DefaultValidator.java:185)        at com.jaspersoft.jasperserver.api.security.validators.ValidatorImpl.validateSQL(ValidatorImpl.java:388)        ... 77 morecom.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)

[glen_l]

Have you managed to solve this?

 I'm having the same issue. I'm trying to use a long query using a "with clause" defining 2 tables. 

 

iReports produces the report without issue but when I publiush it the server errors with the same regex parsing problem. there's nothing wrong with the query and I don't really need Jasper to compare it to a regular expression for me, what can we do?

[kcd]

Instead of sifting through the source code I have opted to run with the previous version. It is a shame because we really want to deploy jasperreport server on MySQL 5.5 which is what we use for our production databases.

I opened a bug ticket. See http://jasperforge.org/projects/jasperreports/tracker/view.php?id=5616

 

[jholden699]

We experienced this issue too. Our DB that we query against is SQL server, and the workaround was simple (but should not be needed). We changed our stored procedure into a table-based function and can now do:

 

select * from <function>(<param1>,<param2>)

 

Hope this helps you too.

[kcd]

Solution found - there are security configuration options to amend or disable the validation.

1 - The following regex fix should work for most cases.

webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties/validation.propertiesValidator.ValidSQL=^s*(?i)(withs+.*)?(select|call|exec(ute)?)s+[^;]+$

2 - Alternatively turn it off

webapps/jasperserver/WEB-INF/classes/esapi/security-config.propertiessecurity.validation.sql.on=false

Post Edited by kcd at 02/14/2012 23:00

Post Edited by kcd at 02/14/2012 23:01

[ruudheemskerk]

Great !

[nikil_sasi]

  i am having 3 tables in a day , like 90 tables in a month. But the table structure are same for all table , only table name wil change for day by day like 30MAR12_A , 30MAR12_B , 30MAR12_C , 31MAR12_A   ...

suppose i have to take data for a day then i will be fetching 3 table of that day. Is it possible to integrate my table structure with iReport , how can i use iReport for my reporting purpose. Suppose i am taking 1month data then i have to fetch all these 90 tables. is there any better way  - to fetch thismuch data from table. Because database structure is not possible to change , then how ?

Could any one help me 

 

[cpaterson]

This worked for me, but I also had to turn off the the input validation as well

security.validation.input.on=false

 

I'm not sure if these changes are recommended, but it got my reports working

[gillett.bob]

Hi

I tried the Regexp entered above but it did not work with sql starting with a 'with' statement.  In the end I modified the regexp supplied by JS to:

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

which seems to work fine with the SQL security validation on.

Now I am not a regexp wizard so if this is bad someone please tell me!

Bob

 

[kkriplani]

Implementing the 2nd solution worked like a charm. Thanks.

KKriplani