"cross-site scripting" for CF - SQL exploit

I am running an environment which uses ColdFusion 5.0 as the application server. I was trying to replicate, in a test environment, what is described as the "The Deadly Database Exploit".

My SQL code imbedded in the ColdFusion code would read... for example:
<CFQUERY NAME="test" DATASOURCE="example_source">
     SELECT *
     FROM       test_table
     WHERE  test_item = '#Form.test_field#'

The variable test_field comes from a different page and is drive by input from the user. I was playing around with it trying to see what happens and used a series of inputs

1. 1324 TRUNCATE TABLE test_table
2. 1324' TRUNCATE TABLE test_table
3. 1234' </CFQUERY><CFQUERY NAME="test"
DATASOURCE="example_source">DELETE FROM test_table WHERE

I am sure you understand what I was trying to do, and why I added some of the single quotes, however none of these things worked. I was wondering if this exploit was fixed by Macromedia in the 5.0 release, because it does not seem that the existing ColdFusion code allows the user to enter CF commands. I am not runny any type of user input filter on the site, and am really interested in assessing whether this potential exploit affects me and is worth fixing.

I would appreciate your thoughts and responses.

Thank you,

Who is Participating?

Improve company productivity with a Business Account.Sign Up

jimmy282Connect With a Mentor Commented:
and you CANT do this with ACCESS because Access doesnt support this feature of multipe quesries separated by ;

Try with SQLServer or Mysql, you will know what I mean
Use this custom tag as first line of your Application.cfm

This solves most of such Hacks.

      Use at your own risk!  Javafuse Inc. makes no warranty claim or guarantee, implicitly or otherwise, to the soundness and general ability of this
      tag to do what it claims.  As well, the user, by using this code, agrees to indemnify the authors: Javafuse inc. to be free from all claims
      which result from the use of the tag "CF_HACKER", or any other name under which the codebase appears and to defend
      Javafuse against any possible litigation resulting from the use of this code in its original format or any augmented forms.  
      Database security is the responsibility of the Architect/Developement Company deploying the site and tag, not Javafuse inc.
      Javafuse is not responsible for lost, damaged, de-valued, compromised data integrity, or any other loss, monetarily, intellectually,
      or percieved,  resulting from the voluntary use of this tag, contained codebase, or augmented code base contained here in
      under any name.
      This tag is free to use for developement and for production with no strings attached!
       While no site is safe against hacking, this tag can help the developer as a tool in the fight against malicious attacks.
       This tag strips most HTML, CFML, Profanity and most core SQL that could be used to "attack" a site.
       Again, use at your own risk!  There is no warranty or charge for this code.
       We ask that if you use this tag, that you email us.  Why?  Because we are always looking to get feedback, and find any additional
       SQL or REGEX that could be used to protect databases from malicious scope data.  As well, by providing this openly for free,
       when changes or modifications are made, we can notify you by email.  We are willing to send out emails when new versions
       or bugs are found, if youre willing to leave us an address. )
       Remember best practices, when possible search and identify based off of numerical or dynamically computed  identifiers.  
       Also use stored procedures when possible, althougth this wont remove CFML or HTML!
       One GOOD example:
       Is to store usernames and password in an extra column containing an alpha/numerically hashed combined value of the two,
       and then compare that computed value against the stored hashed numerical value in the database.  
       This allows you to create where statements like this: WHERE userHash = #hash(username, password)#
       (Dont use allaire crypto functions, they suck as they produce non predictable chars that must be escaped!)
       And here even if the idiot does submit SQL, its dumped into a value like 'ALKJDLKIOIENF98765IDOIDHD98873LKJ', and well
       Ive never heard of that deleting rows before!. )   lol
      One VERY BAD example:
      Just let you user submit any text into a where clause:
      WHERE userName = '#form.userName#' and password='#form.password#'       
      This example is what this tag is designed to help guard against.
      What happens when I pass the following two form fields to the following query? What will happen?
      First the query, sitting on the server waiting to find users.
      <Cfquery name="matchUser" datasource="#DS#">
            select * from users
            where username = '#form.username#' and password = '#form.password#'
      Now I am going to submit the following two form fields to this query:
      <form action="index.cfm" method="post">
            <input type="text" name="username" value="john@cfjava.com">
            <input type="text" name="password" value="fake' ; delete from users where email != ' ">
      The resulting SQL when executed will show this:
      Select * from users
      where username = 'john@cfjava.com' and password= 'fake'; delete from users where email != ' '
      READ THIS:
      (while this exact syntax may not work on your dbase, it wont take but 5 seconds to come up with syntax that will..... >)   )
      Notice anything?  Yes! Your entire users table is now completely empty!  Why? Because you let the user
      submit what ever they wanted into the where clause of an SQL statement.  But why stop there, I could have leveled the database!
      Because odds are if youre leaving security holes like this open, you havent taken the precaution of creating special users for your
      database who have limited privileges!
      So what to do?  Design with security in mind.  Create appropriate users. Hash values to alpha/nums when and where possible.
      Store combined versions of data in an extra column that are dynamically computed on key value matching, it reduces the number of
      comparisons, and keeps you from matching fields to untouched user text.
      And only when you absolutely have to, do you ever let a user submit text into a where clause.  And in this case, use this tag,
      or a similar tag to help manage your risk!  Else you may be liable, in that      a lawyer only has to prove "partial" liability for a partial settlement!  So developer beware!
      The goes for all scopes, if youre really interested in the next security step, then use an URL encryption algor, so that by
      looking not even you can tell where youre going, let alone what youre passing. )
      There a ton of books on Architecture and Design that you can look in too for real explanations.
      TAG USE:
      OK, I will try to show by example. The tag will filter all fields in the FORM and URL scopes by default.
      I want to filter the url and form scopes, filtering all fields; this is the default!
      I want to filter all fields only the form scope:
            <cf_hacker scopes="form" >
      I want to filter all fields only in the form and cookie scopes:
            <cf_hacker scopes="form,cookie">      

      I want to filter all fields except the one named "links" in the form scope:
            <cf_hacker scopes="form" form="links">
      I want to filter the form and url scopes, but skip two FORM fields, named user_links and user_html, that may contain HTML.
      Remember that form and url scopes are set by default, so we do not have to pass them into the tag with the "scopes" parameter.
      So this call is ....
            <cf_hacker form="user_links,user_html">
      the same as this call:
            <cf_hacker scopes="form,url" form="user_links,user_html">
      Which ever one is easier for you to quickly understand...
      I want to filter the FORM, URL and COOKIE scopes, skipping fields in each scope:
            <cf_hacker scopes="form,url,cookie" cookie="user_home" url="user_name" form="image1,image2,image3">
            The tag filters all fields in URL and FORM scopes by default, you dont have to anything but call tag.
            To explicitly filter a scope or scopes, pass a comma delimited list of scopes to filter.
            To skip fields/values/variables in the passed scopes, simple name the scope as a parameter and give a comma delimited list of fields to skip.
      We will be building on this base for quite some time.  So if you come across syntax for one of these database types, please email with any
      additions you would like to see integrated to: john@cfjava.com Please include your name, and country of origin as well, and you will get "props"
      in the next update release of the tag!   . )
      You can thank the following people who have contributed to this tag.  Contribute a database regex and join the wall of shame!
      John Ensign             U.S.A.                  john@cfjava.com
      Lucas B.                   Nederlands        withheld              
      Some notes on the codebase:
      Exempt fields and profanities are run from loops, as they should be; the regex for the database syntax is not, and this is primarily for
      readability.  As well, by laying them out one by one, readers who are not familiar with REGEX can come to grips with this much at least,
      and hopefully begin to add/submit their own match values.  We dont want any flaming retarded statements, like you should loop that too.... etc.
      This tag does add a millisec or two to processing time.  In this particular case its not about milli-secs, its about taking judicious steps
      to minimize our clients risk, and being able to prove you took reasonable and prudent measures to protect your client's data!  
      Its all about RISK MANAGEMENT, nothing is 100%.  And if you had to ask,      then quit now while      your still ahead. )  
      Beware of cheap imitations!  Peeps using the string methods are fooling themselves!
      <cfparam name="attributes.scopes" default="FORM,URL">
      <cfloop list="#attributes.scopes#" index="target">
            <cfset name = "attributes." & #target#>
            <cfparam name="#name#" default="">
      <cfset profanities = "(Edited by Computer101)">
      <cfloop list="#attributes.scopes#" index="scope">

            <cfloop collection="#evaluate("caller.#scope#")#" item="field">
                         if(  listContainsNoCase(evaluate("attributes.#scope#"), field) EQ 0  )
                         //GRAB FIELD VALUE
                              value = evaluate("#scope#.#field#");
                        //IF FIELD IS EMPTY MOVE ONTO NEXT FIELD      
                              if(len(value) EQ 0)
                        //IF THIS IS A FILE FIELD FROM WINDOWS MOVE ONTO NEXT FIELD                  
                              if(REFindNoCase("*\ *\.tmp", value) GT 0)    
                         //HTML and CFML : skipping exempt fields and cleaning all others.
                              value = REReplace(value, "<[^>]*>", "", "All");
                         //SYSTEM CALLS
                              value =  REReplaceNoCase(value, " *sp_ *", "", "All");
                              value =  REReplaceNoCase(value, " *dt_ *", "", "All");      
                              value =  REReplaceNoCase(value, " *db_ *", "", "All");      
                               value =  REReplaceNoCase(value, " *; *delete *from *", "", "All");
                              value =  REReplaceNoCase(value, " *delete *from *", "", "All");
                        // SELECTS AND PASSED SUB-SELECTS
                                value =  REReplaceNoCase(value, " *\( *select *from  *\) *", "", "All");
                                value =  REReplaceNoCase(value, " *; *select *\* *from *", "", "All");
                                value =  REReplaceNoCase(value, " *select *\* *from *", "", "All");
                              value =  REReplaceNoCase(value, " *select *into *from *", "", "All");
                        // INSERTIONS
                            value =  REReplaceNoCase(value, " *; *insert *into *\(  *\) *values *", "", "All");
                            value =  REReplaceNoCase(value, " *insert *into *\(  *\) *values *", "", "All");
                        // UPDATES
                               value =  REReplaceNoCase(value, " *; *update *", "", "All");      
                               value =  REReplaceNoCase(value, " *update *set *= *", "", "All");
                        // CREATIONS/
                               value =  REReplaceNoCase(value, " *create *proc *as *", "", "All");
                              value =  REReplaceNoCase(value, " *create *view *as *", "", "All");
                              value =  REReplaceNoCase(value, " *create *trigger *on *", "", "All");
                              value =  REReplaceNoCase(value, " *create *table *", "", "All");
                              value =  REReplaceNoCase(value, " *create *table *\(  *\) *", "", "All");
                              value =  REReplaceNoCase(value, " *create *database *on *\(  *\) *", "", "All");
                              value =  REReplaceNoCase(value, " *create *database *on *", "", "All");
                              value =  REReplaceNoCase(value, " *create *function *\(  *\) *as *", "", "All");
                              value =  REReplaceNoCase(value, " *create *temporary *table *", "", "All");
                        // ALTERATIONS
                              value =  REReplaceNoCase(value, " *alter *database *remove *", "", "All");
                              value =  REReplaceNoCase(value, " *alter *database *modify *", "", "All");
                              value =  REReplaceNoCase(value, " *alter *database *add *", "", "All");
                              value =  REReplaceNoCase(value, " *alter *proc *as *", "", "All");
                              value =  REReplaceNoCase(value, " *alter *view *as *", "", "All");
                              value =  REReplaceNoCase(value, " *alter *trigger *on *", "", "All");
                              value =  REReplaceNoCase(value, " *alter *table *on *", "", "All");
                        // TRANSACTIONS
                              value =  REReplaceNoCase(value, " *begin *tran *commit *", "", "All");
                              value =  REReplaceNoCase(value, " *begin *distributed *tran *commit *", "", "All");
                        // DROPS
                              value =  REReplaceNoCase(value, " *drop *trigger  *", "", "All");
                              value =  REReplaceNoCase(value, " *drop *view  *", "", "All");
                              value =  REReplaceNoCase(value, " *drop *table *", "", "All");      
                              value =  REReplaceNoCase(value, " *drop *database *", "", "All");
                              value =  REReplaceNoCase(value, " *drop *user *", "", "All");
                              for( index = 1; index LT listLen(profanities); index = index + 1)
                                    value =  REReplaceNoCase(value, " *#ListGetAt(profanities, index)# *", "", "All");            
                              rc = structUpdate( evaluate("caller.#scope#"), #field#, value);
<!------------------------------------------------- TO DO: ------------------------------------------------------
  Fix image file upload fields:
        win -   now working on windows, still not happy with it though. )
      liny/unix -  need to get liny/unix upload naming schema for matching
      mac  - ditto
  use statement
  CF will not give complete header data per RFC convention like asp,php,perl,vb will.
  Plan on converting tag to an advanced version in java automated form field type
  detection using java REGEX for isps or industrial level users.
  Continue adding support for mysql, oracle, and postgres.
  *Most of SQL server is covered.

Name the file as hacker.cfm and out in your Custom Tags Folder.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

I am assuming you are referring to the following article:

The author John Ashenfelter wrote this response:

"It still is a problem -- because its not really language dependent per se. The key is checking/controlling the data type of the CF (or ASP or JSP, etc.) variables and checking the SQL if you're dynamically creating it. If you use stored procedures, you're pretty safe since you can include logic to check the variable type (e.g. "18" instead of "18 TRUNCATE TABLE ITEMS" for the item ID in a search form). If you're using EJB in java, you're set as well since the EJBs create the SQL cleanly (and they typically model the object).
CF5 specifically helps some since with user-defined functions, you can create a few utility functions for checking for danger words ("TRUNCATE, DELETE") and you can create server-side type checking. Or if you're using stored procs, you can use CFPROCPARAM (which has been in CF for a while) to make sure the types match up properly."

DascaluAuthor Commented:
cheekycj, that's one of the articles I looked at, and also contacted the author (John Ashenfelter). This was his response, similar to what you posted:

The problem isn't specific to CF -- its general to this type of application if you have dynamically-created SQL that isn't closely checked. What may be the issue in your case is that the database is locked down properly --
depending on which db you're using, the permissions for the ColdFusion user may prevent DELETEs in particular, but TRUNCATES as well. Also, the language differs slightly from db to db. One quick test is to create your own cfquery
(in code, not using a form) and try the DELETE (on a test table or with a db backup please!) and see if you have the permissions -- or check w/ your DBA if you've got one.

The point is really that you've got to get everything together to secure an app -- db settings, server settings, and code. This sort of thing is kind of like the buffer overflow problem in C programs -- they're easy to put in
unless you really think about it. I find that stored procedures pretty much solve the issue since those variables are strongly typed -- so in CF the CFPROCPARAM lets you say itemID is an integer, so you can't do the "18 TRUNCATE TABLE ITEMS" sort of thing.


John Paul Ashenfel

My problem is that I can't replicate the exploit. I did this on my local box with full db priveleges, using Access2000. If you look at my original question, attempt 3, I placed this variable directly in the form filed:

1234' </CFQUERY><CFQUERY NAME="test"
DATASOURCE="example_source">DELETE FROM test_table WHERE

as I said this didn't work, however if I hardcode this string in the place of #Form.test_field# it works... the record gets deleted. So db priveleges or malformed SQL is not the problem. It seems that when I try to pass the string through a form variable, the server automatically strips the CF tags, or does something else. I have turned the debug on and looked at the query, and... surprise the string is shown without the CF tags. Again I have no filter, or any DB constraints, just a plain installation of CF 5.0 Server and Access2000 on a WIN2000 server box.

jimmy282, thanks for the filter, that's one of the more elaborate ones I have seen, but I am intereseted in why I am not able to replicate the exploit.

Let me know if you have any more thoughts on this, and again thanks for all the responses.

try in your URL

http://server/showentry.cfm?id=DELETE FROM TABLENAME;

instead of

Just a hint!
DascaluAuthor Commented:
jimmy282, our environment is running Access2000, so that the only db i can test. I know SQL allows multiple queries separated by ";" but Access can't, so I can't really do that.
I am not sure what you meant with your suggestion
"http://server/showentry.cfm?id=DELETE FROM TABLENAME;"
If you reffer to using a URL variable rather then using form vars... I have already tried that already with identical results.
DascaluAuthor Commented:
I think I have finally settled this. The problem, or should I say security constraint is Access. As jimmy282 noted, Access2000 can not perform multiple SQL commands using the ";" separator; therefore the only way to do it is to close and then open another query. This will only be handled correctly by CF if the variable has an evaluate() around it and then placed in the SQL querry, like John Ashenfel advised. Luckily this is not what my script does, so that's why it won't work.

jimmy282 i will give you the points, however I will leave this post open for a few days to see if anyone has any more thoughts.


John Paul Ashenfel
Ahhh -- most of this doesn't work with Access since it has such a simple SQL implementation. One of the few advantages for running Access :) I usually throw on a dev edition of SQL Server when I demo this -- much more realistic scenario as well. TRUNCATE obviously doesn't work since its not in JetSQL.

The CF you're trying won't work -- you'd need an EVALUATE() around all of the SQL text in the *source* code, and even then it probably won't parse correctly. The *value* "1234' </CFQUERY><CFQUERY NAME="test" DATASOURCE="example_source">DELETE FROM test_table WHERE
test_item='existing_entry" is what form.x evaluates to -- if you did an insert on a text field, that's what the content of the text field would be.

I tested it on SQL-Server 7/2000 and on MySQL. I've had folks do it on Oracle as well.
ok, let it be open for few days.

Didnt understand, why a 'B'?
Why not A?
DascaluAuthor Commented:
I didn't get a clear answer from you, with an exact reason for why the exploit didn't work. I was looking for something that would completely clarify the issue. I got the answer I wanted in an e-mail from the person who wrote the above mentioned article (I posted the response). You never addressed the issue with my 3rd input (closing and then opening a new querry), your answer only reffered to Access not being able to do multiple commands separated by a ";" which was half the problem.
DascaluAuthor Commented:
don't get me wrong i appreciate your answer which was very helpful, however I was really looking for something which would make things clear for me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.