PDO

Also Known As

TL;DR

Explanation

PDO provides a consistent interface to multiple database backends (MySQL, PostgreSQL, SQLite, etc.) through a unified API. It supports both named (:name) and positional (?) parameter placeholders in prepared statements. PDO::ATTR_EMULATE_PREPARES should be set to false to ensure the database — not PHP — handles the parameterisation. PDO throws PDOException on failure when PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION.

Diagram

flowchart LR
    APP2[PHP App] --> PDO2[PDO layer<br/>database agnostic]
    PDO2 -->|DSN string| MYSQL2[(MySQL)]
    PDO2 -->|DSN string| PGSQL[(PostgreSQL)]
    PDO2 -->|DSN string| SQLITE[(SQLite)]
    subgraph Safe_Query_Flow
        PREP[prepare SQL with placeholders]
        BIND2[execute with values array]
        FETCH[fetch results as array object]
        PREP --> BIND2 --> FETCH
    end
    subgraph Prevents
        INJ[SQL injection<br/>params never interpreted as SQL]
    end
style PDO2 fill:#1f6feb,color:#fff
style PREP fill:#238636,color:#fff
style INJ fill:#238636,color:#fff

Common Misconception

Why It Matters

Common Mistakes

• Using PDO::ERRMODE_SILENT (the default) — errors fail silently and are nearly impossible to debug.
• Leaving PDO::ATTR_EMULATE_PREPARES enabled — use native prepared statements for proper SQL injection protection.
• Catching PDOException but swallowing it without logging — you lose all diagnostic information.
• Creating a new PDO connection on every function call instead of sharing a single connection.

Code Examples

// Interpolated variables — SQL injection:
$id = $_GET['id'];
$stmt = $pdo->query("SELECT * FROM users WHERE id = $id");

// Prepared statement with PDO:
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute([':id' => (int)$id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

\$pdo = new PDO(
    'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
    \$_ENV['DB_USER'],
    \$_ENV['DB_PASS'],
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false, // native prepared statements
    ]
);

// Named placeholders
\$stmt = \$pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
\$stmt->execute([':email' => \$email, ':active' => 1]);
\$user = \$stmt->fetch();

// Insert + last insert ID
\$stmt = \$pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)');
\$stmt->execute([\$userId, \$total]);
\$orderId = \$pdo->lastInsertId();

// Transactions
\$pdo->beginTransaction();
try { \$pdo->commit(); } catch (\Throwable \$e) { \$pdo->rollBack(); throw \$e; }

References

• ↗ https://www.php.net/manual/en/book.pdo.php

Tags

Related Terms