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 the CreateTimeSpan() 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 the Application.cfc file by the variable this.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 a dbType 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 of cfqueryparam 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.