Attribute
Last updated on
16 May 2021
|
Also applies to ColdFusion
Note:
This tag is unsupported in CFFiddle.
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.Adobe recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. For more information, see Accessing and Retrieving Data in Developing ColdFusion Applications.
Category
History
ColdFusion 10: Added the following cfsql types:
- CF_SQL_NCHAR
- CF_SQL_NVARCHAR
- CF_SQL_LONGNVARCHAR
- CF_SQL_NCLOB
- CF_SQL_SQLXML
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 also
cfinsert, cfprocparam, cfprocresult, cfquery, cfstoredproc, cftransaction, cfupdate; Enhancing security with cfqueryparam in the Developing ColdFusion Applications
Attributes
|
|
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, 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 |
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_LONGNVARCHAR · CF_SQL_LONGVARCHAR · CF_SQL_MONEY · CF_SQL_MONEY4 · CF_SQL_NCHAR · CF)SQL_NCLOB · CF_SQL_NVARCHAR · CF_SQL_NUMERIC · CF_SQL_REAL · CF_SQL_REFCURSOR · CF_SQL_SMALLINT · CF_SQL_SQLXML · CF_SQL_TIME · CF_SQL_TIMESTAMP · CF_SQL_TINYINT · CF_SQL_VARCHAR |
|
list |
Optional |
no |
· yes: the value attribute value is a delimited list. · no |
|
maxLength |
Optional |
Length of string in value attribute |
Maximum length of parameter. Ensures that the length check is done by ColdFusion before the string is sent to the DBMS, thereby helping to prevent the submission of malicious strings. |
|
null |
Optional |
no |
Whether parameter is passed as a null value: · yes: tag ignores the value attribute. · no |
|
scale |
Optional |
0 |
Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL. |
|
separator |
Required, if you specify a list in value attribute |
, (comma) |
Character that separates values in list, in value attribute. |
Usage
Use 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:
- 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, 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 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:
ColdFusion |
JDBC |
DB2 |
Oracle |
MSSQL |
|---|---|---|---|---|
CF_SQL_ARRAY |
ARRAY |
|
|
|
CF_SQL_BIGINT |
BIGINT |
Bigint |
|
bigint |
CF_SQL_BINARY |
BINARY |
Char for Bit Data |
|
binaryt |
CF_SQL_BIT |
BIT |
|
|
bit |
CF_SQL_BLOB |
BLOB |
Blob |
blob, bfile |
longvarbinary |
CF_SQL_CHAR |
CHAR |
Char |
char, nchar |
char |
CF_SQL_CLOB |
CLOB |
Clob |
clob,nclob |
|
CF_SQL_DATE |
DATE |
Date |
|
date |
CF_SQL_DECIMAL |
DECIMAL |
Decimal |
number |
decimal |
CF_SQL_DISTINCT |
DISTINCT |
|
|
|
CF_SQL_DOUBLE |
DOUBLE |
Double |
|
double |
CF_SQL_FLOAT |
FLOAT |
Float |
number |
real |
CF_SQL_IDSTAMP |
CHAR |
Char |
char, nchar |
char |
CF_SQL_INTEGER |
INTEGER |
Integer |
|
integer |
CF_SQL_LONGVARBINARY |
LONGVARBINARY |
Long Varchar for Bit Data |
long raw |
image |
| CF_SQL_LONGNVARCHAR | LONGNVARCHAR | LONGVARGRAPHIC | NVARCHAR2 | ntext |
CF_SQL_LONGVARCHAR |
LONGVARCHAR |
Long Varchar |
long |
text |
CF_SQL_MONEY |
DOUBLE |
Double |
|
double |
CF_SQL_MONEY4 |
DOUBLE |
Double |
|
double |
| CF_SQL_NCHAR | NCHAR | NCHAR | NCHAR | nchar |
| CF_SQL_NCLOB | CLOB | NCLOB | NCLOB | nvarchar |
CF_SQL_NULL |
NULL |
|
|
|
CF_SQL_NUMERIC |
NUMERIC |
Numeric |
|
numeric |
| CF_SQL_NVARCHAR | NVARCHAR | NVARCHAR | NVARCHAR2 | nvarchar |
CF_SQL_OTHER |
OTHER |
|
|
|
CF_SQL_REAL |
REAL |
Real |
|
real |
CF_SQL_REFCURSOR |
REF |
|
|
|
CF_SQL_SMALLINT |
SMALLINT |
Smallint |
|
smallint |
CF_SQL_STRUCT |
STRUCT |
|
|
|
| CF_SQL_SQLXML | xml | |||
CF_SQL_TIME |
TIME |
Time |
|
time |
CF_SQL_TIMESTAMP |
TIMESTAMP |
Timestamp |
date |
datetime |
CF_SQL_TINYINT |
TINYINT |
|
|
tinyint |
CF_SQL_VARBINARY |
VARBINARY |
Rowid |
raw |
varbinary |
CF_SQL_VARCHAR |
VARCHAR |
Varchar |
varchar2, nvarchar2 |
varchar |
Example
This example shows the use of CFQUERYPARAM when a 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="cfdocexamples">
SELECT *
FROM courses
WHERE Course_ID=<CFQUERYPARAM VALUE="#Course_ID#"
CFSQLType="CF_SQL_INTEGER">
</CFQUERY>
<CFOUTPUT QUERY="getFirst">
<P>Course Number: #number#<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</h3>
<CFSET Course_ID="20; DELETE courses WHERE Course_ID=20">
<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: #number#<br>
Description: #descript#
</P>
</CFOUTPUT>
</BODY>
</HTML>
When you execute the following code, the CFQUERYPARAM tag returns an error message.
<HTML>
<HEAD>
<TITLE>CFQUERYPARAM Example</TITLE>
</HEAD>
<BODY>
<h3>CFQUERYPARAM Example with Bad String Input</h3>
<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>
</BODY>
</HTML>