How to Prevent ColdFusion SQL Injection

ColdFusion
#1

This article is aimed to show how you can protect your ColdFusion applications from SQL injection attacks. ColdFusion applications vulnerable to SQL injection are at risk of being attacked daily by malicious hosts which can eventually lead to information disclosure.

How to prevent SQL injection

ColdFusion comes packaged with a <cfqueryparam> tag that is designed to protect dynamic SQL queries within your application. This tag will sanitize ColdFusion variables used within your application’s queries to prevent SQL injection.

Listed below is an example of a SQL query vulnerable to SQL injection attacks.

    <cfquery name = "customers" datasource = "MyDSN">
		SELECT firstname, lastname, phone
		FROM contacts
		WHERE customerID = #url.customerid#;
	</cfquery>

As you can see the variable “#url.customerid#” is not sanitized with cfqueryparam which will eventually lead to SQL injection attacks. Listed below is an example of a SQL injection attack that can be used against the above query.

http://mycfsite.tld/showCustomers.cfm?customerid=4123%20DELETE%20FROM%20contacts

If the above URL were to be passed to your application, then the following SQL query would be executed within your database.

SELECT firstname, lastname, phone
	FROM contacts
	WHERE customerID = 4123 DELETE FROM contacts;

The easiest way to fix these type of vulnerabilities would be to implement the use of cfqueryparam for the “#url.customerid#” variable. Below is an example of how this can be done.

	<cfquery name = "customers" datasource = "MyDSN">
		SELECT firstname, lastname, phone
		FROM contacts
		WHERE customerid = <cfqueryparam value = "#url.customerid#" cfsqltype = "cf_sql_integer">;
	</cfquery>

Additionally, you can also protect your SQL queries when utilizing CFScript as well. Here is an example of how you can sanitize your queries when utilizing CFScript:

	<cfscript>
		customers = new Query(datasource = "MyDSN", sql = "SELECT firstname, lastname, phone FROM contacts WHERE customerid = :customerid");
		customers.addParam(name = "customerid", value = "#url.customerid#");
		results = customers.execute().getResult();
	</cfscript>

You can review more information about the implementation of cfqueryparam in Adobe’s documentation.

If you believe your application is falling victim to SQL injection attacks, then our world-class support team will be happy to help identify vulnerable SQL queries within your application. Please feel free to contact our support team and we’ll be happy to help you identify these type of vulnerabilities.

What can happen as a result to SQL injection?

In most cases when a successful SQL injection is performed against your database you’ll see one or more of the following cases:

  • Sensitive information from database stolen such as credit cards, customer information, etc.

  • Uploads of shell files (such as .PHP based files) that all the attacker to see your entire file structure and the contents of those files. They’ll be able to take control of your site’s files.

Obviously the above are extremely concerning for a business if their customers fall victim to their private information getting stolen, or if they are exposed to malware on your site as a result of the injection.

We recommend that every <cfquery> tag used on your site is protected with the use of the <cfqueryparam> tag to prevent bad characters used in SQL injection attempts from going through.

Additional Considerations Regarding Sanitizing Queries

You may use a URL parameter to specify the ORDER BY clause of a query, for example, you give the user the ability to order the results of a page in a particular order - price, newest or such like.

In such instances, even with any parameters using <cfqueryparam> you may be opening yourself up to a SQL injection attack. Take the URL: http://mywebsites.com/products.cfm?categoryId=2&sort=price

This URL may point to a page with the following query:

 <cfquery name=“getProducts” datasource=“myDataSource”>
    SELECT *
    FROM products
    WHERE categoryId = <cfqueryparam cfsqltype=“cf_sql_integer” value=“#url.productId#”>
    ORDER BY #url.sortColumn#
</cfquery>

Manipulating the sortColumn URL variable may result in the following query being executed:

SELECT *
FROM products
WHERE categoryId = 2
ORDER BY price; DROP TABLE products

In order to resolve this, you cannot use <cfqueryparam>. Therefore it would be sensible to sanitize the URL variable by specifying a list of columns that it is possible to sort by; for example:

<cfset permissibleColumnsToSortBy = “price,dateadded”>

<cfquery name=“getProducts” datasource=“myDataSource”>
    SELECT *
    FROM products
    WHERE categoryId = <cfqueryparam cfsqltype=“cf_sql_integer” value=“#url.productId#”>
    <cfif listFindNoCase(permissibleColumnsToSortBy, url.sortColumn) GT 0>ORDER BY #url.sortColumn#</cfif>
</cfquery>

Is there any products that can detect and block SQL injection?

A question we commonly get is if there is anything they can do in addition to the <cfqueryparam> tag in order to protect their site from SQL injection and all other types of attacks.

While we recommend that the queries on the site be updated and protected regardless of any firewall the site is behind, we do offer a Web Application Firewall (WAF) & CDN service that will help protect your website from all kinds of malicious attacks, including SQL injection, as well as help boost the performance of your website.

If you are interested in setting up your site with this WAF/CDN service please contact our support team in order to get additional information & pricing.

Again, we strongly recommend adding the above-mentioned fix for SQL injections regardless of if the site is behind the WAF.

SQL Injection