Problem
When passing a list into a query, and the the list contains STRING data types, each value must be surrounded with single quotes. Using <cfqueryparam /> the list can be formatted on the fly to adhere to this constraint in SQL.
Solution
Using <cfqueryparam /> in all ColdFusion queries is considered best practice for both preventing SQL injection attacks and improving query execution. Not only this, but you can use this tag to format the data without any extra string formatting functions.
Detailed explanation
Let's say you are retrieving a list of all the colors in a table:
<cfquery name="qReadColor" datasource="AdventureWorks"> SELECT DISTINCT Color FROM Production.Product WHERE (Color IS NOT NULL) </cfquery>
Next you quickly generate a list of all the returned values like so:
<cfset myColors = valuelist(qReadColor.Color)>
Finally you pass this list to another query, using <cfqueryparam /> with the list="yes" property:
<cfquery name="qReadProduct" datasource="AdventureWorks"> SELECT TOP (1000) ProductID, Name, ProductNumber, Color FROM Production.Product WHERE (Color IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#myColors#" list="yes">)) </cfquery> <cfdump var="#qReadProduct#" />
Ommiting the list="yes" property will pass the entire list as a single value.
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
