Simple SQL Query Yet JRS Fails Validation

0

 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?

Thanks.
Ectomorph's picture
Joined: Feb 4 2014 - 3:02pm
Last seen: 4 years 8 months ago

3 Answers:

0

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...

hozawa's picture
45109
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 month 1 week ago

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.

Ectomorph - 5 years 5 months ago
0

Hi,

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

Thanks,

Ajinkya

ajinkya_c's picture
976
Joined: Aug 29 2012 - 6:13am
Last seen: 2 years 4 months ago

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

Ectomorph - 5 years 5 months ago

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

jasperjones999 - 1 year 4 months ago
0

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"

 

hozawa's picture
45109
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 month 1 week ago
Feedback
randomness