SQL Validator Issue

[alanm]

Hi

I've just upgraded to JasperServer 7.1.0. I'm implementing security as per the security guide

I'm having an issue with the SQL validator. It's current config is:

Validator.ValidSQL=(?is)^\s*(select|{call|with|exec)\b((?!\binto\b)[^;])*;?\s*$

With SQL query executor logging on Debug - I receive the following error running an Oracle query.

If I select the language as plsql, the report runs fine. When the language is SQL, the below error occurs

I can't see what is wrong with the query

Also, because the report works with pgsql - does this mean pgsql bypasses the SQL validator even though the query text is exactly the same?

019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:152 - DB is Oracle version Oracle Database 11g Release 11.2.0.1.0 - 64bit Production (11/2)
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:156 - driver is Oracle JDBC driver version 11.2.0.2.0 (11/2)
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:160 - jdbc 11/2
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:162 - connection URL is jdbc:oracle:thin:@mtsdb1:1521:mobile
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:204 - system timezone is sun.util.calendar.ZoneInfo[id="Australia/Hobart",offset=36000000,dstSavings=3600000,useDaylight=true,transitions=150,lastRule=java.util.SimpleTimeZone[id=Australia/Hobart,offset=36000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=9,startDay=1,startDayOfWeek=1,startTime=7200000,startTimeMode=1,endMode=3,endMonth=3,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=1]]
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:205 - report timezone is sun.util.calendar.ZoneInfo[id="Australia/Hobart",offset=36000000,dstSavings=3600000,useDaylight=true,transitions=150,lastRule=java.util.SimpleTimeZone[id=Australia/Hobart,offset=36000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=9,startDay=1,startDayOfWeek=1,startTime=7200000,startTimeMode=1,endMode=3,endMonth=3,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=1]]
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:206 - JDBC timezone parameter is null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:207 - JDBC timezone property is null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:213 - JDBC parameters timezone parameter is null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:225 - JDBC parameters timezone property is null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:232 - parameters timezone null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:238 - JDBC fields timezone parameter is null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:250 - JDBC fields timezone property is null
2019-02-22 10:39:40,549 DEBUG JRJdbcQueryExecuter,pool-4-thread-5:257 - fields timezone null
2019-02-22 10:39:40,549 ERROR Validator,pool-4-thread-5:496 - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: select ef_id, cardbalance/100 as cardbalance, minimumamount/100 as minimumamount,valuetocharge/100 as valuetocharge from view_topupcards order by to_number(ef_id)

As a test, I changed the query to a very plain: select ef_id, cardbalance, minimumamount,valuetocharge from view_topupcards order by ef_id

But still have the error. I know I could just use language="plsql" - but does that just bypass the validator - or that that what makes it work correctly?

And if that is the fix - then why doesn't setting the language as sql work?

Any ideas?

Al.

[alanm]

Found it - was due to my lack of familiarity with both java and regex.

  It was the {call text in the validator string. Removed the { and all is well

Added it in to call stored procedures using {call <procedure>} but forgot to escape the {

Now also changed back to SQL and using: exec <procedure> and that seems to work as well

Al.