<cfqueryparam>

edit

Checks the data type of a query parameter. The cfqueryparam tag is nested within a cfquery tag and embedded within the SQL statement.

This tag:

  • Improves security by preventing SQL injection attacks
  • Provides data validation for parameter values
  • Enhances performance by enabling database query caching
  • Correctly handles type conversion between CFML and database types

This tag cannot have a body.

This tag is also supported within <cfscript>

<cfqueryparam value=any sqlType=string CFSQLType=string maxLength=number charset=string scale=number null=boolean list=boolean separator=string >
Attribute Description
value
any, optional 		edit

Specifies the actual value that Lucee passes to the database.

  • For standard parameters: The single value used in the query

  • For list parameters: Can be an array, list string, or other collection type

  • For null values: Set the null attribute to true and this attribute is ignored

    The value will be automatically escaped to prevent SQL injection.
sqlType
string, optional 		edit

The SQL data type that the parameter will be bound to.

Common types include:

  • varchar: For strings (default)
  • integer, bigint, smallint, tinyint: For various sized integers
  • double, decimal, money: For floating point/decimal values
  • bit, boolean: For boolean values
  • date, time, timestamp: For date/time values
  • binary, blob: For binary data

A list of SQL types can be found on the SQL Type page. All can be used with or without the CF_SQL_ prefix. Using the correct type improves security, enables proper type checking, and optimizes query execution.
maxLength
number, optional 		edit

Maximum allowed length of the parameter value (validation check).

  • For string values: The maximum number of characters allowed
  • For binary values: The maximum number of bytes allowed
  • If the value exceeds this length, Lucee will throw an exception
  • If not specified, defaults to the actual length of the provided value
  • If the attribute charset is defined, string length comparison will be byte-based for that encoding
charset
string, optional 		edit

Specifies the character encoding for string validation.

This attribute serves two purposes:

  1. It validates that the given value is compatible with the specified charset
  2. It determines how byte length is calculated for maxLength validation

Common values include UTF-8, ISO-8859-1, or other valid Java charset names.This attribute is used for 2 things:

  • it checks if the given value is compatible with that charset
  • to check the binary length of the value (see attribute maxlength).

Introduced: 5.3.8.25
scale
number, optional 		edit

Number of decimal places to allow for numeric parameters.

  • Only applies to decimal data types (decimal, numeric, money, etc.)
  • Values with more decimal places than specified will be rounded
  • Particularly important for financial calculations to control precision
  • Default value is zero if not specified
null
boolean, optional 		edit

Indicates whether the parameter should be treated as NULL.

  • true: The parameter is passed as NULL to the database (ignores the value attribute)
  • false: The parameter uses the value provided in the value attribute
  • Default is false

SQL comparison with NULL requires special syntax in most databases (using IS NULL rather than = NULL).
list
boolean, optional 		edit

Controls how the parameter value is handled:

  • true: The parameter value is treated as a list of values (typically used with SQL's IN operator)
  • false: The parameter is handled as a simple value
  • Not set: Lucee automatically detects if the value is an array and treats it as a list if it is (but not for byte arrays)

When working with lists/arrays:

  • Empty Arrays: When an empty array is provided with list=true (or auto-detected as a list), Lucee will handle this correctly by effectively omitting the parameter from the query
  • Usage with IN: Typically used with SQL's IN operator
  • Array Elements: Each element in the array will be properly typed according to the specified sqlType
separator
string, optional 		edit

Specifies the character that separates values in string lists.

  • Only used when list="true" and the value is a string (not an array)
  • Default separator is a comma (,)
  • Common alternatives include semicolon (;), pipe (|), or tab
  • Example: For a string like "red;green;blue" with list="true" separator=";", the values would be treated as three separate items

Note: For best results with complex separators or values that might contain the separator character, consider using an array instead of a delimited string.

Unimplemented Attribute(s)

Attribute Description
CFSQLType
string, optional 		edit

This attribute has been deprecated, use the sqlType attribute instead which provides identical functionality.

Maintained for backward compatibility with older code that used the CFML-specific naming convention.
* deprecated *

Usage Notes

edit

Prior to Lucee 6, an empty value would be auto cast to null, since Lucee 6 only strings are handled this way LDEV-4410

In Lucee 6, this is no longer the default behavior and throws an exception, which matches ACF.

You can re-enable the old behavior by setting this environment variable or system property to true.

Environment Variable: LUCEE_QUERY_ALLOWEMPTYASNULL=TRUE
System Property: -Dlucee.query.allowemptyasnull="true"

Examples

edit 
	<cfscript>
		_test = queryNew("_id,_need,_forWorld","integer,varchar,varchar", [[01,'plant', 'agri'],[02, 'save','water']]);
	</cfscript>
	<cfquery name="qTest" dbtype="query">
		select * from _test
		where _id = <cfqueryparam sqltype="integer" value="2" />
	</cfquery>
	<cfdump var="#qtest#" />

Related System Properties / Environment Variables

  • LUCEE_QUERY_ALLOWEMPTYASNULL - In Lucee 5, an empty string passed into a query parameter with a numeric type was interpreted as null. In Lucee 6, this is no longer accepted and throws an exception. You can simulate the old behavior by setting this environment variable or SysProp to `true`. By setting the log level of the datasource log to `warn`, you will receive information in the log when the old behavior is used. This allows you to modify your code for the new behavior without encountering runtime issues with the existing code
    Type: boolean, Default: false

See also