by Ryan Wagener
Table of contents
16 February 2009
A working knowledge of ColdFusion and database concepts is required.
Adobe ColdFusion Enterprise Edition (2016 release) 8 (Download trial)
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
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.
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:
In ColdFusion, you might use a query like the one below on the cars.cfm page to find all car models with that ID:
<cfquery name="models" datasource="cars"> Select pkID, strModelName, strModelDescription from Models where pkID=#url.id# </cfquery>
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).
Figure 1. A database query error message.
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:
This will cause cars.cfm to append the following to the SQL query:
union select BASE_SCHEMA_VER as pkid,name as strmodelname, name as strModelDescription from sysobjects
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.
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
Then use this string to refer to your table in your SQL code. For example:
Select * from #stringDatabasePrefix#_employees
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):
<cfset strEmployees= "adobe_employees">
You could then construct a database query using the following format:
Select * from #strEmployees#
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
strEmployeesto obtain the table name.
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
cferrortag in your Application.cfm file. Use the
templateattribute to specify what page should be displayed when an error occurs. For example:
<cferror type="exception" template="database_error_page.cfm" >
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
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.
Consider a web page that uses the following code to display a list of courses matching a particular course ID:
<cfquery name = "getFirst" dataSource = "cfsnippets"> SELECT * FROM courses WHERE intCourseID = #intCourseID# </cfquery>
The course ID is passed as part of the URL; for example:
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;
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:
<form name="forumPost" action="forumPostp.cfm"> Name: <input type="text" name="strName" id="strName" > <br /> Topic: <textarea name="txtTopic" id="txtTopic"></textarea> <br /> <input type="submit" name="submit" value="submit" </form>
The ColdFusion application uses the following code to insert the information:
<cfparam name="form.strName" default=""> <cfparam name="form.txtTopic" default=""> <cfquery name = "getFirst" dataSource = "cfsnippets"> Insert into forumTopics(strName,txtTopic) Values ('#strName#', '#txtTopic#') </cfquery>
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.
You should use the
cfqueryparamtag within every
cfquerytag to help secure your databases.
The code below uses
cfqueryparamto ensure that the
intCourseIDis an integer, and therefore, that it contains no extra SQL commands.
<cfquery name = "getFirst" dataSource = "cfsnippets"> SELECT * FROM #strDatabasePrefix#_courses WHERE intCourseID = <cfqueryparam value = #intCourseID# CFSQLType = "CF_SQL_INTEGER"> </cfquery>
cfprocparamtag provides similar functionality for stored procedures.
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 sourceattribute within
When accepting variables from a POST or GET you can use
cfparamto test whether they are of the expected type.
For example, the code below ensures that
url.idis a numeric value.
<cfparam name="url.id" type="numeric" default=0>
You can also check the length of string variables that are passed into your page using
The following code checks if the length of
form.statusis greater than 20:
<cfif len(form.status) gt 20>
While the code below performs a similar check on
<cfqueryparam value="#strDescription#" CFSQLType="VARCHAR" maxlength="20">
You can use the
scriptProtectattribute of the
cfapplicationtag 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
scriptProtectattribute 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.
<var name="="<\s*(object|embed|script|applet|meta)"> <string><InvalidTag</string> </var>
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:
<cfset strList="select, insert, update, delete, drop">
To search for these keywords in a GET variable, use code similar to the following:
<cfif len(cgi.query_string) gt 0> //Do some action here to check what is being passed </cfif>
The following code searches through all POST variables in a form for the keyword "Select":
<cfif IsDefined ("form.submit")> <cfloop index="strFormItemName" list="#FORM.FIELDNAMES#"> <!--- dynamically create the form fieldname ---> <cfset strFormItemValue=form[variables.strFormItemName]> <cfloop list="#strList#" index="strListValue"> <cfif FindNoCase(strListValue, strFormItemValue)> <!--- take some action ---> </cfif> </cfloop> </cfloop> </cfif>
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_REFERRERmatches the URL of myForm.cfm. For example:
<cfif HTTP_REFERER IS NOT "<your_url>/myForm.cfm"> <!--- user did not come from referring page, take some action ---> </cfif>
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:
//set a random number <cfset formValidator=Randomize(100000)#> //Set a session with this value <cfset Session.formValidator = formValidator > // add the random number in your form <input type="hidden" name=" formValidator" value="# formValidator#">
//check to see that the form value equals the session value, if not then kick the user out. If the user does pass this step then delete the session <cfif form.formValidator eq session.formValidator>
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.
When appending query strings to your URLs, use the ColdFusion
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.
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.
Thanks to Denis-Claude Fleury for his help with this article.