security

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 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 example

<?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.

Practice

Practice: Build A Safe Product Query

Create a prepared-query plan for product search with an allowed sort column.

Requirements

Bind the search term as a parameter.
Allow only name, price_cents, or created_at as sort columns.
Reject an unsupported sort value.

Show solution

The sort column is selected from an allow-list and the search value remains a parameter.

PHP example

<?php

declare(strict_types=1);

function productSearchQuery(string $term, string $sort): array
{
    $allowedSorts = ['name', 'price_cents', 'created_at'];

    if (!in_array($sort, $allowedSorts, true)) {
        throw new InvalidArgumentException('Unsupported sort column.');
    }

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

$query = productSearchQuery('lamp', 'price_cents');

echo $query['sql'] . PHP_EOL;
echo $query['params']['term'] . PHP_EOL;

// Prints:
// SELECT id, name FROM products WHERE name LIKE :term ORDER BY price_cents
// %lamp%

Only known SQL identifiers are concatenated. User-controlled values stay in the parameter array.