cfqueryparam

<cfquery 
   name = "query_name"
   dataSource = "ds_name"
   ...other attributes...
   SQL STATEMENT column_name = 
   <cfqueryparam value = "parameter value"
      CFSQLType = "parameter type"
      maxLength = "maximum parameter length"
      scale = "number of decimal places"
      null = "yes" or "no"
      list = "yes" or "no"
      separator = "separator character">
   AND/OR ...additional criteria of the WHERE clause...>
   </cfquery>

<!--- This example shows cfqueryparam with VALID input in Course_ID. --->
<h3>cfqueryparam Example</h3>
<cfset Course_ID = 12>
<cfquery name = "getFirst" dataSource = "cfdocexamples">
   SELECT * 
   FROM courses
   WHERE Course_ID = <cfqueryPARAM value = "#Course_ID#"
   CFSQLType = "CF_SQL_INTEGER"> 
</cfquery>
<cfoutput query = "getFirst">
   <p>Course Number: #Course_ID#<br> Description: #descript#</p>
</cfoutput>

<!--- This example shows the use of CFQUERYPARAM when INVALID string data is
   in Course_ID. ----> 
<p>This example throws an error because the value passed in the CFQUERYPARAM 
tag exceeds the MAXLENGTH attribute</p> 

<cfset LastName="Peterson; DELETE employees WHERE LastName='Peterson'">
<!------- Note that for string input you must specify the MAXLENGTH attribute 
   for validation. --------------------------------------------------> 
<cfquery 
   name="getFirst" datasource="cfdocexamples"> 
   SELECT * 
   FROM employees 
   WHERE LastName=<cfqueryparam 
                        value="#LastName#" 
                        cfsqltype="CF_SQL_VARCHAR" 
                        maxlength="17"> 
</cfquery> 
<cfoutput 
   query="getFirst">       <p>
      Course Number: #FirstName# #LastName# 
      Description: #Department# </p> 
</cfoutput> 

Comments

bizarrojack said on Apr 21, 2005 at 8:36 AM :

So I am told, the speed bonus comes in when a RDBMS' query optimizer can be circumvented, i.e. when the optimization plan is cached already. When non-cfqueryparam-ed query's optimization step is trivial, you wont see an improvement, and if it is repeated exactly (including arguments) you would also not see an improvement by using cfqueryparam. Without cfqueryparam (bind variables), queries with different arguments won't appear to be a match in the query optimizer cache lookup, and what is effectively the same query will have to go through the optimization process, basically needlessly.

You can see in the cfdebug output that the SQL without cfqueryparam is like this:
select x,y,z from table where foo = 'bar' . . .
select x,y,z from table where foo = 'baz' . . .
which is two entries in the optimization plan cache, whereas with cfqueryparam it will be
select x,y,z from table where foo = ? . . .
both times, with the variables printed afterwards. These are somewhat bad examples, because the optimization is trivial, and you wont be able to tell from the elapsed time whether that step was repeated or not.

bizarrojack said on Jul 12, 2005 at 7:54 AM :

ColdFusion does not require the "null" attribute, but if you have a numeric field that can be null, it is required to avoid a situational crash - "" (blank) will not be translated to NULL if you use only the "value" attribute even if your DBMS would accept SQL syntax that way; you have to consider the variable twice per cfqueryparam, like

<cfqueryparam value="#variable#" null="#variable is ""#" cfsqltype="CF_SQL_FLOAT">

This may depend on your DBMS.

switchbox said on Jul 14, 2005 at 7:43 PM :

The MSSQL data type uniqueidentifier is a single token, not 2 words. The same applies to smalldatetime and smallmoney.

ASandstrom said on Aug 1, 2005 at 12:58 PM :

In response to switchbox: A documentation bug (#60837) has been entered so that this will be corrected in the next release of ColdFusion.

edgriffiths said on Sep 20, 2005 at 9:05 AM :

To perform an MS SQL 'LIKE' comparison using CFQUERYPARAM place the percent signs within the 'value' attribute:

SELECT moreStuff
FROM myTable
WHERE stuff LIKE <cfqueryparam value="%#substringToCompare#%" cfsqltype="CF_SQL_VARCHAR">

ASandstrom said on Sep 27, 2005 at 7:16 AM :

For information about using the null attribute, see the article by Justin Fidler at http://www.macromedia.com/devnet/server_archive/articles/cfqueryparam_oracle_databases.html

Quoting from the article:
"There are a few things to keep in mind when using null variables. The VALUE parameter must be an empty string. By empty string, I mean two sets of double quotes with nothing in between. If you have anything else in there, even just a blank space, it can throw an error.

Also, make sure to set NULL="YES" or it won't set it to NULL properly. Finally, make sure to use the proper CFSQLTYPE to match the field you're setting to NULL. This may seem a bit counter-intuitive because NULL has no data type, but you may run into problems if you don't"

Oblio said on Nov 18, 2005 at 9:57 AM :

My tests show that Justin Fidler's article no longer applies when issuing nulls. I'm able to use the 'null' attribute to override the 'value' as the documentation describes. Further, the documentation doesn't note that any boolean value can be substituted for the 'yes|no' assignment to 'null'; for example, to only insert form fields that have been filled in, I used this:

<cfqueryparam value="#Trim(FORM.name)#" cfsqltype="CF_SQL_VARCHAR" null="#Len(Trim(FORM.name))#">

boughtonp said on Nov 29, 2005 at 8:20 AM :

Something that I can't see mentioned above, but that is worth noting:
If you enter an invalid or mis-spelt cfsqltype it will not throw an error, it will simply switch to using CF_SQL_CHAR.

No screen name said on Nov 27, 2006 at 12:24 PM :

For MSSQL, the money data type should use CF_SQL_MONEY, not CF_SQL_DECIMAL.

anthony_id said on Dec 8, 2006 at 2:32 PM :

RE: MS SQL LIKE statement

This only works if the % signs fit the datatype. I tried using it with a date datatype and received a type-mismatch error. This makes running a LIKE query on non *CHAR types impossible, so I've reverted to plain old text. This isn't a security concern if type checking/validation occurs on the data before it hits the query (mine is in a CFC), but negates any performance gains from cfqueryparam.

anthony_id said on Dec 8, 2006 at 2:54 PM :

RE: LIKE and MS SQL with cfqueryparam

To get around datatype matching with non character types, it is possible to CAST the column to varchar in the WHERE clause. In this case, the type is CF_SQL_VARCHAR in the cfqueryparam statement.

Note also that MS SQL uses the following format when dates are CAST:
mmm d yyyy (NOTE THERE ARE 2 [TWO!] spaces after mmm)

The Fletch said on Jan 4, 2007 at 2:23 PM :

CF_SQL_TINYINT does not seem to work properly. I used CF_SQL_INT (which is not even listed in the options) and it works just fine for a tinyint datatype in an MSSQL database.

Jon Briccetti said on May 30, 2007 at 8:20 AM :

i've noticed that when using bind variables against views, if we re-build the views in the database and then try to access the cfm code with the bind variable queries, it throws an error. if we switch to using a straight SQL stmt, somethign like ... where column = '#var1#' it works... and then after "a little while" i can switch back to using the cfqueryparam and it all works fine. what **i'm guessing** is that the cached optimization info on the oracle side is no longer valid once we re-build the view, yet it must be trying to use that optimization plan anyway. later, when the optimization chache clears it works again.