cfqueryparam

Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables CFML 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.NOTE: Due to security it's highly recommended to use this tag for any user input or non-static value used in a query to prevent code injections and the like.

<cfqueryparam>

cfqueryparam();

CF4.5+

Lucee

BL 1.0.0+

Attribute Reference

value string

Value that CFML 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 string
Default: `CF_SQL_CHAR`

SQL type that parameter (any type) is bound to. As of CF 11+ or Lucee 4.5+ you can omit the cf_sql_ prefix. See [CFSqlType Cheatsheet](https://cfdocs.org/cfsqltype-cheatsheet) for a mapping of CFSQL data types to DBMS data types.

Values:

cf_sql_bigint
cf_sql_bit
cf_sql_char
cf_sql_blob
cf_sql_clob
cf_sql_nclob
cf_sql_date
cf_sql_decimal
cf_sql_double
cf_sql_float
cf_sql_idstamp
cf_sql_integer
cf_sql_longvarchar
cf_sql_longnvarchar
cf_sql_money
cf_sql_money4
cf_sql_nchar
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
bigint
bit
char
blob
clob
nclob
date
decimal
double
float
idstamp
integer
longvarchar
longnvarchar
money
money4
nchar
nvarchar
numeric
real
refcursor
smallint
sqlxml
time
timestamp
tinyint
varchar

maxlength numeric

Maximum length of parameter.

scale numeric
Default: `0`

Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.

null boolean

Whether parameter is passed as a NULL value. Yes: ignores the value attribute and passes NULL No: passes the value attribute

list boolean

Yes: The value attribute value is a delimited list No: it is not

separator string

Character that separates values in list, in value attribute.

Compatibility

ColdFusion:

Version 4.5+ CF2018+ Throws exceptions on invalid cfsqltype values, prior versions treated invalid types as varchar - see CF-4198194 for reference.

Lucee:

Supports int and cf_sql_int for integer

Railo:

Supports int and cf_sql_int for integer

BoxLang:

Version 1.0.0+ <bx:queryparam> in BoxLang

Links more information about cfqueryparam

Mastering cfqueryparam - A guide to understanding all the ins and outs of using cfqueryparam.

Examples
Sample code using the cfqueryparam tag

Basic example

Shows how to use a cfqueryparam tag within cfquery.

<cfquery name="news">
    SELECT id,title,story
    FROM news
    WHERE id = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
</cfquery>

Using a list on an IN statement

Assumes url.idList is a comma separated list of integers, eg: 1,2,3

<cfquery name="news">
    SELECT id,title,story
    FROM news
    WHERE id IN (<cfqueryparam value="#url.idList#" cfsqltype="cf_sql_integer" list="true">)
</cfquery>

Using an expressions to controll null values

Shows a basic example of using an expression to control whether null is passed to the queryparam

<cfquery name="test">
      INSERT into test ( key, value )
      VALUES(
            <cfqueyparam value="#key#" cfsqltype="cf_sql_varchar" null="#isNumeric(Key) EQ false#">
            <cfqueryparam value="#value#" cfsqltype="cf_sql_varchar" null="#value EQ ''#">
      )
</cfquery>

cfscript equivalent of cfqueryparam

CF 11+ script syntax using queryExecute and struct notation

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
  "SELECT title FROM exampleData WHERE id = :id", 
  { id = 2 },
  { dbtype="query" }
);

writeOutput( result.title[1] );

Expected Result: Man walks on Moon

cfscript equivalent of cfqueryparam

CF 11+ script syntax using queryExecute and struct notation for multiple parameters

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
 "SELECT * FROM exampleData WHERE id = :id AND title = :title", 
 { 
 title={value="Man walks on Moon", cfsqltype="cf_sql_varchar"}, id={value=2, cfsqltype="cf_sql_integer"} 
 },
 { dbtype="query" } 
); 

writeOutput(result.title[1]);

Expected Result: Man walks on Moon

cfscript equivalent of cfqueryparam

CF 11+ script syntax using queryExecute and full array notation

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
  "SELECT title FROM exampleData WHERE id = ?", 
  [
    { value=2, cfsqltype="cf_sql_varchar" }
  ],
  { dbtype="query" }
);

writeOutput( result.title[1] );

Expected Result: Man walks on Moon

cfscript equivalent of cfqueryparam

CF 11+ script syntax using queryExecute and array shorthand

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
  "SELECT title FROM exampleData WHERE id = ?", 
  [ 2 ],
  { dbtype="query" }
);

writeOutput( result.title[1] );

Expected Result: Man walks on Moon

© 2012–present Foundeo, Inc. and the CFDocs contributors.
Licensed under the MIT License.
ColdFusion is a trademark of Adobe Systems Incorporated.
https://cfdocs.org/cfqueryparam

cfqueryparam

Attribute Reference

value string

cfsqltype string Default: CF_SQL_CHAR

maxlength numeric

scale numeric Default: 0

null boolean

list boolean

separator string

Compatibility

ColdFusion:

Lucee:

Railo:

BoxLang:

Links more information about cfqueryparam

Examples Sample code using the cfqueryparam tag

cfsqltype string
Default: `CF_SQL_CHAR`

scale numeric
Default: `0`

Examples
Sample code using the cfqueryparam tag