User Guide

288 Chapter 2: ColdFusion Tags
Usage
Use cfqueryparam in any SQL statement (for example, SELECT, INSERT, UPDATE, and
DELETE) that uses ColdFusion variables.
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; it then lists the values
beneath the query, in order of usage.
Example
<!--- This example shows cfqueryparam with VALID input in Course_ID. --->
<h3>cfqueryparam Example</h3>
<cfset Course_ID = 12>
<cfquery name = "getFirst" dataSource = "cfsnippets">
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. -------------------------------------------------->