I'm running JasperReports Server v5.5 in Glassfish and with a MySQL db. I have an issue when trying to produce a particular report, the server logs the following error message;
Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: SELECT o.orgname, a.accountno, a.level, a.startdate, a.expiry FROM organization o, account a WHERE o.id=a.memberid AND a.acctypeid=1 AND a.status=1 AND a.level='Trial' AND startdate >= ? AND startdate <= ? ORDER BY a.startdate |#] [#|2014-02-19T10:46:36.363+1000|INFO|glassfish3.1.2|javax.enterprise.system.std.com.sun.enterprise.server.logging|_ThreadID=151;_ThreadName=Thread-2;|2014-02-19 10:46:36,362 ERROR AsyncJasperPrintAccessor,pool-16-thread-2:299 - Error during report executioncom.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)
As you can see, the query is simple enough. The startdate and endate values are matched against passed parameters. If I set sql validation to false from within security-config.properties then the report works fine, but ideally I dont want to do that. Can anyone spot anything in the query that is causing JasperReports Server to fail validation?
Try removing the "and startdate >= ? and startdate <= ?" to see if that'll remove the error. If it does, it's probably the SQL validatation rule. You'll have to modify the following regular expression in apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes\esapi\validation.properties
Further info below: