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?

I completely agree with Ageax's suggestions. SQL Injection is a very big concern with pretty much any kind of dynamic SQL, especially when it's coming from an untrusted source like a form. If you have to build this string, I would highly recommend that you build it up in cfscript and put it through some hardcore validation as soon as it comes in from the form. — Shawn
And how exactly are you passing myColumn, myValue and myDatatype? — Shawn
So to add a little context, I'm dealing with an application I inherited that has a multitude of issues that I can't even begin to address here. It is beyond ugly. The code I've provided is really only intended to illustrate the core question I had regarding the failure of the SQL update when the 'sqlString' variable is used, while it doesn't fail if the content of that variable is substituted in. I think Ageax answered that. — mpaul

SOS SOS · Accepted Answer · 2019-01-31T21:39:24

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.

Coldfusion update query throws error when using statements in a variable

2 Answers