User Guide Cancel

cfqueryparam

Last updated on May 30, 2024 | Also applies to ColdFusion

ColdFusion

Open app

CFML Reference User Guide
ColdFusion functions
ColdFusion Tags
1. ColdFusion tag summary
2. ColdFusion tags by category
  1. Application framework tags
    1. cfapplication
    2. cfassociate
    3. cferror
    4. cfimport
    5. cfinterface
    6. cflock
    7. cfscript
    8. cfthread
  2. Communications tags
  3. Database manipulation tags
  4. Data output tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcol
    5. cfcontent
    6. cfdocument
    7. cfdocumentitem
    8. cfdocumentsection
    9. cfflush
    10. cfheader
    11. cflog
    12. cfoutput
    13. cfpresentation
    14. cfpresentationslide
    15. cfpresenter
    16. cfprocessingdirective
    17. cfprint
    18. cfreport
    19. cfreportparam
    20. cfsilent
    21. cftable
  5. Debugging tags
    1. cfdump
    2. cftimer
    3. cftrace
  6. Exception handling tags
    1. cfcatch
    2. cferror
    3. cffinally
    4. cfrethrow
    5. cfthrow
    6. cftry
  7. Extensibility tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcollection
    5. cfcomponent
    6. cfexecute
    7. cfftp
    8. function
    9. cfindex
    10. cfinterface
    11. cfinvoke
    12. cfinvokeargument
    13. cfobject
    14. cfproperty
    15. cfreport
    16. cfreportparam
    17. cfreturn
    18. cfsearch
    19. cfsharepoint
    20. cfspreadsheet
    21. cfwddx
    22. cfxml
  8. File management tags
    1. cfdirectory
    2. cffile
    3. cffileupload
    4. cfftp
    5. cfzip
    6. cfzipparam
  9. Flow-control tags
    1. cfabort
    2. cfbreak
    3. cfcase
    4. cfcontinue
    5. cfdefaultcase
    6. cfelse
    7. cfelseif
    8. cfexecute
    9. cfexit
    10. cfif
    11. cfinclude
    12. cflocation
    13. cfloop
    14. cfrethrow
    15. cfswitch
    16. cfthrow
    17. cftry
  10. Forms tags
    1. cfapplet
    2. cfcalendar
    3. cffileupload
    4. cfform
    5. cfformgroup
    6. cfformitem
    7. cfgrid
    8. cfgridcolumn
    9. cfgridrow
    10. cfgridupdate
    11. cfinput
    12. cfpdf
    13. cfpdfform
    14. cfpdfformparam
    15. cfpdfparam
    16. cfpdfsubform
    17. cfselect
    18. cfslider
    19. cftextarea
    20. cftree
    21. cftreeitem
  11. Internet Protocol tags
    1. cfajaximport
    2. cfajaxproxy
    3. cfftp
    4. cffeed
    5. cfimap
    6. cfhttp
    7. cfhttpparam
    8. cfldap
    9. cfmail
    10. cfmailparam
    11. cfmailpart
    12. cfpop
    13. cfsprydataset
  12. Page processing tags
    1. cfcache
    2. cfcontent
    3. cfflush
    4. cfheader
    5. cfhtmlhead
    6. cfinclude
    7. cfprocessingdirective
    8. cfsetting
    9. cfsilent
  13. Security tags
  14. Variable manipulation tags
    1. cfcookie
    2. cfdump
    3. cfparam
    4. cfregistry
    5. cfsavecontent
    6. cfschedule
    7. cfset
    8. cfsetting
  15. Other tags
    1. cfimage
    2. cflog
    3. cfregistry
3. Tags a-b
4. Tags c
  1. cfcache
  2. cfcalendar
  3. cfcase
  4. cfcatch
  5. cfchart
  6. cfchartdata
  7. cfchartseries
  8. cfclient
  9. cfclientsettings
  10. cfcol
  11. cfcollection
  12. cfcomponent
  13. cfcontent
  14. cfcontinue
  15. cfcookie
  16. Tags d-e
5. Tags f
6. Tags g-h
  1. cfgraph
  2. cfgraphdata
  3. cfgrid
  4. cfgridcolumn
  5. cfgridrow
  6. cfgridupdate
  7. cfheader
  8. cfhtmlhead
  9. cfhtmltopdf
  10. cfhtmltopdfitem
  11. cfhttp
  12. cfhttpparam
7. Tags i
  1. cfif
  2. cfimage
  3. cfimap
  4. cfimapfilter
  5. cfimpersonate
  6. cfimport
  7. cfinclude
  8. cfindex
  9. cfinput
  10. cfinsert
  11. cfinterface
  12. cfinvoke
  13. cfinvokeargument
8. Tags j-l
9. Tags m-o
10. Tags p-q
  1. cfparam
  2. cfpdf
  3. cfpdfform
  4. cfpdfformparam
  5. cfpdfparam
  6. cfpdfsubform
  7. cfpod
  8. cfpop
  9. cfpresentation
  10. cfpresentationslide
  11. cfpresenter
  12. cfprint
  13. cfprocessingdirective
  14. cfprocparam
  15. cfprocresult
  16. cfprogressbar
  17. cfproperty
  18. cfquery
  19. cfqueryparam
11. Tags r-s
  1. cfregistry
  2. cfreport
  3. cfreportparam
  4. cfrethrow
  5. cfreturn
  6. cfsavecontent
  7. cfschedule
  8. cfscript
  9. cfsearch
  10. cfselect
  11. cfservlet
  12. cfservletparam
  13. cfset
  14. cfsetting
  15. cfsharepoint
  16. cfsilent
  17. cfslider
  18. cfspreadsheet
  19. cfsprydataset
  20. cfstoredproc
  21. cfswitch
12. Tags t
  1. cftable
  2. cftextarea
  3. cftextinput
  4. cfthread
  5. cfthrow
  6. cftimer
  7. cftooltip
  8. cftrace
  9. cftransaction
  10. cftree
  11. cftreeitem
  12. cftry
13. Tags u-z
  1. cfupdate
  2. cfwddx
  3. cfwebsocket
  4. cfwindow
  5. cfxml
  6. cfzip
  7. cfzipparam
CFML Reference
1. Reserved words and variables
2. Ajax JavaScript functions
3. ColdFusion ActionScript functions
4. ColdFusion mobile functions
5. Application.cfc reference
6. Script functions implemented as CFCs
7. ColdFusion Flash Form style reference
8. ColdFusion event gateway reference
9. ColdFusion C++ CFX Reference
10. ColdFusion Java CFX reference
11. WDDX JavaScript Objects
Cloud services

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.

History

ColdFusion 11: You can also use the types without the prefix CF_SQL_.

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.

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

Adobe

Get help faster and easier

New user?

cfqueryparam

Description

Category

History

Syntax

See also

Attributes

Usage

Example

Get help faster and easier

Ask the Community

Contact Us