Preventing SQL Injection

In the Input Validation and Sanitization lesson, the validated quantity was still determined by the client; validation only narrowed the valid set. If the same value enters a query text, the question changes: who determines the text of the query? If the text is built from the client’s value, then what the query will do is built from it too.

SE2. In this lesson the value that enters the query is a neutral name: alice and bob. Both names are valid user names; what is measured is whether the query text changes when the name changes. No input that would break the query text is written or tried. All queries run in the in-memory database.

Shared Data

The measurements use the same small table: three users, deliberately inserted out of alphabetical order. The file is only written to disk.

<?php
// data.php — the in-memory database and three users
declare(strict_types=1);

function connect(): PDO {
    $pdo = new PDO('sqlite::memory:');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
    foreach (['bob', 'carol', 'alice'] as $name) {
        $pdo->prepare('INSERT INTO users (name) VALUES (?)')->execute([$name]);
    }
    return $pdo;
}

Concatenation and the Parameterized Query

In a concatenated query, the value becomes part of the text. In a parameterized query (prepared statement) the text is fixed and the value is bound in a separate step: prepare() first prepares only the text with its placeholder, and execute() passes the value separately, without placing it into that text. The text held by a prepared statement can be read from the queryString property; the measurement below compares the query text of the two methods under two names.

<?php
// query.php — the query text of the two methods
declare(strict_types=1);
require 'data.php';

function concatenatedText(string $name): string {
    return "SELECT id, name FROM users WHERE name = '" . $name . "'";
}

function parameterizedText(PDO $pdo, string $name): string {
    $statement = $pdo->prepare('SELECT id, name FROM users WHERE name = ?');
    $statement->execute([$name]);
    return $statement->queryString;
}

The file only defines the functions. The measurement calls the two methods side by side with two names.

<?php
// compare.php — the query text under two methods
declare(strict_types=1);
require 'query.php';

$pdo = connect();
foreach (['alice', 'bob'] as $name) {
    echo 'concatenation  ', concatenatedText($name), "\n";
    echo 'parameterized  ', parameterizedText($pdo, $name), "\n";
}

concatenation  SELECT id, name FROM users WHERE name = 'alice'
parameterized  SELECT id, name FROM users WHERE name = ?
concatenation  SELECT id, name FROM users WHERE name = 'bob'
parameterized  SELECT id, name FROM users WHERE name = ?

Under concatenation, the query text itself changed when the name changed: 'bob' was written into the text instead of 'alice'. Under the parameterized query the text stayed the same under both names; the name was carried outside the text, as a bound value. This difference is the whole of the security feature: that the text’s value was taken from the client means the client can change the structure of the text. When the text is fixed, the value stays only a value, whatever it is.

Party: Who Sets the Text

The same measurement connects to the course’s question. The core file is, as in every lesson, party.php; probe() computes the result without deviation and then again with each external party’s deviation, and party() picks the first party that changed it. The order is client, third party, environment; if none could change it, the server.

<?php
// party.php — who can change this result?
declare(strict_types=1);

// The parties outside the code, in decision order: every request, every external call, every deployment.
const EXTERNAL_PARTIES = ['client', 'third party', 'environment'];

/** Computes the result once without deviation, then once with each external party's deviation. */
function probe(callable $result, array $deviations): array {
    $baseline = $result([]);
    $changed = [];
    foreach (EXTERNAL_PARTIES as $party) {
        if (array_key_exists($party, $deviations)) {
            $changed[$party] = $result([$party => $deviations[$party]]) !== $baseline;
        }
    }
    return $changed;
}

/** The first external party that can change the result; the server if none can. */
function party(array $changed): string {
    foreach (EXTERNAL_PARTIES as $p) {
        if ($changed[$p] ?? false) return $p;
    }
    return 'server';
}

function partyReport(string $name, callable $result, array $deviations): string {
    $changed = probe($result, $deviations);
    $lines = [];
    foreach ($changed as $p => $c) $lines[] = $p . ($c ? ' changed' : ' could not change');
    $owner = party($changed);
    echo "$name → $owner\n    ", implode(' · ', $lines), "\n";
    return $owner;
}

The result is the query text that goes to prepare(). The client’s deviation is sending bob instead of alice.

<?php
// injection.php — the party of the query text
declare(strict_types=1);
require 'party.php';
require 'query.php';

$pdo = connect();
partyReport('concatenation', fn(array $d) => concatenatedText($d['client'] ?? 'alice'), ['client' => 'bob']);
partyReport('parameterized', fn(array $d) => parameterizedText($pdo, $d['client'] ?? 'alice'), ['client' => 'bob']);

concatenation → client
    client changed
parameterized → server
    client could not change

Under concatenation, the client changed the result. Under the parameterized query, all three parties that could change the text could not; the remaining owner is the server, because the text is written as a constant in the code. There is no narrowing here like escaping or validation: the owner of the query text has changed hands completely. Validation narrows the set of the value and the client remains; a prepared statement puts the text somewhere the client cannot reach.

The Bounding Measurement

Binding does not work everywhere: a placeholder stands only in place of a value; parts of the query structure such as a column name, a table name or a sort direction cannot be bound. A person who writes ORDER BY ? and binds a column name has not bound the ordering; they have bound a number or a string constant. Below, the same users table is to be ordered by the name column; with binding, the name of the requested column is passed.

<?php
// ordering.php — binding and the allow list
declare(strict_types=1);
require 'data.php';

const ORDER_ALLOW_LIST = ['name' => 'name', 'id' => 'id'];

function orderByBinding(PDO $pdo, string $column): array {
    $statement = $pdo->prepare('SELECT name FROM users ORDER BY ?');
    $statement->execute([$column]);
    return $statement->fetchAll(PDO::FETCH_COLUMN);
}

function orderByAllowList(PDO $pdo, string $request): array {
    $column = ORDER_ALLOW_LIST[$request] ?? 'id';
    return $pdo->query('SELECT name FROM users ORDER BY ' . $column)->fetchAll(PDO::FETCH_COLUMN);
}

$pdo = connect();
echo 'binding       ', implode(', ', orderByBinding($pdo, 'name')), "\n";
echo 'allow list    ', implode(', ', orderByAllowList($pdo, 'name')), "\n";
echo 'not in list   ', implode(', ', orderByAllowList($pdo, 'password')), "\n";

binding       bob, carol, alice
allow list    alice, bob, carol
not in list   bob, carol, alice

Ordering with binding did not give the expected result: the rows came in insertion order (bob, carol, alice), not in name order. The bound 'name' was read not as a column name but as a string value that is the same in every row, and the ordering changed nothing. The query did not raise an error either; the fault is silent. The allow list really applied the ordering (alice, bob, carol), and the request password, which is not in the list, fell back to the default id order without raising an error. Even when a column name does enter the query, the place it enters from is a list written as a constant in the code; the client’s value only selects a key from the list.

Summary

• The measured class of this lesson is the client under concatenation and the server under the parameterized query: in the second, only the constant string given to prepare() determines the query text.
• Under concatenation, changing the name from alice to bob changed the query text; under the parameterized query the text stayed the same under both names.
• A prepared statement and binding separate the value from the text; the defense takes the result away from its owner and does not stop at narrowing.
• Binding is only for values: ORDER BY ? did not change the ordering, and a column name could not be bound.
• If a part of the query structure comes from the client, the solution is to choose from a constant allow list inside the code; a request that is not in the list falls back to the default.

Next Step

Taking the query text to the server closed one road. If the same value is this time printed onto a page, who determines the text of the output? The Preventing Cross-Site Scripting lesson measures escaping at output time with the same party question.