SQL Injection: Modes of Attack, Defence, and Why It Matters

May 05 2014 05:17 PM | Stephen in Legacy Security Articles

- - - - - Tags: security sql injection hacking
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 injectionattacker 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 moreimportant 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.


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/homeba...dex.asp?story=1
Posted Image
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/homeba...dex.asp?story=4
Posted Image
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 validationstraight 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/homeba...dex.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/homeba...ex.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/homeba...sp?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/homeba...p?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 calleddatabase 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/homeba...=3 HAVING 1=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/homeba...up by s.sID%...
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.


0 Comments