<cfqueryparam>
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
|
Specifies the actual value that Lucee passes to the database.
|
sqlType
string, optional
|
The SQL data type that the parameter will be bound to. Common types include:
A list of SQL types can be found on the SQL Type page. All can be used with or without the |
maxLength
number, optional
|
Maximum allowed length of the parameter value (validation check).
|
charset
string, optional
|
Specifies the character encoding for string validation. This attribute serves two purposes:
Common values include
Introduced: 5.3.8.25 |
scale
number, optional
|
Number of decimal places to allow for numeric parameters.
|
null
boolean, optional
|
Indicates whether the parameter should be treated as NULL.
SQL comparison with NULL requires special syntax in most databases (using |
list
boolean, optional
|
Controls how the parameter value is handled:
When working with lists/arrays:
|
separator
string, optional
|
Specifies the character that separates values in string lists.
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)
Usage Notes
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
<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
- Compatibility / Migration with other CFML engines
- Queries
- QuerySetCell()
- Query Execution in Lucee
- SQL Types
- <cfquery>
- Search Issue Tracker
- Search Lucee Test Cases (good for further, detailed examples)