SQL Injection

SQL injection occurs when untrusted data changes the structure of a database query. Prepared statements keep query structure separate from parameter values.

What Matters

• Use prepared statements for values supplied to SQL queries.
• Never build SQL by concatenating request values into query text.
• Use allow-lists for identifiers such as sort columns because placeholders bind values, not SQL syntax.
• Use least-privilege database credentials.
• Review raw SQL inside frameworks as carefully as standalone PDO code.

Practical Example

<?php

declare(strict_types=1);

function productQuery(string $search): array
{
    return [
        'sql' => 'SELECT id, name FROM products WHERE name LIKE :term',
        'params' => ['term' => '%' . $search . '%'],
    ];
}

print_r(productQuery("lamp' OR 1=1 --"));

// Prints:
// [sql] => SELECT id, name FROM products WHERE name LIKE :term

In Application Work

Search boxes, login forms, filters, report builders, imports, and admin tools all create SQL boundaries. The same rule applies to data read from queues or third-party systems.

Bind Values, Allow-List Structure

Prepared-statement placeholders represent values. They cannot safely stand in for table names, column names, or SQL keywords.

<?php

declare(strict_types=1);

function productSearch(string $term, string $requestedSort): array
{
    $sortColumns = [
        'name' => 'name',
        'price' => 'price_cents',
        'newest' => 'created_at',
    ];

    if (!isset($sortColumns[$requestedSort])) {
        throw new InvalidArgumentException('Sort option is not allowed.');
    }

    return [
        'sql' => 'SELECT id, name FROM products WHERE name LIKE :term ORDER BY ' . $sortColumns[$requestedSort],
        'params' => ['term' => '%' . $term . '%'],
    ];
}

print_r(productSearch('lamp', 'price'));

The search value is bound later. The sort column is selected from code-owned values.

Prepared Statements Are Not Authorisation

A perfectly prepared query can still expose another user's invoice. Apply ownership or permission conditions as part of the query or before returning the record.

Use a database account with only the privileges the application needs. SQL injection is less damaging when the web process cannot create database users, drop unrelated schemas, or access administrative tables.

What To Check

Before moving on, make sure you can:

• separate SQL structure from bound values;
• use allow-lists for dynamic SQL identifiers;
• recognise injection risk outside form submissions;
• apply least privilege to database credentials;
• explain why prepared statements do not replace record-level authorisation.

Values And SQL Structure Are Different

Prepared statements protect data values because the database parses the SQL command separately from bound parameters. A parameter remains data even when it contains quotes, comments, or SQL keywords.

Placeholders generally cannot stand for structural elements such as:

• table or column identifiers;
• ASC or DESC;
• operators;
• complete WHERE clauses;
• a variable number of placeholders;
• SQL functions or expressions.

Attempting to bind a column name either produces an error or treats the name as a string value. Dynamic structure therefore needs a different technique: translate a small, validated application vocabulary into SQL fragments written by the developer.

Allowlist Dynamic Sorts And Columns

Suppose an endpoint accepts sort=newest, sort=price_low, or sort=price_high. Do not copy the request value after ORDER BY.

<?php

declare(strict_types=1);

function orderByFor(string $sort): string
{
    return match ($sort) {
        'newest' => 'created_at DESC',
        'price_low' => 'price_cents ASC, id ASC',
        'price_high' => 'price_cents DESC, id ASC',
        default => throw new InvalidArgumentException('Unsupported sort.'),
    };
}

Every returned fragment is application-owned SQL. Unknown input is rejected. Adding a new sort requires a reviewed code change and a test.

Use the same pattern for report columns, aggregate expressions, and operator choices. The external vocabulary should be smaller and more stable than the database schema. Avoid accepting raw column names merely because the requested name currently matches a real column.

Generate Placeholder Lists Safely

A placeholder represents one value, not an arbitrary comma-separated list. For a bounded list of IDs, validate and normalize the values, generate one placeholder per value, then bind each value.

<?php

declare(strict_types=1);

function idPlaceholders(array $ids): array
{
    if ($ids === []) {
        throw new InvalidArgumentException('At least one ID is required.');
    }

    $normalized = [];

    foreach ($ids as $id) {
        if (!is_int($id) || $id < 1) {
            throw new InvalidArgumentException('IDs must be positive integers.');
        }

        $normalized[] = $id;
    }

    return [
        'sql' => implode(', ', array_fill(0, count($normalized), '?')),
        'values' => $normalized,
    ];
}

Impose a maximum list length. Very large lists can exceed driver limits or produce poor plans even when they are injection-safe. For bulk work, a temporary table, uploaded data set, or engine-specific array feature may be more appropriate.

Prepared Statements Do Not Replace Authorization

A query can be perfectly parameterized and still expose another user's records. Tenant, ownership, and permission filters must be part of the query or enforced through a trustworthy data-access boundary.

For example, loading an order by id = :id is not enough when users may access only their own orders. Include the authorized owner or tenant in the predicate and handle a missing row without revealing whether an inaccessible record exists.

Least-privilege database credentials provide another layer. The web application should not connect as a schema administrator. Read-only reporting processes should not have write access. Migration credentials should not be available to ordinary requests.

Escaping Is Not A General Repair

Legacy code may use database-specific escaping functions. Escaping requires the correct connection, character set, and quoting context, and it is easy to omit in one path. It also does nothing for dynamic identifiers or operators.

Move value handling to prepared statements instead of trying to perfect scattered string concatenation. During migration, add tests around the original behavior, replace one query boundary at a time, and inspect the resulting SQL and bound values.

Do not write a custom escaping helper that gives concatenated SQL a safe-looking API. It can encourage continued structural mixing and obscure places that still need allowlists.

ORMs And Query Builders

ORMs and query builders usually bind ordinary values, but raw-expression APIs remain dangerous. Methods named raw, literal, whereRaw, or similar deserve the same review as hand-written SQL.

Dynamic property names can also become identifiers. Confirm whether the library allowlists or quotes them, and prefer an explicit application mapping. Do not assume a fluent API makes an untrusted fragment safe.

Stored procedures are not automatically immune. A procedure that concatenates input into dynamic SQL can still be injectable. Review the SQL boundary wherever command text is constructed, regardless of which layer constructs it.

Error Handling And Logging

Public errors should not expose SQL text, table names, driver messages, or stack traces. Return a stable application error and log the internal exception with an incident identifier.

Logs also need restraint. Recording a prepared SQL template may be useful, but complete parameter values can contain passwords, tokens, personal data, or search terms. Use redaction and structured metadata. Security diagnostics should help locate the query path without creating a second sensitive-data store.

Testing SQL Injection Defenses

Test more than one famous quote payload. Useful cases include:

• quotes and comment markers in ordinary text values;
• Unicode and connection-character-set boundaries;
• malformed integer lists;
• unknown sort keys and directions;
• raw-expression or report-column parameters;
• requests for another tenant's data;
• database users attempting denied statements;
• error responses that must not reveal schema detail.

The strongest integration test sends input through the actual HTTP or command boundary, executes against the production database engine, and verifies both returned data and durable state. SQLite can be useful for fast tests, but it does not prove MySQL or PostgreSQL driver and syntax behavior.

Code review and static search can supplement tests. Search for direct execution methods, string interpolation near SQL keywords, and raw query-builder calls. Treat every match as a review point rather than automatically labeling it vulnerable.

A Safe Review Sequence

For every dynamic query:

1. Mark each untrusted input.
2. Separate value positions from structural positions.
3. Bind every value with an appropriate type.
4. Map every structural choice through an application allowlist.
5. Add authorization predicates.
6. Bound list sizes and result sizes.
7. use a least-privilege connection.
8. Test rejected input and inspect public errors.

Stored Data Can Become A Second-Order Injection

Input does not become trusted merely because it was previously stored in the database. A user may save a report name, JSON filter, import mapping, or sort expression that another process later uses to assemble SQL. The first request can be safely parameterized while the later report job treats the stored text as query structure. This delayed path is called second-order injection.

Apply the same value-versus-structure analysis whenever stored data re-enters a query builder. A saved column choice should contain an application-owned identifier such as created_date, not raw SQL such as orders.created_at DESC. At execution time, map the identifier through the current allowlist. This also lets schema changes update one mapping instead of rewriting user-controlled fragments.

Multi-tenant applications need an additional invariant: every query must remain inside the authenticated tenant boundary. Binding a tenant ID prevents syntax injection, but it does not guarantee that the correct tenant ID was selected or that every join includes the tenant relationship. Put tenant scoping in a repository boundary that is difficult to bypass, use database row-level security where it fits the platform, and test with records from at least two tenants. A useful negative test authenticates as tenant A, submits identifiers belonging to tenant B, and proves that no row is read or changed.

Database permissions reduce impact when an application defect survives. The web process normally should not connect as a schema owner, create users, or modify unrelated databases. Reporting jobs may need read-only credentials, while migration tooling can use a separate identity available only during deployment. Least privilege does not make unsafe SQL acceptable, but it limits what an injected command can reach.

Treat observability as part of the defense. Record a stable query name, duration, row count, and database error category rather than logging full SQL with secrets or personal data. Parameter values may contain passwords, tokens, search terms, and customer records. Redact them by default and grant access to database diagnostics according to operational need.

Include delayed execution in security tests. Save hostile-looking text through a legitimate form, then run exports, scheduled jobs, administrative searches, and background consumers that reuse it. Review dynamic ORDER BY, identifiers, JSON-path expressions, full-text syntax, and raw ORM fragments separately because ordinary value placeholders cannot represent those grammar positions.

After this lesson, you should be able to explain why parameter binding protects values but not SQL structure, build safe allowlists for dynamic reports and sorting, generate bounded placeholder lists, review ORM raw expressions, enforce authorization independently, and verify defenses against the real database engine.