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.

Database Manipulation Tags

cfinsert cfprocparam cfprocresult cfquery cfstoredproc cftransaction cfupdate

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

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
Informix
Oracle
MSSQL
CF_SQL_ARRAY
ARRAY

 
 
 
CF_SQL_BIGINT
BIGINT
Bigint
int8, serial8
 
bigint
CF_SQL_BINARY
BINARY
Char for Bit Data
 
 
binary
timestamp
CF_SQL_BIT
BIT

boolean
 
bit
CF_SQL_BLOB
BLOB
Blob
blob
blob, bfile
longvarbinary 
CF_SQL_CHAR
CHAR
Char
char, nchar
char, nchar
char
CF_SQL_CLOB
CLOB
Clob
clob
clob,nclob
 
CF_SQL_DATE
DATE
Date
date, datetime, year to day
 
 date
CF_SQL_DECIMAL
DECIMAL
Decimal
decimal, money
number
decimal
CF_SQL_DISTINCT
DISTINCT

 
 
 
CF_SQL_DOUBLE
DOUBLE
Double
 
 
double 
CF_SQL_FLOAT
FLOAT
Float
float
number
real
CF_SQL_IDSTAMP
CHAR
Char
char, nchar
char, nchar
char
CF_SQL_INTEGER
INTEGER
Integer
integer, serial
 
integer
CF_SQL_LONGVARBINARY
LONGVARBINARY
Long Varchar for Bit Data
byte
long raw
longvarbinary
CF_SQL_LONGVARCHAR
LONGVARCHAR
Long Varchar
text
long
longvarchar
CF_SQL_MONEY
DOUBLE
Double
 
 
 double
CF_SQL_MONEY4
DOUBLE
Double
 
 
double 
CF_SQL_NULL
NULL

 
 
 
CF_SQL_NUMERIC
NUMERIC
Numeric
 
 
numeric
CF_SQL_OTHER
OTHER

 
 
 
CF_SQL_REAL
REAL
Real
smallfloat
 
real
CF_SQL_REFCURSOR
REF

 
 
 
CF_SQL_SMALLINT
SMALLINT
Smallint
smallint
 
smallint
CF_SQL_STRUCT
STRUCT

 
 
 
CF_SQL_TIME
TIME
Time
datetime hour to second
 
time 
CF_SQL_TIMESTAMP
TIMESTAMP
Timestamp
datetime year to fraction(5), datetime year to second
date
timestamp
CF_SQL_TINYINT
TINYINT

 
 
tinyint
CF_SQL_VARBINARY
VARBINARY
Rowid
 
raw
varbinary
CF_SQL_VARCHAR
VARCHAR
Varchar
varchar, nvarchar, lvarchar
varchar2, nvarchar2
varchar

<!--- 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"> 
    Course Number: #Course_ID#<br> Description: #descript#</p> 
</cfoutput> 
 
<!--- This example shows the use of CFQUERYPARAM when INVALID string data is  
    in Course_ID. ---->  
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">          
        Course Number: #FirstName# #LastName#  
        Description: #Department# </p>  
</cfoutput>