How to Prevent SQL Injection Attacks

Your company's web site does not have to be the next victim of a SQL injection breach. Here's how to protect against attack.

By Aaron Weiss | August 16, 2012

What do Sony Pictures, PBS, Microsoft, Yahoo, LinkedIn, and the CIA have in common? These organizations and their web sites have all been successfully breached using what has become the weapon of choice for hackers: SQL injection.

SQL, or the Structured Query Language, is the command-and-control language for relational databases such as Microsoft SQL Server, Oracle, and MySQL. In modern web development, these databases are often used on the back end of web applications and content management systems – meaning that both the content and behavior of many web sites is built on data in a database server.

A successful attack on the database that drives a website or web application can potentially give a hacker a broad range of powers, from modifying web site content ("defacing") to capturing sensitive information such as account credentials or internal business data.

The Danger of Unsanitized Input Data

Broadly put, a SQL injection attack sends malicious commands to the database by sneaking through unauthorized channels. By far the most common such channel is unsanitized input data.

How does this work? Consider a website login form, for example. A savvy hacker can try to compromise this form by submitting data that is intentionally malformed in an attempt to push a SQL command to the underlying database.

The vulnerability arises when a web application fails to "sanitize" the user input – i.e., filter out dangerous or non-conforming data – prior to handing it off to the database. Suppose, for example, that a hacker submits a login with the username dummy'. (Notice the single apostrophe.)

If the web site returns a database error when that username is submitted, then the hacker has just learned a piece of valuable information. The error message indicates that the backend code has literally included the user input into the syntax of a SQL query, because the single apostrope caused a syntax error in that query. Needless to say, this loophole reflects insecure coding and poor practice – but is also all too common.
A securely coded web site would have removed that dangerous apostrophe from the hacker's input – in fact, it would have sanitized the input by disallowing all characters except those that are specifically relevant to the data context. For example, a numeric input field (such as "age") should filter out any non-numeric input data before passing it to the SQL command.

But now the hacker has learned that a potential target is not santizing input data and proceeds to submit increasingly sophisticated code snippets into the input form. A smart hacker well-versed in SQL can peck away to reveal snippets of information – potentially revealing field labels and table names. Ultimately the hacker may even be able to dump data from the database or insert data. If the hacker manages to insert a new user account into the database, then all bets are off and the web site may be completely compromised.

Havij: No SQL Mastery Required

SQL is a complex language. It's not a trivial task to construct code snippets that can be injected into a query to successfully compromise a database. This type of hacking requires a solid understanding of the structure of typical SQL queries as well as in-depth knowledge of common website backend databases. So one would think that the number of hackers smart enough to succeed at this type of activity would be quite small. Why then are SQL injections the most common and successful web attack?

The answer is that human beings are very good at creating tools, especially tools that simplify the application of complex knowledge. After all, you don't need to be an automotive engineer to drive a car – or a computer scientist to operate a software program.

One popular "cheat sheet" for aspiring hackers is a list of so-called google dorks. These are Google search queries which are constructed to find web sites that conform to known characteristics. For example, a list of google dorks for SQL injection attacks can easily be found online. These queries are not attacks themselves — they help identify web sites that might be vulnerable to attacks because they use known database backends.

With a target in sights, any hacker with a mouse can fire up a copy of Havij. Developed by Iranian security professionals, Havij is, quite simply, a point-and-click tool for executing SQL injection attacks.

There mere existence of Havij can probably be credited with the widespread success of SQL injection. The app, for which many online tutorials can easily be found, does most of the heavy lifting. Point it at a potential target and Havij probes the site to determine what type of database is in use. Using that knowledge, Havij then builds queries to probe characteristics of the database. Requiring little to no SQL expertise from the end user, Havij can potentially extract fields, tables, and sometimes even full data dumps from a target.

It is important to understand that Havij can't compromise a web site that is well secured against SQL injection. But for those sites that are vulnerable, Havij puts a powerful attack arsenal – one that would otherwise be limited to experts – right into the hands of any "n00b" hacker who has the motivation to try.

Defending Against SQL Injection Attacks

The good news is that there actually is a lot that web site owners can do to defend against SQL injection attacks. Although there is no such thing as a 100 percent guarantee in network security, formidable obstacles can be placed in the path of SQL injection attempts.
1. Comprehensive data sanitization. Web sites must filter all user input. Ideally, user data should be filtered for context. For example, e-mail addresses should be filtered to allow only the characters allowed in an e-mail address, phone numbers should be filtered to allow only the characters allowed in a phone number, and so on.

2. Use a web application firewall. A popular example is the free, open source module ModSecurity which is available for Apache, Microsoft IIS, and nginx web servers. ModSecurity provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.

3. Limit database privileges by context. Create multiple database user accounts with the minimum levels of privilege for their usage environment. For example, the code behind a login page should query the database using an account limited only to the relevent credentials table. This way, a breach through this channel cannot be leveraged to compromise the entire database.

4. Avoid constructing SQL queries with user input. Even data sanitization routines can be flawed. Ideally, using SQL variable binding with prepared statements or stored procedures is much safer than constructing full queries.

Any one of these defenses significantly reduces the chances of a successful SQL injection attack. Implementing all four is a best practice that will provide an extremely high degree ofprotection. Despite its widespread use, your web site does not have to be SQL injection's next victim.