cfqueryparam

Description

Checks the data type of a query parameter. The cfqueryparam tag is nested within a cfquery tag. It is embedded within the query SQL statement. If you specify its optional parameters, cfqueryparam also performs data validation.

Syntax

<cfquery name = "query_name"
  dataSource = "ds_name"
  ...other attributes...
>
  SELECT STATEMENT WHERE column_name = 
  <cfqueryPARAM value = "parameter value"
    CFSQLType = "parameter type"
    maxLength = "maximum parameter length"
    scale = "number of decimal places"
    dbName = "database name"
    null = "Yes" or "No"
    list = "Yes" or "No"
    separator = "separator character">

  AND/OR ...additional criteria of the WHERE clause...
</cfquery>

Attributes

Attribute

Description

value
Required. Specifies the actual value that ColdFusion passes to the right of the comparison operator in a where clause.

CFSQLType
Optional. The SQL type that the parameter (any type) will be bound to. The default value is CF_SQL_CHAR. The CFSQLTypes are as follows:

CF_SQL_BIGINT

CF_SQL_BIT

CF_SQL_CHAR

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. Maximum length of the parameter. The default value is the length of the string specified in the value attribute.

scale
Optional. Number of decimal places of the parameter. The default value is zero. Applicable for CF_SQL_NUMERIC and CF_SQL_DECIMAL.

null
Optional. Yes or No. Indicates whether the parameter is passed as a null. If Yes, the tag ignores the value attribute. The default is No.

list
Optional. Yes or No. Indicates that the parameter value of the value attribute is a list of values, separated by a separator character. The default is No. See the separator attribute for details.

separator
Optional. Specifies the character that separates values in the list of parameter values in the value attribute. The default is a comma. If you specify a list of values for the value attribute, you must also specify the list attribute.

Attribute	Description
value	Required. Specifies the actual value that ColdFusion passes to the right of the comparison operator in a where clause.
CFSQLType	Optional. The SQL type that the parameter (any type) will be bound to. The default value is `CF_SQL_CHAR`. The CFSQLTypes are as follows: `CF_SQL_BIGINT` `CF_SQL_BIT` `CF_SQL_CHAR` `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. Maximum length of the parameter. The default value is the length of the string specified in the `value` attribute.
scale	Optional. Number of decimal places of the parameter. The default value is zero. Applicable for `CF_SQL_NUMERIC` and `CF_SQL_DECIMAL`.
null	Optional. Yes or No. Indicates whether the parameter is passed as a `null`. If Yes, the tag ignores the `value` attribute. The default is No.
list	Optional. Yes or No. Indicates that the parameter value of the `value` attribute is a list of values, separated by a separator character. The default is No. See the `separator` attribute for details.
separator	Optional. Specifies the character that separates values in the list of parameter values in the `value` attribute. The default is a comma. If you specify a list of values for the `value` attribute, you must also specify the `list` attribute.

Usage

Note

For data, you must specify the maxlength attribute to ensure that maximum length validation is enforced.

The cfqueryparam tag does the following:

Allows the use of SQL bind parameters
Allows long text fields to be updated from an SQL statement
Improves performance

The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 drivers support SQL bind parameters. The ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters.

If a database does not support bind parameters, ColdFusion performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. The validation rules follow:

For types 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, data values can be converted to a numeric value.
For types CF_SQL_DATE, CF_SQL_TIME and CF_SQL_TIMESTAMP, data values can be converted to a date supported by the target data source.
For all other types, if the maxLength attribute is used, data value cannot exceed the maximum length specified.

The SQL syntax generated by the ColdFusion server is dependent on the target database. For an ODBC, DB2, or Informix data source, the generated syntax of the SQL statement is as follows:

SELECT *

  FROM courses
  WHERE col1 = ?

For an Oracle 7 or Oracle 8 data source, the syntax of the SQL statement is as follows:

SELECT *

  FROM courses
  WHERE col1 = :1

For a Sybase11 data source, the syntax of the SQL statement is as follows:

SELECT *

  FROM courses
  WHERE col1 = 10

Example

<!-------------------------------------------------------------------- 
This example shows the use of cfqueryparam when valid input is given in
Course_ID. 
---------------------------------------------------------------------->
<html>
<head>
<title>cfqueryparam Example</title>
</head>

<body>
<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_Num#<br>
 Description: #descript#
</P>
</cfoutput>
</body>
</html>

<!---------------------------------------------------------------------
This example shows the use of cfqueryparam when invalid numeric data is in Course_ID. 
---------------------------------------------------------------------->
<html>
<head>
<title>cfqueryparam Example</title>
</head>

<body>
<h3>cfqueryparam Example With Bad Numeric Data</h3>
<cfset Course_ID = "12; DELETE courses WHERE Course_ID = 20">
<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_num#<br>
 Description: #descript#
</P>
</cfoutput>
</body>
</html>

The cfqueryparam tag returns the following error message when this example is executed.

VALUE

Invalid data '12; DELETE courses WHERE Course_ID = 20' for 
    CFSQLTYPE 'CF_SQL_INTEGER'.

<!---------------------------------------------------------------------
This example shows the use of cfqueryparam when invalid string data is in Course_ID. 
---------------------------------------------------------------------->
<html>
<head>
<title>cfqueryparam Example</title>
</head>

<body>
<h3>cfqueryparam Example with Bad String Input</h3>

<cfset LastName = "Peterson; DELETE employees WHERE LastName = 'Peterson'">
<----------------------------------------------------------------------
For string input, 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>
</body>
</html>