SQL Injection Using UNION
Understanding how to create a valid UNION-based attack to extract information
UNION-based attacks allow the tester to easily extract information from the database. Because the UNION operator can only be used if both queries have the exact same structure, the attacker must craft a SELECT statement similar to the original query. To do this, a valid table name must be known but it is also necessary to determine the number of columns in the first query and their data type. To simplify learning, this article explains how it can be done when error reporting is enabled. However, the same principle would apply if it was not the case. For more information refer to the last section of the article.
Table Name Error
Guessing may be an option to find a table name that exists in the database (a good one in some cases), but let’s consider an approach that will guarantee successful results even if luck is not on your side. The best way to find such information is to use system tables instead of user tables. Even if database systems have different naming convention, the number of popular DBMS is really limited and a valid system table name can be found quickly. Notice that at this step, it is not even necessary to specify column names since a minimal SELECT statement can be used. Let’s now see how to get rid of a table name error with an example (this list of system tables was used):
User input (MySQL system table).
1 UNION SELECT 1 FROM information_schema.tables
Query generated.
SELECT name, description, price FROM products WHERE category=1 UNION SELECT 1 FROM information_schema.tables
Error returned.
ORA-00942: table or view does not exist.
This table name is not valid. Let’s try with system tables of another popular DBMS.
User input (Oracle system table).
1 UNION SELECT 1 FROM all_tables
Query generated.
SELECT name, description, price FROM products WHERE category=1 UNION SELECT 1 FROM all_tables
Error returned.
ORA-01789: query block has incorrect number of result columns.
An error is still returned but it is not related to an invalid table name. The attacker now knows a valid table and he can solve the problem related to the number of columns.
Number of Columns
There is basically 2 ways to find how many columns are selected by the original query. The first one is to inject an ORDER BY clause indicating a column number. Given the column number specified is greater than the number of columns in the SELECT statement, an error will be returned. Otherwise, the results will be sorted by the column mentioned. Let’s see both cases.
User input.
1 ORDER BY 2
Query generated (selects only 3 columns).
SELECT name, description, price FROM products WHERE category=1 ORDER BY 2
Result.
The data returned is sorted by description.
We know that the select statement has at least 2 columns. To find the exact number of columns, the number is incremented until an error related to the ORDER BY clause is returned.
Query generated (selects only 3 columns).
SELECT name, description, price FROM products WHERE id=1 ORDER BY 4
Error returned.
ORA-01785: ORDER BY item must be the number of a SELECT-list expression.
We can now conclude that the original query has 3 columns.
The alternative technique to determine the number of columns is to directly inject a new statement with UNION. The number of columns in the injected select is increased until the database engine does not return an error related to the number of columns. Even if this approach is perfectly valid, the first one is more popular.
Data Types
The last step is to determine the data type of each column of the original query. Some DBMS like MySQL and SQL Server are not strict on data types and will allow implicit numeric conversion. Also, in some cases comprehensive error messages can be returned by the database engine to indicate which column has a data type mismatch. In our example, the system uses Oracle which provide none of those "hits" for the attacker. However, after some tests the correct combination can be determined and the structure of the query is discovered.
User input.
1 UNION SELECT 'A', 'B', 3 FROM all_tables
Query generated.
SELECT name, description, price FROM products WHERE category=1 UNION SELECT 'A', 'B', 3 FROM all_tables
Result.
No error message is returned and data is listed.
With just 3 columns it is relatively easy to test the different cases (only 9 combinations). However when the number of columns is large the number of possible combinations exponentially grows. Fortunately, some tools like sqlmap can automate this process.
When doing manual testing, keep in mind that we can split data types in two groups: numeric values and the rest (considered as strings since they are enclosed between quotes). You do not need to test each and every type supported by the database engine.
Extracting Information
Let's now see how the attacker can take advantage of this UNION attack to extract sensible data. The first step is to gather information about the database schema by crafting a valid SELECT statement. It will allow the attacker to list user tables and find their column names. From this point, it is possible to retreive any information as long as the injected query follows the structure established earlier. Here is what the attacker could have done after finding table and column names.
Crafted parameter (extract username and passwords).
1 AND 1=2 UNION SELECT username, password, 1 FROM members
Query generated.
SELECT name, description, price FROM products WHERE category=1 AND 1=2 UNION SELECT username, password, 1 FROM members
Just a quick remark about the last example. An always false condition was added to the WHERE clause of the original query before the UNION operator. It is a common practice when extracting data. By doing so, the attacker makes sure that all the lines returned come from the injected query.
Data Type Conversion
It is possible that all the columns selected by the original query have the same data type. For example, only numeric values could be selected by the query. Extracting text values may seem infeasible but it is possible to overcome this problem by using conversion functions. For more information refer to articles about data conversion and concatenation.
Conclusion
By using the technique explained in this article, the attacker will be able to take advantage of SQL injection vulnerabilities in order to extract sensible information. It is important to mention that error reporting is not always enabled. In a situation where no database error is returned to the attacker, UNION-based SQL injections are still possible but the process of determining the main query’s structure will require blind SQL injection techniques.
