PDO trong PHP: kết nối MySQL và prepared statement

PDO (PHP Data Objects) là cách có sẵn để làm việc với cơ sở dữ liệu. Kết nối bằng new PDO($dsn, $user, $password), rồi chạy mọi truy vấn có chứa giá trị bằng prepare() và execute(), để giá trị được gửi tách riêng khỏi câu 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

Các ví dụ trên trang này cần một máy chủ cơ sở dữ liệu, nên chúng được hiển thị dưới dạng code thuần. Ví dụ SQL injection ở phía dưới thì chạy được.

Kết nối MySQL bằng PDO

Đối số đầu tiên là DSN: tên driver, tiếp theo là thông tin kết nối. Đặt phần kết nối ở một chỗ và include nó ở nơi bạn cần.

<?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 phải nằm trong DSN. Thiếu nó, chữ có dấu và emoji có thể đến nơi thành ? hoặc các byte lỗi.
• Không bao giờ in $e->getMessage() cho người truy cập: nó có thể chứa tên host và tên người dùng. Hãy ghi log.
• Để mật khẩu ngoài code, trong một biến môi trường hoặc một tệp cấu hình nằm ngoài thư mục gốc của web.

Các cơ sở dữ liệu khác chỉ thay đổi DSN: pgsql:host=localhost;dbname=shop cho PostgreSQL, sqlite:/var/data/app.db cho một tệp SQLite. Mỗi loại cần cài driver PDO tương ứng (pdo_mysql, pdo_pgsql, pdo_sqlite); php -m liệt kê những driver bạn đang có.

Prepared statement với ? và :name

Placeholder đánh dấu chỗ một giá trị sẽ được đặt vào. Dùng placeholder theo vị trí ? với một danh sách, hoặc placeholder có tên với một mảng kết hợp:

<?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();

Đừng đặt dấu nháy quanh placeholder: WHERE name = '?' sẽ so sánh với đúng ký tự ?. Một prepared statement cũng có thể được thực thi nhiều lần với các giá trị khác nhau, nên hãy prepare một lần trước vòng lặp, không phải bên trong nó.

Vì sao nối chuỗi trong SQL lại nguy hiểm

Đây là khối code duy nhất trên trang chạy được: nó tạo truy vấn theo cách sai và cách đúng với cùng một đầu vào. Đổi $email rồi chạy lại.

<?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";

Trong truy vấn nối chuỗi, dấu nháy trong đầu vào đóng chuỗi lại, và OR '1'='1' khiến điều kiện đúng với mọi hàng: truy vấn trả về toàn bộ người dùng. Trên một form đăng nhập, điều đó nghĩa là đăng nhập được mà không cần biết mật khẩu, và nếu driver chấp nhận nhiều câu lệnh trong một lần gọi, một đầu vào như '; DROP TABLE users; -- sẽ chạy thêm một câu lệnh do kẻ tấn công chọn. Trong phiên bản prepared, cơ sở dữ liệu nhận câu SQL và giá trị riêng rẽ, nên dấu nháy chỉ là một ký tự trong một email không khớp với ai.

Các hàm escape như addslashes() không phải là cách sửa: chúng bỏ sót những trường hợp phụ thuộc vào bảng mã ký tự của kết nối. Hãy dùng placeholder cho mọi giá trị, kể cả số và những giá trị "đến từ chính code của bạn".

Lấy hàng: fetch, fetchAll và 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() dùng được khi câu SQL không chứa giá trị nào từ bên ngoài. Ngay khi có một biến tham gia, hãy chuyển sang prepare().

Thêm, sửa và xóa

<?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() trả về một chuỗi, nên hãy ép kiểu. Với MySQL, rowCount() sau một UPDATE chỉ đếm những hàng thực sự thay đổi, nên đặt một cái tên bằng đúng giá trị nó đang có sẽ cho 0. Lưu mật khẩu bằng password_hash, không bao giờ lưu dạng văn bản thuần.

Transaction

Transaction khiến nhiều câu lệnh cùng thành công hoặc cùng thất bại. Nếu có bất cứ thứ gì ném lỗi, hãy rollback và không có gì được lưu:

<?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;
}

Cách này dựa vào việc lỗi được ném thành ngoại lệ (mặc định từ PHP 8.0), nên một câu lệnh thất bại sẽ nhảy tới catch. Với MySQL, các bảng phải dùng InnoDB; MyISAM bỏ qua transaction. Trang try/catch nói về Throwable và cách ném lại lỗi.

WHERE IN với một mảng

Một ? chỉ chứa một giá trị, nên IN (?) với một mảng không chạy được. Hãy tạo một placeholder cho mỗi giá trị:

<?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
}

Chuỗi được chèn vào câu SQL chỉ gồm dấu chấm hỏi và dấu phẩy, nên truy vấn vẫn an toàn.

Những lỗi thường gặp với placeholder

• Tên bảng và tên cột. ORDER BY ? sắp xếp theo một chuỗi hằng, không phải theo cột. Hãy chọn định danh từ một danh sách cho phép: $sort = in_array($_GET['sort'] ?? '', ['name', 'price'], true) ? $_GET['sort'] : 'name';, rồi đưa $sort vào câu SQL.
• LIMIT ? với prepare giả lập. Với ATTR_EMULATE_PREPARES mặc định trên MySQL, execute(['10']) gửi đi LIMIT '10', một lỗi cú pháp. Hãy bind nó với PDO::PARAM_INT hoặc tắt chế độ giả lập.
• Dùng cùng một placeholder có tên hai lần. WHERE first = :q OR last = :q chỉ chạy được với prepare giả lập. Với prepare thật, bạn sẽ nhận SQLSTATE[HY093]: Invalid parameter number; hãy dùng :q1 và :q2.
• Quên rằng fetch() trả về false. $user['name'] trên false là một cảnh báo và cho null; hãy kiểm tra kết quả trước.