Coldfusion adding extra quotes when constructing database queries in strings

Question

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 1

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 that you're exposing yourself to.

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>.

Answer 2

ColdFusion automatically escapes single quotes quotes in <cfquery> tags when you use the following syntax:

SELECT * FROM TABLE WHERE Foo='#Foo#'

In case you would want to preserve single quotes in #Foo# you must call #PreserveSingleQuotes(Foo)#.

Be aware the the automatic escaping works only for variable values, not for function results.

SELECT * FROM TABLE WHERE Foo='#LCase(Foo)#' /* Single quotes are retained! */

In that light, the function PreserveSingleQuotes() (see Adobe LiveDocs) is not much more than a "null operation" on the value - turning it into a function result to bypass auto-escaping.

Answer 3

The answer to your question, as others have said, is using preserveSingleQuotes(...)

However, the solution you actually want, is not to dynamically build your queries in this fashion. It's Bad Bad Bad.

Put your SQL inside the cfquery tags, with any ifs/switches/etc as appropriate, and ensure all CF variables use the cfqueryparam tag.

(Note, if you use variables in the ORDER BY clause, you'll need to manually escape any variables; cfqueryparam can't be used in ORDER BY clauses)

Answer 4

fwiw, rumor has it that the next version of ColdFusion will provide a good deal of love to the cfscript enthusiasts. It will be interesting to see if/how adobe crafts the cfquery for script. I'm really looking forward to that.

Answer 5

I voted up Dave's answer since I thought he did a good job.

I'd like to add however that there are also several different tools designed for ColdFusion that can simplify a lot of the common SQL tasks you're likely to perform. There's a very light-weight tool called DataMgr written by Steve Bryant, as well as Transfer from Mark Mandel, Reactor which was originally created by Doug Hughes and one I developed called DataFaucet. Each of these has its own strengths and weaknesses. Personally I think you're apt to consider DataFaucet to be the one that will give you the best ability to stay in cfscript, with a variety of syntaxes for building different kinds of queries.

Here are a few examples:

qry = datasource.select_avg_price_as_avgprice_from_products(); (requires CF8)

qry = datasource.select("avg(price) as avgprice","products");

qry = datasource.getSelect("avg(price) as avgprice","products").filter("categoryid",url.categoryid).execute();

qry = datasource.getSelect(table="products",orderby="productname").filter("categoryid",url.categoryid).execute();

The framework ensures that cfqueryparam is always used with these filter statements to prevent sql-injection attacks, and there are similar syntaxes for insert, update and delete statements. (There are a couple of simple rules to avoid sql-injection.)