ColdFusion 9.0 Resources |
cfqueryparamDescriptionVerifies 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. Adobe 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.adobe.com/go/sn_asb99-04, and Accessing and Retrieving Data in the Developing ColdFusion Applications. Syntax<cfquery name = "query name" dataSource = "data source name" ...other attributes... SQL STATEMENT column_name = <cfqueryparam value = "parameter value" CFSQLType = "parameter type" list = "yes|no" maxLength = "maximum parameter length" null = "yes|no" scale = "number of decimal places" separator = "separator character"> AND/OR ...additional criteria of the WHERE clause...> </cfquery> Note: You can specify
this tag’s attributes in an attributeCollection attribute
whose value is a structure. Specify the structure name in the attributeCollection attribute
and use the tag’s attribute names as structure keys.
See alsocfinsert, cfprocparam, cfprocresult, cfquery, cfstoredproc, cftransaction, cfupdate; Enhancing security with cfqueryparam in the Developing ColdFusion Applications Attributes
UsageUse the cfqueryparam tag 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:
To benefit from the enhanced performance of bind variables, 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:
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:
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> |