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, and Accessing and Retrieving Data in ColdFusion MX Developer's Guide.

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; Enhancing security with cfqueryparam in Accessing and Retrieving Data in ColdFusion MX Developer's Guide

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

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

Use the cfqueryparam tag in any SQL statement (for example, SELECT, INSERT, UPDATE, and DELETE) that uses ColdFusion variables.

You cannot use the cfquery cachedAfter or cachedWithin attributes with cfqueryparam.

For maximum validation of string data, specify the maxlength attribute.

This tag does the following:

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:

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

 

 

CF_SQL_BINARY

BINARY

Char for Bit Data

 

 

binary

timestamp

CF_SQL_BIT

BIT

 

boolean

 

bit

CF_SQL_BLOB

BLOB

Blob

blob

blob, bfile

 

CF_SQL_CHAR

CHAR

Char

char,

nchar

char,

nchar

char, nchar,

unique
identifier

CF_SQL_CLOB

CLOB

Clob

clob

clob,nclob

 

CF_SQL_DATE

DATE

Date

date, datetime, year to day

 

 

CF_SQL_DECIMAL

DECIMAL

Decimal

decimal, money

number

decimal, money, small
money

CF_SQL_DISTINCT

DISTINCT

 

 

 

 

CF_SQL_DOUBLE

DOUBLE

Double

 

 

 

CF_SQL_FLOAT

FLOAT

Float

float

number

float

CF_SQL_IDSTAMP

CHAR

Char

char, nchar

char, nchar

char, nchar, unique
identifier

CF_SQL_INTEGER

INTEGER

Integer

integer, serial

 

int

CF_SQL_LONGVARBINARY

LONGVARBINARY

Long Varchar for Bit Data

byte

long raw

image

CF_SQL_LONGVARCHAR

LONGVARCHAR

Long Varchar

text

long

text, ntext

CF_SQL_MONEY

DOUBLE

Double

 

 

 

CF_SQL_MONEY4

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

 

 

CF_SQL_TIMESTAMP

TIMESTAMP

Timestamp

datetime year to fraction(5), datetime year to second

date

datetime, smalldate
time

CF_SQL_TINYINT

TINYINT

 

 

 

tinyint

CF_SQL_VARBINARY

VARBINARY

Rowid

 

raw

varbinary

CF_SQL_VARCHAR

VARCHAR

Varchar

varchar, nvarchar, lvarchar

varchar2, nvarchar2

varchar, nvarchar, sysname

Example

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

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7