How We Audited a High-Traffic PHP Enterprise Stack on OVH and Mitigated SQL Injection (SQLi) in customized checkout queries

Initial Stack Assessment & Audit Scope

Our engagement began with a deep dive into a high-traffic PHP enterprise stack hosted on OVH. The primary objective was to identify and mitigate critical security vulnerabilities, with a specific focus on SQL Injection (SQLi) within customized checkout queries. The stack comprised a multi-instance Nginx setup for load balancing and SSL termination, a cluster of PHP-FPM 7.4 application servers, and a Galera Cluster for MySQL 5.7. The sheer volume of transactions and the sensitive nature of customer data necessitated a rigorous, multi-layered audit.

The audit scope was defined as follows:

• Static Application Security Testing (SAST) of the core PHP codebase, focusing on input validation and database interaction layers.
• Dynamic Application Security Testing (DAST) simulating common attack vectors against the live application, particularly during the checkout process.
• Database schema review for potential weaknesses and adherence to secure coding practices.
• Configuration review of Nginx, PHP-FPM, and MySQL for security misconfigurations.
• Manual code review of critical checkout and payment processing modules.

SAST: Identifying Vulnerabilities in Checkout Logic

We employed a combination of automated SAST tools and manual code inspection. A key area of concern was the handling of user-supplied data within the checkout flow, which directly influenced database queries. Specifically, we identified several instances where product IDs, quantities, and custom discount codes were concatenated directly into SQL strings without proper sanitization or parameterization.

Consider the following representative snippet from the legacy codebase:

// Legacy vulnerable code
$productId = $_POST['product_id'];
$quantity = $_POST['quantity'];
$discountCode = $_POST['discount_code'];

$sql = "SELECT price FROM products WHERE id = " . $productId . " AND quantity_available >= " . $quantity;
$result = $dbConnection->query($sql);

if ($discountCode) {
    $sqlDiscount = "SELECT discount_percentage FROM discounts WHERE code = '" . $discountCode . "' AND is_active = 1";
    $discountResult = $dbConnection->query($sqlDiscount);
    // ... further processing
}

This code is highly susceptible to SQLi. An attacker could manipulate $_POST['product_id'] to inject malicious SQL. For instance, setting product_id to 1 OR 1=1 -- would bypass the product ID check and potentially retrieve all product prices. Similarly, the discount code field is vulnerable to injection if the is_active check is bypassed or if the query is modified to perform other actions.

DAST: Simulating Real-World Attacks

To validate the findings from SAST and uncover any runtime vulnerabilities, we performed DAST. This involved using tools like OWASP ZAP and Burp Suite to intercept and manipulate HTTP requests during the checkout process. We focused on parameters that were directly used in database queries, such as product identifiers, coupon codes, and user-specific pricing adjustments.

A typical attack scenario involved submitting a crafted payload in the product_id parameter:

POST /checkout/add_to_cart HTTP/1.1
Host: example.com
Content-Type: application/x-www-form-urlencoded
Content-Length: ...

product_id=123+UNION+SELECT+password+FROM+users+WHERE+username+%3D+'admin'--&quantity=1

This payload attempts to union the result of a query that extracts the administrator’s password with the original query’s expected result set. While the exact success depends on the application’s error handling and response structure, the principle is to inject SQL that alters the query’s logic or extracts sensitive data. We observed that in several instances, error messages or unexpected application behavior confirmed the successful injection of malicious SQL.

Mitigation Strategy: Prepared Statements & Input Validation

The primary mitigation for SQLi is the consistent use of prepared statements with parameterized queries. This separates the SQL code from the data, preventing the data from being interpreted as executable SQL commands. We refactored the vulnerable code snippets to utilize PDO (PHP Data Objects) with prepared statements.

The corrected code for the product retrieval and discount application now looks like this:

// Mitigated code using PDO prepared statements
$productId = $_POST['product_id'];
$quantity = $_POST['quantity'];
$discountCode = $_POST['discount_code'];

// Input validation: Ensure numeric values are indeed numeric
if (!filter_var($productId, FILTER_VALIDATE_INT) || !filter_var($quantity, FILTER_VALIDATE_INT) || $quantity <= 0) {
    // Handle invalid input - log error, return error response
    error_log("Invalid product_id or quantity received.");
    // Depending on context, throw exception or return JSON error
    throw new InvalidArgumentException("Invalid product ID or quantity.");
}

// Prepare and execute the product query
$sqlProduct = "SELECT price FROM products WHERE id = :productId AND quantity_available >= :quantity";
$stmtProduct = $dbConnection->prepare($sqlProduct);
$stmtProduct->bindParam(':productId', $productId, PDO::PARAM_INT);
$stmtProduct->bindParam(':quantity', $quantity, PDO::PARAM_INT);
$stmtProduct->execute();
$productResult = $stmtProduct->fetch(PDO::FETCH_ASSOC);

if (!$productResult) {
    // Product not found or insufficient quantity
    throw new Exception("Product not available or insufficient stock.");
}

$price = $productResult['price'];

// Input validation for discount code: Ensure it's alphanumeric or matches expected patterns
if ($discountCode && !preg_match('/^[a-zA-Z0-9_-]+$/', $discountCode)) {
    error_log("Invalid discount code format received: " . $discountCode);
    // Optionally, treat as no discount or throw an error
    $discountCode = null; // Reset to null if format is invalid
}

if ($discountCode) {
    // Prepare and execute the discount query
    $sqlDiscount = "SELECT discount_percentage FROM discounts WHERE code = :discountCode AND is_active = 1";
    $stmtDiscount = $dbConnection->prepare($sqlDiscount);
    $stmtDiscount->bindParam(':discountCode', $discountCode, PDO::PARAM_STR);
    $stmtDiscount->execute();
    $discountResult = $stmtDiscount->fetch(PDO::FETCH_ASSOC);

    if ($discountResult) {
        $discountPercentage = $discountResult['discount_percentage'];
        // Apply discount logic
        $price = $price * (1 - ($discountPercentage / 100));
    }
}

// ... further processing with validated price

In addition to prepared statements, we implemented robust input validation at the application layer. For numeric fields like product_id and quantity, we used filter_var() with FILTER_VALIDATE_INT to ensure they are indeed integers. For string inputs like discount_code, we employed regular expressions (preg_match) to enforce expected character sets and formats, preventing unexpected characters that could be part of an injection attempt.

Database & Server Configuration Hardening

Beyond application code, we reviewed the underlying infrastructure. For the MySQL Galera Cluster, we ensured:

• The secure_file_priv setting was configured appropriately to restrict file operations.
• Remote root access was disabled.
• Strong, unique passwords were enforced for all database users.
• Network access to the database was restricted to application servers via firewall rules (OVH firewall and iptables).
• The general_log was disabled in production to prevent performance degradation and sensitive log exposure.

For Nginx, we focused on:

• Enabling http_only and secure flags for all cookies.
• Configuring rate limiting to prevent brute-force attacks on login and checkout endpoints.
• Disabling unnecessary HTTP methods.
• Ensuring TLS 1.2 and 1.3 were enforced, with strong cipher suites.
• Implementing security headers like Content-Security-Policy, X-Content-Type-Options, and X-Frame-Options.

PHP-FPM configurations were reviewed for:

• Disabling expose_php to hide the PHP version.
• Setting appropriate memory_limit and max_execution_time to prevent resource exhaustion attacks.
• Ensuring PHP error reporting was set to E_ALL & ~E_DEPRECATED & ~E_STRICT in production, with errors logged to a file rather than displayed to users.

Post-Mitigation Testing & Monitoring

Following the implementation of these changes, a comprehensive re-test was conducted using both SAST and DAST methodologies. Automated scans were re-run, and manual penetration testing focused on the previously identified SQLi vectors. We confirmed that all identified vulnerabilities were successfully mitigated and that the application now correctly handles untrusted input.

Crucially, we also implemented enhanced logging and monitoring. Application logs were configured to capture detailed information about database queries, including parameters used (after sanitization for logging sensitive data). We set up alerts for:

• Anomalous database query patterns.
• High rates of input validation failures.
• Unusual HTTP request payloads targeting security-sensitive endpoints.
• Failed login attempts or suspicious checkout attempts.

This proactive monitoring allows for the rapid detection of any new or emerging threats, ensuring the continued security of the enterprise stack.