Contact Us Today!   |   + 1 (301) 424 3903



Preventing SQL Injection Attacks

SQL Injection can damage your website’s data and spread to other sites in your organization. This article explains how it works and how you can prevent it.

 

Exploited vulnerabilities:

A SQL Injection attack relies on the someone sending an HTTP request (web site visitor) being able to add SQL commands to a URL or form variable, and have it sent in a SQL query so that the SQL Server runs it as another SQL command. Any text passed along in URL or form variables can be modified by the user, or automated. URL/Form variables should only be treated as data, and never trusted.  (See http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored for a quick description). If text entered in the URL can get run on the database server as a SQL command, then a malicious user can pass any SQL commands that that SQL login has access to, including making malicious edits, reading from system tables for the database, bypassing site login code, or possibly (if the SQL login has permissions) creating or deleting tables. 

Over the past few months (since about May 2008) there’s been an automated SQL Injection attack running against first just ASP sites, then others, and now ColdFusion sites (searching for URLs containing “.cfm” files). It tries to pass a SQL script in a URL variable to each page, on the hope that at least some pages will pass it directly to the database in a way that the database server will execute them. (See http://www.coldfusionmuse.com/index.cfm/2008/7/18/Injection-Using-CAST-And-ASCII for details on how this SQL works.) 

The malicious SQL script used in this attack loops over system tables (sysobjects, syscolumns) to find character fields in all user tables, and append an HTML string to all character fields, assuming that at least some of those columns will be output directly on some other web page. This HTML string includes a JavaScript block, which will get the browser to download and run that JavaScript URL. (This JS URL will probably change quite often, but will be available enough to be downloaded.) Apparently, this script then turns the browser hitting the page displaying that data into a “bot” to hit other servers, and can do anything that Javascript in the browser can do, especially exploiting browser vulnerabilities.

It only requires successfully hitting one page on one site to get into that database, then the HTML would be displayed on whatever site uses those tables and columns.

The best way to prevent, is to make sure any data passed to SQL queries is only treated as data. All CF Queries should be parameterized (use CFQueryparam to pass the values in).

 

Action taken for containment:

The best permanent fix is for each and every query to be parameterized, so that no user-supplied text is passed to the database directly.   

There are some ways to block some of the SQL keywords in URL and form variables; this is not guaranteed to match, and may have some false positives, but will buy time to protect all queries. 

We set up some CF code on most public sites, set up in Application.cfm (runs at beginning of each request) to search for these SQL keywords in the URL and then abort the page.

 

Action taken for resolution and recovery:

The best permanent fix is for each and every query to be parameterized, so that no user-supplied text is passed to the database directly.   

In the meantime, block those SQL keywords either through CF code (we put on most public sites) or through rewrite rules. 

To repair the infected database, we are running a SQL script similar to the injected SQL to remove the appended HTML from those columns it was added to.

 

Further recommendations:

  • Have daily backups of all databases.
  • Regular backups of code.
  • Do a security audit on your site (TeraTech can do this)
  • Better CF regex filtering of url/form variables at application level
    • May block this set of attacks enough to buy time.
    • Not a replacement for parameterizing all queries.
  • Blocking on webserver level, using rewrite rules. We haven’t quite found a set of rules that work reliably, but will keep looking.
    • May block this set of attacks enough to buy time.
    • Not a replacement for parameterizing all queries.
    • open-source Ionics ISAPI rewrite filter http://www.codeplex.com/IIRF/
      • Filter requires a copy of the DLL and INI file in separate folder for each site, then point each site to that DLL
    • Helicon ISAPI Rewrite (commercial): http://www.isapirewrite.com/
    • Backup the rule configuration files
  • Optionally, look at an application firewall that can block certain types of requests. Rules are options. Not guaranteed to block everything, and can have false positives, but protects the whole server and individual sites, and is highly configurable. Again, not a replacement for parameterizing all queries.
    • Applicure dotDefender; $4000 for a server
      • http://www.applicure.com/
  • Each and every query should pass variables to database using CFQueryParam. Use QueryParam scanner (http://qpscanner.riaforge.org/) on source folders to find queries that might be missing.
  • Any code that uses URL or Form-supplied values to determine SQL columns or sort order, should check against a list of valid columns etc. Don’t use those values directly in the query without checking.
  • If you have cached queries that get user-entered values, CF before 8.01 doesn’t allow caching queries that use cfqueryparam. Consider other options, like caching the query without that WHERE clause and then doing a query on that query.
  • Validate form fields; check values of ID fields entered in URL before getting to query, then display nicer message to user and don’t need to send an error message.

 

Resources: 

CF-Talk discussions on the issue:

http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:57221

http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:57241

http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:310594

 

Good description on how this attack works:

http://www.coldfusionmuse.com/index.cfm/2008/7/18/Injection-Using-CAST-And-ASCII

 

on Ben Forta’s blog:

http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored

http://www.forta.com/blog/index.cfm/2008/7/22/For-Goodness-Sake-Use-CFQUERYPARAM-Already

http://www.forta.com/blog/index.cfm/2008/7/23/Hacker-Webzine-Recommends-Use-Of-CFQUERYPARAM

 

 

QueryParam scanner (search CF code for queries that are probably missing cfqueryparam):

http://qpscanner.riaforge.org/

 

HP Scrawlr: finds web pages vulnerable to SQL injection attacks on your own sites.

http://www.communities.hp.com/securitysoftware/blogs/spilabs/archive/2008/06/23/finding-sql-injection-with-scrawlr.aspx

SQL Reserved word or not, that is the question

Here is a problem that we had on a recent project that we were doing maintenance on. The code had some queries with a table called as "user". The code works fine on the production server but gave an error on our development server. The fix? Put brackets around the table name in all the queries.

eg

select name from [user]

instead of

select name from user


We had different versions of SQL Server on the two boxes and user is now a reserved word on the development box version.

We figure out what words are reserved using Pete Freitag's reserved word tool at:

http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=user

ColdFusion MX 7 & MySQL Connection on Windows & Linux

As you know ColdFusion & MySQL connection is a bit tricky. There is a Technote from Adobe. But I also want to give some simple tips.

You can check my simple tutorial via "http://www.howtoforge.com/coldfusion7_mysql4.1_connection".

SQL tricks - creative JOINs with legacy data

Have you ever needed to extract data from old tables and use it in a way the table designer never planned on? This seems to be an all-too-common experience.

Recently, I learned that I could parse a number out of a string field, cast it as an integer, and actually use that result in a JOIN (as if it were a foreign key) to another table's integer primary key. Here is the JOIN part of my query:

view plain print about
1...
2inner join listings as list
3on list.listing_id =
4CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer )

A few years ago, before I began to understand the power of SQL, I would have done something like this by hand in Excel. Details of the problem and solution are included below if you'd like to read on ...

[More]

BlogCFC was created by Raymond Camden. This blog is running version 5.9.8.012. Contact Blog Owner