Union-Based SQL Injection: Techniques, Risks, and Prevention

What is Union-Based SQL Injection?

Union-based SQL injection is a technique where an attacker manipulates an application’s SQL query by appending a UNION statement. This allows them to combine the results of a malicious query with the application’s legitimate query, enabling the extraction of sensitive data such as usernames, passwords, or credit card information.

The UNION operator in SQL combines the results of two or more SELECT queries, provided they have the same number of columns and matching data types. Attackers exploit this feature to append their queries and retrieve hidden database information.

How it works

SQL databases use the UNION operator to combine results from multiple queries into a single dataset. This is useful when retrieving similar data from different tables.

 

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

 

This is a legitimate use of UNION. Attackers exploit this by injecting additional SQL queries into an application’s existing query to manipulate data retrieval.

Attackers manipulate input fields in web applications to inject malicious SQL queries using the UNION operator. This technique helps them retrieve sensitive data by appending extra queries to an existing one.

A website has a login page where users enter their username and password. The backend executes this SQL query to verify login details:

 

SELECT id, username FROM users WHERE username = 'admin' AND password = 'password123';

 

An attacker, instead of entering a valid username, inputs:

 

' UNION SELECT id, username, password FROM users --

 

The query now becomes:

 

SELECT id, username FROM users WHERE username = ''
UNION SELECT id, username, password FROM users -- ' AND password = 'password123';

 

What Happens Here?

• The UNION SELECT query allows the attacker to fetch data from the users table.
• The — (double dash) comments out the rest of the original query, avoiding syntax errors.
• As a result, all usernames and passwords from the users table get exposed.

For a UNION-based attack to work, both queries must have:

• The same number of columns
• Matching data types

Preventative Measures and Best Practices

1. Use Prepared Statements and Parameterized Queries

Prepared statements prevent union-based SQL injection by ensuring user input is always treated as data, not SQL code. They enforce strict separation between SQL logic and user inputs, making it impossible for attackers to inject UNION statements.

Example:

 

cursor.execute("SELECT id, username, email FROM users WHERE id = %s", (user_id,))

 

Since the query structure is predefined, attackers cannot modify it to extract additional data using UNION SELECT.

2. Implement Web Application Firewalls (WAF)

A Web Application Firewall (WAF) blocks union-based SQL injection attempts by filtering malicious SQL queries before they reach the database. Advanced WAFs use behavior-based analysis, machine learning, and query normalization to filter malicious queries. WAFs analyze:

• UNION SELECT statements attempting to extract unauthorized data.
• ORDER BY manipulations used to determine the number of columns in a query.

3. Limit Database Privileges

Restricting database privileges minimizes the damage even if an attacker exploits a vulnerability. Best practices include:

• Running applications with least privilege—only granting necessary permissions.
• Avoiding the use of admin or root accounts for queries.
• Restricting access to DROP, ALTER, UNION, and SELECT commands where possible.

With restricted privileges, an attacker’s ability to retrieve or manipulate data is significantly reduced.

4. Conduct Regular Security Testing

Regular security assessments help identify and patch vulnerabilities before attackers exploit them. Recommended testing methods include:

• Automated vulnerability scanning to detect SQL injection risks.
• Penetration testing to simulate real-world attacks.
• Code reviews to eliminate insecure SQL queries.

Frequent testing ensures that applications remain secure from evolving SQL injection techniques.

How AppTrana WAAP Protects Against Union-Based SQL Injection

AppTrana WAAP effectively detects and blocks union-based SQL injection attacks through multiple security layers. It leverages signature-based detection to identify known SQL injection patterns, such as UNION SELECT queries, while also utilizing behavioral analysis to detect anomalous query patterns. Every incoming request undergoes real-time inspection, ensuring that malicious SQL payloads are identified and blocked before reaching the backend database.

Additionally, AppTrana provides virtual patching, allowing businesses to autonomously remediate SQL injection vulnerabilities through virtual patches without modifying the source code. This remediation method is crucial for addressing vulnerabilities promptly while developers work on permanent fixes. Moreover, its machine learning-driven adaptive security continuously analyzes evolving attack patterns, automatically updating protection rules to mitigate new threats.