Definition
cfquery is a ColdFusion tag that lets you run SQL statements (like SELECT, INSERT, UPDATE, DELETE) against a database. Think of it as the bridge between your ColdFusion application and your database server. You write SQL inside the tag, ColdFusion sends it to the configured data source (DSN), and returns a query object you can loop over or inspect.
- Platform context: Works in Adobe ColdFusion and Lucee.
- Typical use: Retrieving data to display, writing data from forms, calling stored procedures, and handling transactions.
- Output: A query object (record set) plus an optional result struct with metadata like row count and execution time.
How cfquery Works
Overview of the Request-to-Database Flow
- Your ColdFusion page or component executes a
tag. - ColdFusion reads the SQL and connects to the configured datasource (DSN).
- If parameters are used (via cfqueryparam), they are bound safely to the SQL.
- The database executes the statement and returns rows (for SELECT) or affected row counts (for INSERT/UPDATE/DELETE).
- ColdFusion stores the results in a query object identified by the tag’s name attribute, and optionally returns result details.
Where the Data Source Comes From
- Usually configured in the ColdFusion Administrator or Lucee Admin with a name (DSN).
- The
tag references this DSN via the datasource attribute. - Credentials can be embedded, but best practice is to store them in the DSN rather than in code.
Syntax and Core Attributes
Basic Pattern
SELECT id, email, created_at
FROM users
WHERE active = 1
ORDER BY created_at DESC
Common Attributes
- name: Variable name for the resulting query object.
- datasource: DSN to connect to.
- username / password: Optional; prefer using DSN credentials.
- result: Stores metadata (e.g., SQL, recordCount, generatedKey, executionTime).
- maxrows: Limits number of rows returned.
- timeout: Query timeout in seconds.
- cachedWithin: Enables query caching for a time span.
- blockfactor: Fetch Optimization hint for some drivers.
- dbtype=”query”: Enables Query of Queries (run SQL against an in-memory query result).
Parameterization and Security
Why Use cfqueryparam
- Prevents SQL injection by binding variables safely.
- Clarifies data types for the database.
- Can improve execution plan reuse.
Example
SELECT id, email
FROM users
WHERE id =
Key cfqueryparam Attributes
- value: The variable to bind.
- cfsqltype: Data type (e.g., cf_sql_varchar, cf_sql_integer, cf_sql_timestamp).
- list=”yes”: Treats value as a list for IN clauses.
- null=”yes”: Sends NULL if value is empty and nulls allowed.
- scale / maxlength: Precision or length hints.
Tip: Never concatenate user input into your SQL. Always use cfqueryparam.
Execution Flow: Step-by-Step
- Prepare: Choose the DSN and write SQL.
- Bind Parameters: Use cfqueryparam for every variable.
- Execute: ColdFusion sends prepared statement to DB.
- Receive Result: Query object and optional result struct.
- Render/Use Data: Loop over results, or inspect result.recordCount.
- Handle Errors: Wrap with cftry/cfcatch or cftransaction where needed.
Practical Examples and Real-World Use Cases
1) Listing Active Users
SELECT id, email, role
FROM users
WHERE active =
ORDER BY id DESC
#id# – #email# (#role#)
2) Inserting a New Record with Transaction
INSERT INTO users (email, role, active, created_at)
VALUES (
)
3) Query of Queries (QoQ)
SELECT id, name, region FROM customers
SELECT id, name FROM allCustomers
WHERE region = ‘West’
ORDER BY name
4) cfscript Equivalent (queryExecute)
userId = 42;
q = queryExecute(
“SELECT id, email FROM users WHERE id = ?”,
[ {value: userId, cfsqltype: “cf_sql_integer”} ],
{ datasource: “myDSN”, timeout: 10 }
);
Performance tuning and Caching
Tips
- Use indexes on frequently filtered columns.
- Fetch only the columns you need; avoid SELECT *.
- Set maxrows when only the top N rows are needed.
- Use cachedWithin for read-heavy, slow-changing data.
Example: Caching
SELECT id, title
FROM posts
WHERE views > 1000
ORDER BY views DESC
This caches results for 5 minutes, reducing database load.
Best practices
- Always use cfqueryparam to prevent injection and to declare types.
- Keep SQL readable and maintainable: break lines, alias columns, comment where necessary.
- Use cftransaction for multi-statement operations that must be atomic.
- Centralize DSN Configuration in the Admin or Application.cfc (this.datasource in Lucee/CF) rather than hard-coding.
- Validate and sanitize inputs before executing queries, even when using cfqueryparam.
- Monitor Performance with query logs and the result struct (e.g., executionTime).
- Separate Data access into service/DAO layers in larger apps for cleaner Architecture.
- Prefer queryExecute() in cfscript for script-style components/services.
Common pitfalls and Troubleshooting
- Missing DSN or incorrect datasource name: Verify in Admin and Application.cfc.
- Data type mismatches: Ensure cfqueryparam cfsqltype matches column type.
- Timeout errors: Review long-running SQL, add indexes, or increase timeout temporarily.
- “QoQ” limitations: Query of Queries supports a subset of SQL; complex joins may fail.
- SQL injection risk: Concatenated strings or preserveSingleQuotes without binding variables.
- Transaction deadlocks: Keep transactions short, consistent ordering of updates, and appropriate isolation if needed.
- Unicode/encoding issues: Ensure datasource and database collation/charset are configured correctly.
Key Points at a Glance
- CFQUERY executes SQL against a configured datasource (DSN) and returns a query object.
- Use cfqueryparam for safety, correctness, and potential performance benefits.
- Manage multi-step operations with cftransaction.
- Improve performance with indexes, selective columns, maxrows, and cachedWithin where appropriate.
- For script-based code, use queryExecute() as the cfscript equivalent.
FAQ
What is the difference between CFQUERY and queryExecute()?
CFQUERY is the tag-based way to run SQL, while queryExecute() is the cfscript function that does the same job programmatically. Both support parameters, result structs, and datasource options. Choose based on whether you write in tag-based templates or cfscript components.
How do I protect against SQL injection in ColdFusion?
Use cfqueryparam for every dynamic value. Do not concatenate user input into SQL strings. Validate inputs and enforce expected data types.
Can I call stored procedures with CFQUERY?
Yes, depending on the database, you can call stored procedures using CFQUERY with proper SQL Syntax, or use cfstoredproc for a more structured approach with parameters and return values.
How can I cache query results?
Use the cachedWithin attribute on CFQUERY with CreateTimeSpan to specify the cache duration. This is effective for read-heavy operations where data doesn’t change frequently.
What does the result attribute provide?
The result struct includes metadata such as recordCount, SQL, executionTime, generatedKey (for insert with identity/auto-increment), and more, which are helpful for logging and diagnostics.
