What is SQL Injection? Understanding this Important Threat Vector

Written By: Nigel Stanley
Content Copyright © 2009 Bloor. All Rights Reserved.

Over the next few months I will be producing white papers and articles
that explore the more technical aspects of IT security in the context of
effective information

I will be exploring security issues that relate to the safe keeping of data and
how this can be achieved against a backdrop of what is a complex, disruptive
and interconnected world.

SQL Injection
In August 2009 3 people were charged with the theft of 130 million
credit card numbers from Heartland Payment Systems, 7-Eleven and a supermarket
chain called Hannaford Brothers. It was reportedly the biggest case of identity
theft in US history.

This theft was carried out using SQL injection techniques. What is SQL
injection and why should you be bothered about it?

We all log into websites on a daily basis, be it for shopping, banking
or accessing protected content. In most
cases usernames are stored in a relational database along with other user
details and related information.

The proliferation of databases created to support this growth in
websites with protected content has lead to many insecure and vulnerable sites,
hastily put on line with little thought to the underlying security

This gaping hole in the security of web sites is being aggressively
exploited by SQL injection attacks.

Even more worrying this attack can be carried out against fully patched
databases. It is not a problem with patches, more an issue with the way
databases work. This attack can be made against search pages, feedback forms, customer
comment forms and any other web site pages that rely on a database engine.

All databases can be vulnerable to this attack including MySQL, SQL
Server and Oracle. Despite its name SQL injection is not only specific to Microsoft
SQL Server.

Anatomy of a typical
SQL injection attack

Probably the most vulnerable page for a SQL injection attack would be
the one used to login to a site.

The database table that stores a user’s details would normally have at
least two columns—one for the username
and one for the password. The table would probably be called users or something very similar.

The database will parse the username and password typed into the logon
screen and convert it into a string of
SQL to send to the database, so the database engine receives a line of SQL
similar to this:

SELECT * FROM users WHERE username = ‘Chris Date' AND password = ‘userspassword'

This is a pretty standard SQL statement and would look more or less the
same whichever database you were using to store the user’s data.

Hackers are interested in SQL injection attacks as they can manipulate
the database by sending it some duff data.

Instead of giving a valid username they could type something like this:

SELECT * FROM users WHERE username = ‘ ‘ ' AND password = ‘ '

The hacker has supplied a ‘ character as the username and a blank as the

The database, being well behaved, would parse this query but return an
error message informing the user that they have made a mistake in the line of
code they have just typed in.

Using this chink in the database security armour the hacker will start
to dig around a bit more. The error messages returned from the database will
start to become increasingly more helpful, and begin to point out the structure
of the underlying tables in the database.

We can start to fool the database further by adding in other strings to
the username. For example if we type in ‘ or email=’345 as our username it will
be resolved by the query parser as:

SELECT * FROM users 
WHERE username = ‘ ‘or email='345' AND password = ‘ '

At this point the database may give an error if there is no such column
in the users database as email. On the other hand it may not give an error message
suggesting that there is a column called email after all.

All the hacker needs do is guess the email address for a legitimate user.
As most organisations have a standard email address structure it is very easy
to get an employee name and then use that as your user name in the SQL
injection attack.

As you can see it is scarily easy to build up a picture of a database
structure based on some simple fooling of the text entry screen and some
intelligent guesses as to the likely structure of a database.

How can SQL
injection be avoided?

It is possible to filter out single quotes, double quotes, slash,
backslash, semi colon and extended characters from user input strings, cookie
values and parameters from a URL. In addition numeric values can be converted
into integers before passing to the database.

Database administrators can take further measures in case they are still
attacked. For example the simple measure of deleting all of the extended stored
procedures that are not needed such as xp_sendmail and xp_cmdshell can
immediately start to secure the database.

SQL injection is not a new attack and is avoidable if you take some
basic security measures—whatever database you use.