 is usually the most effective when its use is possible. With it, you can usually retrieve any information from the database in one go. Unlike simply manipulating the 

 technique is not limited to the data that the original query attempted to retrieve.

For example, here is a query that retrieves credit cards from the database.

 statement, the query can retrieve not only credit card information but also data from another table, in this case the user (users) table.

This page's SQL emulator works a bit differently, allowing the above SQL to pass. Queries executed against a real SQL database usually require the 

 to the number of columns returned by the original 

: because the number of columns in the original SELECT statement (id, card_number, and card_type, which is three) is different from the columns returned by the UNION SELECT statement (email and password, which is two).

The survey should be fixed so that in addition to the email and password columns, a third, empty (

You will soon be able to try this in the lab, where a real database server is running.

If the code is not visible, the number of columns must often be deduced. One way is to continuously send more columns until the application no longer crashes due to a database error.

The other, usually more efficient way is to use the ORDER BY clause. ORDER BY can sort the results based on a specific column, for example, credit card numbers could be sorted based on the card number like this:

This in itself is not very useful for determining the number of columns, but ORDER BY also works with an "ordinal number," in other words, a number that indicates "How the results are sorted according to which column." The same query can therefore be written as follows because card_type is the second column in order.

The important thing to note is that ORDER BY with an ordinal value greater than the number of columns causes an error.

We can determine the number of columns with the ORDER BY clauses as follows:

It is important to remember that ultimately this is about the application code expecting to receive a certain number of rows from the database, where for example the first column is a number, the second is text, the third is a timestamp, etc. And if the database returns nothing, or data of the wrong type, or data with the wrong format, the program code may crash, and you may not get the desired information out of the application.

This is one of the limitations of UNION technique. An application that retrieves a large number of columns and uses them in a complex way is often a somewhat challenging target for UNION technique.

It is also important to remember that the order of the columns determines what information the application "thinks" the column is.

So in this case, the application assumes the email to be the identifier (id) of the card, and the password to be the number (card_number) of the card. If the application code does not crash due to the fact that the expected id is most likely a number but instead is an email address, the users' passwords can be found in the card number field on the website.

Try the exercise below. Remember from earlier learning that:

With a apostrophe or quotation mark (depending on the case), one can "escape" from the text to SQL syntax

With two dashes, you can comment out the rest of the (application's own) SQL. Remember to add a space after the comment lines!

By adding WHERE admin=True, you can limit the returned users to only system administrators. Otherwise, you may have to spend a long time trying to log in with different users...

 (note not users) which has columns like email, password, and admin. The value of the 

In this lab, you will practice using the UNION technique to exploit SQL injection in credit card filtering, but this time for stealing user credentials instead of credit cards.

UNION technology and stealing usernames

## UNION technique

Of different SQL injection techniques, **UNION** is usually the most effective when its use is possible. With it, you can usually retrieve any information from the database in one go. Unlike simply manipulating the **WHERE** clause, **UNION** technique is not limited to the data that the original query attempted to retrieve.

For example, here is a query that retrieves credit cards from the database.

@embed 804f675cd130

When the query is added with the **UNION SELECT** statement, the query can retrieve not only credit card information but also data from another table, in this case the user (users) table.

@embed fbc5dd79939e

---

## The number of columns must be the same

**NOTE. **This page's SQL emulator works a bit differently, allowing the above SQL to pass. Queries executed against a real SQL database usually require the **UNION SELECT** query to return the **number of columns** that is **identical** to the number of columns returned by the original **SELECT** query.

For example, this is **wrong**: because the number of columns in the original SELECT statement (id, card\_number, and card\_type, which is three) is different from the columns returned by the UNION SELECT statement (email and password, which is two).

```sql
SELECT id, card_number, card_type FROM credit_cards UNION SELECT email, password FROM users
```

The survey should be fixed so that in addition to the email and password columns, a third, empty (**NULL**) value is returned:

```sql
SELECT id, card_number, card_type FROM credit_cards UNION SELECT email, password, NULL FROM users
```

You will soon be able to try this in the lab, where a real database server is running.

---

## How to determine the number of columns?

If the code is not visible, the number of columns must often be deduced. One way is to continuously send more columns until the application no longer crashes due to a database error.

```sql
UNION SELECT email, password FROM users
```

ERROR

```sql
UNION SELECT email, password, NULL FROM users
```

ERROR

```sql
UNION SELECT email, password, NULL, NULL FROM users
```

OK. So the number of columns is four.

The other, usually more efficient way is to use the ORDER BY clause. ORDER BY can sort the results based on a specific column, for example, credit card numbers could be sorted based on the card number like this:

@embed a97d4643eeb3

This in itself is not very useful for determining the number of columns, but ORDER BY also works with an "ordinal number," in other words, a number that indicates "How the results are sorted according to which column." The same query can therefore be written as follows because card\_type is the second column in order.

@embed d0a77b89f002

The important thing to note is that ORDER BY with an ordinal value greater than the number of columns causes an error.

@embed ef257f6054aa

We can determine the number of columns with the ORDER BY clauses as follows:

```sql
ORDER BY 1
```

OK

```sql
ORDER BY 2
```

OK

```sql
ORDER BY 3
```

OK

```sql
ORDER BY 4
```

ERROR. The number of columns is three.

---

## The order and type of columns matter

It is important to remember that ultimately this is about the application code expecting to receive a certain number of rows from the database, where for example the first column is a number, the second is text, the third is a timestamp, etc. And if the database returns nothing, or data of the wrong type, or data with the wrong format, the program code may crash, and you may not get the desired information out of the application.

This is one of the limitations of UNION technique. An application that retrieves a large number of columns and uses them in a complex way is often a somewhat challenging target for UNION technique.

It is also important to remember that the order of the columns determines what information the application "thinks" the column is.

If the original query is:

```sql
SELECT id, card_number FROM credit_cards
```

And you add a UNION query after that:

```sql
SELECT id, card_number FROM credit_cards UNION SELECT email, password FROM users
```

So in this case, the application assumes the email to be the identifier (id) of the card, and the password to be the number (card\_number) of the card. If the application code does not crash due to the fact that the expected id is most likely a number but instead is an email address, the users' passwords can be found in the card number field on the website.

---

## Exercise

Try the exercise below. Remember from earlier learning that:

- With a apostrophe or quotation mark (depending on the case), one can "escape" from the text to SQL syntax
- With two dashes, you can comment out the rest of the (application's own) SQL. Remember to add a space after the comment lines!
- By adding WHERE admin=True, you can limit the returned users to only system administrators. Otherwise, you may have to spend a long time trying to log in with different users...

@exercise d21eb9adea5b


(MySQL) Using the UNION technique to steal data

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) Using the UNION technique to steal data

UNION technique

Learn to hack — start here