Adobe Solution Partner

July 22, 2008

ColdFusion SQL Injection

Filed under: ColdFusion, Databases — Tags: , , , , — Daryl Banttari @ 7:25 am

Seems there’s a lot of ColdFusion sites out there getting hit by an automated SQL Injection attack, which adds a “<script>” tag to varchar fields using a bit of SQL Server t-sql.

Many years ago, I wrote a script that would find and automatically <cfqueryparam> queries (which has been at Daryl’s ColdFusion Primer. I’ve dug that up and updated it slightly to handle INSERT statements and not parameterize cached queries. (The latter will be highlighted in red.)

(For more information about avoiding SQL Injection in the first place, see my Paranoia 101 page at the aforementioned Primer.)

Place the script (temporarily) in your webroot and run it. It will show you all of the queries in that directory tree and give you checkboxes next to each that will allow you to choose which queries to parameterize. It will also show you what the result of parameterization will look like.

Remember– it’ll show you what it plans to do before it does anything. So you can run it once (and not click the “submit” button) simply to see what files contain unparameterized queries.

Caveats:

This will probably break some queries, especially if you do things like WHERE date > ‘#dateFormat(d)# #timeformat(d)#’ or WHERE NAME LIKE ‘#searchname#%’. USE WITH CAUTION! It’s best to test the changes before moving them into production. Remove the “.old” files once the site is confirmed as working well.

Download the Script

Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • StumbleUpon
  • Technorati
  • TwitThis

21 Comments »

  1. Very cool! The only problem I ran into was some of my queries have a variable with the word "update" in them, and the parameterizer was making those a timestamp, eventhough the real datatype is an integer (I guess it’s because of the word "date" that’s part of "update")?

    Anyway, other than that, this tool worked great!

    Comment by Jake Munson — July 22, 2008 @ 12:00 am

  2. Very cool. I blogged Peter Boughton’s queryparam Scanner last night from RiaForge. I’ll have to give yours a whirl too. I was wondering if it would be possible to auto-fix stuff, but I didn’t think it would be possible to get the correct datatypes. I’ll check out what you did.

    Thanks.

    Comment by Brad Wood — July 22, 2008 @ 12:00 am

  3. What about changing it to also scan .cfc files?

    Comment by duncan — July 24, 2008 @ 12:00 am

  4. Also doesn’t seem to work where a query would have a LIKE in it, e.g.
    WHERE name like ‘#form.name#%’

    Comment by duncan — July 24, 2008 @ 12:00 am

  5. Duncan, I have already blogged a fix to make it scan .cfm, .cfml, and .cfc files.
    http://www.codersrevolution.com/index.cfm/2008/7/24/Parameterize-your-queries-without-lifting-a-finger

    If you successfully implement a change to get it to properly recognize the like systax let me know. I would like to clean up the interface a little bit. I don’t know if Daryl has any time to assist with this. Perhaps he will chime in and let us know.

    Comment by Brad Wood — July 24, 2008 @ 12:00 am

  6. I was having problems with the script doing weird things do to the logic used to find the open CFQUERY tag. On one template, I was just getting thousands of lines that read: [cfqueryparam /] (with greater than/less than in place of brackets.)

    So, I changed my instances of:

    findNoCase("<CFQUERY",TheFile)

    to:

    reFindNoCase("<CFQUERY\s",TheFile)

    And that fixed all my problems.

    Comment by Dan G. Switzer, II — July 24, 2008 @ 12:00 am

  7. I love this app!

    But, any reason it uses the default "CF_SQL_CHAR"?

    I’m using MSSQL and most of my ‘text’ fields are nvarchar. So am I correct that I need to set it to "CF_SQL_VARCHAR"? Is there any way to change this to be the defualt value? Thanks!

    Comment by Jeremy Kay — July 29, 2008 @ 12:00 am

  8. I have set up the app to leave everything UNchecked. I then check ONE query to update and it will update 2 or 3 pages…
    I’ve looked at the code and it seems that there is an error with the ‘hash’ part in the checkbox. It will repeat the same hash multiple times.

    Has anyone else had this problem?

    Comment by Jeremy Kay — July 29, 2008 @ 12:00 am

  9. You are a God! Totally saved me hours. Thanks!

    Comment by John Fitzgerald — August 4, 2008 @ 12:00 am

  10. Will this run on os x?

    Comment by Sam Singer — August 12, 2008 @ 12:00 am

  11. Is this still available? The download link doesn’t work.

    Thanks!

    Comment by Charles Fahey — July 11, 2009 @ 4:14 pm

  12. I just checked, and it seems to work. Just remove the .html to make it into a cfm file.

    Comment by Shannon Hicks — July 21, 2009 @ 3:38 pm

  13. As a DBA, I still believe you should lean your SQL code towards the use of stored procedures. This way, you will be able to separate your application tier from your database tier.
    Also, Sql server will reuse compiled plans and will also check for the data type integrity of the parameters.
    Finally it will remove the dynamic sql that makes the sql injection you fear about.

    Comment by Clement Huge — June 28, 2010 @ 1:41 pm

  14. Hello,

    I have a inquiry for the webmaster/admin here at http://www.webapper.com.

    May I use part of the information from this blog post above if I give a backlink back to this site?

    Thanks,
    Peter

    Comment by buy kinect — October 24, 2010 @ 4:50 am

  15. Sure thing, Peter!

    Comment by Patrick Quinn — October 24, 2010 @ 12:55 pm

  16. Have you thought of placing this on github for people to help with?

    Comment by Mike Henke — June 21, 2011 @ 9:14 pm

  17. Hey Mike. That’s a good thought, but, in all honesty I’m not sure we’d have time to give proper attention to an open source project right now. It’s great to be this busy!

    Comment by Patrick Quinn — June 22, 2011 @ 12:51 pm

  18. Patrick – I can throw up something like https://github.com/mhenke/CFML-in-100-minutes with a readme crediting and linking your this post. The repo would have the script for people to grab, tweak, and contribute back. Then if you guys create a github account eventually, I’ll fork from yours.

    Comment by Mike Henke — June 22, 2011 @ 1:41 pm

  19. That’d be great, Mike. Thanks for the effort. Keep us posted.

    Comment by Patrick Quinn — June 22, 2011 @ 2:59 pm

  20. Here is the github repository if anyone has any updates/fixes/enhancements https://github.com/mhenke/WebApper-ColdFusion-SQL-Injection

    Comment by Mike Henke — June 23, 2011 @ 8:39 pm

  21. Thanks a ton, Mike!

    Comment by Patrick Quinn — June 24, 2011 @ 8:35 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

 

Server Down?

Maximize Web application uptime by drawing upon Webapper's years of experience tuning and stabilizing many of the world's largest ColdFusion Web applications. Contact us today!