PHP PDO Prepared Statements for Beginners

A PDO prepared statement keeps your SQL and your data apart: you write the query with placeholders like ? or :email, call $pdo->prepare(), then pass the real values to execute(). The database never treats those values as SQL, which is what protects you from SQL injection. In this beginner tutorial I connect to MySQL with PDO, then insert, select, update and delete with prepared statements, and cover LIKE searches, IN lists, LIMIT, transactions and the mistakes I made.

I ran every example on PHP 8.4 against a real MariaDB database (MariaDB works the same as MySQL for everything here), and the comments at the end of each block show the actual output. The examples run in order, so the data changes as you go.

Why prepared statements matter

Here's the problem in one example. Imagine a login or search box where someone types [e-mail]' OR '1'='1:

<?php
require __DIR__ . '/db.php';

$email = "[e-mail]' OR '1'='1"; // what an attacker might type

// UNSAFE: the input becomes part of the SQL itself
$rows = $pdo->query("SELECT name FROM students WHERE email = '$email'")->fetchAll();
echo "Unsafe query returned ", count($rows), " rows\n";

// SAFE: the input is sent separately as a value
$stmt = $pdo->prepare('SELECT name FROM students WHERE email = ?');
$stmt->execute([$email]);
echo "Prepared query returned ", count($stmt->fetchAll()), " rows\n";

// Output:
// Unsafe query returned 4 rows
// Prepared query returned 0 rows

When the input is pasted straight into the SQL string, the quote closes the email value early and OR '1'='1' becomes part of the query. It's always true, so the unsafe version returned every student in the table. The prepared version sent the whole input as one plain value, found no student with that strange "email", and returned nothing. That's the whole point of prepared statements.

Connecting to MySQL with PDO

I keep the connection in one file and require it everywhere else:

<?php
// db.php - keep this file outside your public web folder if you can
$host = getenv('DB_HOST') ?: '127.0.0.1';
$name = getenv('DB_NAME') ?: 'school';
$user = getenv('DB_USER') ?: 'app';
$pass = getenv('DB_PASS') ?: '';

$dsn = "mysql:host=$host;dbname=$name;charset=utf8mb4";

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // throw on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // rows as ['col' => value]
    PDO::ATTR_EMULATE_PREPARES   => false,                  // real prepared statements
]);

• charset=utf8mb4 in the DSN makes the connection use full UTF-8, so Bangla text and emoji are stored correctly.
• ERRMODE_EXCEPTION makes PDO throw a PDOException when something goes wrong. Since PHP 8.0 this is the default, but I set it explicitly so nobody has to wonder.
• FETCH_ASSOC returns rows as arrays keyed by column name, instead of duplicating every value under a number too.
• EMULATE_PREPARES => false asks MySQL to do the preparing itself, and it changes some behaviour you'll see later.
• Credentials come from environment variables, not hard-coded in the file. Don't commit passwords to Git, and don't show connection errors to visitors, because the message can reveal details about your server.

For the examples I created a small table:

<?php
require __DIR__ . '/db.php';

$pdo->exec('DROP TABLE IF EXISTS enrolments, students');
$pdo->exec('CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    class_level INT NOT NULL
)');

echo "Table ready\n";

// Output:
// Table ready

INSERT with named placeholders

<?php
require __DIR__ . '/db.php';

$stmt = $pdo->prepare(
    'INSERT INTO students (name, email, class_level) VALUES (:name, :email, :level)'
);

$students = [
    ['name' => 'Rafi',   'email' => '[e-mail]',   'level' => 10],
    ['name' => 'Nusrat', 'email' => '[e-mail]', 'level' => 11],
    ['name' => 'Tanvir', 'email' => '[e-mail]', 'level' => 10],
    ['name' => 'Mim',    'email' => '[e-mail]',    'level' => 12],
];

foreach ($students as $s) {
    $stmt->execute($s); // prepare once, execute many times
    echo "Inserted {$s['name']} with id ", $pdo->lastInsertId(), "\n";
}

// Output:
// Inserted Rafi with id 1
// Inserted Nusrat with id 2
// Inserted Tanvir with id 3
// Inserted Mim with id 4

Named placeholders like :name make longer queries easier to read. The array keys match the placeholder names, and the leading colon in the keys is optional. Notice that I call prepare() once and execute() four times. The query is parsed once and reused, which is cleaner and can be faster for repeated inserts. lastInsertId() gives you the auto-increment ID of the new row.

SELECT: one row, many rows, one column

<?php
require __DIR__ . '/db.php';

// One row, positional ? placeholder
$stmt = $pdo->prepare('SELECT id, name, class_level FROM students WHERE email = ?');
$stmt->execute(['[e-mail]']);
$student = $stmt->fetch(); // an array, or false if no row matched

if ($student === false) {
    echo "Not found\n";
} else {
    echo "{$student['name']} is in class {$student['class_level']}\n";
}

// Many rows, named placeholder
$stmt = $pdo->prepare('SELECT name FROM students WHERE class_level = :level ORDER BY name');
$stmt->execute(['level' => 10]);
foreach ($stmt->fetchAll() as $row) {
    echo "- {$row['name']}\n";
}

// Just one column from every row
$stmt = $pdo->prepare('SELECT name FROM students WHERE class_level >= ?');
$stmt->execute([11]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));

// Output:
// Nusrat is in class 11
// - Rafi
// - Tanvir
// Array
// (
//     [0] => Nusrat
//     [1] => Mim
// )

• fetch() returns the next row, or false when there are no more. Always check for false before using the result.
• fetchAll() returns every row as an array. That's fine for small results. For thousands of rows, loop with fetch() instead.
• fetchAll(PDO::FETCH_COLUMN) gives a flat array of the first column, which is perfect for lists of names or IDs.

Positional ? placeholders are filled in order from a plain array. You can use either style, but not both in the same query.

LIKE searches and IN lists

These two confuse almost every beginner, because the "obvious" way doesn't work:

<?php
require __DIR__ . '/db.php';

// LIKE: put the % signs in the value, not in the SQL
$search = 'a';
$stmt = $pdo->prepare('SELECT name FROM students WHERE name LIKE ? ORDER BY name');
$stmt->execute(['%' . $search . '%']);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

// IN (...): build one ? per value
$ids = [1, 3, 4];
$placeholders = implode(', ', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT name FROM students WHERE id IN ($placeholders)");
$stmt->execute($ids);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

// Output:
// Nusrat, Rafi, Tanvir
// Rafi, Tanvir, Mim

For LIKE, the % wildcards belong in the value you pass, not around the placeholder in the SQL. For IN, one placeholder can only hold one value, so you generate a ? for each item and pass the array. Never implode() the values themselves into the SQL.

LIMIT, OFFSET and dynamic ORDER BY

<?php
require __DIR__ . '/db.php';

// LIMIT/OFFSET: bind as integers
$perPage = 2;
$page = 2;
$stmt = $pdo->prepare('SELECT name FROM students ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();
echo "Page 2: ", implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

// ORDER BY a column chosen by the user: placeholders can't be used for
// column names, so map the input to an allow-list instead
$sortInput = $_GET['sort'] ?? 'name'; // pretend this came from the URL
$columns = ['name' => 'name', 'level' => 'class_level'];
$orderBy = $columns[$sortInput] ?? 'name';

$rows = $pdo->query("SELECT name FROM students ORDER BY $orderBy")->fetchAll(PDO::FETCH_COLUMN);
echo "Sorted by $orderBy: ", implode(', ', $rows), "\n";

// Output:
// Page 2: Tanvir, Mim
// Sorted by name: Mim, Nusrat, Rafi, Tanvir

For pagination, I bind LIMIT and OFFSET with bindValue() and PDO::PARAM_INT. This matters when emulated prepares are on: in my test, passing a LIMIT value as a string through execute() worked with emulation off but failed with a syntax error (SQLSTATE 42000) with emulation on, because PDO quoted it as '2'. Binding as an integer works in both modes.

Placeholders only work for values. You can't use them for table names, column names or keywords like ASC. When the user picks a sort column, map their choice to a fixed list of real column names, and fall back to a default for anything else. The user's input then never reaches the SQL at all.

UPDATE and DELETE with rowCount()

<?php
require __DIR__ . '/db.php';

$stmt = $pdo->prepare('UPDATE students SET class_level = class_level + 1 WHERE class_level = ?');
$stmt->execute([10]);
echo $stmt->rowCount(), " students moved up\n";

$stmt = $pdo->prepare('DELETE FROM students WHERE email = ?');
$stmt->execute(['[e-mail]']);
echo $stmt->rowCount(), " rows deleted\n"; // 0: no error, just nothing matched

// Output:
// 2 students moved up
// 0 rows deleted

rowCount() tells you how many rows were changed. A query that matches nothing isn't an error, so check this when you need to know whether something actually happened, like showing "not found" when deleting a record that doesn't exist.

Transactions: all or nothing

When several queries must succeed together, wrap them in a transaction. If anything fails, rollBack() undoes all of it:

<?php
require __DIR__ . '/db.php';

$pdo->exec('CREATE TABLE IF NOT EXISTS enrolments (
    student_id INT NOT NULL,
    course VARCHAR(40) NOT NULL
)');

try {
    $pdo->beginTransaction();

    $stmt = $pdo->prepare('INSERT INTO students (name, email, class_level) VALUES (?, ?, ?)');
    $stmt->execute(['Sadia', '[e-mail]', 9]);
    $id = (int) $pdo->lastInsertId();

    $stmt = $pdo->prepare('INSERT INTO enrolments (student_id, course) VALUES (?, ?)');
    $stmt->execute([$id, 'Web Development']);

    // Same email again: the UNIQUE column makes this fail
    $stmt = $pdo->prepare('INSERT INTO students (name, email, class_level) VALUES (?, ?, ?)');
    $stmt->execute(['Sadia again', '[e-mail]', 9]);

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Rolled back: SQLSTATE ", $e->getCode(), "\n";
}

$count = $pdo->query("SELECT COUNT(*) FROM students WHERE email = '[e-mail]'")->fetchColumn();
echo "Sadia rows saved: $count\n";

// Output:
// Rolled back: SQLSTATE 23000
// Sadia rows saved: 0

The third insert broke the UNIQUE rule on the email column. Because everything was in one transaction, the first two inserts were undone too, and Sadia wasn't saved in a half-finished state. Transactions need a storage engine that supports them, like InnoDB, which is MySQL's default.

Common mistakes with PDO prepared statements

1. Preparing a query that already contains user input

$pdo->prepare("SELECT * FROM users WHERE id = $id") isn't protected at all, because the value was already pasted into the SQL before prepare() saw it. The value must go into execute() or bindValue().

2. Reusing a named placeholder

<?php
require __DIR__ . '/db.php';

try {
    // The same named placeholder twice, with emulated prepares turned off
    $stmt = $pdo->prepare('SELECT name FROM students WHERE name = :term OR email = :term');
    $stmt->execute(['term' => 'Rafi']);
} catch (PDOException $e) {
    echo "Error: SQLSTATE ", $e->getCode(), "\n";
}

// Fix: give each placeholder its own name
$stmt = $pdo->prepare('SELECT name FROM students WHERE name = :name OR email = :email');
$stmt->execute(['name' => 'Rafi', 'email' => 'Rafi']);
echo $stmt->fetchColumn(), "\n";

// Output:
// Error: SQLSTATE HY093
// Rafi

With emulated prepares turned off, each named placeholder can appear only once, otherwise you get SQLSTATE HY093 ("invalid parameter number"). Give each one its own name, even if the value is the same.

3. Quotes around placeholders

WHERE email = '?' searches for a literal question mark. Placeholders never get quotes. PDO handles that.

4. Showing raw database errors to users

Catch PDOException, log the details, and show a friendly message. Error messages can reveal table names, queries or connection details.

5. Thinking prepared statements replace validation

Prepared statements stop SQL injection, but they don't check that an email is an email or that a number is in range. Validate first, then store.

FAQ

PDO or MySQLi: which should I learn?

Both support prepared statements. I prefer PDO because the same API works with MySQL, SQLite, PostgreSQL and other databases, and named placeholders make queries easier to read.

bindValue, bindParam or execute with an array?

Passing an array to execute() is the simplest and covers most cases. bindValue() lets you set a type, such as PARAM_INT. bindParam() binds a variable by reference, so its value is read when execute() runs, which beginners rarely need.

Do I still need to escape output if I use prepared statements?

Yes. Prepared statements protect the database. When you print data from the database into a page, escape it with htmlspecialchars() to protect against XSS.

Is the connection closed automatically?

Yes, PHP closes it when the script ends. You can set $pdo = null to close it earlier, but in a normal web request you don't need to.

Conclusion

PDO prepared statements come down to one habit: SQL with placeholders goes into prepare(), values go into execute(). Connect with utf8mb4 and exceptions turned on, put % inside LIKE values, generate placeholders for IN lists, bind LIMIT as an integer, use allow-lists for column names, and wrap related writes in transactions.

The PHP manual's page on prepared statements and stored procedures is short and worth reading. You can also see what I'm building on my projects page.

Originally published at mfaysal.com.