Vengala Vinay

Having 9+ Years of Experience in Software Development

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

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

Auditing a High-Traffic Magento 2 Enterprise Stack on OVH

Our engagement began with a critical security audit of a high-traffic Magento 2 Enterprise Edition (now Adobe Commerce) deployment hosted on OVH. The primary concern was a recent spike in suspicious activity and a potential data breach. The stack was complex, involving multiple Magento instances, a heavily customized checkout process, a large product catalog, and significant third-party integrations. The infrastructure comprised several OVH Dedicated Servers, load balancers (likely HAProxy or a similar solution), a clustered MySQL setup (Percona XtraDB Cluster), Redis for caching, and Varnish for HTTP acceleration.

Initial Reconnaissance and Vulnerability Scanning

The first phase involved comprehensive reconnaissance. We utilized a combination of automated tools and manual inspection to map the attack surface. This included:

  • Network Scanning: Nmap was used to identify open ports and running services across all exposed IPs. We focused on web ports (80, 443), SSH (22), database ports (3306), and any other non-standard ports that might indicate custom services.
  • Web Application Scanning: Tools like OWASP ZAP and Burp Suite were configured to crawl and actively scan the Magento frontend and admin interfaces. Special attention was paid to API endpoints and any custom AJAX calls originating from the frontend.
  • Configuration Review: We requested read-only access to server configurations (Nginx/Apache, PHP-FPM, MySQL, HAProxy) and Magento’s `app/etc/env.php` to understand the deployment’s architecture and security posture.
  • Log Analysis: Access to web server logs (Nginx/Apache access and error logs), Magento application logs (`var/log/`), and MySQL slow query logs was crucial for identifying anomalous patterns.

Deep Dive into Customized Checkout Queries

The most critical area of investigation was the customized checkout process. This is a common area for vulnerabilities due to the complexity and the frequent need for custom logic to handle unique business requirements. Our analysis revealed that several custom modules, developed in-house or by a third-party agency, were directly interacting with the Magento database using dynamically constructed SQL queries. This is a significant red flag for SQL injection (SQLi) risks.

Identifying the Vulnerable Code Path

Through code review and dynamic analysis with Burp Suite’s scanner, we pinpointed a specific module responsible for calculating shipping costs based on complex business rules. This module was fetching data from custom tables and also querying core Magento tables like `sales_order` and `quote_address` using parameters passed directly from the user’s session and POST data. The problematic code snippet, simplified for illustration, looked something like this:

Example Vulnerable PHP Code (Illustrative)

Imagine a scenario where a developer, aiming for performance or simplicity, bypassed Magento’s ORM (Object-Relational Mapper) for a specific, complex query. This is a common pitfall.

// Hypothetical vulnerable code within a custom module's helper or model
public function getCustomShippingData($customerId, $addressId) {
    $readConnection = \Magento\Framework\App\ObjectManager::getInstance()
        ->get(\Magento\Framework\App\ResourceConnection::class)
        ->getConnection('read');

    // WARNING: This query is vulnerable to SQL Injection!
    $sql = "SELECT * FROM custom_shipping_rules WHERE customer_id = " . (int)$customerId . " AND address_id = " . (int)$addressId;

    // Further complex logic might involve joining with sales_order or quote_address
    // using data directly from user input without proper sanitization.
    // For example, if $addressId was derived from $_POST['shipping_address_id']
    // without proper validation/escaping.

    $result = $readConnection->fetchAll($sql);
    return $result;
}

In this simplified example, if `$customerId` or `$addressId` were not strictly integers or were manipulated to include SQL fragments, an attacker could inject malicious SQL. A more realistic scenario might involve string concatenation for table names or column names derived from user input, which is even more dangerous.

Exploitation Vector

An attacker could exploit this by manipulating parameters sent to the server. For instance, if the checkout process allowed a user to select a pre-saved address, and the `address_id` was passed as a GET or POST parameter, an attacker could craft a request like:

GET /checkout/customshipping?address_id=123+OR+1=1--&customer_id=456 HTTP/1.1
Host: example.com
...

This could lead to:

  • Data Exfiltration: Extracting sensitive customer data (PII, order history) from the database.
  • Data Manipulation: Modifying order details, prices, or user accounts.
  • Denial of Service: Injecting queries that consume excessive server resources.
  • Bypassing Authentication/Authorization: If such queries were used in admin-facing parts of the application.

Mitigation Strategy: Prepared Statements and Input Validation

The most robust solution for preventing SQLi is the use of prepared statements with parameterized queries. This separates the SQL code from the data, ensuring that any input is treated strictly as data and not as executable SQL commands. Magento’s database abstraction layer (based on PDO) fully supports this.

Refactoring Vulnerable Code

We refactored the identified vulnerable code to use prepared statements. The `Zend_Db_Adapter_Pdo_Mysql` (which Magento’s `readConnection` ultimately uses) provides methods for this. The corrected code would look like this:

// Refactored code using prepared statements
public function getCustomShippingData($customerId, $addressId) {
    $readConnection = \Magento\Framework\App\ObjectManager::getInstance()
        ->get(\Magento\Framework\App\ResourceConnection::class)
        ->getConnection('read');

    // Define the SQL query with placeholders
    $sql = "SELECT * FROM custom_shipping_rules WHERE customer_id = :customerId AND address_id = :addressId";

    // Define the parameters and their types
    $params = [
        ':customerId' => $customerId,
        ':addressId'  => $addressId
    ];

    // Execute the query using prepared statements
    $stmt = $readConnection->prepare($sql);
    $result = $stmt->execute($params); // execute() returns true/false, fetchAll() is called on the statement object

    // Fetching results (example using fetchAll)
    $fetchedData = $stmt->fetchAll(\PDO::FETCH_ASSOC);

    return $fetchedData;
}

In this corrected version, the values for `:customerId` and `:addressId` are sent to the database server separately from the SQL query itself. The database engine then safely substitutes these values into the query, preventing any malicious SQL code from being executed.

Enhanced Input Validation

Beyond prepared statements, rigorous input validation at the application layer is essential. For any data coming from external sources (user input, API calls, etc.), we implemented:

  • Type Casting: Explicitly casting variables to their expected types (e.g., `(int)$customerId`).
  • Whitelisting: Allowing only known-good characters or patterns for string inputs.
  • Data Range Checks: Ensuring numerical inputs fall within acceptable bounds.
  • Magento’s Validation Framework: Leveraging Magento’s built-in validation rules defined in `di.xml` and `validation_rules.xml` for form data and API inputs.

Infrastructure-Level Hardening on OVH

While code-level fixes are paramount, infrastructure hardening complements the security strategy. For the OVH stack, we focused on:

Firewall Rules and Network Segmentation

Ensuring that only necessary ports were open to the public internet. For internal communication between database servers, application servers, and caching layers, strict firewall rules were implemented. OVH’s firewall management tools were configured to restrict access based on source IP addresses where possible.

# Example iptables rule on an application server
# Allow incoming traffic only from load balancer IP on port 80/443
sudo iptables -A INPUT -p tcp --dport 80 -s 192.168.1.10 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 443 -s 192.168.1.10 -j ACCEPT

# Allow outgoing traffic to database server on port 3306
sudo iptables -A OUTPUT -p tcp --dport 3306 -d 192.168.1.20 -j ACCEPT

# Drop all other incoming traffic by default
sudo iptables -P INPUT DROP

Web Application Firewall (WAF)

While not explicitly part of the OVH managed services, we recommended and helped configure a WAF solution (e.g., ModSecurity with OWASP Core Rule Set, or a cloud-based WAF like Cloudflare/AWS WAF if applicable). This provides an additional layer of defense against common web attacks, including SQLi, XSS, and LFI, by inspecting HTTP traffic before it reaches the application servers.

Database Security Best Practices

For the Percona XtraDB Cluster, we reviewed:

  • User Privileges: Ensuring database users had the minimum necessary privileges. Magento’s database user should not have `GRANT OPTION` or broad administrative rights.
  • Network Access: Restricting direct access to the database cluster from the internet. All application servers should connect via specific, allowed IPs.
  • Encryption: Enabling SSL/TLS for database connections between application servers and the database cluster.
  • Regular Patching: Ensuring MySQL/Percona versions were up-to-date with security patches.

Secure Coding Standards and Developer Training

The long-term solution involves embedding security into the development lifecycle. We provided recommendations for:

  • Mandatory Code Reviews: Incorporating security checks into the pull request process.
  • Static Application Security Testing (SAST): Integrating tools like SonarQube or PHPStan with security rules into the CI/CD pipeline.
  • Dynamic Application Security Testing (DAST): Regularly running automated scans (like OWASP ZAP) against staging environments.
  • Developer Training: Educating developers on common vulnerabilities (OWASP Top 10) and secure coding practices, specifically for PHP and Magento development.

Post-Mitigation Verification and Monitoring

After implementing the code changes and infrastructure adjustments, a thorough verification process was conducted. This involved:

  • Re-scanning: Running the same automated vulnerability scans (OWASP ZAP, Burp Suite) to confirm the SQLi vulnerabilities were no longer exploitable.
  • Penetration Testing: Performing targeted manual penetration tests against the previously vulnerable areas.
  • Log Monitoring: Setting up enhanced monitoring and alerting on suspicious database queries (e.g., using MySQL slow query logs with specific thresholds or audit plugins) and application errors.
  • Performance Monitoring: Ensuring the refactored code and security measures did not negatively impact the performance of the high-traffic Magento stack.

This comprehensive approach, combining deep code analysis, secure coding practices, and infrastructure hardening, successfully mitigated the identified SQL injection vulnerabilities and significantly improved the overall security posture of the Magento 2 Enterprise stack on OVH.