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.