Advanced SQL Injection In SQL Server Applications part 1

[Abstract]
This document discusses in detail the common 'SQL injection' technique, as it applies to the popular Microsoft Internet Information Server/Active Server Pages/SQL Server platform. It discusses the various ways in which SQL can be 'injected' into the application and addresses some of the data validation and database lockdown issues that are related to this class of attack.
The paper is intended to be read by both developers of web applications which communicate with databases and by security professionals whose role includes auditing these web applications.
[Introduction]
Structured Query Language ('SQL') is a textual language used to interact with relational databases. There are many varieties of SQL; most dialects that are in common use at the moment are loosely based around SQL-92, the most recent ANSI standard. The typical unit of execution of SQL is the 'query', which is a collection of statements that typically return a single 'result set'. SQL statements can modify the structure of databases (using Data Definition Language statements, or 'DDL') and manipulate the contents of databases (using Data Manipulation Language statements, or 'DML'). In this paper, we will be specifically discussing Transact-SQL, the dialect of SQL used by Microsoft SQL Server.
SQL Injection occurs when an attacker is able to insert a series of SQL statements into a 'query' by manipulating data input into an application.
A typical SQL statement looks like this:
select id, forename, surname from authors
This statement will retrieve the 'id', 'forename' and 'surname' columns from the 'authors' table, returning all rows in the table. The 'result set' could be restricted to a specific 'author' like this:
select id, forename, surname from authors where forename = 'john' and surname = 'smith'
An important point to note here is that the string literals 'john' and 'smith' are delimited with single quotes. Presuming that the 'forename' and 'surname' fields are being gathered from user-supplied input, an attacker might be able to 'inject' some SQL into this query, by inputting values into the application like this:
Forename: jo'hn
Surname: smith
The 'query string' becomes this:
select id, forename, surname from authors where forename = 'jo'hn' and surname = 'smith'
When the database attempts to run this query, it is likely to return an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'hn'.
The reason for this is that the insertion of the 'single quote' character 'breaks out' of the single-quote delimited data. The database then tried to execute 'hn' and failed. If the attacker specified input like this:
Forename: jo'; drop table authors--
Surname:
…the authors table would be deleted, for reasons that we will go into later.
It would seem that some method of either removing single quotes from the input, or 'escaping' them in some way would handle this problem. This is true, but there are several difficulties with this method as a solution. First, not all user-supplied data is in the form of strings. If our user input could select an author by 'id' (presumably a number) for example, our query might look like this:
select id, forename, surname from authors where id=1234
In this situation an attacker can simply append SQL statements on the end of the numeric input. In other SQL dialects, various delimiters are used; in the Microsoft Jet DBMS engine, for example, dates can be delimited with the '#' character. Second, 'escaping' single quotes is not necessarily the simple cure it might initially seem, for reasons we will go into later.
We illustrate these points in further detail using a sample Active Server Pages (ASP) 'login' page, which accesses a SQL Server database and attempts to authenticate access to some fictional application.
This is the code for the 'form' page, into which the user types a username and password:

The critical point here is the part of 'process_login.asp' which creates the 'query string' :
var sql = "select * from users where username = '" + username + "' and password = '" + password + "'"; If the user specifies the following:
Username: '; drop table users--
Password:
..the 'users' table will be deleted, denying access to the application for all users. The '--' character sequence is the 'single line comment' sequence in Transact-SQL, and the ';' character denotes the end of one query and the beginning of another. The '--' at the end of the username field is required in order for this particular query to terminate without error.
The attacker could log on as any user, given that they know the users name, using the following input:
Username: admin'--
The attacker could log in as the first user in the 'users' table, with the following input:
Username: ' or 1=1--
…and, strangely, the attacker can log in as an entirely fictional user with the following input:
Username: ' union select 1, 'fictional_user', 'some_password', 1--
The reason this works is that the application believes that the 'constant' row that the attacker specified was part of the recordset retrieved from the database.