How to double singlequotes in a string using coldfusion or mssql (Replace not working and can't use cfparam)

Question

I got a query containing this:

INNER JOIN
 com
ON
com.company_id = pi.company_id
AND com.company_name LIKE '%TUTTO PER L'UFFICIO%'

coldfusion show this error:

Incorrect syntax near 'UFFICIO'

How can I fix this?

P.S. can't use cfparam because this is not in a query(is in <cfsavecontent variable = "sql_query"> ... </cfsavecontent>)

its working now. i've changed the params of replace() in right order — acesta, Jul 24 '15 at 08:13
@acesta the company name in the example is an actual customer? if so you'd better wipe it out from the post. — Paolo, Jul 24 '15 at 08:23
"can't use cfqueryparam because this is not in a query". I recommend you reverse your approach. You should not be hard-coding data values in your SQL. — Adam Cameron, Jul 24 '15 at 10:02

score 3 · Accepted Answer · answered Jul 24 '15 at 10:10

3

When your SQL string is within a variable, in an attempt to mitigate SQLi, CF will automatically escape any single quotes within the string. This is the correct thing to do with SQL statements, but if you also have data values hard-coded in the SQL statement (which you should not!), it will quite possible cause the problems you are seeing.

You really ought to pull your data values out of your SQL statement, as it's the wrong place for them to be.

If you are unable or unwilling to do it properly, you can work around this by using the preserveSingleQuotes() function, eg:

<cfquery>
    #preserveSingleQuotes(sqlStatementString)#
</cfquery>

answered Jul 24 '15 at 10:10

Adam Cameron

29,677
4
37
78

I use preserveSingleQuotes from time to time and my recollections are that this function will not work in this situation. I'll leave it to @acesta to try it. – Dan Bracuk Jul 24 '15 at 11:17
1

It is worth reiterating that though `preserveSingleQuotes` would work with a *valid* sql statement, it will also reverse CF's attempt to protect against sql injection. So the query will be vulnerable. As Adam said, you really should not do this. The proper approach is to use "parameterized" queries. Not only do bind variables protect against sql injection, they also can boost performance by promoting reuse of query execution plans. – Leigh Jul 24 '15 at 16:03

score 1 · Answer 2 · edited Jul 31 '15 at 19:26

1

You can place double single quotes to escape the quote.

INNER JOIN
 com
ON
com.company_id = pi.company_id
AND com.company_name LIKE '%TUTTO PER L''UFFICIO%'

edited Jul 31 '15 at 19:26

acesta

79
11

answered Jul 24 '15 at 23:38

Logan

88
10

How to double singlequotes in a string using coldfusion or mssql (Replace not working and can't use cfparam)

2 Answers2