Simple SQL Query Yet JRS Fails Validation

[Ectomorph]

 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-1910: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)[/code]

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?

 

Thanks.

 

[hozawa]

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-tomcatwebappsjasperserver-proWEB-INFclassesesapivalidation.properties

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

Further info below:

http://community.jaspersoft.com/documentation/jasperreports-server-administration-guide-beta/configuring-input-validation

[Ectomorph]

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.

[ajinkya_c]

Hi,

Try Startdate with alias a.startdate that may be the issue.

Thanks,

Ajinkya

[Ectomorph]

startdate works just fine in other reports, as mentioned, and the query still fails even if i remove this line completely.

[hozawa]

I think there's something in ajinkya_c's reply. I think I've seen this before.

Try adding "a.status", "a.acctypeid", and "a.acctype" in your "SELECT"

 

[jasperjones999]

problem exists, tibco says "we dont care" thanks for that