Sign in Get started

PHP Tutorial · Databases and Tools · Lesson 36 of 38

PHP PDO and Databases

Connect to MySQL, PostgreSQL or SQLite with PDO and run safe prepared statements.

Connecting with PDO

PDO is PHP's database layer. The same code works with MySQL, PostgreSQL, SQLite and more — only the connection string (DSN) changes:

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=shop;charset=utf8mb4",
    "app_user",
    getenv("DB_PASSWORD"),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
);

Prepared statements — always

Never put user input directly into SQL — that's how SQL injection happens. Use placeholders and pass values separately:

Example 1Try it yourself
<?php
$pdo = new PDO("sqlite::memory:", options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)");

$insert = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
foreach ([["Ada", "[email protected]"], ["Lin", "[email protected]"]] as [$name, $email]) {
    $insert->execute(["name" => $name, "email" => $email]);
}

$userInput = "[email protected]' OR '1'='1";   // an injection attempt
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE email = ?");
$stmt->execute([$userInput]);
var_dump($stmt->fetch(PDO::FETCH_ASSOC));    // false: treated as plain text

$stmt->execute(["[email protected]"]);
print_r($stmt->fetch(PDO::FETCH_ASSOC));
echo $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn(), " users\n";

Output

bool(false)
Array
(
    [id] => 1
    [name] => Ada
)
2 users

Transactions

Group statements that must succeed or fail together:

Example 2Try it yourself
<?php
$pdo = new PDO("sqlite::memory:", options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))");
$pdo->exec("INSERT INTO accounts VALUES ('ada', 100), ('lin', 20)");

function transfer(PDO $pdo, string $from, string $to, int $amount): void
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE name = ?")->execute([$amount, $to]);
        $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE name = ?")->execute([$amount, $from]);
        $pdo->commit();
        echo "transferred $amount\n";
    } catch (PDOException $e) {
        $pdo->rollBack();
        echo "rolled back: balance would go negative\n";
    }
}

transfer($pdo, "ada", "lin", 30);
transfer($pdo, "lin", "ada", 500);
print_r($pdo->query("SELECT name, balance FROM accounts ORDER BY name")->fetchAll(PDO::FETCH_KEY_PAIR));

Output

transferred 30
rolled back: balance would go negative
Array
(
    [ada] => 70
    [lin] => 50
)

New to SQL itself? Start with the SQL tutorial.

Test yourself

What protects a query from SQL injection?

  1. addslashes()
  2. Prepared statements with placeholders
  3. htmlspecialchars()
  4. Using double quotes
Show answer

Answer: Prepared statements with placeholders