SQL Injection: A Persistent Threat : Part Two of the Let's Talk Security Series

Dec 05, 2011

by Jason Dean

Most developers have at least heard of SQL injection. Many likely have a basic understanding of it, but I think very few grasp just what it can do and how dangerous an attack can be. Basic SQL injection is actually a very simple attack to perform and can be done by anyone who has a fundamental understanding of SQL and programming.

What is SQL Injection?

SQL injection is a technique used to insert SQL statements into user input. The injected commands can replace, or augment, existing dynamically-constructed SQL queries. SQL injection is used to read, replace, or destroy data in a database, and can also be used to execute stored procedures and system commands on the database server.

Like many technical explanations, that may seem confusing. So let's look at a simple example.

We begin with two completely unrelated tables in a fictitious content management system. The news table represents news articles accessible from the public-facing side of the web application, and the users table represents system users such as administrators, content managers, and other authenticated users. These tables have absolutely nothing to do with each other, so it might seem that the functionality of the news area would not threaten the administration functions.

Figure 1: A Diagram of the Tables in our CMS

Our fictitious CMS has a public-facing news page that lists news articles and allows users to drill down to the articles themselves, which then display on a news detail page.

Figure 2: Our Fictitious News Detail Page

Take notice of the URL on this screen. The URL string calls for the newsdetail.cfm page and passes in the newsID "2".



/newsdetail.cfm?newsID=2

Whenever we see a parameter passed through a user-controllable scope such as URL, Form, CGI, or Cookie, we need to consider how that parameter is being used. In this case, the newsID value passed in the URL pretty obviously will be used to retrieve a news article from a database, like this:

Listing 1: Our Original Query




<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = #newsID# 


</cfquery>

How can this read-only query on a table that has nothing to do with the administrator section possibly compromise the CMS?

Simple Injection Attacks

What if a malicious hacker changed the URL string to this?

Listing 2: An Example of SQL Injection That Sets a Password




/newsdetail.cfm?newsID=2;UPDATE+users+set+password=54321+WHERE+userID=1

To make it easier to see what is going on here, I will lay it out in a more readable format. Since our application simply takes whatever the URL passes in and assigns it to the newsID variable, then sticks it onto the end of the WHERE clause, this injected code turns the SQL statement into this:

Listing 3: The Effects of Listing 2 on Our SQL Statement




<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = 2;


UPDATE users


SET password = 54321


WHERE userID = 1


</cfquery>

To explain further, anything after the equals sign (=) in the URL string is considered part of newsID until you reach an ampersand (&). This means that newsID actually equals "2;UPDATE+users+set+password=54321+WHERE+userID=1". When newsID gets appended to the dynamically-constructed SQL statement, that whole string is appended.

Once constructed, the SQL statement executes. When the database engine executes that query, it executes the SELECT query just as it would have without the injected code. It is still a perfectly valid SQL query whose expected value (whether "2" or some other integer) is passed in. The semicolon (;) then ends that query and a new UPDATE query changes the password field to "54321" for the user with userID=1.

This works. The password column for the record with userID = 1 now contains the value "54321". If the CMS application has a weak authentication system, we could now log in as the user with the userID of 1, which is the administrator account in most systems.

This attack, like most attacks, depends upon several conditions:

The DSN and database user must have permission to execute UPDATE queries.
A dynamically-constructed database query.
The database system must allow execution of multiple queries in a single cfquery block.

Not all database management systems allow multiple queries in a single query block, but most do. At this time, the above attack works against Microsoft SQL Server 2008, PostgreSQL 8.x, and MySQL 5. It would likely work in previous version of the database management system as well. Note that the attack against MySQL 5 did require enabling the allowMultiQueries attribute in the connection string, but many administrators do, so the attack remains a concern.

I am not suggesting that developers disable allowing multiple queries in a single cfquery block. Personally, I like this feature and would not want to see it go away. But It is one of the enabling factors. If we can mitigate the risk by eliminating the other vulnerabilities, then multiple queries give less cause for concern.

Here is another example of an attack that uses multiple queries.

Listing 4: Multiple-Query Attack:



/newsdetail.cfm?newsID=2;DELETE+FROM+Users

This attack would result in a query that deletes all of the records in the user table, and does the same thing as:

Listing 5: The Effects of Listing 4 on Our Query



<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = 2;


DELETE FROM users

</cfquery>

Disabling multiple queries would not solve our problems anyway. Many SQL injection attacks can take place in a single query. Using the example table structure from above, let's look at one of my favorites:

Listing 6: A SQL Injection Attack Using a UNION Statement



/newsdetail.cfm?newsID=2+AND+1=0+UNION+ALL+SELECT+username,+password+FROM+users+WHERE+userID=1

This attack would produce the following query:

Listing 7: The Effects of Listing 6 on Our Query



<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = 2;


OR 1=0


UNION ALL


SELECT username, password


FROM users


WHERE userID = 1

</cfquery>

Figure 3: Our Site With the Admin Password Compromised

This query would result in output that looks like Figure 3. We negated the first query so that it returns zero records by appending 'AND 1=0' to the WHERE clause. Since 1 never equals 0, this query will never return any records. Then we UNION that query with another query that pulls from the users table. Since UNION queries must contain columns of the same data type as the first query, we were able to match up the username and password columns with the title and body columns. Finally, the WHERE clause in our new query made sure that we only received the record for the user with userID=1. The resulting query has only one record, where the title column equals the administrator username, and the body column equals the administrator password. I was able to successfully execute this attack against all of the major database management systems.

Advanced SQL Injection

The above examples are very basic SQL injection attacks. None require great skill in hacking or advanced encoding techniques. But attacks can get considerably more complicated and do further damage. Using character encoding, attacks can be deployed that result in the execution of stored procedures or system commands.

In the summer of 2008, many web applications became the targets of an attack that exploited vulnerabilities in the way that their SQL statements were constructed. Some of the attacks specifically targeted ColdFusion applications. (See Mark Kruger's blog post for more information.) Like the queries in our other examples, these vulnerable queries were dynamically constructed based on user input. These attacks, however, were quite complex and would execute with something like the following appended to the query string:

Listing 8: An Example of an Extremely Complex SQL Injection Attack



someID=129;DECLARE%20@S%20CHAR(4000);

SET%20@S=CAST(0x44...%20AS%20CHAR(4000));EXEC(@S);

The example above, taken from Mark Kruger's blog post, was shortened for clarity. The real attack can contains hundreds of additional characters that result in a procedure that executes if allowed to pass unchecked to the database engine. The procedure could do anything from deleting data to appending hidden JavaScript into existing data, as the 2008 attacks did, to executing system commands.

Countermeasures

The good news is, simple or complex, SQL injection attacks require the same countermeasures. You should do several things to protect your applications from SQL injection attacks.

Magic Quotes

You may have noticed that all the examples that involve injecting data into fields use numeric values. For example, when I updated the passwords in the user table, I used integers. ColdFusion makes injecting string data very difficult. ColdFusion uses something commonly referred to as "magic quotes" to mitigate the risk of injection attacks. If ColdFusion comes across a single quote (') in a parameter that is used in a SQL query, it will automatically escape that single quote with a second single quote. So if I try to inject a password string like 'adminNewPassword', that value will be added as 'adminNewPassword', which is invalid SQL which will cause an error.

Unfortunately, some developers in the past did not understand this and resorted to using the PreserveSingleQuotes() function in ColdFusion to prevent the escape behavior. As a result, they made SQL injection attacks even easier

Listing 9: PreserveSingleQuotes can Unwittingly Aid a SQL Injection Attack



<cfquery name="qNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = #PreserveSingleQuotes(newsID)#

</cfquery>

With a query that uses PreserveSingleQuotes like this, an attack can inject strings into the code.

Listing 10:



/newsdetail.cfm?newsID=2;UPDATE+users+SET+password=?newPassword?+WHERE+userID = 1

Parameterized Queries and Stored Procedures

Some developers or database administrators will say that you can protect your applications from SQL injection by using stored procedures instead of using queries from within your code. This is not true. While stored procedures can be more difficult to exploit, dynamic stored procedures can still be vulnerable to attack if not constructed properly.

Both stored procedures and queries, if dynamically constructed, need to be parameterized. This is accomplished through the use of prepared statements. Prepared statements allow developers to create queries or stored procedures that are used as query templates. Place holders are then used to bind the incoming parameters to the appropriate places in the template. Instead of creating one large query that is executed all at once, the query is executed with each of the bound parameters being treated as separate units. This separates the incoming data from commands that are being executed.

Let's reexamine the code from Listing 1:

Listing 11: Our Original Query Again



<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = #newsID#

</cfquery>

Here the newsID will be appended to the end of the query and the entire query will execute at once, allowing additional executable code to be appended. But if we use ColdFusion?s cfqueryparam tag to create a bound parameter, the data that is passed in will be handled separately from the executing code.

Listing 12: Creating a Bound Parameter with cfqueryparam



<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID = <cfqueryparam value="#newsID#" cfsqltype="cf_sql_numeric" />

</cfquery>

The cfqueryparam in Listing 12 actually provides two forms of protection. First, it creates a bound parameter, but it also verifies that the data coming in is of the right type. In this case, if the incoming data is not numeric, it will throw an error.

The same can be done using the cfprocparam tag for a stored procedure that expects dynamic data.

Listing 13: Using cfprocparam to Bind a Parameter for a Stored Procedure



<cfstoredproc procedure = "procGetNews" datasource="#dsn#">

<cfprocresult name = "RS1">

<cfprocparam type="IN" cfsqltype="cf_sql_numeric" value="#newsID#" dbvarname="@param1" />

</cfstoredproc>

You can also create prepared statements for lists of values where you use the IN keyword in SQL. For example, let's say you want to request several news articles from the database:

Listing 14: Creating a Prepared Statement with a List of Values



<cfquery name="qGetNews" datasource="#dsn#">


SELECT title, body


FROM news


WHERE newsID IN <cfqueryparam list="yes" value="#newsID#" cfsqltype="cf_sql_numeric" />

</cfquery>

In Listing 14, the newsID value contains a comma-delimited list of values, like "1,5,16,79" and the query will retrieve any records where newsID equals one of those values. Just like the previous example, the list of newsID values is treated separately from SQL statements being executed, so additional statements cannot be injected.

Principle of Least Privilege

The principle of least privilege applies in all areas of application security. Whether we are talking about access control, database servers, or the ColdFusion server itself, nothing should operate with more privilege than it needs to get the job done. This means that the ColdFusion server should not run under the operating system?s administrator account, nor should the database server. It also means that our ColdFusion DSN should not be logging in with "sa", "root", "superuser" or any other account that will gives the DSN privileges beyond what is needed.

In the database management system, several privileges should be restricted unless you have a specific reason to open them up to the DSN that is using them. CREATE, DROP, ALTER, GRANT, REVOKE and the execution of stored procedures are a few examples of privileges that should be restricted unless the application needs them. And if parts of the application do require any of these, consider creating a separate DSN for those parts of the application.

Protecting ORDER BY Clauses and Dynamic Column and Table Names

Unfortunately, prepared statements only work in the WHERE clause of a SQL statement. So if you have a dynamically-generated ORDER BY clause, or if you have a statement that changes which columns or tables are used based on user input, then those statements need to be protected in a different way.

Pages that displays list of items will usually allow you to sort them based on a column name. In our fictitious CMS, this might be possible with the page that lists the news stories.

Listing 15: A Sample Query that Sorts Based on Column Name



<cfquery name="qNews" datasource="#dsn#">


SELECT title, newsDate


FROM news


ORDER BY #columnName# #sortOrder#

</cfquery>

This query gets the news stories and sorts them by the defined column and in the specified sort order. So we might see a URL string that looks like this:

Listing 16: A Sample URL String Based on the Query in Listing 15



/news.cfm?columnName=title&sortOrder=DESC

Both of these dynamic parameters are vulnerable to attack. They can both be manipulated by the end user, so code can be injected.

Listing 17: A SQL Injection Attack on a Dynamic Parameter



/news.cfm?columnName=title&sortOrder=DESC;UPDATE+users+SET+password=123+WHERE+userID=1

Listing 18: Another SQL Injection Attack on a Dynamic Parameter



/news.cfm?columnName=title&sortOrder=DESC;DELETE+FROM+users

These attacks in Listings 17 and 18 append new statements to the sortOrder parameter. Sort order is an easy one to fix, because there are only two options, ASC and DESC. For this, I like to dynamically apply static output.

Listing 19: Dynamically Applying Static Content to the SortOrder Parameter




ORDER BY #columnName# <cfif sortOrder EQ "DESC">DESC<cfelse>ASC</cfif>

Now, regardless of what value is passed in the sortOrder parameter, the only possible values that can be applied are ASC or DESC.

But this ORDER BY statement in Listing 19 is still vulnerable to attack.

Listing 20:



/news.cfm?columnName=title;UPDATE+users+SET+password=123+WHERE+userID=1--

I have now moved the attack to the columnName parameters and added two hyphens (--) to the end of the URL string. The two hyphens in SQL will comment out anything that appears after it. This means that the sortOrder parameter will not be used at all in the query, the database engine will simply use the default sort order, and the attack will complete successfully.

In this case, we are dealing with a small number of possible column names, so we could use a countermeasure similar to what we did for the sortOrder parameter. But we need to be able to accommodate a much larger possible set of column names. In some lists, we may want to be able to sort by half a dozen or more columns. Having several cfif and cfelseif statements might work for very simple queries, but remember, it is also possible to sort on more than one column. That could get very complicated.

To handle the columnName parameter we have two acceptable choices. We could create a white-list of possible column names that are allowed and sanitize the input through a white-list function, or we could have a white-list of acceptable characters for a column name and restrict the length, again sanitizing through a function.

Listing 21: Function sanitizeColumn



<cffunction name="sanitizeColumn" access="public" returntype="string">


<cfargument name="column" type="string" required="false" default="">


<cfset var columnList = "title,body">


<cfif ListFind(columnList,arguments.column)>


<cfreturn arguments.column>


<cfelse>


<!--- Handle the issue any way you like; this is just an example --->


<cflog text="Possible SQL Injection attempt!">


<cfthrow message="There was a problem with the query">


</cfif>

</cffunction>

In this function, we pass in the name of the column that was received, and if it matches the white-list, we return it. If it does not, we throw an error. This way, if anyone tries to pass in a value other than an acceptable white-list value, they will be stopped. The function would be used like this:

Listing 22: Using the sanitizeColumn Function



ORDER BY #sanitizeColumn(columnName)#

<cfif sortOrder EQ "DESC">DESC<cfelse>ASC</cfif>

If we have many columns or many queries of this type, it could get tedious keeping this type of function up-to-date. Alternatively, and with less ongoing maintenance, we could use a white-list function that could handle any column or table name sanitization.

Listing 23: A More Generic White-list Function




<cffunction name="sanitizeParam" access="public" returntype="string">


<cfargument name="param" type="string" required="false" default="">


<cfset var characterRegex = "^[A-Za-z][A-Za-z0-9_]+$">


<cfset maximumLength = 25>


<cfif REFind(characterRegex, arguments.param) AND len(arguments.param) LTE maximumLength>


<cfreturn arguments.param>


<cfelse>


<!--- Handle the issue anyway you like, this is just an example --->


<cflog text="Possible SQL Injection attempt!">


<cfthrow message="There was a problem with the query">


</cfif>

</cffunction>

Just like Listing 21, we pass the parameter value in to this function, but here we run it against a regular expression to make sure it only contains legal characters. The regular expression ensures that the parameter value starts with a letter and then contains one or more letters, numbers or underscores all the way to the end of the string. This means that no semicolons or single quotes can be sneaked into the expression. The function then checks that the parameter is no more than 25 characters long. If you have table names longer than 25 characters, you would need to adjust this accordingly. The idea is that it would be tough to deploy a SQL injection attack with only a 25 character limit. Remember that this will not prevent someone from entering an invalid column name into the URL string. As long as the value entered is less than the number of specified characters and does not contain any illegal characters, this function will let it pass; you still need to properly handle errors.

You will also need these types of functions for a query that has dynamic column names or table names elsewhere in the structure, like you might find in a dynamic search query.

Listing 24: Using the Sanitize Function for a Dynamic Search Query



<cfquery name=?search? datasource="#dsn#">


SELECT #sanitizeParam(form.columnName)#, itemNumber, itemText


FROM #sanitizeParam(form.tableName)#


WHERE #sanitizeParam(form.columnName)# LIKE


<cfqueryparam value="%#form.searchText#%" cfsqltype="cf_sql_varchar" />


ORDER BY #sanitizeParam(form.columnName)#


<cfif sortOrder EQ ?DESC?>DESC<cfelse>ASC</cfif>

</cfquery>

Conclusion

There are many different ways to deploy a SQL injection attack against an application. And since they are so easy to do, many attacks are automated. Fortunately, we can easily mitigate the risk of these attacks through the use of prepared statements, server and DSN best practices, and sanitization functions. If you still have code out there that is vulnerable to this type of attack, you need to take care of it, immediately.

To leave you on a positive note, you can download a tool to help you find your missing cfqueryparams. Peter Boughton's QueryParam Scanner will search through your code looking for missing cfqueryparam tags. You can find the scanner at RIAforge. As with all automated tools, you should not solely rely on it to find all of your mistakes.

Resources

Open Web Application Security Project: SQL Injection Article

SQL Injection Cheatsheet

Wikipedia: SQl Injection Article

QueryParam Scanner

Jason Dean is a Web Application Developer at Dakota County Technical College in Rosemont, MN. Jason blogs at http://12robots.com, is on the cf.Objective() steering committee and can often be found on StackOverflow, Twitter (@JasonPDean) and at ColdFusion conferneces. Jason lives in Farmington, MN with his wife and two children (almost three).