PDO Prepared Statements in PHP: A Runnable SQLite Example

A product name can contain a quote without being an SQL expression. The same input path can also receive text that looks like an attempt to change the query. Your PHP code should send both as values to a fixed SQL statement rather than assembling executable SQL from the supplied text.

This example uses PHP PDO and an in-memory SQLite database to make that boundary visible. It creates three fictional products, prepares a search statement, binds one name and prints the matching rows. There is no MySQL server, Composer package, account or database password to configure. The complete script and README are in the downloadable example ZIP.

Check the prerequisites

Use PHP 8.1 or later from a terminal, with PDO and the pdo_sqlite driver enabled. The checks for this article ran on PHP 8.2.12; a separate PHP 8.1 runtime was not tested. Run these commands to see the configuration file and drivers used by your terminal:

php --ini
php -r "print_r(PDO::getAvailableDrivers());"

The driver list must include sqlite. A web server and a terminal can use different PHP installations, so a working browser application does not prove that your CLI has the same extensions. Enable the driver in the configuration for the actual executable you are running. This example does not edit that configuration automatically.

Save the complete script as demo.php

The database lives only for this run. The schema and seed data are fixed in the source, and the insert statement is reused for each sample row. The searched name arrives as one optional command-line argument; when omitted, it defaults to Desk Lamp. Names are trimmed and checked for valid UTF-8 and a maximum of 80 bytes before the database is created.

<?php
declare(strict_types=1);

if (PHP_SAPI !== 'cli') {
    http_response_code(403);
    header('Content-Type: text/plain; charset=utf-8');
    exit('Run this example from the command line.');
}

if ($argc > 2) {
    fwrite(STDERR, "Usage: php demo.php [product-name]\n");
    exit(2);
}
$name = trim($argv[1] ?? 'Desk Lamp');
if ($name === '' || strlen($name) > 80 || preg_match('//u', $name) !== 1) {
    fwrite(STDERR, "Use a nonempty UTF-8 product name of at most 80 bytes.\n");
    exit(2);
}
if (!class_exists(PDO::class) || !in_array('sqlite', PDO::getAvailableDrivers(), true)) {
    fwrite(STDERR, "Enable PDO and the pdo_sqlite driver for this CLI PHP installation.\n");
    exit(1);
}

try {
    // A fresh temporary database for each run; no files or server credentials.
    $pdo = new PDO('sqlite::memory:', null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    $pdo->exec('CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
    )');

    $insert = $pdo->prepare(
        'INSERT INTO products (id, name, price_cents) VALUES (?, ?, ?)'
    );
    foreach ([[1, 'Desk Lamp', 159900], [2, "Reader's Notebook", 29900], [3, 'USB-C Cable', 49900]] as $row) {
        $insert->execute($row);
    }

    // The SQL template stays fixed. The searched name is a separate value.
    $query = $pdo->prepare(
        'SELECT id, name, price_cents FROM products WHERE name = :name ORDER BY id LIMIT 5'
    );
    $query->bindValue(':name', $name, PDO::PARAM_STR);
    $query->execute();
    $matches = $query->fetchAll();

    echo json_encode(
        ['query' => $name, 'matches' => $matches],
        JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR
    ) . PHP_EOL;
} catch (PDOException $error) {
    fwrite(STDERR, "Database operation failed. Review the local schema and driver setup.\n");
    exit(1);
}

Run a query and inspect the result

Extract the ZIP, open a terminal inside php-pdo-prepared-statements and run:

php demo.php

The expected output is:

{
    "query": "Desk Lamp",
    "matches": [
        {
            "id": 1,
            "name": "Desk Lamp",
            "price_cents": 159900
        }
    ]
}

The integer amount is fictional sample data, not a current product offer. The output uses a structured representation so the input and returned rows are easy to inspect. The important part of the example is the SQL/value boundary; changing how the result is displayed does not change how the query is prepared.

Follow prepare, bind and execute

The select statement contains a named placeholder where a product name belongs. The SQL text is prepared first, the name is bound as a string, and execution supplies that value to the statement. The placeholder is not enclosed in SQL quotes. Do not add manual quote escaping to the already bound name: that would change the value you are trying to find.

This is why the row named Reader's Notebook can be inserted and searched without removing its apostrophe. The PHP prepared-statement guide describes separating parameter values from SQL, and the bindValue reference documents the explicit type used here.

Try normal and SQL-looking input

php demo.php "Reader's Notebook"
php demo.php "Unknown product"
php demo.php "' OR 1=1 --"

The quoted product name returns one row. The unknown name returns an empty matches array. The SQL-looking string also returns an empty array: the fixed statement treats it as the name to compare, rather than a condition that can select the whole table. This check demonstrates this specific query boundary, not a complete security review of an application.

The example performs exact equality. With this sample schema, desk lamp does not match Desk Lamp. It is not a substring search, and adding percent signs to a value does not turn equality into LIKE. If your application needs case-insensitive or partial matching, define those rules deliberately and test them with the chosen database.

What was actually checked

Ten checks ran against PHP 8.2.12 on Windows. The missing-driver check used php -n, which omits the normal configuration; the ordinary checks used the installed SQLite driver. Startup warnings from a pre-existing local PHP configuration were directed to stderr so they were not confused with the script's output. No MySQL integration or production load test is claimed.

Know what a placeholder cannot do

A parameter represents a value, not a table name, column name or an entire ORDER BY clause. If a later version accepts a sort option, map it to a small allowlist of fixed SQL fragments in your code rather than treating a placeholder as an identifier. See the PDO prepare reference for the placeholder rules.

Keep the schema-creation statement fixed as well. Preparing the search does not protect another statement that concatenates untrusted SQL elsewhere. In this example, exec receives only a source-controlled schema string. The insert and search paths pass values separately.

Understand the in-memory database limit

The sqlite::memory: connection is recreated for every script run. That makes the fixture easy to repeat without leaving database files, but it does not preserve edits or model multiple users. The PDO SQLite DSN documentation explains the connection format. Switching to MySQL also requires a different DSN, credentials and checking driver-specific behavior; it is not tested by this SQLite demo.

The script intentionally refuses execution through a web server. When moving the query into a real endpoint, add server-side authorization, deliberate request validation and a controlled response format. Keep connection details out of public source and return application errors without exposing private database diagnostics. A parameterized query is one part of that endpoint, not its entire design.

Keep this tiny fixture while replacing the existing PHP/AJAX search demo's fixed array with database access. Verify the SQL result separately before debugging the request and browser rendering. Once both examples work independently, connect them and retain the quoted-name and no-match cases as integration checks.