Secure your ColdFusion application against SQL injection attacks

Additional Requirements

Microsoft SQL Server 2005 Express Edition

• Download

While not any more vulnerable than any other server side language, ColdFusion does have many options to help you protect your site against SQL attacks. The thing to remember is it is not ColdFusion that is vulnerable or any other language that is at fault but it is the way in which the application is coded that causes the issues.

Developers that have spent any significant amount of time building websites quickly realize that security issues are a vital consideration. Malicious users can target your site at any time, forcing you to spend time identifying and fixing the vulnerability that has been exposed. If they made changes to your site, you'll need even more time to restore the site to its original condition, and in the meantime legitimate users' perception of your site may be tarnished irrevocably.

When someone has gained unauthorized access to your site, they can damage it any number of ways, including:

• Redirecting visitors to another site
• Deleting, changing, or adding dynamic content
• Deleting or modifying tables in your database
• Disabling web pages or introducing errors

You can take some basic steps to significantly decrease the chance of your website being altered without your consent. In this article I will explore some tips and tricks that you can use to help protect your websites. Although there are many other aspects of security to understand and steps you can take, this will give you a better understanding of what can be done right now to protect your sites.

Note: The examples used in this article are based on ColdFusion and Microsoft SQL Server. It is important to note that these products are no more vulnerable than any other server-side software. It is the way the application is coded, rather than the underlying technology, that causes most security problems.

SQL injection is a technique by which a malicious user alters your SQL statements to serve a different purpose than what was originally intended. This can include deleting, updating, or adding records to your database, which would then be reflected on your web page. Any vulnerable dynamic pages on your website that access database records to display content could be altered to:

• Show content that is not yours
• Delete, update, or add content
• Redirect users to another website using JavaScript

How SQL injection works

In many applications, developers use GET and POST variables to build up SQL commands that are executed on the server. (GET variables are passed via the URL and POST variables are sent via form submissions.)

Hackers will modify these variables, assigning them values that the website developer never intended. Variables in Get (variables that are past along the URL) or Post (typically variables in form submissions) have SQL commands which are added to them which in turn are run when you include these variables in your SQL code.

Using this technique, hackers can expose database table names by causing your database calls to error out. For example, when a particular SQL call requires a number and it is passed a string, the call will result in an error. In some cases, the hacker can use SQL injection techniques to discover all of your table names and initiate JavaScript redirects.

How to test your site to see if it is vulnerable to SQL Injection

SQLFury is a developer tool written for the Adobe AIR runtime. It performs SQL injection scans of a target website to identify any SQL injection vulnerabilities.

How database table names can be exposed

Consider a website that uses the URL to pass an ID, which will be referenced to select a particular brand of car from the database. Here is an example URL:

http://localhost/cars.cfm?id=75

In ColdFusion, you might use a query like the one below on the cars.cfm page to find all car models with that ID:

If a malicious user changed the URL parameter to a string (for example: http://localhost/cars.cfm?id=hello) this would cause a database error, and the subsequent error report would expose the table name and several field names (see Figure 1).

Now the malicious user knows there is a table named "Models" and fields named "pkID", "strModelName", and "strModelDescription". With this information, the user could construct a more sophisticated URL, for example:

http://localhost/cars.cfm?id=75%20union%20select%20BASE_SCHEMA_VER%20as%20pkid,
name%20as%20strmodelname,%20name%20as%20strModelDescription
%20from%20sysobjects

This will cause cars.cfm to append the following to the SQL query:

The resulting query could expose all the tables within the database, depending on how the data is outputted to the end user.

These steps can be automated in a script, allowing hackers to search many sites for vulnerabilities with little effort. Now you may be thinking to yourself this requires a lot of work, but someone doing this could run a script which will spider your site and look for vulnerabilities.

There are several techniques that you can use to hide your table names and database information, including using dynamic table names, template error pages, least privileges, and stored procedures.

Use dynamic tables names

You can hide database details by using a site-specific string in all of your database table names. For example, instead of naming tables "employees" and "products", name them "ABC_Inc_employees" and "ABC_Inc_products".

Assign this string to a variable in your Application.cfm file (or some other file included in every page that contains database calls). For example, set stringDatabasePrefix to "ABC_Inc".

Then use this string to refer to your table in your SQL code. For example:

Alternatively, you can define the entire table name as a variable in your Application.cfm file, and then use that variable in your database calls.

For example, you could use the following code in Application.cfm (or another included file):

You could then construct a database query using the following format:

Now if an error is thrown during a database call, the user will not see the actual name of the table. A malicious user would need to guess the value of strDatabasePrefix or strEmployees to obtain the table name.

Use a template error page

You can use custom error pages to control the error information that users see. This enables you to not only hide your tables' names but also to make your error messages consistent with the look and feel of your website.

To set up a custom error page use the cferror tag in your Application.cfm file. Use the template attribute to specify what page should be displayed when an error occurs. For example:

Apply least privilege

It is always a good practice to provide each user and process with the minimal privilege required to access the information they need. This means, for example, that you should configure your database and application so that only read access is granted to tables that should not be updated.

In practice, you can apply this principle by:

• Defining the appropriate read, write, update, and other rights that a user has for each table in Microsoft SQL Server
• Limiting the set of allowed SQL commands using Advanced Settings when defining a data source in ColdFusion Administrator

Use stored procedures

You can use stored procedures to hide the names of your database tables and keep your programming and database logic separate. While a detailed discussion of stored procedures is beyond the scope of this article, you can find more information in A Beginner's Guide to Using Stored Procedures with ColdFusion.

Malicious users can also use GET and POST variables to insert problem-causing data into unprotected databases. Please be aware that users can also alter other types of variables that come into the global scope of your application such as cookies, server variables and many more.

Sending SQL statements via GET variables

Consider a web page that uses the following code to display a list of courses matching a particular course ID:

The course ID is passed as part of the URL; for example:

http://www.adobe.com?intCourseID=57

By modifying the URL, a malicious user could execute additional SQL statements.

The following URL, for example, appends a command to delete the employees table.

http://www.adobe.com?intCourseID=57;Drop table employees;

Sending SQL statements via POST variables (variables in a form)

A malicious user could also initiate the "drop table" hack outlined in the previous section on a form using POST variables instead of using GET variables.

The same concept is used to insert unauthorized page redirects into a page. Consider a page with the following form that allows users to type a name and a topic that will be added to a database:

The ColdFusion application uses the following code to insert the information:

On a separate page the database fields are displayed in a tabular format like the following:

Because there are no restrictions on the text the user enters, a malicious user could type the following in the topic field:

This code would be inserted into the database. Then, whenever a user attempted to visit the page with the database table on it, they would be redirected to www.bad_site.com.

There are several techniques you can use to block malicious data insertion.

Use cfqueryparam or cfprocparam

You should use the cfqueryparam tag within every cfquery tag to help secure your databases.

The code below uses cfqueryparam to ensure that the intCourseID is an integer, and therefore, that it contains no extra SQL commands.

The cfprocparam tag provides similar functionality for stored procedures.

Apply the principle of least privileges

As noted previously, you should strictly limit access to the database by defining user privileges within Microsoft SQL Server for each table. You can use specify a user with limited privileges when establishing a data source connection in ColdFusion Administrator or as the data source attribute within cfquery or cfstoredproc.

Validate string data

When accepting variables from a POST or GET you can use cfparam to test whether they are of the expected type.

For example, the code below ensures that url.id is a numeric value.

You can also check the length of string variables that are passed into your page using cfif or cfqueryparam.

The following code checks if the length of form.status is greater than 20:

While the code below performs a similar check on strDescription.

Use the scriptProtect attribute of cfapplication

You can use the scriptProtect attribute of the cfapplication tag to protect variables from some (but not all) cross-site scripting attacks. On the ColdFusion Administrator Settings page, the Enable Global Script Protection option determines the default script protection setting. You can use the scriptProtect attribute to override the Administrator setting. For example:

The script protection mechanism uses a regular expression defined in the neo-security.xml file to search GET or POST variables that could be malicious.

You can edit neo-security.xml to customize the regular expression used to search for malicious keywords.

For example:

Use custom code to search for potentially malicious keywords

You can also use custom code to check GET and POST variable for potentially malicious keywords.

To do this, create a list of such keywords in your Application.cfm file. For example:

To search for these keywords in a GET variable, use code similar to the following:

The following code searches through all POST variables in a form for the keyword "Select":

Ensure each request for your web page is coming from one of your other web pages

Often when someone hacks your site they will not be manually clicking on links and typing at the keyboard. Instead they will typically write a script that attacks the specific pages that have vulnerabilities.

For example, consider a site with a page myForm.cfm that contains a link to myFormProcessing.cfm, which performs a database query. A malicious user will most often attack myFormProcessing.cfm directly, without clicking through myForm.cfm.

You can use the following techniques to block this kind of attack.

First, in myFormProcessing.cfm you should verify that HTTP_REFERRER matches the URL of myForm.cfm. For example:

This check can be fooled, so don't use this as your only line of defense.

You can also implement image verification on your forms and ask your users to type in a string of letters and numbers presented in an image. This kind of CAPTCHA test is designed to distinguish human users from automated scripts.

A third approach is to add a session to your form page and pass the session as a hidden value in your form. On the page that processes the form you can then check to see if the hidden value matches the session value.

The following code snippets implement this technique:

In myForm.cfm:

In myFormProcessing.cfm:

You can make this a bit more sophisticated by encrypting the random number and then verifying that it is still a number when it is decrypted. Alternatively, you can pass a value and a key instead of the random number itself and use the key to de-crypt the value. See the Encrypt section of the ColdFusion livedocs for more information.

Encrypt data being passed along the URL string

When appending query strings to your URLs, use the ColdFusion Encrypt() and Decrypt() functions so the end user does not see the names of the variables being passed along with the URL string.

You could establish a unique session for the user when they land on the home page and use part of the session ID as the encryption key.

Use HTMLEditFormat

When you allow end users to enter information in a text area, use HTMLEditFormat to encode any HTML the user types; this can prevent users from submitting JavaScript in the form.

Where to go from here

This article has outlined some steps you can take to secure your ColdFusion website from malicious users.

For automated tests, you can use SQLFury, a developer tool written for the Adobe AIR runtime, to perform SQL injection scans of a target website and identify SQL injection vulnerabilities. For more information, see http://www.sqlfury.com/.

While SQL injection is a common technique, hackers use other injection techniques that you should be aware of, including LDAP, ORM, User Agent, XML, and more.

For more information on ColdFusion security, visit the Security page in the ColdFusion Developer Center.

Attributions

Thanks to Denis-Claude Fleury for his help with this article.