ColdFusion: CFQUERY and Evaluate()

Been going back and forth with a friend who does CF development for some company. Anyways, he rings me up to talk about some SQL injection attacks that have been reported on an application he maintains. I take a look at the code and, sure enough, there are. Problem is they’re buried a bit and not easily found. Furthermore, one of the two exploits I’m about to cover is something not many CF developers will ever notice, even if they are aware of the dangers of SQL injection. This is because ColdFusion, on some levels, sucks.

Case 1: Evaluate

Here’s the code:

<cfset session.isadmin = false>

<cfparam name="url.x" default="1">

<cfset variables.msg1 = "Welcome To Our Site">
<cfset variables.msg2 = "Site Administration">

<h1><cfoutput>#Evaluate( "variables.msg#x#" )#</cfoutput></h1>

<cfif session.isadmin is true>
  <p>You may now administer the site as you see fit.</p>
  <p>Buy our product.</p>

This is now how you write CF, this is just a very simplified example to show you the problem that was discovered; in this case, it’s the Evaluate() line. Variable X is being passed on the URL in this example for simplification. In reality, there are any number of ways user-provided data could find it’s way into an Evaluate() statement. I’m just making it easy and obvious here.

Evaluate() will evaluate (and execute) the contents of the string that is passed to the function and return the result. The intention of the example above is to return the relevant message associated with the user’s level of access. Since X is supplied by the user it is possible to inject something into that Evaluate() line. But what?

Long story short, calling this script with the following URL will alter the isadmin session variable to change the user’s access level.

This creates the following Evaluate() statement:
Evaluate( "variables.msg2 eq 'a' or SetVariable(session.isadmin,true)" )

The string now represents a boolean statement. Instead of returning the value of the string variables.msg2 it now will return TRUE or FALSE. And before it does that it will execute every expression in the statement, including a call to the function SetVariable() which alters the isadmin session variable. With that, the user is now an admin and will have full control of the application. What’s worse, more function calls could could be passed to the application which essentially give the user the same level of access to the machine as whatever user ColdFusion is running under. Especially under ColdFusion MX where CreateObject() can be used to create JAVA objects that provide system-level access.

Case 2: Evaluate in a CFQUERY

<cfsetting showdebugoutput="Yes">
<cfset variables.hack = "' OR 1=1 OR Name='">
<cfquery datasource="misc" name="test">
  FROM Test
  WHERE Name = '#Evaluate( "variables.hack" )#'

Again, I’m oversiplifying this a bit, but I’ll show you where this would happen in the real world once you understand what’s going on here.

variables.hack represents the point of injection. We assume this variable has a legit purpose but, at this point in the code execution, it’s value has been altered by a malicious user to what it’s set as in the code. The relevant portion of the SQL injection is OR 1=1 OR which will trigger the database to return all records instead of just the one record intended. There are many other things you can do when you’ve got a SQL injection point like this, but this isn’t about SQL injection it’s about how Evaluate() creates problems.

In ColdFusion, any variable in a CFQUERY block that is wrapped in single quotes ('#variables.string#') will be automatically escaped. In other languages like PHP you have to do this maually, but CF does it automatically. Escaping these strings prevents malicious users from injecting SQL commands into the query. It makes the database treat the string as just a string and not as a series of commands that’s part of the SQL statement.

If you were to run this script on a CF server you would discover that the string in variables.hack is NOT escaped. This allows SQL to be injected into the query. Why is this? The Evaluate() statement is wrapped in single quotes, so what gives?

Well.. actually, ColdFusion is working as intended. There are several passes of the SQL statement made by ColdFusion. The first pass handles the escaping of variables. The second pass handles executing functions and after that the SQL is passed on to the database. If you had something like:

<cfset variables.msg="hack'd">
<cfquery datasource="misc" name="test">
  FROM Test
  WHERE Name = '#Evaluate( "variables.#msg#" )#'

You would find the Evaluate() statement becomes:
Evaluate( "variables.hack''d" )

Two single-quotes is the escape sequence for a single quot e in SQL. The contents of the msg variable are escaped. After this escape, Evaluate() is executed. In this case it’d return an error since variable names can’t contain single-quotes.

So you see, the escaping of variables occurs before Evaluate() is processed. This means the contents of whatever Evaluate() will not be escaped and provides a point of injection for an attacker.

The solution is to set the results of the Evaluate() call to a temp variable and use that temp variable inside the CFQUERY.

<cfset variables.test = Evaluate( "variables.hack" )>
<cfquery datasource="misc" name="test">
  FROM Test
  WHERE Name = '#variables.test#'

Where is this used in the real world? Arrays. Something where you loop through a number set, say 1 to 20, where you insert the Nth value in an array into the database. Something like SET data = '#Evaluate( "userdata[#X#]" )#' where userdata is an array that stores data provided by the user (form data, data pulled from a database that was set through another application, etc.)

This problem exists for other functions used in a CFQUERY.

That is, any function that performs an evaluation of a string will have the same problem. IIF() is one such function. I’m not sure what others there are off the top of my head, but it’s definately something to think about.

So the point to remember is to never pass any data that, at any time could be set by the user, through Evaluate() and IIF().

And there you go. A couple exploit vectors (as the security guys say) in ColdFusion that you need to be aware of if you’re a CF developer.


7 thoughts on “ColdFusion: CFQUERY and Evaluate()

  1. A great post Ruthsarian! To prevent SQL injection attacks in CF you should always ALWAYS use the cfqueryparam tag. The majority of times I’ve seen a developer needing to use evaluate() (which you should never use if possible) is to handle dynamic fields but CF can handle dynamic fields using array notation. For example:


    Using cfqueryparam tag, not using the evaluate() function and always scoping your variables, are some of the top most important best practices in ColdFusion.

  2. Yikes my cf code was erased! Let me repost the code I had up but instead of html arrows for the tags I will use brackets:

    [cfparam name=”url.x” default=”1″]

    [cfset variables.msg1 = “Welcome To Our Site”]
    [cfset variables.msg2 = “Site Administration”]


    I’m referencing the variables scope (which is a structure in CF) with array notation. I do this to avoid using evaluate(). Do note that for array notation you have to use brackets.

  3. I certainly missed one part of your statement Ruthsarian! I thought you loved CF! I certainly can’t agree with the “ColdFusion, on some levels, sucks” statement one bit. CF is better against SQL injection than any other language. As I stated earlier you must always use the cfqueryparam tag. I’m wondering if you even know of its existence?? If not let me point you to the documentation for it. Its truly a gifted tag to have! Sucks for all other languages if you ask me. I pity them.

  4. All languages can be subject to attacks like SQL injection. This is not indigenous to CF. Good programming practices can prevent attacks. For example, in “case 1″ you can fix the issue that you addressed by simply adding a type attribute to the cfparam. ie. [cfparam name=”url.x” default=”1″ type=”integer”]

    It is always a good idea to keep URL variables restricted to integers and use other methods of passing values from template to template.

    Just 2 cents,

  5. The example to obtain admin privileges is not very good since how would you know the “isadmin” variable was the one to set!? Answer, you would not. Sure you could fire away hundreds of them but if the variable was called “admin3453454524lk343423423sdsdsdsksuryfndc” then would you find it then?

  6. @Fred: complete and utter bollocks. Your example is the perfect example of going for security through obscurity, while better methods _should_ be encouraged.

    If the product is commercially available, people have insight in the source code and can know the variable name.

    If the product was developed in-house, it may still be possible to get to the source code because of other security bugs to view file content or so.

    Also, did you even wonder about the amount of entropy in coldfusion variable names? Do you encourage developers to use your proposed naming scheme? I doubt it. Even for completely blind attacks the amount of entropy in variable naming is not that big and you might indeed be able to brute force it.

    Your proposal for ‘security’ boils down to using plain-text passwords with a non-existent password policy biased to syntactically sugared strings.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s