03.3 Prepared statements and safe database queries

01 | WHAT CHANGES WHEN A USER SUPPLIES A VALUE?

Use PHP 8.1 or later for these examples. Create a project folder with public and private subfolders. Start the local server from the project root with php -S 127.0.0.1:8000 -t public , then open the requested .php page through HTTP.

The public folder is the document root: files the browser can request. The private folder is a sibling, not a child of public. PHP can read it through filesystem paths, but it is not directly served by this configuration.

The examples are for local single-user practice. Database pages require a running MariaDB or MySQL server and the PDO MySQL driver. PHP’s built-in web server does not install or start the database.

A fixed connection-check query reads all clubs. A search form needs a value from the request. Pasting that value directly into SQL can break quoting or let input change the query’s meaning.

A prepared statement separates SQL structure from supplied values. PHP passes the values separately when executing the statement.

02 | READ THE TWO STEPS

<?php
// Snippet: $pdo is an open connection from openDatabase().
$statement = $pdo->prepare(
    'SELECT club_id, club_name, capacity FROM Clubs WHERE club_id = :id'
);
$statement->execute(['id' => 1]);
$club = $statement->fetch();

prepare describes the query with a named placeholder. execute binds the data. fetch returns the next row or false when none exists.

The placeholder is not quoted: ':id' would be a literal string. The execute array key is id, matching the named marker. Use a distinct marker for each supplied position when native prepares require it.

03 | A COMPLETE SEARCH FORM

All pages share public/helpers.php for HTML text and ordinary quoted-attribute output.

public/helpers.php

<?php
function escapeHtml(string $text): string
{
    return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}

public/search.php

<?php
require_once __DIR__ . '/helpers.php';
require_once dirname(__DIR__) . '/private/database.php';
$raw = $_GET['q'] ?? '';
$query = is_string($raw) ? trim($raw) : '';
$error = '';
$rows = [];
try {
    $pdo = openDatabase();
    // This exercise intentionally allows LIKE pattern characters % and _.
    $statement = $pdo->prepare(
        'SELECT club_id, club_name, capacity FROM Clubs '
        . 'WHERE club_name LIKE :pattern ORDER BY club_id'
    );
    $statement->execute(['pattern' => '%' . $query . '%']);
    $rows = $statement->fetchAll();
} catch (PDOException | RuntimeException $exception) {
    error_log($exception->getMessage());
    http_response_code(500);
    $error = 'Search unavailable. Check the local setup.';
}
?>
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Prepared search</title></head>
<body>
<h1>Prepared search</h1>
<p>This exercise supports SQL LIKE patterns; an empty search lists all clubs.</p>
<form action="search.php" method="get">
    <label for="q">Search : </label>
    <input id="q" name="q" value="<?= escapeHtml($query) ?>">
    <button type="submit">Search</button>
</form>
<?php if ($error !== ''): ?><p role="alert"><?= escapeHtml($error) ?></p><?php endif; ?>
<ul>
    <?php foreach ($rows as $club): ?>
        <li><?= escapeHtml($club['club_name']) ?>: <?= (int) $club['capacity'] ?> places</li>
    <?php endforeach; ?>
</ul>
<?php if (!$rows && $error === ''): ?><p>No matches.</p><?php endif; ?>
</body>
</html>

Reuse the private configuration and database function from 03.2. Open search.php through the local server. An empty search intentionally lists all clubs.

04 | FOLLOW THE VALUE THROUGH THE REQUEST

The form sends q by GET. PHP checks that it is a string, trims it and builds a pattern value such as %robot%. The SQL instruction itself remains a fixed string.

PDO binds the pattern; the database compares it with club names; fetchAll retrieves the matching rows; the HTML escapes names before displaying them.

The percent signs are data with LIKE pattern meaning. Parameter binding prevents them from becoming SQL syntax, but does not remove their intended wildcard meaning.

05 | TEST QUOTING AND PATTERNS

Try robot, an unknown word and a value containing an apostrophe. Input punctuation should not break SQL syntax or expand the query into another instruction.

Because this exercise offers LIKE patterns, % can match every name and _ matches one character. If your product promises literal substring search, implement and test appropriate escaping of LIKE metacharacters rather than claiming binding handles that automatically.

String matching case and accent behaviour depends on database collation. Do not assume every MariaDB/MySQL installation has the same comparison rules.

06 | WRITES USE PARAMETERS TOO

<?php
// Snippet for an open $pdo connection.
$name = "A Programmer's Club";
$capacity = 12;
$statement = $pdo->prepare(
    'INSERT INTO Clubs (club_name, capacity) VALUES (:name, :capacity)'
);
$statement->execute(['name' => $name, 'capacity' => $capacity]);
$newId = $pdo->lastInsertId();

Validate required text and capacity before this operation. lastInsertId refers to the generated ID on this connection; it is not a count of records.

Repeated execution of this insert would attempt repeated creation. The unique club-name rule may reject it. The complete CRUD lesson handles page feedback and redirects.

07 | IDENTIFIERS ARE NOT BOUND VALUES

<?php
// Snippet: fixed queries selected from a known allow-list.
$queries = [
    'name' => 'SELECT club_id, club_name, capacity FROM Clubs ORDER BY club_name, club_id',
    'capacity' => 'SELECT club_id, club_name, capacity FROM Clubs ORDER BY capacity DESC, club_id',
];
$sort = $_GET['sort'] ?? 'name';
if (!is_string($sort) || !isset($queries[$sort])) {
    $sort = 'name';
}
$rows = $pdo->query($queries[$sort])->fetchAll();

A parameter represents a data value, not a table name, column name or ASC/DESC keyword. Selecting from complete fixed queries prevents a raw sort value becoming SQL structure.

This is also why a comma-separated string bound to one IN (?) marker is one value, not an automatically expanded list. Multiple-value queries need an explicitly designed placeholder list.

08 | FETCH RESULTS AND CHECK MUTATIONS

Use fetch or fetchAll for SELECT results. Do not rely on PDOStatement::rowCount as a portable SELECT-row count; count the fetched array or issue COUNT for the intended question.

For UPDATE, an affected-row count of zero may mean unchanged values rather than a missing record, depending on driver settings and the operation. A clear application checks the condition it actually cares about.

Bind integers explicitly with bindValue(..., PDO::PARAM_INT) when the query requires integer parameter typing, such as a parameterised limit. These foundation search examples avoid that extra issue.

09 | FOUR SEPARATE RESPONSIBILITIES

A perfectly parameterised DELETE is still wrong if the requester lacks permission. A valid search string still needs HTML escaping when displayed. Prepared statements are essential at their boundary, not a complete application design.

10 | PRACTISE A BOUND LOOKUP

Next, connect these patterns to dynamically generated edit and delete controls.

KEEP LEARNING

Previous page 03.4 Create, read, update and delete records

Official references: JSON decoding · PDO connections · Prepared statements · MariaDB grants