Module 14 built APIs that take input from the outside world. This module is about what happens when that input is actively hostile. SQL injection is the oldest, simplest, and still one of the most damaging vulnerabilities in web applications, and the entire fix comes down to one idea: never let user input become part of the SQL itself.
Learning Objectives
After completing this lesson, you'll be able to:
- Explain exactly why #variable# directly inside a <cfquery> is dangerous.
- Secure a tag-based query with cfqueryparam, including every relevant attribute.
- Secure a queryExecute() call in CFScript with named parameters.
- Safely parameterize a list of values for a SQL IN clause.
- Know a real, documented example of Adobe patching exactly this class of bug.
How Parameterization Fits Together
User Input Arrives
form, url, or cookie value
cfqueryparam Binds It
treated strictly as data
Database Executes Safely
injected SQL can't run
Correct Result Returned
query behaves as intended
Why Direct Variable Concatenation Is Dangerous
When a variable is placed directly inside a query string, the database can't tell the difference between the SQL you wrote and data an attacker controls. If username comes from a form field, and someone submits ' OR '1'='1 as their username, the query's actual logic changes, potentially returning every row in the table instead of none.
<cfquery name="getUser" datasource="myDSN">
SELECT userID, username, email
FROM users
WHERE username = '#form.username#'
AND password = '#form.password#'
</cfquery>This isn't a theoretical risk, it's exploitable with nothing more than a browser and a form field, no special tools required.
The Fix: cfqueryparam
cfqueryparam wraps a value so the database driver sends it as a bound parameter, not as literal SQL text. The database then treats it strictly as data, even if it contains characters that would otherwise be interpreted as SQL syntax.
<cfquery name="getUser" datasource="myDSN">
SELECT userID, username, email
FROM users
WHERE username = <cfqueryparam value="#form.username#" cfsqltype="cf_sql_varchar">
AND password = <cfqueryparam value="#form.password#" cfsqltype="cf_sql_varchar">
</cfquery>cfqueryparam's Attributes
| Attribute | Meaning |
|---|---|
| value | The actual value being passed (required) |
| cfsqltype | The SQL data type, e.g. cf_sql_varchar, cf_sql_integer (default: cf_sql_char) |
| maxLength | Caps the value's length, checked before it ever reaches the database |
| null | yes/no, whether to pass a real database NULL instead of value |
| list | yes/no, treat value as a delimited list rather than one value |
| separator | The list's delimiter when list="yes" (default: comma) |
| scale | Decimal places, for cf_sql_numeric / cf_sql_decimal types |
A Real Example: Parameterizing a List for an IN Clause
A comma-delimited list of IDs going into a SQL IN clause needs list="true" specifically, a plain cfqueryparam would otherwise try to bind the whole list as one single value.
<cfquery name="news" datasource="myDSN">
SELECT id, title, story
FROM news
WHERE id IN (<cfqueryparam value="#url.idList#" cfsqltype="cf_sql_integer" list="true">)
</cfquery>The Same Fix in CFScript: queryExecute()
queryExecute() takes the SQL as its first argument and a struct of named parameters as its second, using :name placeholders in the SQL instead of string concatenation. Each parameter can be a plain value, or a struct specifying cfsqltype explicitly.
getUser = queryExecute(
"SELECT userID, username, email FROM users WHERE username = :userParam",
{
userParam: { value: url.username, cfsqltype: "cf_sql_varchar" }
}
);queryExecute() also supports positional ? placeholders with an array of values instead of named :placeholders with a struct, named parameters are generally easier to read once a query has more than one or two of them.
A Real Example: Adobe Patching This Exact Class of Bug
This isn't just a theoretical lesson. In September 2026, Adobe shipped a coordinated security patch for both supported ColdFusion versions that added automatic identifier validation to cfgridupdate and cfstoredproc specifically, because those two tags could have table or stored-procedure names built from unvalidated input, the exact same root problem as an unparameterized query, just with identifiers instead of values.
Common Beginner Mistakes
Assuming cfqueryparam alone makes every query safe
It protects values, not identifiers. A table or column name built from user input (dynamic ORDER BY, dynamic table selection) can't be parameterized with cfqueryparam at all, it needs to be validated against an allowlist of known-safe values instead.
Skipping cfsqltype or leaving it at the default
The default (cf_sql_char) doesn't catch a type mismatch the way an explicit cf_sql_integer would. Setting the correct type lets ColdFusion reject obviously wrong input (like SQL syntax where a number was expected) before the query ever reaches the database.
Forgetting list="true" when parameterizing a SQL IN clause
Without it, cfqueryparam tries to bind the entire comma-delimited string as one single value, which doesn't match multiple rows the way the query actually intends.
Assuming an ORM or stored procedure is automatically injection-proof
ormExecuteQuery() with a raw, concatenated HQL/SQL string, or a <cfprocparam> that isn't properly bound, can reintroduce the exact same vulnerability. The tool abstracts the SQL, it doesn't eliminate the need to parameterize.
Best Practices
- Use cfqueryparam (or queryExecute()'s named parameters) for every single value from outside the application, no exceptions for "trusted" input.
- Set cfsqltype explicitly to the real expected type, don't rely on the default.
- Validate dynamic identifiers (table/column names) against an allowlist, since they can't be parameterized with cfqueryparam.
- Keep the database account behind your datasource limited to only the permissions it actually needs (principle of least privilege), so even a successful injection has a limited blast radius.
Interview Questions
Why does #variable# directly inside a <cfquery> create a SQL injection vulnerability?
The database can't distinguish between the SQL you wrote and data an attacker controls, since the variable's value becomes literal text inside the query string. A value like ' OR '1'='1 changes the query's actual logic rather than being treated as a simple comparison value.
What does cfqueryparam actually do differently from plain string interpolation?
It sends the value to the database as a bound parameter rather than as part of the SQL text, so the database treats it strictly as data regardless of what characters it contains. It never gets a chance to be interpreted as SQL syntax.
How would you safely parameterize a comma-delimited list of IDs for a SQL IN clause?
cfqueryparam with list="true" (and the correct cfsqltype for the list's values), or the equivalent in queryExecute()'s named-parameter struct. Without list="true", the whole delimited string gets bound as one value instead of multiple.
Does cfqueryparam protect a query that builds its table or column name from user input?
No, cfqueryparam only parameterizes values, not SQL identifiers like table or column names. A dynamic table/column name needs to be checked against an allowlist of known-safe values instead, since it can't be bound as a parameter at all.
Summary
In this lesson, you secured a tag-based query with cfqueryparam, covered its key attributes (cfsqltype, maxLength, null, list, separator), parameterized a list for a SQL IN clause, did the same thing in CFScript with queryExecute()'s named parameters, and saw a real example of Adobe patching this exact class of vulnerability in cfgridupdate and cfstoredproc.
What's Next?
The next lesson covers XSS prevention: the output-side equivalent of this lesson, encoding data correctly before it goes back out into HTML, an HTML attribute, or JavaScript.