cfqueryparam

Description

Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times.

This tag is nested within a cfquery tag, embedded in a query SQL statement. If you specify optional parameters, this tag performs data validation.

Macromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. For more information, see Security Bulletin ASB99-04, "Multiple SQL Statements in Dynamic Queries," at
www.macromedia.com/devnet/security/security_zone/asb99-04.html, and Accessing and Retrieving Data in ColdFusion MX Developer's Guide.

Syntax

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

Attributes

Attribute	Req/Opt	Default	Description
value	Required		Value that ColdFusion passes to the right of the comparison operator in a `where` clause. If `CFSQLType` is a date or time option, ensure that the date value uses your DBMS-specific date format. Use the `CreateODBCDateTime` or `DateFormat` and `TimeFormat` functions to format the date value.
CFSQLType	Optional	CF_SQL_CHAR	SQL type that parameter (any type) is bound to: CF_SQL_BIGINT CF_SQL_BIT CF_SQL_CHAR CF_SQL_BLOB CF_SQL_CLOB CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARCHAR
maxLength	Optional	Length of string in `value` attribute	Maximum length of parameter. Ensures that the length check is done by ColdFusion before the string is sent to the DBMS, thereby helping to prevent the submission of malicious strings.
scale	Optional	0	Number of decimal places in parameter. Applies to `CF_SQL_NUMERIC` and `CF_SQL_DECIMAL`.
null	Optional	No	Whether parameter is passed as a null value: Yes: tag ignores the `value` attribute. No
list	Optional	No	Yes: the `value` attribute value is a delimited list. No
separator	Required, if you specify a list in `value` attribute	, (comma)	Character that separates values in list, in `value` attribute.

Usage

Use the cfqueryparam tag in any SQL statement (for example, SELECT, INSERT, UPDATE, and DELETE) that uses ColdFusion variables.

You cannot use the cfquery cachedAfter or cachedWithin attributes with cfqueryparam.

For maximum validation of string data, specify the maxlength attribute.

This tag does the following:

Allows the use of SQL bind parameters, which improves performance.
Ensures that variable data matches the specified SQL type.
Allows long text fields to be updated from a SQL statement.
Escapes string variables in single-quotation marks.

To benefit from the enhanced performance of bind variables, you must use cfqueryparam for all ColdFusion variables, and your DBMS must support bind variables. If a DBMS does not support bind parameters, ColdFusion validates and substitutes the validated parameter value back into the string. If validation fails, it returns an error message.

The validation rules are as follows:

For these types, a data value can be converted to a numeric value: CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT
For these types, a data value can be converted to a date supported by the target data source: CF_SQL_DATE, CF_SQL_TIME, CF_SQL_TIMESTAMP
For all other types, if the maxLength attribute is used, a data value cannot exceed the maximum length specified.

ColdFusion debug output shows the bind variables as question marks and lists the values beneath the query, in order of usage.

Note: To insert an empty string into a Microsoft Access table using the SequelLink ODBC Socket or SequelLink Access driver, the CFSQLType attribute must specify CF_SQL_LONGVARCHAR.

The following table shows the mapping of ColdFusion SQL data types with JDBC SQL types and those of the listed database management systems:

ColdFusion	JDBC	DB2	Informix	Oracle	MSSQL
CF_SQL_ARRAY	ARRAY
CF_SQL_BIGINT	BIGINT	Bigint	int8, serial8
CF_SQL_BINARY	BINARY	Char for Bit Data			binary timestamp
CF_SQL_BIT	BIT		boolean		bit
CF_SQL_BLOB	BLOB	Blob	blob	blob, bfile
CF_SQL_CHAR	CHAR	Char	char, nchar	char, nchar	char, nchar, unique identifier
CF_SQL_CLOB	CLOB	Clob	clob	clob,nclob
CF_SQL_DATE	DATE	Date	date, datetime, year to day
CF_SQL_DECIMAL	DECIMAL	Decimal	decimal, money	number	decimal, money, small money
CF_SQL_DISTINCT	DISTINCT
CF_SQL_DOUBLE	DOUBLE	Double
CF_SQL_FLOAT	FLOAT	Float	float	number	float
CF_SQL_IDSTAMP	CHAR	Char	char, nchar	char, nchar	char, nchar, unique identifier
CF_SQL_INTEGER	INTEGER	Integer	integer, serial		int
CF_SQL_LONGVARBINARY	LONGVARBINARY	Long Varchar for Bit Data	byte	long raw	image
CF_SQL_LONGVARCHAR	LONGVARCHAR	Long Varchar	text	long	text, ntext
CF_SQL_MONEY	DOUBLE	Double
CF_SQL_MONEY4	DOUBLE	Double
CF_SQL_NULL	NULL
CF_SQL_NUMERIC	NUMERIC	Numeric			numeric
CF_SQL_OTHER	OTHER
CF_SQL_REAL	REAL	Real	smallfloat		real
CF_SQL_REFCURSOR	REF
CF_SQL_SMALLINT	SMALLINT	Smallint	smallint		smallint
CF_SQL_STRUCT	STRUCT
CF_SQL_TIME	TIME	Time	datetime hour to second
CF_SQL_TIMESTAMP	TIMESTAMP	Timestamp	datetime year to fraction(5), datetime year to second	date	datetime, smalldate time
CF_SQL_TINYINT	TINYINT				tinyint
CF_SQL_VARBINARY	VARBINARY	Rowid		raw	varbinary
CF_SQL_VARCHAR	VARCHAR	Varchar	varchar, nvarchar, lvarchar	varchar2, nvarchar2	varchar, nvarchar, sysname

Example

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

View comments in LiveDocs