How to properly escape colon in QueryExecute sql statement?










1















Update: Not satisfied with this answer but I found that not passing a param struct will cause CF2016 to ignore colons. Looks like CF2018 doesn't have the issue either way (though I can only test that with query of query at the moment).



We have generated sql queries that do not use query params going through QueryExecute(). I am sometimes seeing the error Space is not allowed after parameter prefix ':' and found it is caused by a string literal with a colon and space. For example:



select 'test this: error'


I was not able to find an official way to escape the colon and the common escapes didn't work, but figured out this workaround...



sqlstring = replace(sqlstring, ": ", ":'+' ", "all")


However that doesn't account for other situations that could potentially come up that wouldn't be a string literal such as a column name with colon and space and likely many more I can't think of at the moment.



Is there an official way to escape a colon passed to QueryExecute not part of a queryparam?










share|improve this question



















  • 3





    Newer JDBC drivers support : to escape a colon. If that doesn't work for you, you need to build the JDBC connection manually and send a simple Statement instead of a PreparedStatement. ColdFusion's queryExecute/cfquery will always end up as a PreparedStatement, so that's not an option.

    – Alex
    Nov 14 '18 at 23:43
















1















Update: Not satisfied with this answer but I found that not passing a param struct will cause CF2016 to ignore colons. Looks like CF2018 doesn't have the issue either way (though I can only test that with query of query at the moment).



We have generated sql queries that do not use query params going through QueryExecute(). I am sometimes seeing the error Space is not allowed after parameter prefix ':' and found it is caused by a string literal with a colon and space. For example:



select 'test this: error'


I was not able to find an official way to escape the colon and the common escapes didn't work, but figured out this workaround...



sqlstring = replace(sqlstring, ": ", ":'+' ", "all")


However that doesn't account for other situations that could potentially come up that wouldn't be a string literal such as a column name with colon and space and likely many more I can't think of at the moment.



Is there an official way to escape a colon passed to QueryExecute not part of a queryparam?










share|improve this question



















  • 3





    Newer JDBC drivers support : to escape a colon. If that doesn't work for you, you need to build the JDBC connection manually and send a simple Statement instead of a PreparedStatement. ColdFusion's queryExecute/cfquery will always end up as a PreparedStatement, so that's not an option.

    – Alex
    Nov 14 '18 at 23:43














1












1








1








Update: Not satisfied with this answer but I found that not passing a param struct will cause CF2016 to ignore colons. Looks like CF2018 doesn't have the issue either way (though I can only test that with query of query at the moment).



We have generated sql queries that do not use query params going through QueryExecute(). I am sometimes seeing the error Space is not allowed after parameter prefix ':' and found it is caused by a string literal with a colon and space. For example:



select 'test this: error'


I was not able to find an official way to escape the colon and the common escapes didn't work, but figured out this workaround...



sqlstring = replace(sqlstring, ": ", ":'+' ", "all")


However that doesn't account for other situations that could potentially come up that wouldn't be a string literal such as a column name with colon and space and likely many more I can't think of at the moment.



Is there an official way to escape a colon passed to QueryExecute not part of a queryparam?










share|improve this question
















Update: Not satisfied with this answer but I found that not passing a param struct will cause CF2016 to ignore colons. Looks like CF2018 doesn't have the issue either way (though I can only test that with query of query at the moment).



We have generated sql queries that do not use query params going through QueryExecute(). I am sometimes seeing the error Space is not allowed after parameter prefix ':' and found it is caused by a string literal with a colon and space. For example:



select 'test this: error'


I was not able to find an official way to escape the colon and the common escapes didn't work, but figured out this workaround...



sqlstring = replace(sqlstring, ": ", ":'+' ", "all")


However that doesn't account for other situations that could potentially come up that wouldn't be a string literal such as a column name with colon and space and likely many more I can't think of at the moment.



Is there an official way to escape a colon passed to QueryExecute not part of a queryparam?







coldfusion cfml coldfusion-2016






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 16 '18 at 15:13







Dan Roberts

















asked Nov 14 '18 at 20:45









Dan RobertsDan Roberts

2,62622738




2,62622738







  • 3





    Newer JDBC drivers support : to escape a colon. If that doesn't work for you, you need to build the JDBC connection manually and send a simple Statement instead of a PreparedStatement. ColdFusion's queryExecute/cfquery will always end up as a PreparedStatement, so that's not an option.

    – Alex
    Nov 14 '18 at 23:43













  • 3





    Newer JDBC drivers support : to escape a colon. If that doesn't work for you, you need to build the JDBC connection manually and send a simple Statement instead of a PreparedStatement. ColdFusion's queryExecute/cfquery will always end up as a PreparedStatement, so that's not an option.

    – Alex
    Nov 14 '18 at 23:43








3




3





Newer JDBC drivers support : to escape a colon. If that doesn't work for you, you need to build the JDBC connection manually and send a simple Statement instead of a PreparedStatement. ColdFusion's queryExecute/cfquery will always end up as a PreparedStatement, so that's not an option.

– Alex
Nov 14 '18 at 23:43






Newer JDBC drivers support : to escape a colon. If that doesn't work for you, you need to build the JDBC connection manually and send a simple Statement instead of a PreparedStatement. ColdFusion's queryExecute/cfquery will always end up as a PreparedStatement, so that's not an option.

– Alex
Nov 14 '18 at 23:43













1 Answer
1






active

oldest

votes


















0














I suppose you could separate the string out and have it be passed in



result = QueryExecute("
SELECT :mystring AS ...
",
mystring : "test this: error"
);


Seems like a lot of work though.






share|improve this answer






















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53308446%2fhow-to-properly-escape-colon-in-queryexecute-sql-statement%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    I suppose you could separate the string out and have it be passed in



    result = QueryExecute("
    SELECT :mystring AS ...
    ",
    mystring : "test this: error"
    );


    Seems like a lot of work though.






    share|improve this answer



























      0














      I suppose you could separate the string out and have it be passed in



      result = QueryExecute("
      SELECT :mystring AS ...
      ",
      mystring : "test this: error"
      );


      Seems like a lot of work though.






      share|improve this answer

























        0












        0








        0







        I suppose you could separate the string out and have it be passed in



        result = QueryExecute("
        SELECT :mystring AS ...
        ",
        mystring : "test this: error"
        );


        Seems like a lot of work though.






        share|improve this answer













        I suppose you could separate the string out and have it be passed in



        result = QueryExecute("
        SELECT :mystring AS ...
        ",
        mystring : "test this: error"
        );


        Seems like a lot of work though.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 14 '18 at 21:18









        James A MohlerJames A Mohler

        7,135123353




        7,135123353





























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53308446%2fhow-to-properly-escape-colon-in-queryexecute-sql-statement%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            How to read a connectionString WITH PROVIDER in .NET Core?

            Museum of Modern and Contemporary Art of Trento and Rovereto

            In R, how to develop a multiplot heatmap.2 figure showing key labels successfully