1. Introduction to Union SQL Injection

In the realm of SQL injection attacks, Union-based SQL injection stands out as a powerful technique that allows attackers to extract sensitive information from multiple tables and databases within a vulnerable application. By leveraging the UNION clause in SQL, attackers can manipulate queries and retrieve data that was never intended to be accessible.

1.1 Definition and purpose of the UNION clause in SQL

The UNION clause is a fundamental feature of SQL that enables the combination of result sets from multiple SELECT statements into a single result set. Its primary purpose is to merge the rows returned by two or more SELECT queries, provided that they have the same number of columns and compatible data types.

The basic syntax of the UNION clause is as follows:

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

In this example, the UNION clause combines the rows from table1 and table2, returning a single result set that includes all the unique rows from both tables. The UNION clause automatically removes any duplicate rows, ensuring that the final result set contains only distinct values.

It's important to note that the UNION clause requires the following conditions to be met:

1. The number of columns in each SELECT statement must be the same.

2. The data types of the corresponding columns must be compatible or implicitly convertible.

3. The columns in each SELECT statement must be specified in the same order.

When these conditions are satisfied, the UNION clause performs a vertical concatenation of the result sets, appending the rows from the second SELECT statement to the rows from the first SELECT statement.

1.2 How UNION injection can be used to extract data from multiple tables and databases

In the context of SQL injection attacks, the UNION clause can be exploited to perform Union-based SQL injection. This technique allows attackers to append their own malicious SELECT statements to the original query, enabling them to retrieve sensitive information from other tables or even different databases.

Here's a step-by-step explanation of how Union-based SQL injection works:

1. Identifying a vulnerable parameter: Attackers begin by identifying a parameter in the application that is susceptible to SQL injection. This could be a user input field, a URL parameter, or any other entry point where user-supplied data is incorporated into an SQL query.

2. Determining the number of columns: To construct a valid UNION query, attackers need to know the number of columns returned by the original SELECT statement. They can do this by using techniques like ORDER BY or UNION SELECT with different numbers of columns until they find the correct number that doesn't generate an error.

3. Matching the data types: Once the number of columns is determined, attackers need to ensure that the data types of the columns in their injected SELECT statement match or are compatible with the original query's columns. They can use techniques like NULL values or type-casting to accommodate different data types.

4. Extracting data from other tables: With the number of columns and data types matched, attackers can now craft a malicious UNION SELECT statement to retrieve sensitive information from other tables. They can target tables that store user credentials, personal information, or any other valuable data.

5. Expanding the attack scope: Union-based SQL injection is not limited to a single table or database. Attackers can potentially access data from different tables within the same database or even switch to other databases by leveraging the UNION clause and appropriate SELECT statements.

Here's an example of a Union-based SQL injection attack:

Suppose a vulnerable application has a query that retrieves user information based on a provided user ID:

SELECT name, email FROM users WHERE id = $userId;

An attacker can exploit this query by injecting a malicious UNION SELECT statement:

' UNION SELECT username, password FROM admin_users--

If the application fails to properly sanitize the $userId parameter, the resulting query becomes:

SELECT name, email FROM users WHERE id = '' UNION SELECT username, password FROM admin_users-- ';

In this case, the attacker has successfully retrieved sensitive information (usernames and passwords) from the admin_users table, which was not originally intended to be accessible.

Union-based SQL injection attacks can have severe consequences, as they allow attackers to bypass authorization, steal sensitive data, and compromise the security of the application and its users. It is crucial for developers to implement proper input validation, parameterized queries, and other security measures to prevent such attacks.

In the following sections, we will delve deeper into the techniques and steps involved in executing Union-based SQL injection attacks, as well as explore real-world examples and best practices for prevention.

2. Understanding the UNION Clause

2.1 Combining results from multiple SELECT statements

• Example: Using UNION to combine results from the 'ports' and 'ships' tables

• Code snippet:

    SELECT * FROM ports UNION SELECT * FROM ships;
  

2.2 Requirements for using UNION

• Data types of selected columns must match across all positions

• The number of columns in each SELECT statement must be equal

3. Exploiting UNION Injection with Even Columns

3.1 Injecting a UNION query into user input

• Example: Extracting username and password from the 'passwords' table

• Code snippet:

    SELECT * from products where product_id = '1' UNION SELECT username, password from passwords-- '
  

3.2 Assumptions and requirements

• The original query and the injected query must have the same number of columns

4. Exploiting UNION Injection with Uneven Columns

4.1 Using junk data to match the number of columns

• Example: Using numbers or strings as junk data

• Code snippet:

    SELECT * from products where product_id = '1' UNION SELECT username, 2 from passwords
  

4.2 Ensuring data type compatibility

• Junk data must match the data type of the corresponding column

• Using 'NULL' as a universal junk data value

4.3 Positioning the payload

• Using numbers as junk data to track payload positions

• Example: Injecting into a query with four columns

• Code snippet:

    UNION SELECT username, 2, 3, 4 from passwords-- '
  

5. Detecting the Number of Columns

5.1 Using ORDER BY

• Injecting order by clauses with increasing column numbers until an error occurs

• Example: ' order by 1-- -, ' order by 2-- -, etc.

• The last successful order by number reveals the total number of columns

5.2 Using UNION

• Injecting UNION queries with different numbers of columns until a successful response is received

• Example: ' UNION select 1,2,3-- -, ' UNION select 1,2,3,4-- -, etc.

• The first successful UNION query reveals the correct number of columns

10. Further Reading and Resources

• SQL Injection Cheat Sheet

• OWASP SQL Injection Prevention Cheat Sheet

• SQLi Labs

11. Conclusion

11.1 Recap of key points

• UNION clause combines results from multiple SELECT statements

• UNION injection allows data extraction from different tables and databases

• Matching the number of columns and data types is crucial for successful exploitation

• Detecting the number of columns using ORDER BY and UNION techniques

• Locating the injection point by identifying displayed columns

6. Locating the Injection Point

After determining the number of columns in the vulnerable SQL query, the next crucial step in performing a successful Union-based SQL injection attack is to locate the exact injection point. This involves identifying which columns are actually displayed in the application's output and placing the malicious payload in the correct column to extract the desired information.

6.1 Identifying displayed columns

When an SQL query is executed, it may return multiple columns from the database. However, not all of these columns are necessarily displayed or rendered in the application's output. Some columns may be used internally by the application for processing or stored in variables without being directly visible to the user.

To identify which columns are displayed, attackers can employ a technique using numbered junk data. They can inject a UNION SELECT statement with numbered values or strings in each column position. For example:

' UNION SELECT 1,2,3,4-- -

By observing the application's response, attackers can determine which numbered values or strings appear in the output. The positions of the displayed values correspond to the column positions that are rendered by the application.

For instance, if the application's output displays the values "2" and "4" from the injected UNION SELECT statement, it indicates that the second and fourth columns are being rendered in the output, while the first and third columns are not displayed.

6.2 Placing the injection in the correct column

Once the displayed columns are identified, attackers need to place their malicious payload in one of those columns to successfully extract the desired information. They can replace the numbered junk data with the actual SQL query or function they want to inject.

For example, if the second column is found to be displayed, an attacker can replace the value "2" with a query to retrieve sensitive information:

' UNION SELECT 1,@@version,3,4-- -

In this case, the @@version function is injected into the second column, which will display the version of the database server in the application's output.

By strategically placing the injection in the correct column, attackers can retrieve specific data, such as user credentials, database schema information, or other sensitive details.

It's important to test the injection with a sample query to confirm that the payload is successfully executed and the desired information is retrieved. Attackers may need to adjust the payload or try different column positions until they achieve the intended result.

7. Checkpoint: Test Your Understanding

7.1 Exercise: Determine the number of columns in a vulnerable application using ORDER BY and UNION techniques

To reinforce your understanding of determining the number of columns, consider the following exercise:

1. Given a vulnerable application with a SQL injection vulnerability, use the ORDER BY technique to find the number of columns in the affected query. Start with ORDER BY 1, then increment the number until an error occurs. The last successful ORDER BY number represents the total number of columns.

2. Verify your finding using the UNION technique. Inject a UNION SELECT statement with increasing numbers of columns until the application's response indicates a successful injection. The number of columns in the first successful UNION SELECT statement confirms the total number of columns.

7.2 Exercise: Locate the injection point by identifying the displayed columns

To practice locating the injection point, try the following exercise:

1. Using the vulnerable application from the previous exercise, inject a UNION SELECT statement with numbered junk data in each column position. For example: ' UNION SELECT 1,2,3,4-- -.

2. Observe the application's output and note which numbered values are displayed. These positions correspond to the displayed columns.

3. Replace the numbered junk data in one of the displayed column positions with a sample payload, such as @@version, to confirm the successful injection and retrieval of information.

8. Real-World Applications and Examples

Union-based SQL injection attacks have been successfully employed in various real-world scenarios to compromise the security of applications and gain unauthorized access to sensitive data. Let's explore a few examples:

8.1 Bypassing authentication and accessing sensitive data

Attackers can use Union-based SQL injection to bypass authentication mechanisms and gain unauthorized access to sensitive information. By injecting malicious UNION SELECT statements, attackers can manipulate the query to return valid user credentials or session tokens, allowing them to log in as authenticated users without knowing the actual passwords.

For example, an attacker could inject a payload like:

' UNION SELECT username, password FROM users-- -

If the application fails to properly sanitize the input and the injection is successful, the attacker can retrieve valid username and password combinations from the users table, effectively bypassing the authentication process.

8.2 Enumerating database schema and extracting information from multiple tables

Union-based SQL injection can be used to enumerate the database schema and extract sensitive information from multiple tables. Attackers can leverage information_schema tables or system tables to retrieve metadata about the database structure, table names, column names, and data types.

For instance, an attacker could inject a payload like:

' UNION SELECT table_name, column_name FROM information_schema.columns-- -

This injection would return the names of tables and their corresponding columns from the information_schema.columns table, providing valuable information about the database schema.

Furthermore, attackers can use UNION SELECT statements to join data from multiple tables and extract sensitive information. For example:

' UNION SELECT username, password FROM users JOIN admin_users ON users.id = admin_users.user_id-- -

This payload combines data from the users and admin_users tables, potentially exposing sensitive information about administrative accounts.

8.3 Escalating privileges and gaining unauthorized access

Union-based SQL injection can also be employed to escalate privileges and gain unauthorized access to restricted functionalities or areas of the application. By manipulating the query to return specific user roles or permission levels, attackers can trick the application into granting them elevated privileges.

For example, an attacker could inject a payload like:

' UNION SELECT 'admin', 'password' FROM users-- -

If the application relies on the returned user role to determine access privileges, the attacker can inject a fixed value like 'admin' to falsely represent themselves as an administrative user, gaining unauthorized access to privileged features or sensitive data.

9. Best Practices and Prevention

To protect against Union-based SQL injection attacks, developers must adhere to best practices and implement appropriate security measures. Here are some key recommendations:

9.1 Input validation and sanitization

One of the most effective ways to prevent SQL injection attacks is to properly validate and sanitize user input. This involves:

• Validating input data against a whitelist of allowed characters or patterns.

• Removing or escaping special characters that have significance in SQL syntax, such as single quotes, double quotes, semicolons, and dashes.

• Using prepared statements or parameterized queries to separate user input from the SQL query structure.

9.2 Parameterized queries and prepared statements

Parameterized queries and prepared statements are techniques that allow developers to define the SQL query structure separately from the user input. Instead of concatenating user input directly into the query string, placeholders or parameters are used to represent the input values.

For example, instead of constructing a query like:

query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"

Developers should use parameterized queries:

query = "SELECT * FROM users WHERE username = @username AND password = @password"
parameters = [@username = inputUsername, @password = inputPassword]

By using parameterized queries, the database engine treats user input as data rather than executable code, effectively mitigating the risk of SQL injection attacks.

9.3 Least privilege principle and database user permissions

Applying the principle of least privilege is crucial in minimizing the potential impact of SQL injection attacks. This involves:

• Granting database users only the minimum permissions necessary to perform their intended tasks.

• Avoiding the use of highly privileged accounts, such as 'sa' or 'root', for application database connections.

• Restricting access to sensitive tables or columns based on user roles and permissions.

By limiting the privileges of database users, even if an SQL injection vulnerability is exploited, the attacker's ability to access or manipulate sensitive data is significantly reduced.

In addition to these best practices, developers should also keep their application frameworks, libraries, and database management systems up to date with the latest security patches. Regular security audits and penetration testing can help identify and address any potential SQL injection vulnerabilities in the application.

Remember, preventing SQL injection attacks requires a multi-layered approach that combines input validation, parameterized queries, least privilege access, and ongoing security monitoring and testing.