SQL Injection Tutorial

This article covers the core principles of SQL injection. Specific attacks such as query stacking and are detailed in later articles of this tutorial and heavily rely on techniques exposed below. If you are new to SQL injection, you should consider reading introduction articles before continuing.

WHERE Clause Manipulation

User supplied parameters frequently end up in the WHERE clause of dynamically built queries. Therefore, the only way to achieve a successful attack is to craft this segment of the query from vulnerable input. This can lead to powerful attacks when combined with other techniques, but WHERE clause manipulation is frequently used in its most basic form for login bypass or fast testing.

Numeric parameters can also be vulnerable to SQL injections as explained in the insufficient solutions article. This is possible because weakly typed languages like PHP do not force variables to keep their initial data type. As a result, it is possible to insert a crafted SQL statement in any vulnerable parameter to make a SQL injection attack. After determining the expected data type, the attack can be performed in a similar way it would have been with a vulnerable string parameter.

Direct Injection

Direct injection is possible in a vulnerable numeric parameter. It does not require quotation escaping and the crafted SQL segment can be inserted right after the parameter value. Even though it is the simplest case of SQL injection, examples with string parameter are far more popular since injecting string into numeric fields is confusing. Here is what an attack might look like in this situation:

All products in the database are returned by the query even if no product has the id 9999. If you are wondering what the script behind the last example might look like, here it is:

Quoted Injection

In this case, the vulnerable parameter is surrounded by quotes. At first sight it might seems that quoted injections are limited to string parameters but it could also be used with a date or some DBMS specific type where quotes are used to define the value. To make things simpler those types are grouped under the name of string parameters. The attack will almost be identical to the direct injection; the only difference is that the ending quote must be handled by the malicious parameter in order to create a valid query. Below is an example of a successful attack.

Here again, all products are returned by the query even if there is no such category. The script vulnerable is pretty similar to the last one. The only difference is that it uses quotes.

SQL injection Using UNION

The UNION operator allows the attacker to extract sensitive information from the database. In most cases, the crafted SQL segment is submitted to the database engine via a WHERE clause manipulation. To achieve a successful attack, the parameter must end the original query and contain a UNION operator followed by a valid query.

A common practice when using this technique is to make sure the WHERE clause of the first query is always false. That way, the data returned by the first query is eliminated and the results only contain data returned by the injected query.

The main challenge for the attacker is to create a valid query after the UNION operator. Both queries (before and after UNION) must have the same structure, otherwise an error will be raised by the database engine. Because the first query’s structure is ignored and database’s table names are unknown by the attacker, he will have to gather some information before achieving a successful attack. Techniques to find this information are explained in the SQL injection error analysis article, but for now let’s suppose we are given a valid query to simplify the example below. The script here is the one shown in the last example.

Instead of returning products, the crafted query returns usernames and passwords of all members in the database. Let’s take a look at what was been done in this example. First, the WHERE clause is altered in order to make sure the first query never returns data as recommended earlier. The second query is then integrated with the UNION operator. Finally, the query is terminated by taking care of the quote that is left over with a true condition.

Input Integration

Most common cases have been explained, but examples presented up to now do not cover all possible scenarios of user input integration into SQL queries. Let’s just think about UPDATE and INSERT statements. A vast majority of them are built dynamically according to information supplied by the user. Later articles of this tutorial explain how vulnerabilities present in those statements can be exploited.

Uncommon user input integration should also be considered when searching for vulnerabilities. Every section of a query could be defined by user supplied parameter. For example, a column name integrated directly in the ORDER BY clause of a SELECT statement could be vulnerable to SQL injection. Possibilities are endless and when shortcuts are used by programmers to quickly develop functionalities, we often see security flaws appear…

Some Cases Are Dead Ends

Of course, all parameters are not vulnerable to SQL injection. But it is possible that a vulnerable parameter is integrated in some way that it would be impossible to make a successful attack. Such a situation could happen when the user input is partially sanitized or when it gets integrated into a complex sub query.