---
title: 'The Database Access Layer'
source: 'https://academia.sh/en/courses/php-web/database-access-layer'
course: 'PHP on the Web: State, Data and Security'
language: en
updated: '2026-10-21T05:03:37+00:00'
license: 'CC BY-SA 4.0'
objectives:
    - 'Open a connection with PDO, choose the error mode and run a prepared statement with named and positional placeholders'
    - 'Distinguish the forms in which the fetch modes return the same row'
    - 'Measure which party can change the type of the returned value'
    - 'Show that an explicit type cast moves the result from the environment to the server'
---

# The Database Access Layer

Sets up a PDO connection, the error mode, prepared statements, named and positional placeholders and fetch modes; when a connection option comes from the environment, measures who can change the type of the returned value.

The previous lesson measured the case where a setting comes from the environment rather than from the code: the same code could behave differently in a different deployment. The database connection is where this case appears most often. Connection settings are kept outside the code, and the party that performs the deployment can change them. The question is this: if a connection option comes from the environment, what type does the database return?

The measuring axis of this lesson is who can change a result. Outside the code there are three parties, and the decision order is fixed: first the client, then the third party, then the environment. If none of them can change the result, the result belongs to the server. The order follows the width of the attack surface: the client is everyone who sends a request, the third party is only the parties the application connects to, and the environment is in the hands of whoever performs the deployment only. In this lesson the dominant party is the environment. The core is the file below; every lesson gives it verbatim and it is never changed.

```php
<?php
// party.php — who can change this result?
declare(strict_types=1);

// The parties outside the code, in decision order: every request, every external call, every deployment.
const EXTERNAL_PARTIES = ['client', 'third party', 'environment'];

/** Computes the result once without deviation, then once with each external party's deviation. */
function probe(callable $result, array $deviations): array {
    $baseline = $result([]);
    $changed = [];
    foreach (EXTERNAL_PARTIES as $party) {
        if (array_key_exists($party, $deviations)) {
            $changed[$party] = $result([$party => $deviations[$party]]) !== $baseline;
        }
    }
    return $changed;
}

/** The first external party that can change the result; the server if none can. */
function party(array $changed): string {
    foreach (EXTERNAL_PARTIES as $p) {
        if ($changed[$p] ?? false) return $p;
    }
    return 'server';
}

function partyReport(string $name, callable $result, array $deviations): string {
    $changed = probe($result, $deviations);
    $lines = [];
    foreach ($changed as $p => $c) $lines[] = $p . ($c ? ' changed' : ' could not change');
    $owner = party($changed);
    echo "$name → $owner\n    ", implode(' · ', $lines), "\n";
    return $owner;
}
```

**DA1.** The database is an in-memory database and the connection is rebuilt for every measurement; when the connection closes the data disappears, so every measurement starts with the same two members. **DA2.** The environment's deviation is modeled as a connection option: the party that performs the deployment supplies the options array, and the code does not write it itself. **DA3.** The client sends only the member name; its deviation is sending `bob` instead of `alice`. **DA4.** The result is not a value but the PHP type of the `score` column.

## The Connection and the Prepared Statement

The data access layer is the only place where the application talks to the database; in PHP, PDO (PHP Data Objects) takes on this job. What the layer is for was covered in the database lessons; here we look only at how PDO makes it concrete. The file below opens the connection and prepares a table with two members. It has no output and is only written to disk; the later blocks pull it in with `require`.

```php
<?php
// connection.php — connects to the in-memory database, sets up the members table
declare(strict_types=1);

function connect(array $options = []): PDO {
    $pdo = new PDO('sqlite::memory:', options: $options);
    $pdo->exec('CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, score INTEGER)');
    $pdo->exec("INSERT INTO members (name, score) VALUES ('alice', 7), ('bob', 3)");
    return $pdo;
}

/** Reports which type a member's score has in PHP. */
function scoreType(string $name, array $options, bool $explicitCast): string {
    $pdo = connect($options);
    $statement = $pdo->prepare('SELECT score FROM members WHERE name = :name');
    $statement->execute(['name' => $name]);
    $score = $statement->fetchColumn();
    return get_debug_type($explicitCast ? (int) $score : $score);
}
```

The error mode determines how PDO reports an error. There are three options: the silent mode leaves the error only as a return value and an error code, the warning mode raises a warning, and the exception mode throws a `PDOException`. Since PHP 8.0 the default is the exception mode; this means that a failed query interrupts the flow and cannot go unnoticed.

A prepared statement sends the query text and the values in two separate steps. `prepare()` prepares the query text; `execute()` binds the values in place of the placeholders. A placeholder is written in two forms: the named placeholder (`:min`) matches the value by its name, and the positional placeholder (`?`) matches it by its order. The value is never written into the query text. What this separation adds to input safety is the job of the Security topic; here only the mechanism is set up.

The fetch mode chooses the form in which a row returns to PHP. `FETCH_ASSOC` gives an array keyed by column names, `FETCH_NUM` gives an array keyed by position numbers, and `FETCH_COLUMN` gives only the values of the first column.

```php
<?php
// database.php — prepared statement, placeholders and fetch modes
declare(strict_types=1);
require 'connection.php';

$pdo = connect();
echo 'error mode is exception: ', var_export($pdo->getAttribute(PDO::ATTR_ERRMODE) === PDO::ERRMODE_EXCEPTION, true), "\n";

$named = $pdo->prepare('SELECT name, score FROM members WHERE score > :min');
$named->execute(['min' => 2]);
echo 'named, FETCH_ASSOC: ', json_encode($named->fetchAll(PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE), "\n";

$positional = $pdo->prepare('SELECT name FROM members WHERE score > ?');
$positional->execute([5]);
echo 'positional, FETCH_COLUMN: ', json_encode($positional->fetchAll(PDO::FETCH_COLUMN)), "\n";

$ordered = $pdo->prepare('SELECT name, score FROM members ORDER BY id');
$ordered->execute();
echo 'FETCH_NUM: ', json_encode($ordered->fetch(PDO::FETCH_NUM)), "\n";

try {
    $pdo->prepare('SELECT missing FROM members')->execute();
} catch (PDOException $e) {
    echo 'invalid query: ', get_class($e), "\n";
}
```

```
error mode is exception: true
named, FETCH_ASSOC: [{"name":"alice","score":7},{"name":"bob","score":3}]
positional, FETCH_COLUMN: ["alice"]
FETCH_NUM: ["alice",7]
invalid query: PDOException
```

Three things are visible. The default error mode is the exception mode. The same two rows come back as arrays keyed by column names with the named placeholder, and as names only with the positional placeholder. A query that asks for a column that does not exist does not quietly return nothing; it throws a `PDOException`.

## Write Queries and the Affected Rows

A read query returns rows; a write query returns no rows and leaves an effect instead. Two pieces of information describe this effect. `lastInsertId()` gives the key of the most recently inserted row. `rowCount()` says how many rows the write query affected. Both are read after the query finishes successfully; since the error mode is the exception mode, a failed write never reaches this line anyway.

`bindValue()` is another way of binding a value to a prepared statement together with its type. The `execute()` array treats every value as text; `bindValue()` declares a type such as `PDO::PARAM_INT` through its third argument. The file below inserts a member, updates its score and reads the number of affected rows.

```php
<?php
// write.php — insert, update and affected rows
declare(strict_types=1);
require 'connection.php';

$pdo = connect();

$insert = $pdo->prepare('INSERT INTO members (name, score) VALUES (:name, :score)');
$insert->bindValue(':name', 'carol');
$insert->bindValue(':score', 9, PDO::PARAM_INT);
$insert->execute();
echo 'last key: ', $pdo->lastInsertId(), "\n";

$update = $pdo->prepare('UPDATE members SET score = score + 1 WHERE score > :min');
$update->execute(['min' => 5]);
echo 'affected rows: ', $update->rowCount(), "\n";

$missing = $pdo->prepare('UPDATE members SET score = 0 WHERE name = :name');
$missing->execute(['name' => 'nobody']);
echo 'missing member, affected rows: ', $missing->rowCount(), "\n";
```

```
last key: 3
affected rows: 2
missing member, affected rows: 0
```

The third line is not an error. An `UPDATE` that matches no rows finishes successfully and affects zero rows. To find out whether a write left an effect, the value of `rowCount()` is checked; no exception is thrown in this case.

## The Type of the Returned Value

Even when the column type in the database is `INTEGER`, the type in which it arrives in PHP depends on a connection option. Since PHP 8.1, in the default behavior of the SQLite driver an integer column returns as `int`. If the `PDO::ATTR_STRINGIFY_FETCHES` option is on, every value returns as a `string`. The code stays the same, only the option changes. Because the party that performs the deployment supplies the option, the deviation comes from the environment.

The result is the type of the `score` column. The client's deviation changes the member name, but `alice` and `bob` both have a score of the same type. The file below first shows the two types, then takes the party report.

```php
<?php
// type.php — who can change the type of the score column?
declare(strict_types=1);
require 'party.php';
require 'connection.php';

echo 'default: ', scoreType('alice', [], false), "\n";
echo 'stringify fetches on: ', scoreType('alice', [PDO::ATTR_STRINGIFY_FETCHES => true], false), "\n";

partyReport(
    'score type',
    fn(array $d) => scoreType($d['client'] ?? 'alice', $d['environment'] ?? [], false),
    ['client' => 'bob', 'environment' => [PDO::ATTR_STRINGIFY_FETCHES => true]]
);
```

```
default: int
stringify fetches on: string
score type → environment
    client could not change · environment changed
```

The client cannot change the type: whichever member it asks for, the score returns with the same type. The environment can change it: when the option is turned on, the same query brings back a `string` instead of an `int`. By the decision order the first party that can change it is the environment, so the lesson belongs to the environment. The returned type looks like a mere layout detail, but when the value is passed to a typed parameter that expects an `int`, receiving a `string` under `strict_types` throws a `TypeError`.

## The Bounding Measurement

Ask the same question once more, but this time let the code convert the returned value to `int` itself. The query, the placeholder and the option are the same; only the `(int)` cast has been added. The environment's deviation still changes the connection option and the database still returns a `string`, but the code corrects it before it reaches the result.

```php
<?php
// bound.php — the same measurement with an explicit cast
declare(strict_types=1);
require 'party.php';
require 'connection.php';

partyReport(
    'score type, explicit cast',
    fn(array $d) => scoreType($d['client'] ?? 'alice', $d['environment'] ?? [], true),
    ['client' => 'bob', 'environment' => [PDO::ATTR_STRINGIFY_FETCHES => true]]
);
```

```
score type, explicit cast → server
    client could not change · environment could not change
```

The result moved from the environment to the server. The environment option can still make a change, but the change no longer affects the result, because the type of the result is now determined by the code's own cast. Not every defense mechanism moves the owner this way; for some results the owner stays unchanged. In this example, however, the cast pulls the result into the code.

## Summary

- The PDO connection, the error mode, the prepared statement and the fetch modes make up the data access layer; the default error mode is the exception mode.
- A prepared statement separates the query text from the values; a placeholder is written as named (`:min`) or positional (`?`).
- The fetch mode chooses the form of the row: an array keyed by column names, a positional array or a single column.
- The type of the returned value depends on a connection option, and because the option comes from the environment, the party in this lesson is the environment.
- When the code converts the value to `int` explicitly, the environment's deviation can no longer change the result and the result passes to the server.

## Next Step

We have set up the connection and a single query; but a task often consists of more than one write. When the second write fails, what happens to the first? The next lesson handles a write that stops halfway by means of transactions.