Jump to content
  • Semicolon in Fixed String generating SQL Validation Error

    CategoryBug report
    Component: 15696

    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)


    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.







    User Feedback

    Recommended Comments

    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,

    Link to comment
    Share on other sites

    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.
    Link to comment
    Share on other sites

    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.

    Link to comment
    Share on other sites

    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.
    Link to comment
    Share on other sites

    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:



    Good luck - greg


    Link to comment
    Share on other sites

  • Create New...