PDO ב-PHP: חיבור ל-MySQL ו-prepared statements

PDO (PHP Data Objects) היא הדרך המובנית לדבר עם מסד נתונים. מתחברים עם new PDO($dsn, $user, $password), ואז מריצים כל שאילתה שמכילה ערך עם prepare() ו-execute(), כך שהערך נשלח בנפרד מה-SQL:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'secret', [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();      // an array, or false if no row matched

הדוגמאות בעמוד הזה צריכות שרת מסד נתונים, ולכן הן מוצגות כקוד רגיל. הדוגמה של SQL injection בהמשך כן רצה.

חיבור ל-MySQL עם PDO

הארגומנט הראשון הוא DSN: שם הדרייבר, ואחריו פרטי החיבור. שימו את החיבור במקום אחד וכללו אותו במקום שבו אתם צריכים אותו.

<?php
// db.php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // rows as ['id' => 1, ...]
    PDO::ATTR_EMULATE_PREPARES   => false,                   // real prepared statements, real int types
];

try {
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), $options);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database unavailable.');
}

return $pdo;

• charset=utf8mb4 שייך ל-DSN. בלעדיו, סימנים דיאקריטיים ואימוג'י עלולים להגיע כ-? או כבתים משובשים.
• לעולם אל תדפיסו את $e->getMessage() למבקרים: היא יכולה להכיל את המארח ואת שם המשתמש. רשמו אותה ללוג.
• השאירו את הסיסמה מחוץ לקוד, במשתנה סביבה או בקובץ הגדרות מחוץ לתיקיית ה-web.

מסדי נתונים אחרים משנים רק את ה-DSN: pgsql:host=localhost;dbname=shop ל-PostgreSQL, sqlite:/var/data/app.db לקובץ SQLite. כל אחד צריך את דרייבר ה-PDO שלו מותקן (pdo_mysql, pdo_pgsql, pdo_sqlite); php -m מפרט את אלה שיש לכם.

prepared statements עם ? ו-:name

ממלא מקום מסמן לאן הולך ערך. השתמשו בממלאי מקום לפי מיקום ? עם רשימה, או בממלאי מקום עם שם עם מערך אסוציאטיבי:

<?php
// Positional
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = ? AND price < ?');
$stmt->execute(['books', 20]);

// Named: easier to read when there are many values
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = :cat AND price < :max');
$stmt->execute(['cat' => 'books', 'max' => 20]);

// bindValue when you want to set the type explicitly
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY id LIMIT :limit');
$stmt->bindValue('limit', 10, PDO::PARAM_INT);
$stmt->execute();

אל תשימו מירכאות סביב ממלא מקום: WHERE name = '?' משווה לטקסט המילולי ?. אפשר גם להריץ prepared statement הרבה פעמים עם ערכים שונים, אז הכינו אותו פעם אחת לפני לולאה, לא בתוכה.

למה שרשור מחרוזות ב-SQL מסוכן

זה הבלוק היחיד בעמוד שרץ: הוא בונה את השאילתה בדרך הלא נכונה ובדרך הנכונה עבור אותו קלט. שנו את $email והריצו שוב.

<?php
$email = "nobody@example.com' OR '1'='1";

// Wrong: the input becomes part of the SQL
$sql = "SELECT * FROM users WHERE email = '" . $email . "'";
echo "Concatenated:\n$sql\n\n";

// Right: the SQL stays fixed, the value travels separately
$sql = 'SELECT * FROM users WHERE email = ?';
$params = [$email];
echo "Prepared:\n$sql\n";
echo 'Values: ', json_encode($params), "\n";

בשאילתה המשורשרת, המירכאה שבקלט סוגרת את המחרוזת, ו-OR '1'='1' הופך את התנאי לאמת לכל שורה: השאילתה מחזירה את כל המשתמשים. בטופס התחברות זה אומר להתחבר בלי לדעת את הסיסמה, ואם הדרייבר מקבל כמה פקודות בקריאה אחת, קלט כמו '; DROP TABLE users; -- מריץ פקודה שנייה לבחירת התוקף. בגרסה המוכנה מסד הנתונים מקבל את ה-SQL ואת הערך בנפרד, ולכן המירכאה היא רק תו בתוך אימייל שלא מתאים לאף אחד.

פונקציות escape כמו addslashes() אינן תיקון: הן מפספסות מקרים שתלויים בקידוד התווים של החיבור. השתמשו בממלאי מקום לכל ערך, כולל מספרים וערכים ש"מגיעים מהקוד שלכם".

שליפת שורות: fetch, fetchAll ו-fetchColumn

<?php
// One row
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([42]);
$user = $stmt->fetch();                 // ['id' => 42, 'name' => 'Ada', ...] or false
if ($user === false) {
    http_response_code(404);
}

// All rows
$rows = $pdo->query('SELECT id, name FROM users ORDER BY name')->fetchAll();

// Loop row by row without building an array of all rows
$stmt = $pdo->query('SELECT id, name FROM users');
foreach ($stmt as $row) {
    echo $row['name'], "\n";
}

// One value
$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

// Two columns as key => value
$names = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);  // [42 => 'Ada', ...]

query() בסדר כשה-SQL לא מכיל ערכים מבחוץ. ברגע שמעורב משתנה, עברו ל-prepare().

הכנסה, עדכון ומחיקה

<?php
$stmt = $pdo->prepare('INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)');
$stmt->execute([$email, $name, password_hash($password, PASSWORD_DEFAULT)]);
$id = (int) $pdo->lastInsertId();

$stmt = $pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
$stmt->execute([$newName, $id]);
echo $stmt->rowCount();                 // rows changed

$stmt = $pdo->prepare('DELETE FROM sessions WHERE expires_at < NOW()');
$stmt->execute();

lastInsertId() מחזירה מחרוזת, אז המירו אותה. ב-MySQL, rowCount() אחרי UPDATE סופרת שורות שבאמת השתנו, ולכן הגדרת שם לערך שכבר יש לו נותנת 0. שמרו סיסמאות עם password_hash, לעולם לא כטקסט גלוי.

טרנזקציות

טרנזקציה גורמת לכמה פקודות להצליח או להיכשל יחד. אם משהו זורק, בצעו rollback ושום דבר לא נשמר:

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, $from]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, $to]);
    $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')->execute([$from, $to, 100]);

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

זה מסתמך על כך ששגיאות זורקות חריגות (ברירת המחדל מאז PHP 8.0), כך שפקודה שנכשלה קופצת ל-catch. ב-MySQL, הטבלאות חייבות להשתמש ב-InnoDB; MyISAM מתעלמת מטרנזקציות. עמוד try/catch מכסה את Throwable וזריקה מחדש.

WHERE IN עם מערך

? יחיד מחזיק ערך יחיד, ולכן IN (?) עם מערך לא עובד. בנו ממלא מקום אחד לכל ערך:

<?php
$ids = [3, 8, 15];

if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));   // "?,?,?"
    $stmt = $pdo->prepare("SELECT * FROM products WHERE id IN ($placeholders)");
    $stmt->execute($ids);
    $products = $stmt->fetchAll();
} else {
    $products = [];                     // IN () would be a syntax error
}

המחרוזת ששולבה לתוך ה-SQL היא רק סימני שאלה ופסיקים, ולכן השאילתה עדיין בטוחה.

טעויות נפוצות עם ממלאי מקום

• שמות טבלאות ועמודות. ORDER BY ? ממיין לפי מחרוזת קבועה, לא לפי העמודה. בחרו מזהים מתוך רשימת היתר: $sort = in_array($_GET['sort'] ?? '', ['name', 'price'], true) ? $_GET['sort'] : 'name';, ואז שימו את $sort ב-SQL.
• LIMIT ? עם prepares מדומים. עם ברירת המחדל של ATTR_EMULATE_PREPARES ב-MySQL, execute(['10']) שולחת LIMIT '10', שגיאת תחביר. קשרו אותו עם PDO::PARAM_INT או כבו את האמולציה.
• אותו ממלא מקום עם שם פעמיים. WHERE first = :q OR last = :q עובד רק עם prepares מדומים. עם prepares אמיתיים מקבלים SQLSTATE[HY093]: Invalid parameter number; השתמשו ב-:q1 וב-:q2.
• שכחה ש-fetch() מחזירה false. $user['name'] על false הוא אזהרה ו-null; בדקו קודם את התוצאה.