This content has been marked as final. Show 3 replies
However when I put the same string within a cfquery the single quotes
get replaced by double quotes as follows
AND idbillcode IN ( ''1100 '',''1200 '',''1300 '',''1700 '',''1800
'',''7001 '') which throws an error.
Anybody has any clues.
That is ColdFusion escaping the single quotes, by doubling them so that
you can search for strings such as "singhpk's code does not work".
(Note the single quote/apostrophe that would normally break this string
if it was not escaped.
To tell CF not to do this, one uses the preserveSingleQuotes() function.
The documentation has all the details.
I found the answer PreserveSingleQuotes. Thanks
A better solution would be to use <cfqueryparam list="Yes">, that way to don't expose yourself to SQL injection (plus it helps your queries run faster):
AND idbillcode IN ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#corlist#" list="Yes">)
<cfqueryparam> automatically determines whether to add single quotes or not based on the cfsqltype - so your corlist can just be a comma-delimited list of numbers and CF will handle qualifying them.