Coldfusion update query throws error when using statements in a variable

Question

Attempting to use a variable containing a comma separated set of column updates. The variable ends up containing valid SQL (or more precisely valid Coldfusion syntax), but the query throws an error: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '<'.

I start off with an initial column to update:

<cfset sqlString = myColumn & ' = <cfqueryparam value="#myValue#" cfsqltype="#myDataType#" null="#NOT len(trim(myValue))#">'>

Then loop through some form elements doing this:

<cfset sqlString = sqlString & ', ' & myColumn & ' = <cfqueryparam value="#myValue#" cfsqltype="#myDataType#" null="#NOT len(trim(myValue))#">'>

I end up of course with an unbroken string in sqlString, but for readability, it contains something like this:

id = <cfqueryparam value="123" cfsqltype="cf_sql_integer">,
csp = <cfqueryparam value="5" cfsqltype="cf_sql_integer">,
Q185 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
Q3 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
Q177 = <cfqueryparam value="" cfsqltype="cf_sql_date" null="YES">

So I attempt to use that in a an update query like this:

<cfquery name="update_answers" datasource="#application.datasource#">
   UPDATE answers
   SET    #PreserveSingleQuotes(sqlString)#                                                     
   WHERE  rec_id = #id#
</cfquery>

I've tried this with and without the PreserveSingleQuotes function to no avail.

If I output the contents of sqlString and paste it directly into the query like so, it works fine:

<cfquery name="update_answers" datasource="#application.datasource#">
   UPDATE answers
          id = <cfqueryparam value="123" cfsqltype="cf_sql_integer">,
          csp = <cfqueryparam value="5" cfsqltype="cf_sql_integer">,
          Q185 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
          Q3 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
          Q177 = <cfqueryparam value="" cfsqltype="cf_sql_date" null="YES">                                                    
   WHERE  rec_id = #id#
</cfquery>

Again, I'm showing line breaks here for readability, but it doesn't matter if I paste the sqlString contents into the query with or without line breaks; it WILL work.

Any ideas?

Answer 1

tldr;

CFQueryparam cannot be nested within a string. It must be used directly within <cfquery> tags. To build sql statements, with dynamic parameters, take a look at the cfscript equivalent of cfqueryparam.

Security Issues

While you probably only tried PreserveSingleQuotes() out of desperation, never use that function unless you understand the repercussions, because it basically creates a great big sql injection hole in your application.

<cfset sqlString = sqlString & ', ' & myColumn

Also, be very careful with that sort of dynamic sql statement. Even if you protect all of the parameters with cfqueryparam, the query is still vulnerable to sql injection because myColumn is a user supplied value. Unfortunately cfqueryparam can't protect object names (table names, column names, etc..), only literals (string, date, etc..). So if you absolutely must use dynamic column names in raw sql, be sure to validate them against a white list and reject the request if invalid columns are detected.