How to override SQL sanitization in ColdFusion

Question

I have the unfortunate task of cleaning up a bunch of old ColdFusion code. Queries are all over the place, I am working on moving them all to common CFCs for easier maintenance.

I am running into a problem because cfquery is automatically converting the single quotes to double-single-quotes. How can I override that behavior?

More specific information is below.

---

So here is the query I started with:

<cfquery name="getObjectInfo" datasource="#BaseDS#">
  SELECT groupName AS lastname, '[Group]' AS firstname
  FROM   groups
  WHERE  groups.group_id = #objectreference_id#
</cfquery>

The weird thing here is that a literal is being "selected", because of the way we want it displayed (again, I didn't write this, I'm just trying to clean it up a little). So in the common function, there is an optional parameter for the select clause:

  <cffunction name="fSelGroup" access="public" returntype="query"
              hint="Returns query selecting given group.">

    <cfargument name="intGroupID" type="numeric" required="true"
                hint="ID of group to be returned." />
    <cfargument name="strSelectAttributes" type="string" required="false"
                hint="Attributes to be selected in query"
                default="*" />

    <cfquery name="getObjectInfo" datasource="#Application.DataSource#">
      SELECT #Arguments.strSelectAttributes#
      FROM   Groups
      WHERE  Group_ID = #Arguments.intGroupID#
    </cfquery>

    <cfreturn getObjectInfo />

  </cffunction>

Here is the problem: When I pass in "GroupName AS LastName, '[Group]' AS FirstName" for the strSelectAttributes parameter, the query that is sent to the database is:

SELECT GroupName AS LastName, ''[Group]'' AS FirstName
FROM   Groups
WHERE  Group_ID = 4

You see, my quotes got "sanitized" into an invalid query.

Answer 1

ColdFusion does not escape all single quotes, but only those that arrive in the query through variable interpolation. This is the offender:

SELECT #Arguments.strSelectAttributes#

This is usually a helpful thing and a small line of defense against SQL injection attacks. So rule number one is (here and everywhere else): Don't build your SQL string from variables.

If you positively have to use variables to build an SQL string, despite all the possible negative effects, use the PreserveSingleQuotes() function:

SELECT #PreserveSingleQuotes(Arguments.strSelectAttributes)#

This function stops ColdFusion from auto-escaping the single quotes.

And any other function call does the same thing, by the way. Try:

SELECT #LCase(Arguments.strSelectAttributes)#

which means that PreserveSingleQuotes() is really just a no-op that turns a string into a function result, preventing the automatic variable interpolation routine from happening.

Answer 2

Put a call to preserveSingleQuotes() around your variable. It's made specifically for writing dynamic SQL. Also, you really, really should use cfqueryparam for your values, and I hope you're sanitizing your input somehow so that arguments.strSelectAttributes can't contain something like ';drop table groups; in it.

<cfquery name="getObjectInfo" datasource="#Application.DataSource#">
  SELECT #preserveSingleQuotes(Arguments.strSelectAttributes)#
  FROM   Groups
  WHERE  Group_ID = <cfqueryparam value="#Arguments.intGroupID#" cfsqltype="cf_sql_integer"/>
</cfquery>

Answer 3

See also http://stackoverflow.com/questions/266586/coldfusion-adding-extra-quotes-when-constructing-database-queries-in-strings

Answer 4

If you really wanting to clean up the code step two is converting that spaghetti into stored procedures.