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
?
<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