PHP
Summary - Prepared statements
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
Next lesson: Inserting records with INSERT
