How to properly escape colon in QueryExecute sql statement?

user_8345 0
Viewed 874

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-2016 coldfusion cfml
1 Answers

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.

user_18458690
Related