Summary - Prepared statements

PHP course · Advanced · Lesson 14/92

Build a secure search app with prepare, execute, bindParam and named placeholders.

Summary - Prepared statements

In this group you learned the five pillars of prepared statements: prepare() builds the template, execute() runs it with values, bindParam() binds by reference, bindValue() copies the value now, and the :name placeholder matches by name, not order.

A prepared statement separates SQL from data, blocking SQL injection. After an INSERT, lastInsertId() returns the new id and rowCount() the affected row count.

Now we will combine these pieces into a secure search app.

A secure search app

Now let's combine what you learned: the searchBooks() function will filter books by author and year. The function will build a template with :author and :minYear named placeholders using prepare(), bind the variables by reference with bindParam(), run execute() and return results with fetchAll().

This structure automatically blocks SQL injection; whatever the author name is, it is treated as a plain text value.

The same function can be called repeatedly with different author and year values.

Exercise Summary - Secure book search

Write a searchBooks(PDO $pdo, string $author, int $minYear): array function. Build the template 'SELECT title FROM books WHERE author = :author AND year >= :minYear' with prepare(). Bind with bindParam(':author', $author, PDO::PARAM_STR) and bindParam(':minYear', $minYear, PDO::PARAM_INT). Call execute() and return fetchAll(PDO::FETCH_ASSOC). Call searchBooks($pdo, 'Atay', 1973) and print the title field of each row.

Show example solution

array. Inside, write $stmt = $pdo->prepare("SELECT title FROM books WHERE author = :author AND year >= :minYear"). Call $stmt->bindParam(':author', $author, PDO::PARAM_STR) and $stmt->bindParam(':minYear', $minYear, PDO::PARAM_INT). Run $stmt->execute(). return $stmt->fetchAll(PDO::FETCH_ASSOC). Outside, call $results = searchBooks($pdo, 'Atay', 1973) and foreach ($results as $row) { echo $row['title'] . "\n"; }.
Example solution:
<?php
// Secure search: prepare + execute + named placeholder + bindParam combined

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER)");
$pdo->exec("INSERT INTO books (title, author, year) VALUES ('Les Miserables', 'Hugo', 1862)");
$pdo->exec("INSERT INTO books (title, author, year) VALUES ('The Time Regulation Institute', 'Atay', 1973)");
$pdo->exec("INSERT INTO books (title, author, year) VALUES ('The Disconnected', 'Atay', 1972)");

function searchBooks(PDO $pdo, string $author, int $minYear): array {
    $stmt = $pdo->prepare("SELECT title FROM books WHERE author = :author AND year >= :minYear");
    $stmt->bindParam(':author', $author, PDO::PARAM_STR);
    $stmt->bindParam(':minYear', $minYear, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

$results = searchBooks($pdo, 'Atay', 1973);
foreach ($results as $row) {
    echo $row['title'] . "\n";
}

Test yourself

Which is the correct flow in prepared statements?
- execute → prepare → fetch
- prepare → bindParam → execute → fetch
- fetch → prepare → execute
- prepare → fetch → execute
Show answer

prepare → bindParam → execute → fetch — Correct! First prepare, then bindParam, then execute, finally fetch.

Complete the bindParam call in the search function

$stmt->____(':author', $author, PDO::PARAM_STR);

Show answer

$stmt->bindParam(':author', $author, PDO::PARAM_STR);

Match the five pillars

prepare()

Builds the template
execute()

Runs the query
bindParam()

Binds by reference
bindValue()

Binds the value now
:name

Named placeholder

All lessons in Advanced

Summary - Prepared statements