SQL Injection is unfortunately a large problem on the internet. If you are not familiar with this and you are a web developer you need to look into it…. badly.
Recently ColdFusion sites have fallen victim to a large amount SQL injection attacks. From what I gather this was an exploit using a combination of characters to trick ColdFusion. I have been approached by a couple sites asking me to repair their SQL injection problems after they got hit with the latest wave of exploitation.
I am not a ColdFusion programmer by any means (I can do it but it really isn’t my first choice of technologies) so forgive my post if some of this seems “obvious” to the ColdFusion masters out there.
First step was identifying the pages with issues and what the issues were exactly. Turns out in this case there is kind of a double whammy. ColdFusion itself had an exploit in it regardless of the development efforts. I had the server guy apply some patches to the server which Adobe released and then began to review the site in detail.
Those patches would protect against the core exploit but, the site itself had many places where it wasn’t programmatically protected against SQL injection. Since this site had well over 500 CFM pages I needed a quick way to identify the problem spots. I leveraged the Acunetix Web Vulnerability Scanner which can be found at: http://www.acunetix.com/vulnerability-scanner/download.htm.
After running a quick scan on the site (I should mention here this is OF COURSE a copy of the live site to a test server) I was delighted to see that the scanner will only show the first 200 SQL injection vulnerabilities on the site and then stop identifying them. I would assume it stopped at 200 because it was sad…. or maybe there is a setting somewhere to make that more, I have no idea. Here are a list of some of the errors I saw:

The GET variable catID has been set to ‘”.
The GET variable catID has been set to %2527.

Allowing the program to successfully set the catID variable which is used directly in SQL queiries can be avoided. So, let’s start by resolving the CatID issues.
I see great gems like this:


SELECT PF.*,
MF.mfrName,MF.logofilename
FROM productsAndFamilies PF LEFT OUTER JOIN manufacturers MF
ON PF.mfrID = MF.mfrID
WHERE CatId= #URL.catId#
AND PF.visible=1

The URL.catId variable can be used to leverage the SQL injection exploit. So how do we solve this? When creating a site I usually leverage SQL stored procs and parameterized queries to protect my db and do simple datatype checks on my user input to make sure nothing silly is going on. I am not going to write all stored procs for this site since that would be very time consuming and frankly out of the budget of the client. So, I am left with parameterizing (that a word?) the query and making sure people are messing with my variables. I little trip to the ColdFusion manual and I find how to do this. It is done like so:


SELECT PF.*,
MF.mfrName,MF.logofilename
FROM productsAndFamilies PF LEFT OUTER JOIN manufacturers MF
ON PF.mfrID = MF.mfrID
WHERE CatId=
AND PF.visible=1

Basically I am making sure that this query gets executed with URL.catID as an integer. So, I did that and re-ran the tool… same errors. Great, WTF? it still claims this is a SQL Injection prone page. Great, so where to from here?
I explored the tool and the docs some more and found an option called “Launch this attack with the HTTP editor”… Sounds cool, I’ll try it. So I can get a picture of if SQL is in fact being compromised I also attach SQL Profiler to the DB to see if SQL is getting hit with the attack.
Well, I ran the attack. Nothing went to SQL. WTF? Sounds good to me. There is a tab on the test page that reads “Response Data”. It had a bunch of HTML on it but the message was basically a Cold Fusion error message:
Error Diagnostic Information

Invalid parameter type

Cannot convert ‘ to number.

Please, check the ColdFusion manual for the allowed conversions between data types

The error occurred while processing an element with a general identifier of (CFPARAM), occupying document position (1:1) to (1:41).

So I see this as a good thing. You tried to mess around and I slapped you down crazy hacker man. But alas the tool does not see it that way and since the client wants a “Clean Report” from the utility I had to keep on digging.
Now that SQL is protected from bad things we have to make our site not rely on exceptions to alert the user. NEVER rely on exceptions when you can control them. We KNOW that if a non-numeric value is passed in it will throw an exception. So, why don’t we just determine if there is a bad value and handle it gracefully.
A quick trip to that there intarweb leads me to the following CF Function called IsNumeric(). This is a good thing, I can swing this. So I go with:


CatId Must Be Numeric

GREAT SUCCESS! The utility doesn’t want exceptions thrown anywhere on the site (imagine that!). Now, I would suggest you elaborate on the check, maybe send yourself a message or log it somewhere so you can be alerted to pesky people or attacks on your site. The CFABORT tells ColdFusion to stop processing the page.
I hope that this helps anyone dealing with this issue and puts those protection methods into their SQL injection protection library.
Always make sure user input is valid.
Always use parameterized queries (and stored procs if you can).

Leave a Reply

Your email address will not be published. Required fields are marked *