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 

 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.

 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.

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

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

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.

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.

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

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

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 

 is the current database schema, which is 

If you want to find out the columns of the table, search for the 

 table and set the condition to display only the columns whose 

 is the desired table (the example below does not work directly because the table names are randomized in this lab).

Run an SQL query that retrieves the tables in the database. It is recommended, but not mandatory, to also perform a query that returns the columns of the desired table for the purpose of the exercise.

In this lab, you will practice figuring out the schema structure of a MySQL database.

MySQLi - Schema clarification

## 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.

```sql
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.

![](sanity:image-3fba987c6bbdb0764d467b68e2e25c203918d0dc-925x284-png)

---

## 

---

## 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.

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

![](sanity:image-ad68d372dcf13c8fbcbbb5fabb1479f31fd9264e-838x571-png)

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**.

![](sanity:image-678e159946ff728ec14db7c95c0ca4a7f3f25a57-838x571-png)

---

## 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()**.

```sql
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).

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

@exercise eec44290b1d9


(MySQL) Determining the structure of the schema

SQL injection is probably the cause of the majority of serious data breaches, mainly because vulnerability is very likely in a large application if the technology choices are bad, and the result is usually leaking the entire database to the attacker.

SQL injection is likely the cause of the majority of serious data breaches, mainly because vulnerability is very likely in a large application if the technology choices are poor, and the consequence is usually the leakage of the entire database to the attacker.

(MySQL) Determining the structure of the schema

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

Learn to hack — start here