SQL Injections

We study a powerful code injection technique that exploits security vulnerabilities of a website. SQL statements are injected in the input field of the web application with the aim of executing improper queries in the database.

A simple vulnerable site

This is an example of vulnerable website (use haxor/sqleet to login). We report a snippet of the underlying code:

The lastname POST variable is not filtered (see line 22 above), so we can set its value to any string of our choice:

For instance, to obtain the list of all users it is enough to inject one of the following values:

which will produce, in turn, the following queries:

In all the examples we start our injection by closing the quote '. This will compare lastname with the empty string giving false. To make the result true we just add OR with some always true statement such as 1=1, 1, ''=''. In the first three cases we need to comment out the last quote using -- (and a white space after the two dashes) or # to avoid an error because of the unbalanced quotes. In the last case, instead, we use the quote in the query to close the last quote in ''=', so that we don’t need to comment it out.

A copy of the database used on the vulnerable website is locally available on the testbed machine. After logging in with your account, you can start experimenting with MySQL in the following way:

Now you can try injecting code in the query by simply cut-and-pasting the malicious code between the two quotes. For example:

You see that the dashes and the space comment out the quote and also the semi-column (which terminates command line SQL). This is why we have to insert an extra semi-column in the next line.

Basic injections

How can we show more interesting data (like passwords) using injections?

UNION ALL can be used to combine two (or more) results:

NOTE: The number of columns in the SELECT statements must be equal. If the numer of columns in the first SELECT statement is unknown, we can guess it by adding some 1 until the query is successfully executed:

EXERCISE: Try to print the list of user passwords on the vulnerable website

What if there is just one column in the first SELECT but we want to dump several columns in one shot? CONCAT is our friend:

Sometimes the page code does not iterate over the result set, but prints only the first row. We need a way to aggregate several rows into one single row. We can use GROUP_CONCAT for this:

Notice the use of SEPARATOR to indicate how to separate the different rows in the GROUP_CONCAT.

Retrieving database structure

Until now we assumed to know the name of the columns/tables/databases we were interested to dump. In reality, tables have fancy prefixes and cannot be easily guessed, hence we need a reliable way to extract the database structure.

In several DBMS (MySQL, Postgres) there’s a database called INFORMATION_SCHEMA that stores all the information of all the databases. We can dump known fields to get the whole db structure.

List databases:

List tables:

List the columns of all relevant databases:

EXERCISE: Try to print the databases, the tables and the columns of relevant databases on the vulnerable website

Filter evasion

Web developers often filter user-provided input to prevent malicious payloads to be executed on their servers. Sometimes, however, filters do not provide an effective defense and can be bypassed.

For instance, if white-spaces are not allowed, we can use as a separator for our SQL queries tabs, new lines, carriage returns or even comment symbols like /**/.

If single quotes are forbidden, it is still possible to provide hexadecimal literals to get strings since they are automatically converted depending on the context:

There are many ways to bypass filters on function names. Here is an example:

Notice that /*!50000 … executes the commented out text if the version of MySQL is greater than or equal the specified one (5.00.00 in this case). See here for more detail.

Advanced techniques

Reading files

It works only if the db user has the FILE privilege and the accessed file is readable by the mysql user

(this example will not work on testbed since the FILE privilege is not granted to the user sqli_example)

Creating files

It works only if the db user has the FILE privilege and the mysql user is allowed to write files in that directory. This is very dangerous as an attacker may create PHP files to obtain a shell on the server as follows:

Now it is enough to execute:

Notice that the command id is executed on the server!

Misc

Is it possible to concatenate multiple queries by adding a ‘;’ (semicolon)?
Most SQL server implementations allow multiple statements to be executed in the same call. On the other hand, some APIs such as PHP mysql_query() function do not allow this.
For example:

However this does not work on the vulnerable website since it uses mysql_query().

Fixes

To prevent SQL injections you should always rely on prepared statements using consistent interfaces to databases such as PDO, as they force a structured and consistent access to databases by interposing an abstract independent layer.

When prepared statements are not available (for example in old applications) it is crucial to properly sanitize the input by:

  • Typecasting for numeric parameters, for example using intval. This prevents injecting arbitrary payloads since the input is casted into an integer number.
  • Escaping string input parameters in a query, for example using mysqli_real_escape_string. Notice that escaping is not in general bullet proof. For example, mysql_real_escape_string, (without the ‘i’!) can be circumvented by exploiting different charsets and is now deprecated.

Leave a Reply

Your email address will not be published. Required fields are marked *