Database Security & Performance — PHP Course

What you'll learn

Protect the database as both a security boundary and a shared performance resource. You will combine least privilege, secret handling, query plans, indexes, and recovery drills into one operating discipline.

By the end of this lesson, you'll be able to:

Apply least-privilege access
Read an EXPLAIN plan and test an index
Define backup and restore evidence

Core mental model

Professional PHP is less about memorizing APIs and more about choosing a clear boundary for each responsibility. Use this table as a decision guide while reading the examples.

Concept	What it protects	Decision rule
Least privilege	Limited blast radius	Grant the app only operations it performs.
Query plan	Evidence of database work	Inspect before guessing at optimization.
Recovery objective	Testable resilience	Define tolerated data loss and recovery time.

Professional workflow

Build the feature in small, verifiable steps. Each step leaves the system in a state you can test.

Describe the database operating boundary boundary: its inputs, outputs, invariants, and expected failures.
Implement the smallest happy path behind an explicit contract.
Add validation and translate low-level failures into language the caller understands.
Exercise the boundary with realistic data, then inspect output, logs, and resource cleanup.
Refactor only after behavior is protected by a repeatable check.

Make the boundary visible

Name inputs, outputs, side effects, and failure cases before adding framework or infrastructure code. That habit keeps advanced PHP understandable as the application grows.

Guided code lab

Match an index to a query

Equality columns lead, followed by the range/order column used by the catalog query.

performance.sql

CREATE INDEX courses_catalog_idx
    ON courses (status, category_id, published_at DESC);

EXPLAIN ANALYZE
SELECT id, title, published_at
FROM courses
WHERE status = 'published' AND category_id = 12
ORDER BY published_at DESC
LIMIT 20;

Allow-list query structure

Values are bound and the only dynamic identifier is selected from trusted code.

sort.php

<?php
$sorts = ['newest' => 'published_at DESC', 'title' => 'title ASC'];
$orderBy = $sorts[$_GET['sort'] ?? 'newest'] ?? $sorts['newest'];

$statement = $pdo->prepare(
    "SELECT id, title FROM courses WHERE category_id = :category ORDER BY $orderBy LIMIT 50"
);
$statement->execute(['category' => $categoryId]);

Production practice

Contract

Use separate runtime and migration identities, parameterize data, and document every elevated permission.

Verification

Benchmark before and after changes with production-like cardinality; perform scheduled restore tests.

Operations

Track latency percentiles, lock waits, connection saturation, replication lag, and backup age.

Common failure mode

A successful backup job does not prove recovery. Only restoring into an isolated environment and validating data proves the backup is usable.

Independent workshop

Audit a slow and over-privileged catalog database, then produce a measured hardening plan.

Your finished workshop must include:

Privilege matrix
Before/after query plans and timings
Documented restore drill with acceptance checks

Definition of done

Run the happy path and at least two failure paths, explain one design tradeoff in a short README, and leave the code formatted and ready for review.

Recap & quick check

Key takeaways

Parameterization and least privilege work together.
Query plans replace optimization guesses.
Indexes trade faster reads for write/storage cost.
Recovery is a practiced capability.

Quick check

1. What proves a backup is useful?

2. What should precede adding an index?

3. Should the web app run migrations as an admin?

Keep the workshop: later phases deliberately build on these boundaries, so today's small example can become part of your portfolio architecture.