Hi,
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?
3 Answers:
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
Validator.ValidSQL=(?is)^\\s*(select)\\s+[^;]+;?\\s*$
Further info below:
http://community.jaspersoft.com/documentation/jasperreports-server-admin...
Many thanks for your suggestion, hozawa, but the "and startdate >= ? and startdate <= ?" works just fine in similar report queries I have running successfully. For example, the query below has no problems generating a report;
select o.orgname, a.accountno, a.level, c.fee, a.discount, a.startdate, a.expiry
FROM organization o, account a, charges c
where o.id=a.memberid
and c.acclevel=a.level
and status=1
and acctypeid=2
and acctype=2
and startdate >= $P{startDate} and startdate <= $P{endDate}
order by level asc
Note: The above query was taken directly from the report thats why the startdate and enddate have the parameterized EL as opposed to '?' as shown in the error snippet.