Difference between revisions of "Testing for MS Access"

Revision as of 13:49, 20 August 2008 (view source) S4tan (talk | contribs) ← Older edit		Latest revision as of 16:03, 31 July 2014 (view source) Andrew Muller (talk | contribs)
(46 intermediate revisions by 8 users not shown)
Line 1:		Line 1:
−	== Short Description of the Issue ==	+	{{Template:OWASP Testing Guide v4}}
−	In this paragraph we describe how to exploit sql injection vulnerabilties when the
−	backend database is MS Access. In particular we focus on how to exploit blind sql injection
−	as this kind of vulnerabilities are more frequent.
−	After an initial introduction on which are the typical functions
−	that are useful to exploit a sql injection vulnerability,
−	we will introduce a clever  methodology to exploit blind sql injection.
−	== Black Box testing and example ==	+	== Summary ==
		+	As explained in the generic [[SQL injection]] section, SQL injection vulnerabilities occur whenever user-supplied input is used during the construction of a SQL query without being adequately constrained or sanitized. This class of vulnerabilities allows an attacker to execute SQL code under the privileges of the user that is used to connect to the database. In this section, relevant SQL injection techniques that utilize specific features of [http://en.wikipedia.org/wiki/Microsoft_Access Microsoft Access] will be discussed.
−	=== Standard Test ===
−	First of all, we start showing a typical example of SQL error that we can encounter when
−	we execute our test:
−	<nowiki>	+	== How to Test ==
−	Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft JET Database Engine<br/><b>Description:</b>
−	</nowiki>
−	If we get this error then it's reasonable to think that we are testing an application with a MS Access Database as backend.	+	=== Fingerprinting ===
−	We have to say that, unfortunately, we start soon with a bad news since MS Access doesn't support any comment character in the sql query,	+	Fingerprinting the specific database technology while testing SQL-powered application is the first step to properly asses potential vulnerabilities. A common approach involves injecting standard SQL injection attack patterns (e.g. single quote, double quote, ...) in order to trigger database exceptions. Assuming that the application does not handle exceptions with custom pages, it is possible to fingerprint the underline DBMS by observing error messages.
−	so we can't use the trick of inserting the chars /* or -- or # to truncate the query. On the other hand, we can fortunately  bypass this limit with the NULL character. If we insert the char %00 at some place in
−	the query, all the remaining characters after the NULL are not considerated. That happens
−	because at the low level the strings are considerated terminated with the NULL char.
−	However the NULL character can sometimes cause troubles. If we try every char in the ASCII
−	charset we notice that there is another value that can be used in order to truncate the query.
−	The character is 0x16 (%16 in url encoded format) or 22 in decimal. So if we have the following query:
−	<nowiki>
−	SELECT [username],[password] FROM users WHERE [username]='$myUsername' AND [password]='$myPassword'
−	</nowiki>
−	we can truncate the query with the following two urls:	+	Depending on the specific web technology used, MS Access driven applications will respond with one of the following errors:
−	<nowiki>	+	Fatal error: Uncaught exception 'com_exception' with message Source: Microsoft JET Database Engine
−	http://www.example.com/index.asp?user=admin'%00&pass=foo
−	http://www.example.com/index.asp?user=admin'%16&pass=foo
−	</nowiki>
−	==== Attributes enumeration ====	+	or
−	In order to enumerate the attributes of a query we can use the same method used for the Database
−	MS SQL Server. In short, we can obtain the name of the attributes by error messages. For example
−	if we know the existence of a parameter because we got it by an error message due to the
−	' character, we can also know the name of the remaining attributes with the following query:
−	<nowiki>	+	Microsoft JET Database Engine error '80040e14'
−	' GROUP BY Id%00
−	</nowiki>
−	in the error message we can notice that the name of the next attribute is shown. We iterate the	+	or
−	method until we obtain the name of all the attributes. If we don't know the name of at least
−	one attribute, we can insert a fictitious column name, and just like by magic we obtain the name of
−	the first attribute.
−	==== Obtaining Database Scheme ====	+	Microsoft Office Access Database Engine
−	In MS Access exist various table that can be used to obtain the name of the table of a
−	particular database. In the default configuration this table is not accessible, however it's
−	worth a try. The names of the table are:
−	# MSysObjects
−	# MSysACEs
−	# MSysAccessXML
−	For example if a union sql injection vulnerability exists, you can use the following query:	+	In all cases, we have a confirmation that we're testing an application using MS Access database.
−	<nowiki>
−	' UNION SELECT Name FROM MSysObjects WHERE Type = 1%00
−	</nowiki>
−	These are the main steps that you can use to exploit a sql injection vulnerability on	+	=== Basic Testing ===
−	MS Access. There are also some functions that can be useful to exploit custom
−	queries. Some of these functions are:
−	# ASC: Obtain the ascii value of a character passed as input	+	Unfortunately, MS Access doesn't support typical operators that are traditionally used during SQL injection testing, including:
−	# CHR: Obtain the character of the ascii value passed as input
−	# LEN: Return the length of the string passed as parameter
−	# IIF: Is the IF construct, for example the following statement IIF(1=1, 'a', 'b') return 'a'
−	# MID: This function permits to extract substring, for example the following statement mid('abc',1,1)
−	return 'a'
−	# TOP: This function permits to specify the maximum number of results that the query should return from the top. For example
−	# TOP 1 will return only 1 row.
−	# LAST: This function is used to select only the last row of a set of rows. For example the following query
−	SELECT last(*) FROM users will return only the last row of the result.
−	Some of these functions will be used to exploit a blind sql injection as we see in the next	+	* No comments characters
−	paragraph. For other functions please refer to References.	+	* No stacked queries
		+	* No LIMIT operator
		+	* No SLEEP or BENCHMARK alike operators
		+	* and many others
−	=== Blind sql injection testing ===
−	Blind sql injection vulnerabilities are by no mean the most frequent type of vulnerability
−	that you will find. Generally you find a sql injection in a parameter where no union
−	query is possible. Also, usually there is no chance to execute shell command or to read/write
−	a file. All you can do is infer the result of your query. For our test we take the
−	following example:
−	<nowiki>	+	Nevertheless, it is possible to emulate those functions by combining multiple operators or by using alternative techniques. As mentioned, it is not possible to use the trick of inserting the characters <code>/*</code>, <code>--</code> or <code>#</code> in order to truncate the query. However, we can fortunately bypass this limitation by injecting a 'null' character. Using a null byte <code>%00</code> within a SQL query results in MS Access ignoring all remaining characters. This can be explained by considering that all strings are NULL terminated in the internal representation used by the database. It is worth mentioning that the 'null' character can sometimes cause troubles too as it may truncate strings at the web server level. In those situations, we can however employ another character: 0x16 (%16 in URL encoded format).
−	http://www.example.com/index.asp?myId=[sql]
−	</nowiki>
−	where the id parameter is used in the following query:
−	<nowiki>	+	Considering the following query:
−	SELECT * FROM orders WHERE [id]=$myId
−	</nowiki>
−	For our test we will consider the myId parameter vulnerable to blind sqli vulnerability.	+	SELECT [username],[password] FROM users WHERE [username]='$myUsername' AND [password]='$myPassword'
−	We want to extract the content of the table users, in particular of the column
−	username (we have already seen how to obtain the name of the attributes thanks
−	to the error messages and other tecniques). It is supposed that the reader alreday knowns the theory behind
−	the blind sql injection attack, so we go straight to show some example. A typical query that
−	can be used to infer the first character of the username of the 10th rows is:
−	<nowiki>	+	We can truncate the query with the following two URLs:
−	http://www.example.com/index.asp?id=IIF((select%20mid(last(username),1,1)%20
−	from%20(select%20top%2010%20username%20from%20users))='a',0,'ko')
−	</nowiki>
−	If the first character is 'a', this query will return a 0 (a "true response"), otherwise a	+	http://www.example.com/page.asp?user=admin'%00&pass=foo
−	'ko' string. Now we will explain why we have used this particular query.	+	http://www.example.com/page.app?user=admin'%16&pass=foo
−	The first thing to point out is that with the functions IFF, MID and LAST we extract the first
−	character of the username of the selected row. Unfortunately, the original query returns a set of records
−	and not only one record, so we can't use this methodology directly. We must first select only one row.
−	We can use the TOP function, but it only works with the first row. To select the other
−	queries we must use a trick. We want to infer the username of the row number 10.
−	First we use the TOP function to select the first ten rows with the query:
−	<nowiki>	+	The <code>LIMIT</code> operator is not implemented in MS Access, however it is possible to limit the number of results by using the <code>TOP</code> or <code>LAST</code> operators instead.
−	SELECT TOP 10 username FROM users
−	</nowiki>
−	Then we extract from this set the last row with the function LAST. Once we have only one row and	+	http://www.example.com/page.app?id=2'+UNION+SELECT+TOP+3+name+FROM+appsTable%00
−	exactly the row that we want, we can use the IFF, MID and LAST function to infer the value
−	of the username.
−	It may be interesting to notice the use of the IFF. In our example we use IFF to return a number or a string.
−	With
−	this trick we can distinguish when we have a true response or not. This is because id is of numeric type,
−	so if we compare it with a string we obtain a sql error, otherwise with the 0 value we have
−	no errors. Of course if the parameter was of type string we can use different values. For example
−	we can have the following query:
−	<nowiki>	+	By combining both operators, it is possible to select specific results. String concatenation is possible by using <code>& (%26)</code> and <code>+ (%2b)</code> characters.
−	http://www.example.com/index.asp?id=inexistenValueHere'%20or%20'a'=IIF((select%20mid(last(username),1,1)%20from%20(select%20top%2010%20username%20from%20users))='a','a','b')%00
−	</nowiki>
−	that returns a query that is always true if the first character is 'a' or a query that is always false in the other case (assuming that in the users table there is no id with value 'inexistenValueHere')
−	Thanks to this metodology we can infer the value of the username. To understand when we have	+	There are also many other functions that can be used while testing SQL injection, including but not limited to:
−	obtained the complete value we have two choices:	+	* ASC: Obtain the ASCII value of a character passed as input
		+	* CHR: Obtain the character of the ASCII value passed as input
		+	* LEN: Return the length of the string passed as parameter
		+	* IIF: Is the IF construct, for example the following statement IIF(1=1, 'a', 'b') return 'a'
		+	* MID: This function allows you to extract substring, for example the following statement mid('abc',1,1) return 'a'
		+	* TOP: This function allows you to specify the maximum number of results that the query should return from the top. For example TOP 1 will return only 1 row.
		+	* LAST: This function is used to select only the last row of a set of rows. For example the following query SELECT last(*) FROM users will return only the last row of the result.
−	# We try all the printable values, when no one is valid then we have the complete value.
−	# We can infer the length of the value (if it's a string value we can use the LEN function) and stop when we have found all the characters.
−	== Tricks ==	+	Some of these operators are essential to exploit blind SQL injections. For other advanced operators, please refer to the documents in the references.
−	Sometimes we are blocked by some filtering function, here we see some tricks to bypass these filters.
−	=== Alternative Delimiter ===
−	Some filter strips away the space from the input string. We can bypass this filter using
−	the following values as delimiter instead of the white space:
−	<nowiki>	+	==== Attributes Enumeration ====
−	9
−	a
−	c
−	d
−	20
−	2b
−	2d
−	3d
−	</nowiki>
−	For example we can execute the following query:	+	In order to enumerate the column of a database table, it is possible to use a common error-based technique.
		+	In short, we can obtain the attributes name by analyzing error messages and repeating the query with different selectors. For example,
		+	assuming that we know the existence of a column, we can also obtain the name of the remaining attributes with the following query:
−	<nowiki>	+	' GROUP BY Id%00
−	http://www.example.com/index.asp?username=foo%27%09or%09%271%27%09=%09%271	+
−	</nowiki>	+
		+	In the error message received, it is possible to observe the name of the next column. At this point, we can iterate the
		+	method until we obtain the name of all attributes. If we don't know the name of the first attribute, we can still insert a fictitious column name and obtain the name of the first attribute within the error message.
		+
		+
		+	==== Obtaining Database Schema ====
		+
		+	Various system tables exist by default in MS Access that can be potentially used to obtain table names and columns. Unfortunately, in the default configuration of recent MS Access database releases, these tables are not accessible. Nevertheless, it is always worth trying:
		+
		+	* MSysObjects
		+	* MSysACEs
		+	* MSysAccessXML
		+
		+
		+	For example, if a union SQL injection vulnerability exists, you can use the following query:
		+
		+	' UNION SELECT Name FROM MSysObjects WHERE Type = 1%00
		+
		+
		+	Alternatively, it is always possible to bruteforce the database schema by using a standard wordlist (e.g. [http://code.google.com/p/fuzzdb/ FuzzDb]).
		+
		+
		+	In some cases, developers or system administrators do not realize that including the actual ''.mdb'' file within the application webroot can allow to download the entire database. Database filenames can be inferred with the following query:
		+
		+	http://www.example.com/page.app?id=1'+UNION+SELECT+1+FROM+name.table%00
		+
		+
		+	where <code>name</code> is the ''.mdb'' filename and <code>table</code> is a valid database table.
		+	In case of password protected databases, multiple software utilities can be used to crack the password. Please refer to the references.
		+
		+
		+	=== Blind SQL Injection Testing ===
		+
		+	[[Blind SQL Injection]] vulnerabilities are by no means the most easily exploitable SQL injections while testing real-life applications. In case of recent versions of MS Access, it is also not feasible to execute shell commands or read/write arbitrary files.
		+
		+
		+	In case of blind SQL injections, the attacker can only infer the result of the query by evaluating time differences or application responses. It is supposed that the reader already knows the theory behind blind SQL injection attacks, as the remaining part of this section will focus on MS Access specific details.
		+
		+
		+	The following example is used:
		+
		+	http://www.example.com/index.php?myId=[sql]
		+
		+	where the id parameter is used within the following query:
		+
		+	SELECT * FROM orders WHERE [id]=$myId
		+
		+
		+	Let's consider the <code>myId</code> parameter vulnerable to blind SQL injection. As an attacker, we want to extract the content of column 'username' in the table 'users', assuming that we have already disclosed the database schema.
		+
		+
		+	A typical query that can be used to infer the first character of the username of the 10th rows is:
		+
		+	http://www.example.com/index.php?id=IIF((select%20MID(LAST(username),1,1)%20from%20(select%20TOP%2010%20username%20from%20users))='a',0,'no')
		+
		+	If the first character is 'a', the query will return 0 or otherwise the string 'no'.
		+
		+
		+	By using a combination of the IFF, MID, LAST and TOP functions, it is possible to extract the first character of the username on a specifically selected row. As the inner query returns a set of records, and not just one, it is not possible to use it directly. Fortunately, we can combine multiple functions to extract a specific string.
		+
		+
		+	Let's assume that we want to retrieve the username of the 10th row. First, we can use the TOP function to select the first ten rows using the following query:
		+
		+	SELECT TOP 10 username FROM users
		+
		+
		+	Then, using this subset, we can extract the last row by using the LAST function. Once we have only one row and exactly the row containing our string, we can use the IFF, MID and LAST functions to infer the actual value of the username. In our example, we employ IFF to return a number or a string. Using this trick, we can distinguish whether we have a true response or not, by observing application error responses. As <code>id</code> is numeric, the comparison with a string results in a SQL error that can be potentially leaked by <code>500 Internal Server Error pages</code>. Otherwise, a standard <code>200 OK</code> page will be likely returned.
		+
		+
		+	For example, we can have the following query:
		+
		+	http://www.example.com/index.php?id='%20AND%201=0%20OR%20'a'=IIF((select%20MID(LAST(username),1,1)%20from%20(select%20TOP%2010%20username%20from%20users))='a','a','b')%00
		+
		+	that is TRUE if the first character is 'a' or false otherwise.
		+
		+
		+	As mentioned, this method allows to infer the value of arbitrary strings within the database:
		+
		+	# By trying all printable values, until we find a match
		+	# By inferring the length of the string using the LEN function, or by simply stopping after we have found all characters
		+
		+
		+	Time-based blind SQL injections are also possible by abusing [http://technet.microsoft.com/it-it/library/cc512676%28en-us%29.aspx heavy queries].
−	to bypass a hypothetical login form.
	== References ==		== References ==
−	'''Whitepapers'''<br>	+
		+	* http://nibblesec.org/files/MSAccessSQLi/MSAccessSQLi.html
		+	* http://packetstormsecurity.com/files/65967/Access-Through-Access.pdf.html
		+	* http://seclists.org/pen-test/2003/May/74
	* http://www.techonthenet.com/access/functions/index_alpha.php		* http://www.techonthenet.com/access/functions/index_alpha.php
−	* http://www.webapptest.org/ms-access-sql-injection-cheat-sheet-IT.html	+	* http://en.wikipedia.org/wiki/Microsoft_Access

---

Latest revision as of 16:03, 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

As explained in the generic SQL injection section, SQL injection vulnerabilities occur whenever user-supplied input is used during the construction of a SQL query without being adequately constrained or sanitized. This class of vulnerabilities allows an attacker to execute SQL code under the privileges of the user that is used to connect to the database. In this section, relevant SQL injection techniques that utilize specific features of Microsoft Access will be discussed.

How to Test

Fingerprinting

Fingerprinting the specific database technology while testing SQL-powered application is the first step to properly asses potential vulnerabilities. A common approach involves injecting standard SQL injection attack patterns (e.g. single quote, double quote, ...) in order to trigger database exceptions. Assuming that the application does not handle exceptions with custom pages, it is possible to fingerprint the underline DBMS by observing error messages.

Depending on the specific web technology used, MS Access driven applications will respond with one of the following errors:

Fatal error: Uncaught exception 'com_exception' with message Source: Microsoft JET Database Engine

or

Microsoft JET Database Engine error '80040e14'

or

Microsoft Office Access Database Engine

In all cases, we have a confirmation that we're testing an application using MS Access database.

Basic Testing

Unfortunately, MS Access doesn't support typical operators that are traditionally used during SQL injection testing, including:

• No comments characters
• No stacked queries
• No LIMIT operator
• No SLEEP or BENCHMARK alike operators
• and many others

Nevertheless, it is possible to emulate those functions by combining multiple operators or by using alternative techniques. As mentioned, it is not possible to use the trick of inserting the characters /*, -- or # in order to truncate the query. However, we can fortunately bypass this limitation by injecting a 'null' character. Using a null byte %00 within a SQL query results in MS Access ignoring all remaining characters. This can be explained by considering that all strings are NULL terminated in the internal representation used by the database. It is worth mentioning that the 'null' character can sometimes cause troubles too as it may truncate strings at the web server level. In those situations, we can however employ another character: 0x16 (%16 in URL encoded format).

Considering the following query:

SELECT [username],[password] FROM users WHERE [username]='$myUsername' AND [password]='$myPassword'

We can truncate the query with the following two URLs:

http://www.example.com/page.asp?user=admin'%00&pass=foo
http://www.example.com/page.app?user=admin'%16&pass=foo

The LIMIT operator is not implemented in MS Access, however it is possible to limit the number of results by using the TOP or LAST operators instead.

http://www.example.com/page.app?id=2'+UNION+SELECT+TOP+3+name+FROM+appsTable%00

By combining both operators, it is possible to select specific results. String concatenation is possible by using & (%26) and + (%2b) characters.

There are also many other functions that can be used while testing SQL injection, including but not limited to:

• ASC: Obtain the ASCII value of a character passed as input
• CHR: Obtain the character of the ASCII value passed as input
• LEN: Return the length of the string passed as parameter
• IIF: Is the IF construct, for example the following statement IIF(1=1, 'a', 'b') return 'a'
• MID: This function allows you to extract substring, for example the following statement mid('abc',1,1) return 'a'
• TOP: This function allows you to specify the maximum number of results that the query should return from the top. For example TOP 1 will return only 1 row.
• LAST: This function is used to select only the last row of a set of rows. For example the following query SELECT last(*) FROM users will return only the last row of the result.

Some of these operators are essential to exploit blind SQL injections. For other advanced operators, please refer to the documents in the references.

Attributes Enumeration

In order to enumerate the column of a database table, it is possible to use a common error-based technique. In short, we can obtain the attributes name by analyzing error messages and repeating the query with different selectors. For example, assuming that we know the existence of a column, we can also obtain the name of the remaining attributes with the following query:

' GROUP BY Id%00

In the error message received, it is possible to observe the name of the next column. At this point, we can iterate the method until we obtain the name of all attributes. If we don't know the name of the first attribute, we can still insert a fictitious column name and obtain the name of the first attribute within the error message.

Obtaining Database Schema

Various system tables exist by default in MS Access that can be potentially used to obtain table names and columns. Unfortunately, in the default configuration of recent MS Access database releases, these tables are not accessible. Nevertheless, it is always worth trying:

• MSysObjects
• MSysACEs
• MSysAccessXML

For example, if a union SQL injection vulnerability exists, you can use the following query:

' UNION SELECT Name FROM MSysObjects WHERE Type = 1%00

Alternatively, it is always possible to bruteforce the database schema by using a standard wordlist (e.g. FuzzDb).

In some cases, developers or system administrators do not realize that including the actual .mdb file within the application webroot can allow to download the entire database. Database filenames can be inferred with the following query:

http://www.example.com/page.app?id=1'+UNION+SELECT+1+FROM+name.table%00

where name is the .mdb filename and table is a valid database table. In case of password protected databases, multiple software utilities can be used to crack the password. Please refer to the references.

Blind SQL Injection Testing

Blind SQL Injection vulnerabilities are by no means the most easily exploitable SQL injections while testing real-life applications. In case of recent versions of MS Access, it is also not feasible to execute shell commands or read/write arbitrary files.

In case of blind SQL injections, the attacker can only infer the result of the query by evaluating time differences or application responses. It is supposed that the reader already knows the theory behind blind SQL injection attacks, as the remaining part of this section will focus on MS Access specific details.

The following example is used:

http://www.example.com/index.php?myId=[sql]

where the id parameter is used within the following query:

SELECT * FROM orders WHERE [id]=$myId

Let's consider the myId parameter vulnerable to blind SQL injection. As an attacker, we want to extract the content of column 'username' in the table 'users', assuming that we have already disclosed the database schema.

A typical query that can be used to infer the first character of the username of the 10th rows is:

http://www.example.com/index.php?id=IIF((select%20MID(LAST(username),1,1)%20from%20(select%20TOP%2010%20username%20from%20users))='a',0,'no') 

If the first character is 'a', the query will return 0 or otherwise the string 'no'.

By using a combination of the IFF, MID, LAST and TOP functions, it is possible to extract the first character of the username on a specifically selected row. As the inner query returns a set of records, and not just one, it is not possible to use it directly. Fortunately, we can combine multiple functions to extract a specific string.

Let's assume that we want to retrieve the username of the 10th row. First, we can use the TOP function to select the first ten rows using the following query:

SELECT TOP 10 username FROM users

Then, using this subset, we can extract the last row by using the LAST function. Once we have only one row and exactly the row containing our string, we can use the IFF, MID and LAST functions to infer the actual value of the username. In our example, we employ IFF to return a number or a string. Using this trick, we can distinguish whether we have a true response or not, by observing application error responses. As id is numeric, the comparison with a string results in a SQL error that can be potentially leaked by 500 Internal Server Error pages. Otherwise, a standard 200 OK page will be likely returned.

For example, we can have the following query:

http://www.example.com/index.php?id='%20AND%201=0%20OR%20'a'=IIF((select%20MID(LAST(username),1,1)%20from%20(select%20TOP%2010%20username%20from%20users))='a','a','b')%00

that is TRUE if the first character is 'a' or false otherwise.

As mentioned, this method allows to infer the value of arbitrary strings within the database:

1. By trying all printable values, until we find a match
2. By inferring the length of the string using the LEN function, or by simply stopping after we have found all characters

Time-based blind SQL injections are also possible by abusing heavy queries.

References

• http://nibblesec.org/files/MSAccessSQLi/MSAccessSQLi.html
• http://packetstormsecurity.com/files/65967/Access-Through-Access.pdf.html
• http://seclists.org/pen-test/2003/May/74
• http://www.techonthenet.com/access/functions/index_alpha.php
• http://en.wikipedia.org/wiki/Microsoft_Access