Advanced SQL Injection In SQL Server
Applications
Chris Anley [chris@ngssoftware.com]
An NGSSoftware Insight Security Research (NISR) Publication
©2002 Next Generation Security Software Ltd
http://www.ngssoftware.com
Table of Contents
[Abstract] ............................................................................................................................ 3
[Introduction] ...................................................................................................................... 3
[Obtaining Information Using Error Messages] ................................................................. 7
[Leveraging Further Access]............................................................................................. 12
[xp_cmdshell] ............................................................................................................... 12
[xp_regread].................................................................................................................. 13
[Other Extended Stored Procedures] ............................................................................ 13
[Linked Servers]............................................................................................................ 14
[Custom extended stored procedures]........................................................................... 14
[Importing text files into tables] ................................................................................... 15
[Creating Text Files using BCP]................................................................................... 15
[ActiveX automation scripts in SQL Server]................................................................ 15
[Stored Procedures]........................................................................................................... 17
[Advanced SQL Injection]................................................................................................ 18
[Strings without quotes]................................................................................................ 18
[Second-Order SQL Injection]...................................................................................... 18
[Length Limits] ............................................................................................................. 20
[Audit Evasion]............................................................................................................. 21
[Defences] ......................................................................................................................... 21
[Input Validation].......................................................................................................... 21
[SQL Server Lockdown]............................................................................................... 23
[References] ...................................................................................................................... 24
Appendix A - 'SQLCrack' ................................................................................................. 25
(sqlcrack.sql)................................................................................................................. 25
Page 2
[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
Page 3
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:
<HTML>
<HEAD>
<TITLE>Login Page</TITLE>
</HEAD>
<BODY bgcolor='000000' text='cccccc'>
<FONT Face='tahoma' color='cccccc'>
<CENTER><H1>Login</H1>
<FORM action='process_login.asp' method=post>
<TABLE>
<TR><TD>Username:</TD><TD><INPUT type=text name=username size=100%
Page 4
width=100></INPUT></TD></TR>
<TR><TD>Password:</TD><TD><INPUT type=password name=password size=100%
width=100></INPUT></TD></TR>
</TABLE>
<INPUT type=submit value='Submit'> <INPUT type=reset value='Reset'>
</FORM>
</FONT>
</BODY>
</HTML>
This is the code for 'process_login.asp', which handles the actual login:
<HTML>
<BODY bgcolor='000000' text='ffffff'>
<FONT Face='tahoma' color='ffffff'>
<STYLE>
p { font-size=20pt ! important}
font { font-size=20pt ! important}
h1 { font-size=64pt ! important}
</STYLE>
<%@LANGUAGE = JScript %>
<%
function trace( str )
{
if( Request.form("debug") == "true" )
Response.write( str );
}
function Login( cn )
{
var username;
var password;
username = Request.form("username");
password = Request.form("password");
var rso = Server.CreateObject("ADODB.Recordset");
var sql = "select * from users where username = '" + username + "'
and password = '" + password + "'";
trace( "query: " + sql );
rso.open( sql, cn );
if (rso.EOF)
{
rso.close();
%>
Page 5
