<cfqueryparam>

Any time a value from outside your code — a form field, a URL parameter, anything a user could type — ends up inside a SQL statement, there's a risk: SQL injection, where an attacker crafts input that changes what the query actually does, potentially reading or destroying data it shouldn't.

<cfqueryparam> is ColdFusion's defense against this. Instead of gluing a value directly into the SQL string, it passes the value as a proper bind variable — the database treats it strictly as data, never as SQL syntax.

Learning Objectives

After completing this lesson, you'll be able to:

Explain what SQL injection is and why cfqueryparam prevents it.
Use cfqueryparam with the correct cfsqltype.
Use the list attribute for a SQL IN clause.
Handle NULL values correctly.
Use the equivalent named-parameter binding in queryExecute().

The Problem: SQL Injection

Concatenating user input directly into SQL lets an attacker inject their own SQL. This example is intentionally vulnerable — never write code like this.

Vulnerable — do not use

<cfquery name="user" datasource="cfdocexamples">
    SELECT * FROM Users WHERE Username = '#url.username#'
</cfquery>

NOTE

If url.username is set to something like ' OR '1'='1, the query's logic changes entirely — potentially returning every user in the table, not just one.

The Fix: cfqueryparam

Tag Syntax

<cfquery name="user" datasource="cfdocexamples">
    SELECT * FROM Users
    WHERE Username = <cfqueryparam value="#url.username#" cfsqltype="cf_sql_varchar">
</cfquery>

NOTE

Now url.username is always treated as a literal value being compared, never as part of the SQL statement itself — no matter what it contains.

Common cfsqltype Values

cfsqltype	For
cf_sql_varchar	Text
cf_sql_integer	Whole numbers
cf_sql_decimal	Decimal numbers
cf_sql_date / cf_sql_timestamp	Dates and date-times
cf_sql_bit	Booleans (true/false)

NOTE

Matching the type to the actual database column type helps catch bad data early and lets the database optimize the query properly.

Using cfqueryparam in a SQL IN Clause

The list attribute lets a single cfqueryparam represent multiple values — each one still safely bound, not just glued together as a string.

Tag Syntax

<cfquery name="selected" datasource="cfdocexamples">
    SELECT * FROM Employees
    WHERE Emp_ID IN (<cfqueryparam value="#idList#" cfsqltype="cf_sql_integer" list="true">)
</cfquery>

NOTE

idList here is a comma-delimited string like "3,7,12" — each value in the list is still individually bound and validated as an integer.

Handling NULL Values

Tag Syntax

<cfquery datasource="cfdocexamples">
    INSERT INTO Employees (FirstName, MiddleName)
    VALUES (
        <cfqueryparam value="#firstName#" cfsqltype="cf_sql_varchar">,
        <cfqueryparam value="#middleName#" cfsqltype="cf_sql_varchar" null="#middleName EQ ''#">
    )
</cfquery>

NOTE

When null evaluates to true, the value attribute is ignored entirely and an actual database NULL is inserted instead of an empty string.

The CFScript Equivalent: Named Parameters

queryExecute() takes a struct of parameters, referenced in the SQL by name with a colon prefix — the CFScript equivalent of cfqueryparam.

CFScript

<cfscript>

user = queryExecute(
    "SELECT * FROM Users WHERE Username = :username",
    {username: {value: url.username, cfsqltype: "cf_sql_varchar"}},
    {datasource: "cfdocexamples"}
);

</cfscript>

NOTE

Each parameter can be a plain value for simple cases, or a struct with value + cfsqltype when you need to specify the type explicitly, as shown here.

Real-World Example: A Safe Search Feature

Tag Syntax

<cfquery name="results" datasource="cfdocexamples">
    SELECT FirstName, LastName
    FROM Employees
    WHERE LastName LIKE <cfqueryparam value="#searchTerm#%" cfsqltype="cf_sql_varchar">
</cfquery>

NOTE

Even with a wildcard % appended, the parameter is still safely bound — the % is just part of the string value being compared, not SQL syntax.

Common Beginner Mistakes

Skipping cfqueryparam for values that "seem safe"

A URL parameter, a hidden form field, an ID from a link — all of these can be tampered with by a user before reaching your code. Any value that isn't a hardcoded literal you wrote yourself should go through cfqueryparam.

Using the wrong cfsqltype

Passing cf_sql_varchar for a numeric column can cause type-conversion errors or unexpected sorting behavior — match the type to the actual database column.

Forgetting list="true" when passing a delimited string for an IN clause

Without it, cfqueryparam treats the whole "3,7,12" string as a single value being compared, not three separate values — which breaks an IN clause.

Best Practices

Use cfqueryparam (or named parameter binding in queryExecute()) for every dynamic value in every query, without exception.
Always specify cfsqltype explicitly rather than relying on a default.
Use the null attribute rather than trying to pass an empty string when a database NULL is what's actually needed.

Interview Questions

What does cfqueryparam actually protect against?

SQL injection — it ensures a value is always treated strictly as data by the database, never interpreted as part of the SQL statement itself.

How do you use cfqueryparam for a SQL IN clause with multiple values?

Pass a delimited string as the value and set list="true".

How do you insert a real database NULL with cfqueryparam?

Set null="true" (or an expression that evaluates to true) — this makes cfqueryparam ignore the value attribute entirely and pass NULL instead.

What's the CFScript equivalent of cfqueryparam?

Named parameters in queryExecute()'s second argument — a struct where each key can be a plain value or a {value, cfsqltype} struct.

Summary

In this lesson, you learned why SQL injection happens, how cfqueryparam prevents it by binding values instead of concatenating them into SQL text, how to use it for IN clauses and NULL values, and its CFScript equivalent — named parameters in queryExecute().

What's Next?

The next lesson covers Query of Queries — running SQL against an existing query result instead of a database.