sqlcoldfusionsql-injectioncfqueryparam

Trouble adding cfqueryparam tags


I'm trying to secure this code but every time I add cfqueryparam tags I get errors about parameter binding. I am certain I am setting the cfsqltype attribute to the right value. The last select statement is where all hell breaks loose.

<CFQUERY name="getLatestSurveyID" datasource="#REQUEST.dsn#">
    SELECT TOP 1
        SurveyID
    FROM
        TUser_WelcomeHome
    ORDER BY
        SurveyID DESC
</CFQUERY>


    <!--- Throw the Reasons/Subreasons into the DB --->
    <!---adding cfqueryparam tags breaks following CFIF block--->
<CFIF ListLen(SESSION.WHSurveyStruct.reasonString, ";") gt 0>
    <CFQUERY name="insertReasons" datasource="#REQUEST.dsn#">
        INSERT INTO TWelcomeHome_Reason
        (ReasonID, SubReasonID, SurveyID)
        SELECT #sanitize(ListFirst(SESSION.WHSurveyStruct.reasonString, ";"))#, #sanitize(getLatestSurveyID.SurveyID)# <!---error occures if adding cfqueryparam tags on this line--->
        <CFLOOP list="#sanitize(ListRest(SESSION.WHSurveyStruct.reasonString, ';'))#" index="thisReason" delimiters=";">
            UNION ALL
            SELECT #sanitize(thisReason)#, #sanitize(getLatestSurveyID.SurveyID)#
        </CFLOOP>

    </CFQUERY>

The above code works but if I did the following change it wouldn't work:
<cfqueryparam value=#sanitize(getLatestSurveyID.SurveyID)# cfsqltype="cf_sql_integer">

Here is the error caused by parametrization
<cfqueryparam value=#sanitize(getLatestSurveyID.SurveyID)# cfsqltype="cf_sql_integer">

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The INSERT statement conflicted with the FOREIGN KEY constraint "FK_WelcomeHome_TSupplier". The conflict occurred in database "d21wca1", table "dbo.TSupplier", column 'SupplierID'. The error occurred in D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 215 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 183 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 174 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 1 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 215 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 183 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 174 Called from D:/Resource/www/dev/ww1test.owktravel.com/welcome_survey/welcome_survey_router.cfm: line 1 213 : #sanitize(SESSION.WHSurveyStruct.SupplierID)#NULL, 214 : #sanitize(SESSION.WHSurveyStruct.CruiselineID)#NULL, 215 : #sanitize(SESSION.WHSurveyStruct.UserID)# 216 : ) 217 :

EDIT: I'm stilling having trouble understanding what the loop is doing. Aren't the SELECT statements missing FROM?


Solution

  • <cfqueryparam> cannot be used in a SELECT clause the way you are using it.

    It can only be used in WHERE clause or part of a 'normal' INSERT or UPDATE