How to Query A Database with CFQuery in CFML
In this tutorial, you'll learn how to build and execute database queries with the cfquery
tag in CFML. We'll cover all the attributes and how to use them, the tag and script syntax versions, how to iterate over each row in a record set, and how to prevent SQL injection attacks.
The Attributes
Here are all the attributes for the cfquery
tag:
name
string - The name of the query, used to reference the generated query's resultset; the only required attribute for this tag.blockFactor
numeric - The maximum number of rows to retrieve at a time from the server, from 1 to 100.cachedAfter
date - A date value used to determine when to reference cached data. If the date of the original query is after the provided date, the application server will use the cached query data if it exists instead of fetching new data. Examples of valid date values to pass in are "July 19, 2023" or "07/19/2023".cacheID
string - The ID used when storing or removing query results in the cache.cacheRegion
string - The cache region used to cache the query result. The default is the "query" region.cachedWithin
timestamp - The timespan the query data is stored on the server, from the original date the query was executed to the provided timespan using theCreateTimeSpan()
function. If the original query execution date surpasses the provided timespan, the resulting query will flush from the cache. To query against cached data, the SQL statement, data source, query name, user name, and password should all be an exact match.clientInfo
struct - A structure containing properties of the client in the database connection.dataSource
string - The name associated with the database connection, set up in the ColdFusion and Lucee Administrator console. The data source attribute is not required if already defined in theApplication.cfc
file by the variablethis.datasource
.dbType
string - Values can either be query to gather data from a cached resultset via QoQ (Query of Query) or hql which is associated with ORM (Object-Relational Mapping).debug
boolean - If yes, returns the SQL statement provided as well as the record count and additional query-specific data. If no, The "Administrator Database Activity" option in the Adminstrator is enabled and suppresses this data for faster processing. Do not use this attribute in production environments.disableAutoGenKeys
boolean - Disabled by default (false). If set to true, the resultset will not return an auto-generated key from a SQL INSERT statement.fetchClientInfo
boolean - Disabled by default (no). If set to yes, a struct data type with key-value pairs that were passed by the last query will be returned.maxRows
numeric - The maximum number of rows to return in a record set. In cases where a large number of records are expected or pagination is not implemented in the query statement, this attribute should be set to a low enough number the system can handle.ormOptions
struct - A structure that takes ORM options for HQL queries. Used with adbType
value of hql.password
string - The database password; only required if not provided in the Adminstrator.result
string - The key-value pairs with query-related data, returned in struct format.timeout
numeric - The maximum number of seconds the query is permitted to attempt execution before termination.username
string - The database username; only required if not provided in the Adminstrator.returnType
string - Returns the query resultset as either an Array, JSON/Array, or Query (the default).
Code Examples
Tag Syntax
<cfquery name="qryEmployees" datasource="cfdocexamples">
SELECT FirstName,
LastName
FROM Employees
ORDER BY FirstName, LastName;
</cfquery>
Script Syntax
<cfscript>
statement = "SELECT FirstName, LastName FROM Employees ORDER BY FirstName, LastName";
options = {
result = "qryEmployees",
datasource = "cfdocexamples"
};
QueryExecute(statement, [], options);
</cfscript>
Looping Over Query Resultsets
To loop over the results queried from the Employees
table in our above example, you could use the following code:
<cfoutput query="qryEmployees">
#FirstName# #LastName#<br/>
</cfoutput>
You can also check the current row against the record set length to determine when the last record has been reached:
<cfoutput query="qryEmployees">
#FirstName# #LastName#<br/>
<cfif currentRow eq recordCount>
This is the last employee.
</cfif>
</cfoutput>
Result cfquery
Variables
Here is a list of the most commonly used result variables:
sql
- The SQL statement that was executed.currentRow
- The current row in the record set loop.recordCount
- The number of records, or rows, returned from the query.columnList
- The list of the table column headings returned in a comma-delimited list format.sqlParameters
- An ordered array ofcfqueryparam
values passed into the query.cached
- Returns true if the query was cached, and false if it wasn't.generatedKey
- The ID returned from a SQL INSERT statement.
How to Cache Queries
To cache a query's results, we'll use the CreateTimeSpan()
function in the cachedWithin
attribute. The function is defined with four required numeric parameters using the following syntax:
CreateTimeSpan(days, hours, minutes, seconds);
As an example, let's cache the same query for ten seconds:
<cfquery name="qryEmployees" datasource="cfdocexamples" cachedWithin="#CreateTimeSpan(0,0,0,10)#">
SELECT FirstName,
LastName
FROM Employees
ORDER BY FirstName, LastName;
</cfquery>
If you repeatedly refresh the page and execute the same query, you'll notice a performance increase on the second execution and on. This is because the application server is now fetching the data from the cache stored in memory versus executing another database call.
Once the ten seconds have surpassed the original query execution timespan, a new record set will be fetched from the database.
How to Prevent SQL Injection Attacks
SQL injection attacks are malicious attempts by hackers to steal or destroy information from a database by injecting additional queries into the existing query statement.
To show an example of how this might work, let's assume a user inputs their first and last name in a form to retrieve their info from the database. A typical user would enter their first and last name as requested, but someone with malicious intent could insert another query into one of the fields and retrieve private information.
The following query opens up your database to malicious attacks:
<cfquery name="qryEmployee" datasource="cfdocexamples">
SELECT *
FROM Employees
WHERE FirstName = '#form.firstName#'
AND LastName = '#form.lastName#';
</cfquery>
Here, no checks or filters are preventing these types of attacks, so whatever the user enters will be passed into the database.
To prevent yourself, your users, and your database from SQL injection attacks by user input, always utilize the cfqueryparam
tag. The variables are evaluated by the given data type before they're passed into the database, preventing harm to your users and environment.
<cfquery name="qryEmployee" datasource="cfdocexamples">
SELECT *
FROM Employees
WHERE FirstName = <cfqueryparam value="#form.firstName#" cfsqltype="cf_sql_varchar">
AND LastName = <cfqueryparam value="#form.lastName#" cfsqltype="cf_sql_varchar">;
</cfquery>
Conclusion
In this extensive guide, you learned how to properly use the cfquery
tag, the ins and outs of all the attributes, how to iterate over record sets, and how to prevent SQL injection attacks.
Created: July 19, 2023
Comments
There are no comments yet. Start the conversation!