How to correct this SQL Injection on a ColdFusion site?

Question

There was an automated review of the code for a small internal website my company created, and I've been asked to fix the detected vulnerabilities.

How do I go about correcting this? The original developer has long since left the company, and i'm not too familar with coldfusion.

<cfquery name="qry_Products" datasource="#request.mssql_dsn#" username="#request.mssql_uid#" password="#request.mssql_pwd#">
    UPDATE  tblProduct
    SET     #form.F# = <cfif currVal is 1>0<cfelse>1</cfif>
    WHERE   ID = #form.ID#
</cfquery>

The automated review says there is an issue on the 3rd and 4th lines. I guess the problem is form.F and form.ID.

Answer 1

Dynamic SQL table column names from the form is not the best approach. But if you must, then I would create a map structure which controls the values which are finally rendered in the original query and for form.ID you should use <cfqueryparam>, this will prevent any sql injection attempts.

Still, I recommend using proper column names instead of a dynamic variable.

<cfset columnValueMap = {
    'formValue1': 'dbColumnName1',
    'formValue2': 'dbColumnName2',
    'formValue3': 'dbColumnName3'
}>
<cfif structKeyExists(columnValueMap, form.F)>
    <cfquery name="qry_Products" datasource="#request.mssql_dsn#" username="#request.mssql_uid#" password="#request.mssql_pwd#">
        UPDATE  tblProduct
        SET     #columnValueMap[form.F]# = <cfif currVal is 1>0<cfelse>1</cfif>
        WHERE   ID = <cfqueryparam value="#form.ID#" cfsqltype="integer">
    </cfquery>
</cfif>