ColdFusion adding extra quotes when constructing database queries in strings

sql string coldfusion railo cfml
Alex picture Alex · Nov 5, 2008 · Viewed 7.5k times · Source

I am coding in ColdFusion, but trying to stay in cfscript, so I have a function that allows me to pass in a query to run it with <cfquery blah > #query# </cfquery>

Somehow though, when I construct my queries with sql = "SELECT * FROM a WHERE b='#c#'" and pass it in, ColdFusion has replaced the single quotes with 2 single quotes. so it becomes WHERE b=''c'' in the final query.

I have tried creating the strings a lot of different ways, but I cannot get it to leave just one quote. Even doing a string replace has no effect.

Any idea why this is happening? It is ruining my hopes of living in cfscript for the duration of this project

Answer

ale picture ale · Nov 5, 2008

ColdFusion, by design, escapes single quotes when interpolating variables within <cfquery> tags.

To do what you want, you need to use the PreserveSingleQuotes() function.

<cfquery ...>#PreserveSingleQuotes(query)#</cfquery>

This doesn't address, however, the danger of SQL injection to which you are exposing yourself.

Using <cfqueryparam> also allows your database to cache the query, which in most cases will improve performance.

It might be helpful to read an old Ben Forta column and a recent post by Brad Wood for more information about the benefits of using <cfqueryparam>.

Related questions

UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:\temp\123\abc\111 2 c:\temp\123\abc\222 3 c:\temp\123\abc\333 4 c:\temp\123\…

Read More
Remove all spaces from a string in SQL Server

What is the best way to remove all spaces from a string in SQL Server 2008? LTRIM(RTRIM(' a b ')) would remove all spaces at the right and left of the string, but I also need to remove the …

Read More
How do I remove the first characters of a specific column in a table?

In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for …

Read More