3.1MySQL & PDO: connection and prepared statements
The essentials in 30 seconds
- PDO is the modern interface: unified object connection (new PDO), multi-database, errors as exceptions with ERRMODE_EXCEPTION.
- Prepared statements are non-negotiable: :name or ? placeholders, data passed to execute() — the database treats them as pure data, never as code.
- CRUD reads as four verbs: INSERT / SELECT / UPDATE / DELETE, results via fetch() (one row) or fetchAll() (all).
Running case — Riad saves his shop from an injection
Riad, web developer in Constantine, manages a shop's orders by concatenating the entered email into his SELECT — a test with a trapped quote returns the whole clients table. He rewrites everything with PDO: centralized connection in a config.php file with ERRMODE_EXCEPTION, prepare() + execute() queries with named placeholders, fetch/fetchAll to read. Same shop, now impenetrable through that vector.
This same case returns in the lesson, the diagram, the script and the 48h challenge.
1. MySQL, PDO and centralized connection
A real application durably stores its data: MySQL (or MariaDB), a relational database, organises data into tables (typed columns), rows (records), queried in SQL: SELECT (read), INSERT (create), UPDATE (modify), DELETE (remove) — the four operations of CRUD.
The modern approach is PDO (PHP Data Objects): a unified, object-oriented interface working with several databases (portability), elegant prepared statements with named placeholders, and error handling via exceptions in PDO::ERRMODE_EXCEPTION mode (clean try/catch). Riad's reflexes: centralize the connection (a single reused config/connection file, never one connection per page) and never version credentials — they live outside the code (environment variables), never hardcoded.
2. Prepared statements: prepare + execute, always
The danger: building SQL by concatenating user data ('SELECT … WHERE email = ' . $email) creates a critical SQL injection flaw — the attacker injects SQL executed by the database (reading, modifying, destroying). The solution is the prepared statement: you prepare with placeholders (? or named :email) — $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email') — then execute passing the data separately — $stmt->execute(['email' => $email]) — and fetch with fetch() or fetchAll().
The rule is absolute: every query with variable data MUST be prepared; NEVER concatenate data into SQL. Riad applies it to the whole CRUD: INSERT of an order, SELECT of clients, UPDATE of the status, DELETE of a draft — each execute() receives its values, the SQL contains only placeholders.
🔎 Concrete example — PDO connection and prepared SELECT
Riad looks up a client by email, safely.
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$stmt = $pdo->prepare('SELECT id, nom, email FROM clients WHERE email = :email');
$stmt->execute(['email' => $emailSaisi]);
$client = $stmt->fetch();Reading: connection with exceptions, :email placeholder, data in execute(), one row via fetch().
Lesson: even with a trapped email, the database only sees data — no executed code.
Section vocabulary
- Database / MySQL
- System durably storing data; MySQL/MariaDB: relational database (tables, rows, columns) queried in SQL (SELECT/INSERT/UPDATE/DELETE = CRUD).
Simple example: Riad's shop orders table.
What it is not: a PHP array emptied at the end of each request.
Reflex: store in a database as soon as data must last.
- PDO
- PHP Data Objects: modern, unified, object-oriented interface for connecting to databases and running queries; multi-database, exceptions, named placeholders.
Simple example: Riad's centralized connection with ERRMODE_EXCEPTION.
What it is not: the obsolete, removed mysql_* functions.
Reflex: always PDO with ERRMODE_EXCEPTION.
- SQL injection
- Critical flaw: data concatenated into SQL is interpreted as executed code (reading, modifying, destroying the database).
Simple example: The trapped quote that emptied Riad's clients table.
What it is not: a mere harmless display typo.
Reflex: never concatenate data into SQL.
- Prepared statement
- Query with placeholders (? or :name): prepare() sends the code, execute() sends the data separately — the database treats them as pure data.
Simple example: prepare('… WHERE email = :email') then execute(['email' => $emailSaisi]).
What it is not: a concatenation '…' . $email inside the SQL string.
Reflex: prepare + execute for every query with variable data.
- CRUD / fetch
- Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE); reading via fetch() (one row) or fetchAll() (all).
Simple example: Riad creates, reads, modifies then deletes a test order.
What it is not: a single copy-pasted SELECT with no writing.
Reflex: master the four verbs before any framework.
In practice — Persisting data with MySQL and PDO
- Create a database and a table in MySQL (via phpMyAdmin); connect from PHP with PDO (host, database, user, password outside the code).
- Build a CRUD with PREPARED STATEMENTS: INSERT (create), SELECT (read, fetch/fetchAll), UPDATE (modify), DELETE (remove).
- Systematically use placeholders (:name or ?) and pass data via execute() — NEVER any data concatenation into SQL.
- Handle errors with try/catch (ERRMODE_EXCEPTION) and check that a trapped input no longer breaks anything.
Ready-to-say script — presenting your PDO database access (30 s)
“Orders live in MySQL via PDO: centralized connection with exceptions, full CRUD in prepared statements with named placeholders, reading with fetch and fetchAll. Tested with a trapped input: the database treats it as plain data, injection is impossible.”
Say in review; show one prepare/execute and the trapped test.
Copy-paste template — PDO + prepared CRUD
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
// CREATE
$stmt = $pdo->prepare('INSERT INTO clients (nom, email) VALUES (:nom, :email)');
$stmt->execute(['nom' => $nom, 'email' => $email]);
// READ
$stmt = $pdo->prepare('SELECT id, nom, email FROM clients WHERE email = :email');
$stmt->execute(['email' => $email]);
$client = $stmt->fetch();
$tous = $pdo->query('SELECT id, nom FROM clients')->fetchAll();
// UPDATE + DELETE
$stmt = $pdo->prepare('UPDATE clients SET nom = :nom WHERE id = :id');
$stmt->execute(['nom' => $nom, 'id' => $id]);
$stmt = $pdo->prepare('DELETE FROM clients WHERE id = :id');
$stmt->execute(['id' => $id]);Independent
A prepared PDO clients CRUD is enough to make stored quotes reliable.
SME in Constantine
Riad centralizes the shop's connection and prepares every back-office query.
Product team
Enforce a single connection file for consistent queries across modules.
Beginner
This week: one table, one PDO connection and one prepared SELECT read with fetch().
✓ Best practices
- Centralize the PDO connection (one place, reused everywhere).
- Always prepare: placeholders + execute(), never concatenation.
- Enable ERRMODE_EXCEPTION and read with fetch/fetchAll as needed.
✗ Common pitfalls
- Concatenating data into SQL, even “just for testing” (critical injection).
- Creating a PDO connection in every file (duplication, scattered credentials).
- Using the obsolete mysql_* functions instead of PDO.
Key takeaways
- A real application STORES its data in a DATABASE. MySQL/MariaDB (relational: tables, rows) queried in SQL (SELECT/INSERT/UPDATE/DELETE = CRUD).
- PDO = the MODERN, safe interface for connecting and running queries (multi-database, exceptions, named placeholders).
- CENTRAL: PREPARED STATEMENTS. Concatenating data = CRITICAL SQL INJECTION flaw. Placeholders (:name / ?) + data in execute().
- ABSOLUTE RULE: every query with variable data MUST be prepared; fetch/fetchAll to read — the core PHP-with-database skill.
48h challenge — apply tomorrow
Connect 1 PDO with exceptions and run a prepared INSERT + SELECT CRUD tested with a trapped input.
- I tell prepare() (code) apart from execute() (data).
- My CRUD is fully prepared, with no concatenation.
- My connection is centralized, errors as exceptions.
Frequently asked questions
What is SQL injection, and why are prepared statements the solution?
An SQL injection is an attack where data sent by the attacker (field, URL parameter) ends up executed as SQL because you concatenated it into the query: login bypass, table reading or destruction. The cause: mixing code and data in one string. The prepared statement separates the two: prepare() sends the code with placeholders, execute() sends the data separately — the database treats them as pure data, even trapped. It is THE protection, to apply always, without exception.
Why use PDO, and how to structure database access?
PDO is the modern approach: multi-database abstraction (same API for MySQL, PostgreSQL…), elegant prepared statements with named placeholders, consistent object-oriented API, errors as exceptions (try/catch). To structure: centralize the connection (one config/connection file or class, reused everywhere), credentials out of the repo (environment), and reuse the same PDO throughout the code — no more per-page recreated connection.
What does a PDO prepared statement separate?