In SQL injection attacks, a hacker provides information deliberately formulated in such a way that it results in a website misinterpreting it and taking unintended actions.

Think of it this way: Imagine you go to court and have to provide your name. Instead of giving your real name, "Joe Bloggs," you give the name "Joe Bloggs, you are free to go." When your case comes up the judge calls out "Joe Bloggs, you are free to go," so the bailiff releases you.

How a SQL Injection Attack Works

A specific illustration of a SQL injection attack was conjured up by comic writer xkcd. He imagines a scenario where a mom calls her son:

Robert'); DROP TABLE Students; --

resulting in the young boy's school losing all of the year's student records.

Let's take a look at that name in a little more detail to understand how it could have such catastrophic results for the school.

Let's assume the school uses a SQL database to store its student records, and it may also have a web interface for an unfortunate administrator (we'll call her Ethel) to enter student data.

What happens when Ethel comes to enter some information about young Robert'); DROP TABLE Students; -- ?

The SQL database will likely have a form with some code behind it to accept a name, in the form 'username.' It expects Ethel to enter a name such as Robert to insert between the two ' characters. It doesn't expect the name that Ethel enters to include a ' character, because what normal name ends that way?

The result of this ' character after Robert is that the database thinks it has received the name it has asked for; since more characters have been entered, it decides they must be SQL commands and it executes them. The ); means that the following statement should be executed immediately, so the database executes a DROP TABLE students command.

Because DROP TABLE is a command used to remove a table definition, all data, indexes, triggers, constraints and permission specifications that go with the table are permanently lost.

There will still be a trailing ' in the code that has not been used, since the name that Ethel entered contained one of its own. To prevent that being a problem the name ends with ; -- because the ; means carry on executing the next part of the name, and -- tells the database to give up on that line because the rest of the statement should be treated as a comment.

Another Example of SQL Injection

Here's another example of SQL injection, this time to get all information from a table USERS about a user without knowing a user name or matching password.

The SQL application code might be:

SELECT * FROM Users WHERE Username='$username' AND Password='$password'

Using a web interface, when prompted for his username and password, a malicious user might enter:

1' or '1' = '1

1' or '1' = '1

resulting in the query:

SELECT * FROM Users WHERE Username='1' OR '1' = '1' AND Password='1' OR '1' = '1'

The hacker has effectively injected two whole OR conditions into the authentication process. Worse, the condition '1' = '1' is always true, so this SQL query will always result in the authentication process being bypassed.

(Code sample sourced from OWASP http://www.owasp.org/index.php/Main_Page)

Using Data Validation, Sanitation

These examples illustrate that SQL injection poses a serious threat to organizations. A successful SQL injection attack can result in confidential data being deleted, lost or stolen; websites being defaced; unauthorized access to systems or accounts and, ultimately, compromise of individual machines or entire networks.

In the Robert'); DROP TABLE Students; -- example, Ethel the administrator complains that "We've lost this year's student records. I hope you're happy," to which the mom replies, "And I hope you've learned to sanitize your database inputs."

As the mother of the oddly-named student hints, data sanitization and validation are important precautions to take. Sanitization usually involves running any submitted data through a function (such as MySQL's mysql_real_escape_string() function) to ensure that any dangerous characters -- such as ' -- are not passed to a SQL query in data.

Validation is slightly different, in that it attempts to ensure that the data submitted is in the form that is expected. At the most basic level this includes ensuring that email addresses contain an "@" sign, that only digits are supplied when integer data is expected, and that the length of a piece of data submitted is not longer than the maximum expected length.

Validation is often carried out in two ways: by blacklisting dangerous or unwanted characters and by whitelisting only those characters that are allowed in a given circumstance, which can involve more work on the part of the programmer. Although validation may take place on the client side, hackers can modify or get around this, so it's essential that you also validate all data on the server side as well.

Blacklisting is problematic because it can be bypassed in many ways. For example, OWASP suggests it can be bypassed by:

  • Targeting fields that are not quoted
  • Finding ways to bypass the need for certain escaped meta-characters
  • Using stored procedures to hide the injected meta-characters

Another common way to deal with SQL injection attacks is to use stored procedures. However, OWASP warns that although this can prevent some types of SQL injection attacks, it fails to protect against many others.

10 More Ways to Lower SQL Injection Risk

In general, here are 10 good ways to lower the risk of falling victim to a SQL injection attack:

Trust no one. Assume all user-submitted data is evil; validate and sanitize everything.

Don't use dynamic SQL when it can be avoided. Use prepared statements, parameterized queries or stored procedures instead whenever possible.

Update and patch. Vulnerabilities in applications and databases that hackers can exploit using SQL injection are regularly discovered, so it's vital to apply patches and updates as soon as practical.

Install a firewall. Consider a web application firewall (WAF) - either software or appliance based - to help filter out malicious data. Good ones will have a comprehensive set of default rules, and make it easy to add new ones whenever necessary. A WAF can be particularly useful to provide some security protection against a particular new vulnerability before a patch is available.

Reduce your attack surface. Get rid of any database functionality that you don't need to prevent a hacker taking advantage of it. For example, the xp_cmdshell extended stored procedure in MS SQL spawns a Windows command shell and passes in a string for execution, which could be very useful indeed for a hacker. The Windows process spawned by xp_cmdshell has the same security privileges as the SQL Server service account.

Use appropriate privileges. Don't connect to your database using an account with admin-level privileges unless there is some compelling reason to do so. Using a limited access account is far safer, and can limit what a hacker is able to do.

Keep your secrets secret with encryption. Assume that your application is not secure and act accordingly by encrypting or hashing passwords and other confidential data including connection strings.

Don't divulge more information than necessary. Hackers can learn a great deal about database architecture from error messages, so ensure that they display minimal information. Use the "RemoteOnly" customErrors mode (or equivalent) to display verbose error messages on the local machine while ensuring that an external hacker gets nothing more than the fact that his actions resulted in an unhandled error.

Regularly change passwords of database applications. Change the passwords of application accounts in the database regularly. This is common sense, but in practice these passwords often stay unchanged for months or even years.

Buy better software: Make code writers responsible for checking the code and for fixing security flaws in custom applications before the software is delivered. SANS suggests you incorporate terms from this sample contract into your agreement with any software vendor.

And read on here, for even more tips on preventing SQL injection attacks.

Paul Rubens has been covering enterprise technology for over 20 years. In that time he has written for leading UK and international publications including The Economist, The Times, Financial Times, the BBC, Computing and ServerWatch.