How to override SQL sanitization in ColdFusion

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.

Solution

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.