CRUD with prepared statements: PHP

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrite all four CRUD operations with prepared statements, and wrap related changes in a transaction.

The one rule

Every real query involves values from outside: the product a visitor clicked, the email they typed, the quantity they chose. The tempting way is to glue those values into the SQL string. Never do it. Here is why:

php

<?php
$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, is_admin INTEGER)");
$pdo->exec("INSERT INTO users (email, is_admin) VALUES ('asha@example.com', 0), ('admin@example.com', 1)");

$typed = "nobody@example.com' OR '1'='1";   // what an attacker types into the login form
$sql = "SELECT email FROM users WHERE email = '$typed'";

echo $sql . "\n";
print_r($pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN));

result

SELECT email FROM users WHERE email = 'nobody@example.com' OR '1'='1'
Array
(
    [0] => asha@example.com
    [1] => admin@example.com
)

The visitor's quote character ended the string early, and the rest of their input became part of the SQL. The query that should have found nobody returned every user. This is SQL injection, and it has been behind many of the largest data breaches. Escaping quotes by hand is fragile. The reliable fix is a prepared statement.

Prepared statements

A prepared statement sends the SQL with placeholders where the values go, then sends the values separately. The database has already decided the structure of the query before it sees the values, so a value can never change it:

php

<?php
$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, is_admin INTEGER)");
$pdo->exec("INSERT INTO users (email, is_admin) VALUES ('asha@example.com', 0), ('admin@example.com', 1)");

$statement = $pdo->prepare('SELECT email FROM users WHERE email = ?');

$statement->execute(["nobody@example.com' OR '1'='1"]);
print_r($statement->fetchAll(PDO::FETCH_COLUMN));

$statement->execute(['asha@example.com']);
print_r($statement->fetchAll(PDO::FETCH_COLUMN));

result

Array
(
)
Array
(
    [0] => asha@example.com
)

The attack is now just an odd email address that matches nothing. Two steps every time: prepare() the SQL, then execute() it with an array of values. A prepared statement can be executed many times with different values.

Placeholders come in two styles. ? is filled by position; :name by name, which reads better when there are several:

php

$statement = $pdo->prepare('SELECT * FROM products WHERE category = :category AND price <= :max');
$statement->execute(['category' => 'Electronics', 'max' => 100]);

Placeholders stand for values only. Table names, column names and keywords such as ASC cannot be placeholders. When a visitor chooses the sort column, compare it with a list you control, as shown below.

Create: INSERT

The examples below share the products table from the previous lesson, set up in a file:

php

<?php
$pdo = new PDO('sqlite:' . __DIR__ . '/shop.sqlite', options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    price REAL NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
)');
return $pdo;

php

<?php
$pdo = require __DIR__ . '/db.php';

$insert = $pdo->prepare('INSERT INTO products (name, price, stock) VALUES (:name, :price, :stock)');

foreach ([['Mechanical Keyboard', 89.5, 60], ['Ceramic Mug Set', 18.75, 120], ['Puzzle 1000 Pieces', 19.99, 0]] as [$name, $price, $stock]) {
    $insert->execute(['name' => $name, 'price' => $price, 'stock' => $stock]);
    echo "Added $name with id " . $pdo->lastInsertId() . "\n";
}

result

Added Mechanical Keyboard with id 1
Added Ceramic Mug Set with id 2
Added Puzzle 1000 Pieces with id 3

lastInsertId() returns the AUTO_INCREMENT (or SQLite AUTOINCREMENT) value of the row just inserted, which you need to redirect to the new item's page or to insert related rows.

Read: SELECT one row and many

php

<?php
$pdo = require __DIR__ . '/db.php';

function findProduct(PDO $pdo, int $id): ?array
{
    $statement = $pdo->prepare('SELECT id, name, price, stock FROM products WHERE id = ?');
    $statement->execute([$id]);
    return $statement->fetch() ?: null;
}

print_r(findProduct($pdo, 2));
var_dump(findProduct($pdo, 99));

$search = $pdo->prepare('SELECT name FROM products WHERE name LIKE ? ORDER BY name');
$search->execute(['%' . 'mug' . '%']);
print_r($search->fetchAll(PDO::FETCH_COLUMN));

result

Array
(
    [id] => 2
    [name] => Ceramic Mug Set
    [price] => 18.75
    [stock] => 120
)
NULL
Array
(
    [0] => Ceramic Mug Set
)

fetch() returns false when there is no row, which the function turns into null. For a LIKE search, the % wildcards go in the value, not in the SQL.

Choosing columns and sort order safely

php

<?php
$pdo = require __DIR__ . '/db.php';

$requested = $_GET['sort'] ?? 'price';   // imagine ?sort=price came from the URL
$allowed = ['name' => 'name', 'price' => 'price', 'stock' => 'stock'];
$column = $allowed[$requested] ?? 'name';

$rows = $pdo->query("SELECT name, $column AS value FROM products ORDER BY $column DESC")->fetchAll();
foreach ($rows as $row) {
    echo "{$row['name']}: {$row['value']}\n";
}

result

Mechanical Keyboard: 89.5
Puzzle 1000 Pieces: 19.99
Ceramic Mug Set: 18.75

The column name comes from your array, never from the request itself, so it is safe to place in the SQL.

Update and delete

rowCount() tells you how many rows an UPDATE or DELETE changed, which is how you know whether anything matched:

php

<?php
$pdo = require __DIR__ . '/db.php';

$sell = $pdo->prepare('UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty');

$sell->execute(['qty' => 2, 'id' => 1]);
echo "Keyboard sale: " . $sell->rowCount() . " row updated\n";

$sell->execute(['qty' => 1, 'id' => 3]);
echo "Puzzle sale: " . $sell->rowCount() . " rows updated (sold out)\n";

$delete = $pdo->prepare('DELETE FROM products WHERE id = ?');
$delete->execute([3]);
echo "Deleted " . $delete->rowCount() . " product\n";

echo "Keyboard stock: " . $pdo->query('SELECT stock FROM products WHERE id = 1')->fetchColumn() . "\n";

result

Keyboard sale: 1 row updated
Puzzle sale: 0 rows updated (sold out)
Deleted 1 product
Keyboard stock: 58

The condition stock >= :qty inside the UPDATE means the database itself refuses to sell what is not there, even if two customers buy the last item at the same moment.

Transactions

Placing an order touches several tables: insert the order, insert its lines, reduce stock. If the third step fails, the first two must not remain. A transaction groups statements so they all succeed or none do:

php

<?php
$pdo = require __DIR__ . '/db.php';
$pdo->exec('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER, qty INTEGER)');

function placeOrder(PDO $pdo, int $productId, int $qty): string
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare('INSERT INTO orders (product_id, qty) VALUES (?, ?)')->execute([$productId, $qty]);
        $orderId = $pdo->lastInsertId();

        $stock = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?');
        $stock->execute([$qty, $productId, $qty]);
        if ($stock->rowCount() === 0) {
            throw new RuntimeException("Not enough stock");
        }

        $pdo->commit();
        return "Order $orderId placed";
    } catch (Throwable $e) {
        $pdo->rollBack();
        return "Order failed: " . $e->getMessage();
    }
}

echo placeOrder($pdo, 2, 5) . "\n";
echo placeOrder($pdo, 2, 500) . "\n";
echo "Orders saved: " . $pdo->query('SELECT COUNT(*) FROM orders')->fetchColumn() . "\n";
echo "Mug stock: " . $pdo->query('SELECT stock FROM products WHERE id = 2')->fetchColumn() . "\n";

result

Order 1 placed
Order failed: Not enough stock
Orders saved: 1
Mug stock: 115

The second order inserted a row, then failed on stock; rollBack() removed that row again, so only one order exists. The MySQL lesson on transactions explains what happens inside the database.

Common mistakes

Interpolating any input into SQL, even "just a number" or something from a cookie. Use placeholders.
Trying to bind a column name or ORDER BY direction. Pick it from an allowed list.
Putting the % wildcards in the SQL instead of the bound value for LIKE searches.
Forgetting rollBack() when an exception happens inside a transaction.

Exercise

Write a function restock(PDO $pdo, string $name, int $qty): bool that adds $qty to the stock of the product with that name, and returns whether a product was found. Use a named placeholder for each value, and test it with "Ceramic Mug Set" and "Unicorn Lamp".

Show one possible answer

php

<?php
$pdo = require __DIR__ . '/db.php';

function restock(PDO $pdo, string $name, int $qty): bool
{
    $statement = $pdo->prepare('UPDATE products SET stock = stock + :qty WHERE name = :name');
    $statement->execute(['qty' => $qty, 'name' => $name]);
    return $statement->rowCount() > 0;
}

var_dump(restock($pdo, 'Ceramic Mug Set', 30));
var_dump(restock($pdo, 'Unicorn Lamp', 10));
echo $pdo->query("SELECT stock FROM products WHERE name = 'Ceramic Mug Set'")->fetchColumn() . "\n";

result

bool(true)
bool(false)
145

Key points

Never put input into SQL text. prepare() the SQL with placeholders, then execute() with the values.
Placeholders are ? (by position) or :name (by name); they stand for values only, not table or column names.
lastInsertId() gives the ID of the new row; rowCount() the number of rows an UPDATE or DELETE changed.
beginTransaction(), commit() and rollBack() make several changes succeed or fail together.

The one rule

Prepared statements

Create: INSERT

Read: SELECT one row and many

Choosing columns and sort order safely

Update and delete

Transactions

Common mistakes

Exercise

Key points

Check yourself