How to properly escape colon in QueryExecute sql statement?
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
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
add a comment |
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
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
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 simpleStatementinstead of aPreparedStatement. ColdFusion'squeryExecute/cfquerywill always end up as aPreparedStatement, so that's not an option.
– Alex
Nov 14 '18 at 23:43
add a comment |
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
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
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
coldfusion cfml coldfusion-2016
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
edited Nov 16 '18 at 15:13
Dan Roberts
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
asked Nov 14 '18 at 20:45
Dan RobertsDan Roberts
2,62122738
2,62122738
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 simpleStatementinstead of aPreparedStatement. ColdFusion'squeryExecute/cfquerywill always end up as aPreparedStatement, so that's not an option.
– Alex
Nov 14 '18 at 23:43
add a comment |
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 simpleStatementinstead of aPreparedStatement. ColdFusion'squeryExecute/cfquerywill always end up as aPreparedStatement, 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
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
add a comment |
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.
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
add a comment |
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.
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
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.
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
answered Nov 14 '18 at 21:18
James A MohlerJames A Mohler
7,135123353
7,135123353
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 simpleStatementinstead of aPreparedStatement. ColdFusion'squeryExecute/cfquerywill always end up as aPreparedStatement, so that's not an option.– Alex
Nov 14 '18 at 23:43