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.
- Chapter 20, "Accessing and Retrieving Data," in Developing ColdFusion MX Applications
|
|
Category
|
Database manipulation tags
|
|
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>
|
|
See also
|
cfinsert, cfprocparam, cfprocresult, cfquery, cfstoredproc, cftransaction, cfupdate
|
|
|
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. -------------------------------------------------->
<cfquery
name="getFirst" datasource="cfsnippets">
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>
|