SQL Injection Part III - Don't Forget Sorting
So... you have diligently added CFQUERYPARAM to every input variable. Your database is secure and safe from SQL Injection - right? Well... maybe not. Did you remember to account for the ORDER BY Clause? Let me explain.
Let's suppose we have created a simple search and drill down. Searching gives us back a record set which we present in a tabular format - like so:
| User | City | |
|---|---|---|
| Dick | archiesfriend@aol.com | Allentown |
| Bob | Bob@cubandisco.com | Springfield |
| Harry | Mommasboy@myspace.com | Toledo |
This is ok, but sometimes when there are 60 or 70 results we wish to make them sortable. Perhaps we don't want to use client side sorting for this so we decide to simply re-run the query with a different sort order. We choose to do the following:
First, we change the header for each of the items to include a link to the results and a URL variable for order - something likes this.
<th>
<a href="rs.cfm?eml=#form.keyword#&orderby=U.User">User</a>
</th>
Next, in our query code we add something like this.
<a href="rs.cfm?eml=#form.keyword#&orderby=U.User">User</a>
</th>
<cfparam name="url.orderby" default="C.city"/>
<cfquery ....>
SELECT U.User, U.Email
C.City
FROM Users U JOIN contact C
ON U.user_id = C.user_id
WHERE U.email LIKE
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="%#form.eml#%"/>)
ORDER BY #url.orderBy#
</cfquery>
Simple right? Actually, we have just opened up a gaping hole in our DB. Someone could easily pass something like:
<cfquery ....>
SELECT U.User, U.Email
C.City
FROM Users U JOIN contact C
ON U.user_id = C.user_id
WHERE U.email LIKE
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="%#form.eml#%"/>)
ORDER BY #url.orderBy#
</cfquery>
?orderby=C.city; truncate table contact
We can't use CFQUERYPARAM on an ORDER BY clause - so how do we solve this problem (short of moving all of our ordering to the client side). Here are two possible approaches.
Fix 1 - Validate
The simplest way to do this is to simply make sure that URL.orderby matches a list of potential strings:
<cfparam name="url.orderby" default="C.city"/>
<cfset oList = "U.User,U.Email,C.City"/>
<cfquery ....>
SELECT U.User, U.Email
C.City
FROM Users U JOIN contact C
ON U.user_id = C.user_id
WHERE U.email LIKE
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="%#form.eml#%"/>)
<cfif listfindnocase(oList,url.orderby)>
ORDER BY #url.orderBy#
</cfif>
</cfquery>
This is fine if we are writing our application out of whole cloth - but what if we are repairing legacy code and we want to secure it as fast as possible? We don't have time to go and look up every column name in the DB and create lists. Here's a trick to try that serves as a nice work around.
<cfset oList = "U.User,U.Email,C.City"/>
<cfquery ....>
SELECT U.User, U.Email
C.City
FROM Users U JOIN contact C
ON U.user_id = C.user_id
WHERE U.email LIKE
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="%#form.eml#%"/>)
<cfif listfindnocase(oList,url.orderby)>
ORDER BY #url.orderBy#
</cfif>
</cfquery>
Fix 2 - Q of a Q
The real danger of Injection is that a malicious user can change the contents of the DB. We can, however, use Q of a Q to take the process of ordering out of the picture altogether.
<cfparam name="url.orderby" default="city"/>
<cfquery name="test" datasource="blah">
SELECT U.User, U.Email
C.City
FROM Users U JOIN contact C
ON U.user_id = C.user_id
WHERE U.email LIKE
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="%#form.eml#%"/>)
</cfquery>
<!--- now order it --->
<cfquery name="test" dbtype="query">
SELECT *
FROM test
ORDER BY #Url.Orderby#
</cfquery>
See what I mean? No live data was harmed in the making of this ORDER BY clause.
<cfquery name="test" datasource="blah">
SELECT U.User, U.Email
C.City
FROM Users U JOIN contact C
ON U.user_id = C.user_id
WHERE U.email LIKE
(<cfqueryparam cfsqltype="CF_SQL_CHAR" value="%#form.eml#%"/>)
</cfquery>
<!--- now order it --->
<cfquery name="test" dbtype="query">
SELECT *
FROM test
ORDER BY #Url.Orderby#
</cfquery>
Of course we will need good error trapping to insure that malicious attempts (which will now generate CF errors rather than JDBC errors) are caught.
One more note on this second approach. We might consider using it anyway if we are heavily caching data. If the sample DB query above is cached for, say an hour, and a user clicks on all three columns to re-order it using the first approach we will end up with three separate cached queries - identical save for ordering. If, however, we are using the second approach, we will have 1 cached query that is simply reordered in memory for each sort request. For some applications this is an excellent choice - especially if they are heavily used and DB resources are scarce.
18 Comments
-
@Mark,
Several years ago, at about the same time I started rewriting my queries to utilize CFQUERYPARAM throughout, I ran across this ORDER BY issue. At that point I started actually aliasing my sorts, so that the URL might be '?sort=name' but then I would actually convert to real SQL in the object, something like this:
[cfparam name="url.sort" default="name"]
switch (url.sort) {
case "age":
sortBy = "DateOfBirth, LastName";
break;
case "title":
sortBy = "JobTitle, LastName";
break;
default:
sortBy = "LastName";
}
... then in the query:
ORDER BY #sortBy#
So the default will capture any hack attempt, since an invalid url.sort will effectively become 'name' and the hack entry never makes it to the CFQUERY. This had the additional benefit of allowing me to add complexity to my sort options without having to go back and change links. For instance, if the system now has 3 people named 'Smith', I can simply convert the default sortBy = "LastName, FirstName" and now any request to sort by 'name' sorts on both data points regardless of where the request comes from.
Kudos, too, on the reminder to people to cache large queries so that re-sorting can be done with QofQ rather than additional round-trips to the DB. -
Is there a reason you don't recommend cfqueryparaming the order by string:
ORDER BY <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.orderby#" /> -
@Terrence,
Yes... because a bound variable is not a construct that can be used in an order by clause. It's expecting a constant. If you try it you should get a message.. something like "variables are only allowed when ordering by an expression referencing a column" ... which sounds like it SHOULD be allowed by order by - but it really means that variables are bound to column values and not to the names of columns themselves. -
"The real danger of Injection is that a malicious user can change the contents of the DB."
Trust me, from personal experience, that's only the tip of it. The real danger is when they use their injection to download executable code and propagate it across your network, infiltrating the soft underbelly of your organization, gaining access to desktops, servers, databases, and anything else having keyloggers and trojans all over the place can get them. Next to that, having a spammer append a URL to your comment field or even drop your tables is pretty minor.
And for the example, I'd just do OrderBy=1/2/3 or A/b/c and then switch/case that to the actual value (or if I was really lazy, maybe even just it for the index in an array of column names)... why on earth would you use real database column names in your URL? That just saves the script kiddies effort. -
Great post. I truly wished the "lead developer" at my job cared even a little about SQL injections. I constantly attempt to secure the code, whereas the lead says "it's okay, if anything happens we have backups". That is his way to secure the sites we work on :/ . Maybe I should pass along what was in the previous reply to him... but even then I doubt he would care. Either way, excellent posts - the entire series ;). -
Can't you just do:
Order BY #Replace(URL.OrderBy, " ", "", "all")#
Then you're "?orderby=C.city; truncate table contact" attack example will result in:
ORDER BY C.city;truncatetablecontact -
@Esmeralda -- Just do a little research on how SQL injection works, then send him a link that does something scary but harmless using his code.
Or better yet, get a copy of HP's tool, Scrawlr, and run it against the insecure content.
http://www.communities.hp.com/securitysoftware/blo... -
@John,
If you note Mark's earilier post (http://www.coldfusionmuse.com/index.cfm/2008/7/18/...), hacks these days are likely to use the URL escape characters for just this reason. In other words, you're more likely to see:
"?orderby=C.city;%20truncate%20table%20contact;"
This will not get caught by the Replace(), but it will render with spaces properly in the SQL. -
@jfish - It was a bit of a "too early in the morning" thought :)
I personally use a switch statement. As you pointed out, it also has the added benefit that you can specify other fields as well if you want. -
Interesting discussion but doesn't this just mean that a primary defense is to filter out "bad" sql words in any url (e.g. insert|delete|select|update|create|alter|drop|
truncate|grant|revoke|declare|exec|backup|restore|
set|execute|dbcc|deny|union) with a regular expression and then only those example words with starting with [^A-Za-z] or ending with [^A-Za-z]. -
@ortho,
Except that not all hack attempts will use simple ASCII attacks, as the earlier post noted. See http://www.coldfusionmuse.com/index.cfm/2008/7/18/... for a disturbing report of URL escaped injection attacks. In addition, the list of reserved words to test for can get a little long: http://msdn.microsoft.com/en-us/library/aa238507.a...
Just a reminder that those who want to hack are sometimes highly motivated. -
Our system was not SQL Injection proof and we recently suffered an attack that corrupted the data in some of our database tables. The attack was quite elegant and fortunately did not cause severe damage other than the appending of a SCRIPT sting to a bunch of VARCHAR fields. This was meant to actually execute a JS file and this qualifies as a XSS attack.
In researching the Web for a solution for the problem, and a way to immunize our CF application against further attacks, we came across the CFQUERYPARAM solution, but our application has over 5000 files, each with one or more Queries and Stored Procedure calls. Implementing such a solution in such an extensive amount of files was impossible in a timely fashion, so I looked for another solution and came across a ColdFusion written function (isSqlInjection) that showed some promise but some shortcomings as well.
I wanted something that we could deploy fast and that would immunize the entire application in one single swoop.
As far as I understand, SQL injections can come from either FORM fields or from URL passed variables. Therefore we developed a function that was placed in our application.cfm and therefore used by all our CFM files. The function used a custom developed Regular Expression to check all URL and FORM fields for possible SQL Injections.
We were able to develop this in one day and implement it immediately. That same night we were able to catch and prevent 2 more SQL Injection attempts.
We have since improved the script and it now does the following:
• Checks all FORM and URL input for SQL injection code
• Interfaces (CFHTTP) with ARIN WHOIS Database Search (http://ws.arin.net/whois/) to get ISP information for the offender’s IP.
• Automatically sends an abuse report to the ISP concerning the attack.
• Displays a message informing the hacker that the attack was logged, that his/her ISP was contacted and that he/she is breaking the law
• Sends us an email with the SQL Injection string, IP address and other information.
• Stores the hacker’s IP address in an APPLICATION array (Black List).
o Each time a page in our application is requested, the IP address (CGI.REMOTE_ADDR) is compared with those in the Black List and if it is present, page execution is halted right at the application.cfm level returning a blank page to the browser
o Black List entries that are older than one hour are cleared by a scheduled task on an hourly basis.
We are making this code available to other CF developers for free. Please request a copy by email. luism@grouptraveltech.com.
If after receiving it you have suggestions or improvements, please send them my way as well. -
Luis,
Excellent stuff... thank you for contributing to this thread. I especially like the idea of reaching out to the ISP. You said that you send a message informing the hacker that the attack was logged. I was wondering where you get the email address in order to send such a message? Are you pulling it from the whois info?
As I understand it, this attack is done using widely dispurse computers infected with an agent (virus... adware etc). I suspect that these computers are unwitting accomplices - so I wonder about the effectiveness of such a message ... although informing the ISP should help because they will be as concerned as we are.
Any way - thanks for the info and generous offer. If you want to send me the code I can add a follow up post if you like.
-mark -
Hi Mark
Would love to send you the code, but I do not have an email address to send it to.
>> You said that you send a message informing the
>> hacker that the attack was logged.
The message to the hacker is displayed on the screen. The message that is sent by email is to the hacker's ISP Abuse Email Address. And yes, I do get that email address from the WhoIs info.
I wish I could get the hacker's email address from the WhoIs info, but that would just make retaliation too easy.... -
when you talk about querying a cached query to save resources isnt that the same as using - cachedwithin="#CreateTimeSpan(0,0,2,0)#" -
Hi Mark,
Excellent post. Just thought I'd add this note: ColdFusion's Query-of-Queries feature doesn't currently support functions and complex expressions in the ORDER BY clause (ex: ORDER BY max(myCol)). A workaround is to first alias the expression in the pre-QoQ SELECT statement, and then use that alias in the QoQ's ORDER BY clause.
Example:
<cfquery name="myQuery">
SELECT someColumn, max(col2) AS maxCol2 FROM someTable GROUP BY someColumn
</cfquery>
<cfquery dbtype="query">
SELECT * FROM variables.myQuery ORDER BY maxCol2
</cfquery>
If max(col2) was used in QoQ's ORDER BY clause, CF would throw an error.
As for query-related functions, I'd also love queryAppend(): http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbu...
Thanks!,
-Aaron Neff
Sites to Watch
Aggregators
RSS
Related Blog Entries