(MySQL) Using the UNION technique to steal data

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.

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.

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

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:

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.

UNION SELECT email, password FROM users

ERROR

UNION SELECT email, password, NULL FROM users

ERROR

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:

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:

ORDER BY 1

OK

ORDER BY 2

OK

ORDER BY 3

OK

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:

SELECT id, card_number FROM credit_cards

And you add a UNION query after that:

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