Semicolon in Fixed String generating SQL Validation Error

Just a quick update - for an interim work around I have created a fixed value string parameter with a value of ";" and am using this where I would normally use a semicolon. This stops the SQL validation error from occurring. ie

 

CASE WHEN (P.InvalidEmail = 0) AND (W.InvalidEmailAddress1 = 0) AND (LTRIM(RTRIM(W.EmailAddress)) != '' ) AND (LTRIM(RTRIM(W.EmailAddress1)) != '') THEN $P{prmSemicolon} ELSE '' END AS Email,

Very disappointed that absolutely no response has been received. Not a good advert when we are running a proving environment with the ultimate aim of getting our customers to pay for the commercial version.

I found your post whilst trying to solve a problem with MS SQL hash tables which require a # in the SQL (which doesn't work). One option was to use temp tables which required the use of a semicolon. My belief is that JS Server adds a final ; onto the end of your SQL. It is possible to remove the test for ; in the validation.properties file .... What I found from this is that the SQL seems to terminate after the first semicolon. Therefore it is not honouring perfectly valid SQL with multiple semicolons. I tried many experiments which all failed, hence came up with the above.

 

Also what is interesting is that JS Studio does not use the same validator and hence it is possible to get a report working in JS Studio which will then fail on the server.

 

Yes, also very disappointed.

Hi Mark_54, Thanks for the acknowledgement and the heads up on the # table challenge. We use them occasionally although haven't needed them in a report yet! I'll be keeping the parameter solution to hand. Just a thought; I've used stored procs for queries that require more complex ways of handling data, that might help where you need the hash table.

Hi Mark, Dale,

First, if you purchased the professional product, then definitely submit a support case when you encounter an issue like this.

 

Second, JRS has SQL Validation, partly to thwart SQL Injection, and it is configurable. For a quick test/workaround just you can disable sql validation:

security.validation.sql.on=false in file \WEB-INF\classes\esapi\security-config.properties

But for long-term, production, you want validation, so you should modify the config to allow semi-colons per:

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-security-guide/v790/protecting-against-sql-injection

 

Good luck - greg