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>

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
Default value: "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
Default value: "Length of string in value attribute" Maximum length of parameter.

SCALE |
| Optional
Default value: "0" Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.

NULL |
| Optional
Default value: "No" Whether parameter is passed as a null value. Yes: tag ignores the value attribute No

LIST |
| Optional
Default value: "No" Yes: The value attribute value is a delimited list No

SEPARATOR |
| Required, if you specify a list in value attribute
Default value: ", (comma)" Character that separates values in list, in value attribute.