How SQL Injection happens in PHP bulk email systems and how to fix it

How SQL Injection Happens in PHP Bulk Email Systems and How to Fix It

Introduction

In the Orbis AppSec security review, we discovered a critical SQL injection vulnerability in admin/utilities/bulkEmailSystem.php at line 260. The vulnerability existed in the validateDatabases() function, which processes a list of database names submitted through the bulk email interface. These database names came directly from user-controlled POST form data and were interpolated directly into raw SQL queries without any validation or sanitization—a classic SQL injection pattern.

The specific issue: the $db variable was used in this query without any checks:

$query = "SHOW TABLES IN {$db} WHERE Tables_in_{$db} = 'Records'...";

An attacker with access to the bulk email form could submit a malicious database name like hdb_test; DROP TABLE users; -- which would be interpolated into the query, causing unintended SQL commands to execute. This could lead to data deletion, unauthorized access, or complete database compromise.

The Vulnerability Explained

The Vulnerable Code

The bulk email system's validateDatabases() function (lines 427-434 in the original code) looked like this:

private function validateDatabases($db_list) {
    foreach($db_list as $db){
        // Required tables are 'Records', 'recDetails', 'sysUGrps', and 'sysUsrGrpLinks'
        $query = "SHOW TABLES IN {$db} WHERE Tables_in_{$db} = 'Records' 
                  OR Tables_in_{$db} = 'recDetails' 
                  OR Tables_in_{$db} = 'sysUGrps' 
                  OR Tables_in_{$db} = 'sysUsrGrpLinks'";

        // Execute query...
    }
}

The critical flaw: $db comes directly from user-controlled form data and is never validated before being inserted into the SQL query string.

Why This Matters

Unlike traditional SQL injection where user input goes into data values (which can be parameterized), database names cannot be parameterized in SQL—they're structural syntax elements. This means:

1. The attacker's input is treated as literal SQL syntax, not as a string value
2. Standard prepared statement escaping doesn't apply to identifiers
3. Only strict input validation can prevent this attack

Specific Attack Scenario

An attacker with access to the admin bulk email interface could:

1. Intercept the POST request to the bulk email form
2. Submit a malicious database name: hdb_test; DROP TABLE users; --
3. The query becomes:
   sql SHOW TABLES IN hdb_test; DROP TABLE users; -- WHERE Tables_in_hdb_test; DROP TABLE users; -- = 'Records'...
4. MySQL executes two statements: the SHOW TABLES query (benign) and the DROP TABLE users command (catastrophic)

The -- comment terminator ensures the rest of the malformed query is ignored. In this scenario, the entire users table would be deleted.

Real-World Impact

For the bulk email system specifically:
- Attackers could delete critical tables (Records, recDetails, etc.) used for email recipient lists
- Could insert malicious data into system tables
- Could escalate privileges by manipulating the sysUGrps (user groups) table
- Could exfiltrate sensitive email records before deletion

The Fix

The security fix adds input validation before the user-supplied database name reaches the SQL query:

private function validateDatabases($db_list) {
    foreach($db_list as $db){
        // NEW: Whitelist validation - only allow alphanumeric, underscore, and hyphen
        if (!preg_match('/^[a-zA-Z0-9_-]+$/', $db)) {
            continue; // Skip database names with invalid characters
        }

        // Required tables are 'Records', 'recDetails', 'sysUGrps', and 'sysUsrGrpLinks'
        $query = "SHOW TABLES IN {$db} WHERE Tables_in_{$db} = 'Records' 
                  OR Tables_in_{$db} = 'recDetails' 
                  OR Tables_in_{$db} = 'sysUGrps' 
                  OR Tables_in_{$db} = 'sysUsrGrpLinks'";

        // Execute query...
    }
}

What Changed

Line 430-432 (new validation):

if (!preg_match('/^[a-zA-Z0-9_-]+$/', $db)) {
    continue; // Skip database names with invalid characters
}

This regex pattern ensures that $db contains only:
- Letters: a-z, A-Z
- Numbers: 0-9
- Underscore: _
- Hyphen: -

Any database name containing SQL metacharacters (;, ', ", --, /*, */, etc.) is rejected before reaching the SQL query.

How This Prevents the Attack

If an attacker submits hdb_test; DROP TABLE users; --:
1. The regex matches characters up to the semicolon: hdb_test ✓
2. Then encounters ; which is not in the allowed character set
3. preg_match() returns false
4. The if condition triggers, and continue skips this entry
5. The malicious SQL never reaches the database query execution

Why This Fix is Comprehensive

This approach is particularly effective for database names because:

1. Database identifiers naturally follow strict naming conventions — valid MySQL database names already consist of these characters
2. Whitelist approach — we define what's allowed rather than what's blocked (safer than blacklisting)
3. Fails securely — invalid inputs are rejected entirely, not attempted to be "fixed"
4. No performance impact — a single regex check is negligible compared to database operations

Prevention & Best Practices

For Database Identifiers Specifically

Since database and table names cannot be parameterized, follow these practices:

1. Always validate with a whitelist regex for identifiers:
   php if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $identifier)) { throw new InvalidArgumentException("Invalid identifier"); }
   Note: MySQL identifiers must start with a letter or underscore, not a number.

2. Never construct SQL with concatenation for identifiers — always validate first

3. Use backticks for identifier quoting only as a supplementary measure, not primary defense:
   php $query = "SHOW TABLES IN `{$db}`"; // Still requires validation!

For User Input in General

1. Use prepared statements for data values:
   php $stmt = $mysqli->prepare("SELECT * FROM records WHERE email = ?"); $stmt->bind_param("s", $user_email);

2. Validate and sanitize all inputs at the entry point

3. Implement a security layer that flags suspicious patterns (like semicolons in database names)

Detection Tools

• Semgrep: Use rule php-lang/security/sql-injection-user-input to detect similar patterns
• SonarQube: Configured to flag string interpolation in SQL queries
• Orbis AppSec: Automatically scans for tainted data flow from HTTP parameters to SQL queries

Relevant Standards

• OWASP Top 10: A03:2021 – Injection
• CWE-89: Improper Neutralization of Special Elements used in an SQL Command
• OWASP SQL Injection Prevention Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Key Takeaways

• Database names require special handling: Unlike parameterized data values, table and database identifiers cannot be safely parameterized—whitelist validation is the correct approach
• The validateDatabases() function now prevents SQL injection by rejecting any database name containing non-alphanumeric characters (except underscore and hyphen) before the name reaches the query builder
• Regex validation ^[a-zA-Z0-9_-]+$ is sufficient for MySQL identifiers and eliminates SQL metacharacters completely
• String interpolation in SQL queries is dangerous — always validate user-controlled identifiers before embedding them in query strings
• The bulk email system's attack surface has been reduced — the most dangerous input vector (database selection) now has a hard security boundary

How Orbis AppSec Detected This

Source: The $db variable originates from user-submitted form data in the bulk email POST request, representing the selected database name(s).

Sink: The dangerous call site is the raw SQL query construction at line 260 (validateDatabases() function) where {$db} is directly interpolated: $query = "SHOW TABLES IN {$db} WHERE Tables_in_{$db} = 'Records'..."

Missing control: There was no input validation or sanitization between the form submission and the SQL query construction. The $db variable was trusted without verification of its contents.

CWE: CWE-89 (Improper Neutralization of Special Elements used in an SQL Command)

Fix: Added regex-based input validation using preg_match('/^[a-zA-Z0-9_-]+$/', $db) to reject any database names containing SQL metacharacters before query execution.

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

SQL injection remains one of the most dangerous vulnerabilities in web applications, particularly in administrative interfaces like bulk email systems. While parameterized queries have largely solved SQL injection for data values, database identifiers require a different approach—strict whitelist validation.

This fix demonstrates that effective security doesn't always require complex solutions. By understanding why a vulnerability exists (unvalidated identifiers in SQL), we can apply a precisely targeted mitigation (regex validation) that's both secure and maintainable.

When working with database or table names in PHP, always remember: validate identifiers with a whitelist regex before any SQL concatenation. This simple practice eliminates an entire class of injection attacks.

For developers maintaining similar bulk operations, email systems, or administrative utilities, audit your code for places where user input becomes SQL identifiers—they're high-value targets for attackers and often overlooked in security reviews.