

	Redspin Research
	Redspin Presentations
	Redspin Videos
	Redspin Data Sheets
	Redspin White Papers
	Technical Resources
	Regulatory Resources
	Security Management Advisory

Redspin SMA Sign Up!

Sign yourself or a colleague up for our free Redspin Security Management Advisory monthly newsletter. We will not share your email address.

* = Required Information

Name:

* Email:

Redspin Security Management Advisory

Back To Redspin Security Management Advisory Headlines

	Volume 14 \| August 2009
SQL Injection

Let's assume for a moment that you have your firewall configuration dialed in impeccably, that your patch management server never rests, and that your state-of-the-art IDS lets you sleep peacefully at night, as it continuously identifies any irregularities from the network's accepted traffic patterns. Even your web-server contains no known vulnerabilities, and it is responsibly segmented from the internal network into a distinct security zone. Yet, there remains a critical risk lurking beneath the surface. Your network potentially stands to crumble if unfiltered input fields on your website allow an attacker to exploit the database-layer embedded within so many of today's most common web applications.

Are You At Risk?
The following web application security checklist may help auditors to determine if their organization's website is likely to be at risk. Any divergence between your organization's development process and secure coding best practices should be a major signal for concern:

Security Checklist for the Database Controller:

	Is there a database employed behind the website?
	Has this database been restricted by source IP only on the Internet or restricted to the internal network only?
	Is the database being run with default (administrative, full access) credentials or have users been created with specific access rights?
	Have unneeded permissions and stored procedures been disabled (xp..cmdshell)?

Security Checklist for the Website Developer:

	Is the website's code peer reviewed and developed carefully by security-conscious developers? (Is it internally developed one-off code from Google or open source code that has faced widespread scrutiny by security experts)?
	Can the developers provide the controls implemented which have addressed the OWASP Top 10 Web Vulnerabilities? (for example: "I have addressed the dangers of cross-site scripting by ensuring that special HTML characters are converted to their HTML representatives)
	Is there any filtering being performed on non-static parts of the website? Is this filtering being referenced from a single location/single function, with a well defined input and output which represents the single point of entry into the database? What type of filtering is being performed? (Parameterizing SQL,whitelisting, blacklisting, etc.)
	Do the developers know the difference between client-side (Javascript) and server-side input filtering? (Client-side can be disabled and is for user convenience only)
	Are error pages disclosing useful user information without disclosing sensitive information? Is there a function that prevents content that should never be displayed to the user from being put on the website? (SQL Errors, ASP Errors, Path names, Internal Error Numbers)
	When major site upgrades are performed, is the security and validity of the site tested by an independent party that is technically capable of critically reviewing the code for security best practices?

SQL Injection
SQL Injection refers to a set of methods and techniques designed to exploit an SQL database server that sits behind a web application. While most firewalls block all inbound traffic to the internal network, they typically allow traffic from the public internet to web applications through HTTP/HTTPS. There are a range of SQL Injection attack scenarios, all based around the insertion of simple characters into web-application input forms. Arbitrary commands can then be executed in the database application-layer, enabling access to sensitive data or administrative privileges on the database server. In recent years, SQL Injection has been the unseen methodology behind many high profile data breaches and site defacements.

Compromise by way of SQL Injection can entail harvesting of the entire back-end database (presumably full of confidential customer information), or even gaining administrative control of the internal network. Less severe gradients of attack may stop at defacement or the insertion of illicit code or spy-ware into the sites HTML content. What these gradients of consequences equate to, ultimately, is the loss of loyal customers and the irrecoverable damage to the reputation of your organization.

In a sampling of financial institution and corporate clients, Redspin found that the engineering team was capable of compromising around 30% of the websites that they looked at through SQLinjection. Other analysts have reported even more staggering numbers, with as many as 60% of sites vulnerable to SQL-injection. Relative to the context of buffer overflow attacks, SQL appears to be on the rise and to be a new paradigm, while buffer overflow attacks are on the decline, probably due again to the effectiveness of firewalls at filtering this type of attack. SQL character insertion is much more subtle and is not well detected by firewalls or automated scanners, making it a challenging class of vulnerabilities to properly mitigate.

During the course of Redspin's analysis, a number of multi-billion dollar banks hosting complex e-banking applications were found to be at risk. The most troubling dimension of the statistic, however, is the range of organizations hosting seemingly benign brochure ware content on their websites who were either completely unaware of the presence of SQL Injection or who did not understand the implications of the risk. Many of today's websites are, on the surface, little more than "digital postcards" which are passively viewed by the user, yet behind the site's static visual layout, a backend database is usually providing the dynamic content to the front-end web application. A basic example is a text-input field that allows viewers to search for relevant company press releases by a search string. These types of applications are thought to be benign because they are so simple, yet all it takes to be at risk to an SQL Injection vulnerability is a simple login page on your site that is used, for example, to store preferences for registered users, or even just a helpful stock-ticker lookup field tucked away in the corner of your site. Oftentimes, SQL Injection is found to be present on the most innocuous and seemingly benign input forms. Attackers realize this, and therefore target the unfiltered and forgotten input forms left on the site, whereas developers and database administrators remain complacent.

Redspin's research suggests that organizations sail along under the winds of a misplaced and false confidence in their expensive and sleek network devices. The big "fear" of external risk is diluted today by the high effectiveness of firewalls. Alarmingly, however, this also obscures a crucial fact and, in this case, the devil is in the details: Security has changed and web attacks now represent the "new prism" to effectively side-step even the most sophisticated of firewall configurations.

The quintessential example of initiating an SQL Injection attack is the single quotation character insertion. It is a basic litmus-test for webmasters to employ on their own sites in order to assess the effectiveness of the input filtering controls which should be in place. Although a simplistic example, this mode of character insertion is sometimes the first form of reconnaissance performed by an attacker, roughly similar by way of analogy to a port-scan in the context of a conventional attack. For example, the screenshot below would indicate to an attacker that input filtering controls have either not been deployed or have not been configured correctly.

Figure 1:
Testing for SQL Injection with a Single Quote:

      Microsoft SQL Native Client error '80040e14'
      Unclosed quotation mark after the character string "'.
      /password.html, line 23

The above error message is caused when the single quote submitted to the webpage is passed to a database as an invalid query, causing an error in the SQL language which is then returned to the attacker. While text forms are a common location of this attack, seemingly harmless functions, such as page layout and web logging functions, may also be sources of SQL injection, which must be identified if user input is accepted. In order to prevent this attack, the website must be more selective on accepting characters from the user. For example, a white list of approved characters, including letters and numbers is safe to pass to the database, but characters such as the single quote, are not. Presented below are three basic, but effective, examples of input filtering code supplements.

Approach 1 – Use Parametrized Queries (avoid 'string concatenation'):

Instead of:

function runCommand( customerID )
      SqlCommand c = connection.CreateCommand();
      c.CommandText = "SELECT * FROM Customers WHERE
      CustomerID = '"+ customerID+"'";
end function

Use:

function runCommand( customerID )
      SqlCommand command = connection.CreateCommand();
      command.CommandText = "SELECT * FROM Customers WHERE
      CustomerID = @CustomerID";
      command.Parameters.Add(
            new SqlParameter("@CustomerID",
            SqlDbType.Int)).Value = customerID;
end function

This prevents all SQL Injection.

Approach 2 – Escape Single Quotes (the classic attack character):

function escape( input )
input = replace(input, "'", "''")
escape = input
end function

Approach 3 – Reject Known Bad Input:

function validate_string( input )
      known_bad = array( "select", "insert", "update",
      "delete", "drop", "--", "'" )
      validate_string = true
      for i = lbound( known_bad ) to ubound( known_bad )
            if ( instr( 1, input, known_bad(i),
            vbtextcompare ) <> 0 )
then
               validate_string = false
               exit function
            end if
      next
end function

Approach 4 – Allow Only Good Input:

function validatepassword( input )
      good_password_chars =
"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"       "validatepassword = true
      for i = 1 to len( input )
            c = mid( input, i, 1 )
            if ( InStr( good_password_chars, c ) = 0 ) then
               validatepassword = false
               exit function
            end if
      next
end function

Organizations who want to react responsibly to the threat of SQL injection will be cognoscente in the first place as to what extent their site is database-driven, especially when they assume that it is a completely passive brochure-ware site. In terms of website content, functionality, and industry there are no limitations to who may be at risk. Those who assume that their sites are not database-driven, when they in-fact are, represent the most at-risk group. Security-conscious organizations should not take the evidently accepted attitude of "everyone else has SQL-injection present, its too difficult too fix on every page, so I won't bother" and should urgently attempt to educate themselves as to how input filtering and other techniques can greatly improve your site's security. SQL-injection demands more awareness and education than conventional vulnerabilities because SQL-injection is highly resistant to detection by automated vulnerability scanners and no unified approach to mitigation currently exists. Becoming educated as much as possible on the issue of web application vulnerabilities may very well save your network from falling because of an attack that could have otherwise been effectively blocked.

Quick Reference: Best and Worst Means of Mitigating SQL Injection

Parameterizing SQL Statements (This is the best method, and the code supplements provided in this paper are basic examples of this technique)
Whitelisting, by requiring different functions for different input, such as for sensitive data fields like social security numbers, addresses, comments, etc.
Blacklisting, by staying on top of the common attack characters employed by the attackers (this obviously begins with the single quote, but extends far beyond).
Error Output Filtering (aka: Blind SQL Injection)
Ignoring the risk altogether, and pretending it does not exist on the site. (The worst method)

Redspin in the News
Redspin Vice President of Engineering, Matt Marshall addresses SQL Injection in:

News Logos