SQL_VALIDATION_PATTERN

[joao.narezzi]

I have a subreport that uses a query with variables (@variable). When publishing to jasperserver, the SQL_VALIDATION_PATTERN error occurs. I changed the security.validation.sql.on option to false but it didn't work. 

 

I use Jasperserver version 8.0

 

Any idea?

 

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Thomas Tibcken]

Hi, can you please upload the jrxml files that you use, so we can have a look at the code you use? 

THX TT

[joao.narezzi]

I need to do the recursion from the start date to the end date. Example:2023-06-21 (Initial Date)2023-06-25 (End Date)I need to create a table containing:2023-06-212023-06-222023-06-232023-06-242023-06-25

 

[Badr Draifi]

Hi Joao, we don't recommend you turning off the sql validation, your system will be less secure and exposed to sql injection attacks. You can leave the validation on and adjust your regular expression to allow the @ symbole. Can you please share your jrxml file and the stack trace of the error in the japserserver.log file? Thanks

[jgust]

>> I need to do the recursion from the start date to the end date. Example:

>> 2023-06-21 (Initial Date)>> 2023-06-25 (End Date)>> I need to create a table containing:>> 2023-06-21>> 2023-06-22>> 2023-06-23>> 2023-06-24>> 2023-06-25

 

In Oracle, I would use "CONNECT BY LEVEL" to assist in creating fake rows
 

SELECT extract(YEAR from SYSDATE) - LEVEL + 3 yrFROM DUALCONNECT BY LEVEL <= 7;SELECT trunc(SYSDATE) - LEVEL + 3 dtFROM DUALCONNECT BY LEVEL <= 7;[/code]

 

I'm sure other databases have a similar ability.

Another approach and more general would be to create a CTE with a cross join.  In the example below fake_rows returns 100 rows.  That is then used in MyDate to increment the date.  Works great for when I need to create a By Date revenue report and enforce a row for when there is no data on the given date.

with fake_rows as (    select a.fr, row_number() over (order by a.fr) as rn    from (        SELECT 1 AS fr from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual    ) a    cross join (        SELECT 1 AS fr from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual UNION ALL        SELECT 1 from dual UNION ALL SELECT 1 from dual    ) b), MyDate as (    select sysdate, sysdate + fr.RN as dt, rn    from fake_rows fr)select * from mydate;[/code]

 

[Mehak Rajkumar]

We are eagerly anticipating your response to the comment mentioned above. Please provide the requested information so that we can assist you in addressing your question effectively.