Understanding and Reliably Preventing SQL Injection

1. Why SQL injection is so dangerous

SQL injection has been in the OWASP Top 10 for more than two decades and still sits at the top today under category A03:2021 Injection. The reason is simple: a successful SQL injection does not just grant an attacker read access to individual records, it can expose the entire database, often including customer data, password hashes and payment information, without any valid authentication being needed. In many cases union-based injection or stored procedures even allow write access, data manipulation, or dropping files onto the server via functions like INTO OUTFILE, which can open the door to remote code execution.

The fact that SQL injection still shows up regularly in security incidents despite decades of awareness rarely stems from a lack of knowledge, but from concrete code locations: legacy scripts written before PDO became widespread, custom modules with their own SQL statements for reports or exports, and ORM fallbacks like whereRaw() or query() that developers fill with string concatenation instead of parameters under time pressure. Especially in Magento projects with many third-party modules and custom extensions, a single unreviewed piece of code with direct string concatenation is enough to compromise the entire shop, including the customer database.

2. How SQL injection works mechanically

The mechanism behind SQL injection is structurally simple: an application builds a SQL query string by inserting user input directly into the string instead of treating it as separate data. A query like SELECT * FROM customer WHERE email = '" . $_POST['email'] . "' expects the input to be a harmless email address. If an attacker instead sends the string ' OR '1'='1, the concatenation produces the condition WHERE email = '' OR '1'='1', which is true for every row in the table. The query suddenly returns every customer record, not just the one matching the given email address.

Crucially, the attacker does not just alter conditions, they control the entire query syntax. With a closing quote followed by a comment marker like -- or #, the rest of the original query can be commented out, so any subsequent conditions like AND active = 1 no longer apply. Some database drivers additionally allow stacked queries, where a semicolon introduces a second, completely separate statement, for example a DROP TABLE. Whether stacked queries work depends on the driver in use: PDO with MySQL does not allow them by default, but PHP's mysqli does in certain configurations.

3. Classic, union-based and blind/time-based injection

In classic, error-based or union-based SQL injection, the attacker sees the effect of their input directly in the application's response: an error message reveals the database structure, or a UNION SELECT appends extra columns from any table to the regular result and displays them in the frontend, for example in a product list or search result page. This requires the number and data type of columns in the UNION SELECT to match the original query, which attackers quickly work out by systematic trial and error with ORDER BY.

Blind SQL injection is used when the application shows no database errors or raw data. With boolean-based blind injection, the attacker sends conditions like AND 1=1 and AND 1=2 and observes whether the response differs, for example a visible product versus an empty page. Even more subtle is time-based blind injection: with a condition like AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0), the response time becomes the signal. If the server responds five seconds later, the condition is true. Character by character, an entire password hash can be extracted this way, even if the application shows no visible difference at all.

-- Original application query (built via string concatenation)
-- SELECT id, name, price FROM product WHERE category_id = <input>

-- 1. UNION-based: append attacker-chosen columns to the result set
1 UNION SELECT username, password_hash, NULL FROM admin_user --

-- 2. Boolean-based blind: compare responses for true vs. false conditions
1 AND 1=1   -- page renders normally
1 AND 1=2   -- page renders empty or different

-- 3. Time-based blind: infer data one character at a time via delay
1 AND IF(SUBSTRING((SELECT password_hash FROM admin_user LIMIT 1),1,1)='a', SLEEP(5), 0)

4. Prepared statements and parameterized queries as the real fix

The only structurally reliable protection against SQL injection is prepared statements with parameterized queries. The decisive difference from string concatenation lies in the sequence: the application first sends only the query structure with placeholders to the database, for example SELECT * FROM customer WHERE email = ?. The database driver parses and compiles this structure completely before a single user value is even transmitted. Only afterwards are the actual values sent in a separate step over the binary protocol and bound to the already compiled query.

Because the values never reach the SQL parser, they fundamentally cannot alter the query structure anymore. A quote or a comment marker inside the input value simply stays part of the string, no matter what it looks like. This fundamentally distinguishes prepared statements from escaping functions, which try to neutralize dangerous characters in advance but always remain in the same string context that the attacker also controls. In PHP, PDO handles this separation with bindParam() or directly when calling execute() with a value array, without developers needing to escape anything manually.

<?php
declare(strict_types=1);

// VULNERABLE: user input concatenated directly into the SQL string
function findCustomerByEmailUnsafe(PDO $pdo, string $email): array|false
{
    // An attacker sending "' OR '1'='1" turns this into a query
    // that matches every row in the table.
    $sql = "SELECT * FROM customer WHERE email = '" . $email . "'";
    $result = $pdo->query($sql);

    return $result->fetch(PDO::FETCH_ASSOC);
}

// SECURE: prepared statement, value bound as data, not SQL
function findCustomerByEmailSafe(PDO $pdo, string $email): array|false
{
    $stmt = $pdo->prepare('SELECT * FROM customer WHERE email = :email');
    $stmt->execute(['email' => $email]);

    return $stmt->fetch(PDO::FETCH_ASSOC);
}

5. Why escaping alone is not enough

Escaping functions like mysqli_real_escape_string() or addslashes() were long considered sufficient protection, but they are structurally less safe than prepared statements. They try to mask special characters like quotes in advance, but still operate in the same string that the application subsequently inserts into the query via concatenation. This opens up bypass opportunities tied to the query context: in numeric contexts without surrounding quotes, for example WHERE id = " . $id, escaping does not help at all, because there is no quote to escape. A value like 1 OR 1=1 works unchanged.

Also historically well known is the bypass via multi-byte character sets: with a misconfigured character set like GBK, a leading backslash escape character could combine with the following byte to form a valid multi-byte character, causing the actual escape character to disappear from the resulting string and reactivating the following quote. Bugs like this show that escaping depends on the correct character set configuration of the connection and is therefore an additional source of error that structurally does not exist with prepared statements, because values are never embedded into a string context at all.

6. ORM and query builder safety

Modern ORMs like Doctrine or query builders like Laravel's use parameterized queries internally by default and are safe against SQL injection under normal use. A call like $qb->where('email = :email')->setParameter('email', $input) binds the value exactly like a manual prepared statement. However, the security promise of an ORM only applies to the default path, not to every API method the ORM offers, and that is exactly where most gaps arise in practice.

Methods like whereRaw(), DB::statement(), directly embedded strings, or orderBy() with a column name taken from the request completely bypass parameterization the moment a developer inserts user input via string interpolation. ORDER BY and table names are particularly tricky, because column and table names fundamentally cannot be passed as bind parameters. Database drivers only allow parameters for values, not for identifiers. The only safe approach here is a whitelist of allowed column names against which the user input is validated before it is inserted into the query.

7. Using Magento ResourceConnection and the Select API safely

Magento wraps database access via \Magento\Framework\App\ResourceConnection and provides a Zend_Db_Select-compatible API through getConnection()->select() that natively supports placeholders and bindings. The where() method accepts a ? placeholder pattern and binds values automatically and safely, for example $select->where('email = ?', $email). For more complex cases with dynamically composed conditions, the connection additionally offers quoteInto(), which correctly masks a value and inserts it into a condition string without developers having to escape anything manually.

In custom modules, the critical mistake is almost always the same: a developer calls $connection->query() directly with a concatenated string instead of using the Select API or bound parameters. Care is also needed with collections: addFieldToFilter('entity_id', $id) with an integer passed directly from the request is usually safe thanks to the internal Zend_Db binding, but the explicit array form addFieldToFilter('entity_id', ['eq' => $id]) makes the intent unambiguous and prevents misinterpretation with more complex conditions like ['in' => $ids] or ['like' => $term].

<?php
declare(strict_types=1);

namespace Mironsoft\Catalog\Model;

use Magento\Framework\App\ResourceConnection;

/**
 * Reads product data by SKU using safe, parameterized queries.
 */
class ProductLookup
{
    /**
     * @param ResourceConnection $resourceConnection Magento database connection resolver.
     */
    public function __construct(
        private readonly ResourceConnection $resourceConnection
    ) {
    }

    /**
     * VULNERABLE: raw string concatenation into a query.
     *
     * @param string $sku Product SKU coming from user input.
     * @return array<string, mixed>|false
     */
    public function findBySkuUnsafe(string $sku): array|false
    {
        $connection = $this->resourceConnection->getConnection();
        // Never do this: $sku could contain "' OR '1'='1"
        $query = "SELECT * FROM catalog_product_entity WHERE sku = '" . $sku . "'";

        return $connection->fetchRow($query);
    }

    /**
     * SECURE: Select API with a bound placeholder.
     *
     * @param string $sku Product SKU coming from user input.
     * @return array<string, mixed>|false
     */
    public function findBySkuSafe(string $sku): array|false
    {
        $connection = $this->resourceConnection->getConnection();
        $table = $this->resourceConnection->getTableName('catalog_product_entity');

        $select = $connection->select()
            ->from($table)
            ->where('sku = ?', $sku);

        return $connection->fetchRow($select);
    }
}

8. Common pitfalls in Magento modules and defense in depth

Typical entry points in Magento projects rarely lie in core code, but in custom extensions: setup scripts and data patches that process bulk data with raw query() calls during migrations, custom admin grids with their own sorting and filtering logic, and reporting modules that take column names or sort direction directly from GET parameters. Since column names, as mentioned, cannot be parameterized, any ORDER BY logic that reacts to user input needs a fixed whitelist of allowed values against which it is validated before the value reaches the query.

Prepared statements are the primary line of defense, but should be complemented by further layers. The database user that the Magento application connects with should only have the privileges it actually needs: no FILE privilege for INTO OUTFILE, no DROP on production tables, and no access to system-level databases. A web application firewall can intercept known injection patterns like UNION SELECT or SLEEP() before they reach the application, but it never replaces safe query construction in code, it only reduces the window of exposure until a patch is available.

In addition, continuous logging of database errors and unusually slow queries helps, since time-based blind injection stands out through artificial delays once outliers in query runtime are systematically evaluated. A simple audit script that regularly scans the code for dangerous patterns complements automated static analysis and manual code reviews ahead of every release.

#!/usr/bin/env bash
# audit-sql-concat.sh - find likely unsafe SQL concatenation in the codebase
# Run before every release as part of the security checklist.
set -euo pipefail

echo "[*] Scanning app/code for raw query() calls with string concatenation..."
grep -rnE "->query\(.*\.\s*\\\$" app/code --include="*.php" || true

echo "[*] Scanning for direct variable interpolation in SQL strings..."
grep -rnE "SELECT .*\\\$[a-zA-Z_]+" app/code --include="*.php" || true

echo "[*] Scanning for ORDER BY built from request parameters..."
grep -rnE "ORDER BY.*\\\$_(GET|POST|REQUEST)" app/code --include="*.php" || true

echo "[*] Done. Review every match manually before deploying."

{
  "event": "suspicious_query_detected",
  "severity": "high",
  "timestamp": "2026-07-12T09:14:32Z",
  "source_ip": "203.0.113.42",
  "endpoint": "/catalogsearch/result",
  "signal": "time_based_delay",
  "query_duration_ms": 5023,
  "matched_pattern": "SLEEP(",
  "action_taken": "request_blocked",
  "waf_rule_id": "942100"
}

9. SQL injection protections compared

The table below compares the most common unsafe patterns from Magento and PHP projects with their safe alternatives, including the technical reasoning why the safe pattern works structurally and not just by chance.

No single pattern from the table fully replaces the others. Prepared statements prevent the injection itself, whitelisting secures the spots that fundamentally cannot be parameterized, and minimal database privileges limit the damage if a vulnerability is overlooked anyway. Only the interplay of all three layers makes a Magento application structurally robust against SQL injection.

10. Summary

SQL injection always arises from the same fundamental mistake: user input is treated as part of the SQL syntax instead of pure data. Classic, union-based and blind variants differ only in how an attacker reads out the result, not in the underlying vulnerability. Prepared statements with parameterized queries solve the problem structurally, because values never reach the SQL parser and the query structure remains fixed regardless of input content. Escaping functions, by contrast, remain in the same string context and are susceptible to charset bypasses and context errors.

In Magento projects, this means concretely: ResourceConnection and the Select API with ? placeholders and quoteInto() instead of query() with string concatenation, a fixed whitelist for column names with dynamic ORDER BY, and a database user with minimal privileges as a last line of defense in case a vulnerability is still overlooked. Anyone who consistently enforces these patterns in code reviews and automated audits permanently closes one of the most common, and at the same time most easily avoidable, vulnerability classes in the OWASP Top 10.

11. FAQ: Understanding and Preventing SQL Injection