| Category: | Feature request |
Priority: | Normal |
Status: | New |
| Project: | Severity: | Feature |
Resolution: | Open |
|
| Component: | Reproducibility: | Always |
Assigned to: |
I have same error in my report. I am getting this error if I use “common table expression” in my sql query. I use MS SQL Server.
for example:
declare @var integer
or
with a as ( select…)
Solution for this problem:
Go to // WEB-INF/classes/esapi/validation.properties
Find property:
Validator.ValidSQL=(?is)^\\s*(select)\\s+[^;]+;?\\s*$
And change in to:
Validator.ValidSQL=(?is)^\\s*(select|with|declare)\\s+[^;]+;?\\s*$
If you want to use other sql constructions (in start of query string), just add to regular expressions your sql command.
The usage of “common table expression” accelerates the process of report making (building) considerably.
v5.0.0
4 Comments:
Alexander's solution works great in v6.3. Thanks!
Before and After:
Validator.ValidSQL=(?is)^\\s*(select|call)\\s+[^;]+;?\\s*$
Validator.ValidSQL=(?is)^\\s*(select|call|with)\\s+[^;]+;?\\s*$
Thanks a lot i was trying to build the report using a CTE in the query it works fine in my local system but when i published the report into jasper server it throughs error, can you please help me where i can set the above property that you had mentioned.
Assuming I don't have access to the web server to set a validator property, where would I put the before and after?
Are you saying that I can input that within the "Dataset and Query" so that I can use a CTE (Common Table Expression) in my query?