[#10911] - Semicolon in Fixed String generating SQL Validation Error

Category:
Bug report
Priority:
Normal
Status:
New
Project: Severity:
Major
Resolution:
Open
Component: Reproducibility:
Always
Assigned to:
1

Using the following in a Select clause causes a SQL Validation error:

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

Changing the ";" to a "-" so that the line reads

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

runs without error. The (anomonised) error is:
2018-06-04 14:48:11,382 ERROR Validator,***-Replen subreports #1:496 - Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: SELECT ....

....
2018-06-04 14:48:11,382 ERROR JRFillSubreport,pool-4-thread-67:879 - Fill 1: exception
com.jaspersoft.jasperserver.api.JSSecurityException: An error has occurred. Please contact your system administrator. (6632)
Arguments:
at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:497)

The error does not occur on 6.4.2

The idea of the code is to generate a semicolon separated list of email addresses for passing to a mail server.

Additional Information: Data source is SQL Server 2012 STD

Please let me know if you require any further information.

Regards
Dale

v7.1
Dataset Run
dale.page's picture
Joined: Sep 27 2016 - 7:37am
Last seen: 3 weeks 6 days ago

4 Comments:

#1

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,

#2

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.

#3

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.

#4

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.

Feedback