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();

  1. CFDocs
  2. Tags
  3. Database Tags
  4. cfqueryparam
  5. CF4.5+
  6. Lucee
  7. BL 1.0.0+
  8. 0 Issue
  9. Edit
See Also:   cfquery   queryExecute

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

Examples
Sample code using the cfqueryparam tag

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>

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>

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>

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

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

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

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

Signup for cfbreak to stay updated on the latest news from the ColdFusion / CFML community. One email, every friday.

Fork me on GitHub