# <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>](script.md)

```
<cfqueryparam
    value=any
    sqlType=string
    CFSQLType=string
    maxLength=number
    charset=string
    scale=number
    null=boolean
    list=boolean
    separator=string
>
```

# Attributes

| Attribute | Type | Required | Description | Default |
|-----------|------|----------|-------------|---------|
| value | any | No | 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 | No | 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](/guides/cookbooks/Sql-Types.html). 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 | No | 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 | No | 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). |  |
| scale | number | No | 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 | No | 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 | No | 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 | No | 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. |  |


# 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](https://luceeserver.atlassian.net/browse/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

```cfml
	<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](../../recipes/environment-variables-system-properties.md#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*



# Categories

[Compatibility / Migration with other CFML engines](../../categories/compat.md), [Queries](../../categories/query.md)

# See Also

[QuerySetCell()](../functions/querysetcell.md), [Query Execution in Lucee](../../recipes/query-handling.md), [SQL Types](../../recipes/sql-types.md), [<cfquery>](query.md)