Vengala Vinay

Having 9+ Years of Experience in Software Development

Home » Code Auditing Guidelines: Detecting and Fixing SQL Injection (SQLi) in customized checkout queries in Your PHP Monolith

Code Auditing Guidelines: Detecting and Fixing SQL Injection (SQLi) in customized checkout queries in Your PHP Monolith

Identifying SQL Injection Vulnerabilities in Custom Checkout Queries

Many legacy PHP monoliths, particularly those with custom e-commerce functionalities, often feature deeply embedded SQL queries within their checkout processes. These queries, designed for specific business logic, are prime candidates for SQL injection (SQLi) if not meticulously crafted. The danger lies in dynamic query construction that directly interpolates user-supplied data without proper sanitization or parameterization. This section focuses on pinpointing such vulnerabilities within your PHP codebase.

A common pattern to scrutinize involves queries that fetch product details, customer information, or apply discounts based on user input. Look for instances where variables, especially those originating from $_GET, $_POST, $_REQUEST, or even $_COOKIE, are concatenated directly into SQL strings.

Static Analysis Techniques for SQLi Detection

Before diving into runtime analysis, static code analysis can reveal many potential SQLi vectors. Employing tools like PHPStan with security extensions or custom grep patterns can significantly speed up the audit process. The goal is to identify patterns of string concatenation with user-controlled input within SQL queries.

Consider a hypothetical checkout query that applies a special discount code. A vulnerable implementation might look like this:

// In checkout_process.php
$discount_code = $_POST['discount_code'];
$user_id = $_SESSION['user_id']; // Assume this is properly managed

// Vulnerable query construction
$sql = "SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items WHERE order_id = (
    SELECT MAX(order_id) FROM orders WHERE user_id = {$user_id}
)) AND price > (SELECT discount_amount FROM discounts WHERE code = '{$discount_code}')";

$result = $db_connection->query($sql);
// ... process results

The critical vulnerability here is the direct interpolation of $discount_code into the SQL string. An attacker could provide a malicious string like ' OR '1'='1 or '; DROP TABLE users; -- to manipulate the query’s execution.

To automate detection, you can use grep to search for common patterns. This is a starting point and will yield false positives, but it’s effective for initial sweeps:

# Search for direct concatenation of $_POST, $_GET, $_REQUEST variables into SQL strings
grep -r -E '(\$_POST\[|"\.\$_POST\[|\$_GET\[|"\.\$_GET\[|\$_REQUEST\[|"\.\$_REQUEST\[)' --include="*.php" . | grep -i 'SELECT\|INSERT\|UPDATE\|DELETE\|FROM\|WHERE'

# More specific pattern looking for string concatenation with SQL keywords
grep -r -E '("|\')\s*\.\s*(\$_POST\[|"\.\$_POST\[|\$_GET\[|"\.\$_GET\[|\$_REQUEST\[|"\.\$_REQUEST\[).*\s+(SELECT|FROM|WHERE|AND|OR|UNION|INSERT|UPDATE|DELETE)' --include="*.php" .

Runtime Analysis and Dynamic Testing

Static analysis is a good first step, but dynamic testing is crucial to confirm vulnerabilities and understand their impact. This involves sending crafted inputs to your application and observing the database behavior or application responses.

Tools like OWASP ZAP or Burp Suite are invaluable for this. Configure your browser to proxy through these tools and then navigate through your checkout process. When you encounter a field that might be used in a database query (like a discount code input, a product ID in a URL parameter, or a search term), use the proxy’s repeater or intruder functionalities to send payloads.

Example Payload Testing (Discount Code Field):

  • Basic True Condition: Send ' OR '1'='1. If the query returns unexpected results (e.g., applying a discount when it shouldn’t, or returning all products), it’s a strong indicator.
  • Boolean-Based Blind SQLi: Send ' AND 1=1 -- and then ' AND 1=2 -- . Observe if the application’s response differs (e.g., different content, different error messages, different loading times). This can be used to infer data.
  • Error-Based SQLi: If your application displays database errors, try payloads that intentionally cause errors, like ' OR 1=CONVERT(int, (SELECT @@version)) -- . If the database version is returned in an error message, you have a clear vulnerability.
  • Union-Based SQLi: This is more complex and requires knowing the number of columns returned by the original query. A payload might look like ' UNION SELECT 1,2,3,4 -- (adjusting the number of columns). If the query executes without error and returns data, you can then try to extract information.

For the vulnerable query example above, sending ' OR '1'='1 as the discount code might result in the query becoming:

SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items WHERE order_id = (
    SELECT MAX(order_id) FROM orders WHERE user_id = 123
)) AND price > (SELECT discount_amount FROM discounts WHERE code = '' OR '1'='1')

This modified query would likely return all products whose price is greater than the discount_amount from the *first* row in the discounts table (because '1'='1' is always true, and the subquery might not behave as expected or might return an arbitrary value if the code column is not unique or indexed properly). If the application then proceeds to apply a discount based on this potentially inflated price, it’s a clear sign of SQLi.

Remediation: Parameterized Queries and Prepared Statements

The most robust defense against SQL injection is to use parameterized queries or prepared statements. This separates the SQL code from the data, ensuring that user input is treated strictly as data, not executable SQL commands.

In PHP, using PDO (PHP Data Objects) is the recommended approach. Let’s refactor the vulnerable query using PDO prepared statements.

Refactoring with PDO Prepared Statements

First, ensure you have a PDO database connection established. It’s best practice to have this connection managed centrally.

// Assuming $pdo is your PDO connection object
// Example:
// $dsn = "mysql:host=localhost;dbname=your_db;charset=utf8mb4";
// $options = [
//     PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
//     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
//     PDO::ATTR_EMULATE_PREPARES   => false,
// ];
// try {
//     $pdo = new PDO($dsn, 'username', 'password', $options);
// } catch (\PDOException $e) {
//     throw new \PDOException($e->getMessage(), (int)$e->getCode());
// }

Now, rewrite the vulnerable query using prepared statements. We’ll use named placeholders (:discount_code) for clarity.

// In checkout_process.php (Refactored)
$discount_code = $_POST['discount_code'] ?? ''; // Use null coalescing operator for safety
$user_id = $_SESSION['user_id']; // Assume this is properly managed

// Prepared statement for fetching products based on user's recent order and discount code
$sql = "SELECT p.* FROM products p WHERE p.id IN (
    SELECT oi.product_id FROM order_items oi WHERE oi.order_id = (
        SELECT MAX(o.order_id) FROM orders o WHERE o.user_id = :user_id
    )
) AND p.price > (
    SELECT d.discount_amount FROM discounts d WHERE d.code = :discount_code
)";

try {
    $stmt = $pdo->prepare($sql);

    // Bind parameters
    $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
    $stmt->bindParam(':discount_code', $discount_code, PDO::PARAM_STR);

    $stmt->execute();
    $products_eligible_for_discount = $stmt->fetchAll();

    // ... process $products_eligible_for_discount
    // Now, you can apply the discount logic to these products.
    // For example, if $products_eligible_for_discount is not empty,
    // it means the discount code was valid and the products meet the criteria.

} catch (\PDOException $e) {
    // Log the error and handle it gracefully
    error_log("SQL Error in checkout: " . $e->getMessage());
    // Display a user-friendly error message
    echo "An error occurred while processing your order. Please try again later.";
    // Potentially exit or redirect
    exit;
}

In this refactored code:

  • The SQL query uses placeholders (:user_id, :discount_code) instead of directly embedding variables.
  • $pdo->prepare() sends the SQL query structure to the database for parsing.
  • $stmt->bindParam() associates PHP variables with these placeholders. Crucially, PDO handles the escaping and quoting of these values, ensuring they are treated as literal data.
  • $stmt->execute() runs the prepared query with the bound values.
  • Error handling with try-catch blocks is essential to catch potential database issues and prevent sensitive information from being exposed.

Handling Dynamic Table/Column Names

A common pitfall is when table or column names themselves need to be dynamic, as prepared statements cannot directly parameterize identifiers (like table or column names). If your checkout logic requires dynamic table/column names based on user input (e.g., selecting from different product tables based on a category ID), you must implement strict whitelisting.

Example: Dynamic Table Selection (Vulnerable)

$product_type = $_GET['type']; // e.g., 'electronics', 'apparel'
// VULNERABLE: Direct interpolation of table name
$sql = "SELECT * FROM {$product_type}_products WHERE id = :product_id";

Remediation: Whitelisting

Create a predefined list of allowed table names and validate the user input against this list. If the input doesn’t match any allowed name, reject the request or use a default.

$allowed_product_tables = [
    'electronics_products' => 'electronics',
    'apparel_products'     => 'apparel',
    'home_goods_products'  => 'home_goods',
];

$product_type_input = $_GET['type'] ?? '';
$product_id = $_GET['id'] ?? 0; // Assume ID is numeric and validated separately

$selected_table = null;
foreach ($allowed_product_tables as $table_name => $type_alias) {
    if ($product_type_input === $type_alias) {
        $selected_table = $table_name;
        break;
    }
}

if ($selected_table === null) {
    // Handle invalid product type - e.g., show error, redirect
    die("Invalid product type specified.");
}

// Now use the validated table name in the query
// Use prepared statements for actual data values
$sql = "SELECT * FROM {$selected_table} WHERE id = :product_id";

try {
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':product_id', $product_id, PDO::PARAM_INT); // Assuming product_id is always an integer
    $stmt->execute();
    $product_data = $stmt->fetch();

    if (!$product_data) {
        echo "Product not found.";
    } else {
        // Display product details
        print_r($product_data);
    }

} catch (\PDOException $e) {
    error_log("SQL Error fetching product: " . $e->getMessage());
    echo "An error occurred while retrieving product details.";
}

Regular Auditing and Defense in Depth

SQL injection is an evolving threat. Regular code audits, both automated and manual, are essential. Integrate security scanning tools into your CI/CD pipeline. Beyond code, ensure your database user has the minimum necessary privileges. For instance, the user connecting to the database for checkout operations should not have permissions to drop tables or modify schema if it’s not strictly required.

Furthermore, implement input validation on the application side for all user-supplied data. While this is not a substitute for parameterized queries, it acts as an additional layer of defense, catching malformed or unexpected data before it even reaches the database layer.