Datenbanken mit PDO

new PDO("mysql:host=localhost;dbname=shop;charset=utf8mb4", $user, $pass, $optionen);
new PDO("pgsql:host=localhost;dbname=shop", $user, $pass);
new PDO("sqlite:/pfad/datei.db");
new PDO("sqlite::memory:");

<?php
$pdo = new PDO("sqlite::memory:", options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$pdo->exec("CREATE TABLE nutzer (id INTEGER PRIMARY KEY, name TEXT NOT NULL, punkte INTEGER DEFAULT 0)");

$stmt = $pdo->prepare("INSERT INTO nutzer (name, punkte) VALUES (:name, :punkte)");
foreach ([["Mia", 120], ["Tom", 80], ["Zoe", 200]] as [$n, $p]) {
    $stmt->execute([":name" => $n, ":punkte" => $p]);
}
echo "Letzte ID: ", $pdo->lastInsertId(), "\n";

$alle = $pdo->query("SELECT * FROM nutzer ORDER BY punkte DESC")->fetchAll();
print_r($alle);

$eine = $pdo->prepare("SELECT name FROM nutzer WHERE punkte > ?");
$eine->execute([100]);
echo implode(", ", $eine->fetchAll(PDO::FETCH_COLUMN)), "\n";
$z = $pdo->query("SELECT count(*) FROM nutzer")->fetchColumn();
echo "Anzahl: $z\n";

$upd = $pdo->prepare("UPDATE nutzer SET punkte = punkte + ? WHERE name = ?");
$upd->execute([10, "Tom"]);
echo "Geändert: ", $upd->rowCount(), "\n";
foreach ($pdo->query("SELECT name, punkte FROM nutzer") as $zeile) echo $zeile["name"], "=", $zeile["punkte"], " ";
echo "\n";

<?php
$pdo = new PDO("sqlite::memory:", options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE nutzer (name TEXT, geheim TEXT)");
$pdo->exec("INSERT INTO nutzer VALUES ('mia', 'a'), ('tom', 'b')");

$eingabe = "x' OR '1'='1";

// GEFÄHRLICH: liefert alle Zeilen
$unsicher = $pdo->query("SELECT name FROM nutzer WHERE name = '$eingabe'")->fetchAll(PDO::FETCH_COLUMN);
echo "unsicher: ", implode(",", $unsicher), "\n";

// SICHER: der Wert bleibt ein Wert
$stmt = $pdo->prepare("SELECT name FROM nutzer WHERE name = ?");
$stmt->execute([$eingabe]);
echo "sicher: ", count($stmt->fetchAll()), " Treffer\n";

<?php
$pdo = new PDO("sqlite::memory:", options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE konto (name TEXT PRIMARY KEY, stand INTEGER CHECK (stand >= 0))");
$pdo->exec("INSERT INTO konto VALUES ('Mia', 100), ('Tom', 50)");

function ueberweise(PDO $pdo, string $von, string $nach, int $betrag): void
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare("UPDATE konto SET stand = stand - ? WHERE name = ?")->execute([$betrag, $von]);
        $pdo->prepare("UPDATE konto SET stand = stand + ? WHERE name = ?")->execute([$betrag, $nach]);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        echo "Abbruch: ", $e->getMessage(), "\n";
    }
}
ueberweise($pdo, "Mia", "Tom", 30);
ueberweise($pdo, "Tom", "Mia", 500);
foreach ($pdo->query("SELECT * FROM konto ORDER BY name") as $z) echo $z["name"], ":", $z["stand"], " ";
echo "\n";

<?php
final class NutzerRepository
{
    public function __construct(private PDO $pdo) {}

    public function finde(int $id): ?array
    {
        $s = $this->pdo->prepare("SELECT * FROM nutzer WHERE id = :id");
        $s->execute(["id" => $id]);
        return $s->fetch() ?: null;
    }

    public function erstelle(string $name): int
    {
        $this->pdo->prepare("INSERT INTO nutzer (name) VALUES (?)")->execute([$name]);
        return (int) $this->pdo->lastInsertId();
    }
}

$pdo = new PDO("sqlite::memory:", options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec("CREATE TABLE nutzer (id INTEGER PRIMARY KEY, name TEXT)");
$repo = new NutzerRepository($pdo);
$id = $repo->erstelle("Mia");
print_r($repo->finde($id));
var_dump($repo->finde(99));