(MySQL) Determining the structure of the schema

What if we don't know the structure of the database?

If we know in advance that there is a user table in the database with the email and password columns, the attack can be quite easy. However, we cannot always know in advance what the structure of the database looks like, so let's learn ways to find out.

Used in investigation is the UNION technique, which should be utilized whenever possible due to its efficiency.

Start by opening the exercise from the bottom of the page and going to the credit card page, just like in the previous module.

Derivation of the Number of Columns

As a memory refresher, we will once again clarify the number of columns using the ORDER BY technique.

ORDER BY 1--
ORDER BY 2--
...
ORDER BY 9--

Sorting according to the ninth column causes an error, so we can conclude that there are eight columns.

Finding the Reflection Point

Reflection point, meaning the point on the page/HTTP response where you can see the column(s) fetched from the database, you can conveniently find out by searching for a row resembling the one below in the database.

UNION SELECT 111,222,333,444,555,666,777,888--

Then you know that, for example, the seventh column is one that you can retrieve from the application. One such column is enough to conduct an attack.

Database version detection

In MySQL databases, you can retrieve the version of the database by querying the value @@version.

Searching the Database

A database server can have multiple different databases. You can determine the name of the database using the database() value.

Retrieving Database Tables

In a MySQL database, there is always a special system table called information_schema. The table does not contain application data, but contains information about the database schema, i.e., the structure of the database. The table is extremely useful for an attacker.

Your aim is to use the UNION technique in the same way you used it in previous modules to steal user credentials, but the UNION query you want to execute is as follows: Show the information_schema.tables table for rows where the table_name is the current database schema, which is database().

SELECT table_name FROM information_schema.tables WHERE table_schema = database()

Retrieving Database Columns

If you want to find out the columns of the table, search for the column_name column in the information_schema.columns table and set the condition to display only the columns whose table_name is the desired table (the example below does not work directly because the table names are randomized in this lab).

UNION SELECT column_name FROM information_schema.columns WHERE table_name="goag_creditcard"