cfqueryparam

Description

Checks the data type of a query parameter. 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:

Category

Database manipulation tags

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"
    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

Attributes

Attribute Req/Opt Default Description
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:
  • With a native database driver, you cannot specify the #now()# function in this attribute. (Use the DateFormat or TimeFormat function.)
  • With an ODBC driver, you can specify the #now()# function in this attribute.
CFSQLType
Optional
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
Length of string in value attribute
Maximum length of parameter.
scale
Optional
0
Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.
null
Optional
No
Whether parameter is passed as a null value.
  • Yes: tag ignores the value attribute
  • No
list
Optional
No
  • Yes: The value attribute value is a delimited list
  • No
separator
Required, if you specify a list in value attribute
, (comma)
Character that separates values in list, in value attribute.

Usage

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

This tag does the following:

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 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:

The SQL syntax that the ColdFusion server generates depends on the target database. For an ODBC, DB2, or Informix data source, the syntax is as follows:

SELECT * 
  FROM courses
  WHERE col1 = ?

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

SELECT * 
  FROM courses
  WHERE col1 = :1

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

SELECT * 
  FROM courses
  WHERE col1 = 10

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> 

Comments