Prepared Statements trong MySQL - Trở thành Lập trình viên PHP

Trong bài hướng dẫn tự học PHP và MySQL này, bạn sẽ học cách sử dụng Prepared Statements trong MySQL bằng PHP.

Prepared Statement là gì?

Prepared Statement (còn được gọi là câu lệnh được tham số hóa) chỉ đơn giản là một mẫu truy vấn SQL có chứa trình giữ chỗ thay vì các giá trị tham số thực tế.

Các trình giữ chỗ này sẽ được thay thế bằng các giá trị thực tế tại thời điểm thực hiện câu lệnh.

MySQLi hỗ trợ sử dụng trình giữ chỗ vị trí ẩn danh (?), Như được hiển thị bên dưới:

INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?);

Trong khi đó, PDO hỗ trợ cả trình giữ chỗ vị trí ẩn danh (?), Cũng như các trình giữ chỗ được đặt tên.

Một trình giữ chỗ được đặt tên bắt đầu bằng dấu hai chấm (:) theo sau là một mã định danh, như thế này:

INSERT INTO persons (first_name, last_name, email)
VALUES (:first_name, :last_name, :email);

Việc thực hiện Prepared Statement bao gồm hai giai đoạn: Chuẩn bị và Thực thi.

Chuẩn bị: Ở giai đoạn chuẩn bị, một mẫu câu lệnh SQL được tạo và gửi đến máy chủ cơ sở dữ liệu. Máy chủ phân tích mẫu câu lệnh, thực hiện kiểm tra cú pháp và tối ưu hóa truy vấn và lưu trữ nó để sử dụng sau.
Thực thi: Trong quá trình thực thi, các giá trị tham số được gửi đến máy chủ. Máy chủ tạo một câu lệnh từ mẫu câu lệnh và sử dụng các giá trị này để thực thi nó.

Prepared Statement rất hữu ích, đặc biệt trong các tình huống khi bạn thực thi một câu lệnh cụ thể nhiều lần với các giá trị khác nhau.

Ví dụ, bạn cần thực hiện một loạt các câu lệnh INSERT. Prepared Statement sẽ giúp bạn tối ưu hóa công việc này.

Phần sau đây mô tả một số lợi ích chính của việc sử dụng Prepared Statement.

Ưu điểm của việc sử dụng Prepared Statement

Prepared Statement có thể thực hiện cùng một câu lệnh lặp đi lặp lại với hiệu quả cao, bởi vì câu lệnh chỉ được phân tích cú pháp một lần, trong khi nó có thể được thực thi nhiều lần.

Nó cũng giảm thiểu việc sử dụng băng thông, vì mỗi lần thực thi, chỉ có các giá trị giữ chỗ cần được truyền đến máy chủ cơ sở dữ liệu thay vì truyền cả câu lệnh SQL hoàn chỉnh.

Prepared Statement cũng cung cấp sự bảo vệ mạnh mẽ chống lại SQL injection (Hành động cố gắng truyền câu lệnh SQL để thao túng CSDL), bởi vì các giá trị tham số không được nhúng trực tiếp bên trong chuỗi truy vấn SQL.

Các giá trị tham số được gửi đến máy chủ cơ sở dữ liệu tách biệt với truy vấn bằng một giao thức khác và do đó không thể can thiệp vào nó.

Máy chủ sử dụng các giá trị này trực tiếp tại điểm thực hiện, sau khi mẫu câu lệnh được phân tích cú pháp.

Đó là lý do tại sao các Prepared Statement ít bị lỗi hơn và do đó được coi là một trong những yếu tố quan trọng nhất trong bảo mật cơ sở dữ liệu.

Ví dụ sau đây sẽ cho bạn thấy Prepared Statement được chuẩn bị thực sự hoạt động như thế nào:

Ví dụ sử dụng Prepared Statement

Cách #1. Sử dụng Prepared Statement bằng MySQLi hướng thủ tục

<?php
/* Cố gắng kết nối máy chủ MySQL. Giả sử bạn đang chạy MySQL
Máy chủ có cài đặt mặc định (user là 'root' và không có mật khẩu) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Kiểm tra kết nối
if($link === false){
    die("ERROR: Không thể kết nối. " . mysqli_connect_error());
}
 
// Chuẩn bị câu lệnh INSERT
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    // Liên kết biến với tham số trong câu lệnh đã chuẩn bị
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    /* Thiết lập giá trị tham số và thực thi
    câu lệnh 1 lần nữa để chèn một hàng khác */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* Thiết lập giá trị tham số và thực thi
    câu lệnh 1 lần nữa để chèn một hàng */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    mysqli_stmt_execute($stmt);
    
    echo "Chèn bản ghi thành công.";
} else{
    echo "ERROR: Không thể chuẩn bị truy vấn: $sql. " . mysqli_error($link);
}
 
// Đóng câu lệnh
mysqli_stmt_close($stmt);
 
// Đóng kết nối
mysqli_close($link);
?>

Cách #2. Sử dụng Prepared Statement bằng MySQLi hướng đối tượng

<?php
/* Cố gắng kết nối máy chủ MySQL. Giả sử bạn đang chạy MySQL
Máy chủ có cài đặt mặc định (user là 'root' và không có mật khẩu) */
$mysqli = new mysqli("localhost", "root", "", "demo");
 
// Kiểm tra kết nối
if($mysqli === false){
    die("ERROR: Không thể kết nối. " . $mysqli->connect_error);
}
 
// Chuẩn bị câu lệnh INSERT
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = $mysqli->prepare($sql)){
    // Liên kết biến với tham số trong câu lệnh đã chuẩn bị
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    
    /* Thiết lập giá trị tham số và thực thi
    câu lệnh 1 lần nữa để chèn một hàng khác */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    $stmt->execute();
    
    /* Thiết lập giá trị tham số và thực thi
    câu lệnh 1 lần nữa để chèn một hàng */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    $stmt->execute();
    
    echo "Chèn bản ghi thành công.";
} else{
    echo "ERROR: Không thể chuẩn bị truy vấn: $sql. " . $mysqli->error;
}
 
// Đóng câu lệnh
$stmt->close();
 
// Đóng kết nối
$mysqli->close();
?>

Cách #3. Sử dụng Prepared Statement bằng PDO

<?php
/* Cố gắng kết nối máy chủ MySQL. Giả sử bạn đang chạy MySQL
Máy chủ có cài đặt mặc định (user là 'root' và không có mật khẩu) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // Thiết lập PDO erorr thành Ngoại lệ
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Không thể kết nối. " . $e->getMessage());
}
 
// Cố gắng thực thi câu lệnh INSERT
try{
    // Chuẩn bị câu lệnh INSERT
    $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
    $stmt = $pdo->prepare($sql);
    
    // Ràng buộc tham số
    $stmt->bindParam(':first_name', $first_name, PDO::PARAM_STR);
    $stmt->bindParam(':last_name', $last_name, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    
    /* Thiết lập giá trị tham số và thực thi
    câu lệnh 1 lần nữa để chèn một hàng khác */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    $stmt->execute();
    
    /* Thiết lập giá trị tham số và thực thi
    câu lệnh 1 lần nữa để chèn một hàng */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    $stmt->execute();
    
    echo "Chèn bản ghi thành công.";
} catch(PDOException $e){
    die("ERROR: Không thể chuẩn bị / thực thi truy vấn: $sql. " . $e->getMessage());
}
 
// Đóng câu lệnh
unset($stmt);
 
// Đóng kết nối
unset($pdo);
?>

Như bạn có thể thấy trong ví dụ trên, chúng tôi đã chuẩn bị câu lệnh INSERT chỉ một lần nhưng thực hiện nó nhiều lần bằng cách truyền tập hợp các tham số khác nhau.

Giải thích về ví dụ về Prepared Statement (Kiểu thủ tục)

Bên trong câu lệnh SQL INSERT (dòng số 12) của ví dụ trên, các dấu hỏi được sử dụng làm trình giữ chỗ cho các giá trị của trường first_name, last_name, email.

Hàm mysqli_stmt_bind_param() (dòng số 16) liên kết các biến với trình giữ chỗ (?) trong mẫu câu lệnh SQL.

Các phần giữ chỗ (?) Sẽ được thay thế bằng các giá trị thực được giữ trong các biến tại thời điểm thực thi. Chuỗi định nghĩa kiểu được cung cấp ở đối số thứ hai, tức là chuỗi ‘sss‘ chỉ định rằng kiểu dữ liệu của mỗi biến liên kết là chuỗi.

Chuỗi định nghĩa kiểu xác định kiểu dữ liệu của các biến liên kết tương ứng và chứa một hoặc nhiều trong bốn ký tự sau:

b — binary (như là hình ảnh, PDF, …)
d — double (Số thập phân)
i — integer (Số nguyên)
s — string (ký tự)

Số lượng biến liên kết và số lượng ký tự trong chuỗi định nghĩa kiểu phải khớp với số lượng giữ chỗ trong câu lệnh SQL mẫu.

Sử dụng Web Form để truyền dữ liệu và sử dụng Prepared Statement

Nếu bạn nhớ từ bài INSERT trong MySQL, chúng ta đã tạo một Form HTML để chèn dữ liệu vào cơ sở dữ liệu.

Ở đây, chúng ta sẽ mở rộng ví dụ đó bằng cách thực hiện Prepared Statement.

Bạn có thể sử dụng cùng một biểu mẫu HTML, nhưng chỉ cần đảm bảo rằng bạn đang sử dụng tên tệp chính xác trong thuộc tính action của biểu mẫu.

Đây là mã PHP được cập nhật để chèn dữ liệu. Nếu bạn xem ví dụ một cách cẩn thận, bạn sẽ thấy chúng tôi đã không sử dụng mysqli_real_escape_opes() để làm sạch dữ liệu đầu vào của người dùng, giống như chúng ta đã làm trong ví dụ chương trước.

Vì trong các câu lệnh đã chuẩn bị, dữ liệu đầu vào vào của người dùng không bao giờ được thay thế trực tiếp vào chuỗi truy vấn, do đó chúng không cần phải được làm sạch.

Hướng dẫn nhận dữ liệu thông qua Form HTML và sử dụng Prepared Statement với MySQLi hướng thủ tục

<?php
/* Cố gắng kết nối máy chủ MySQL. Giả sử bạn đang chạy MySQL
Máy chủ có cài đặt mặc định (user là 'root' và không có mật khẩu) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Kiểm tra kết nối
if($link === false){
    die("ERROR: Không thể kết nối. " . mysqli_connect_error());
}
 
// Chuẩn bị câu lệnh INSERT
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    // Liên kết biến với tham số trong câu lệnh đã chuẩn bị
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    // Thiết lập tham số
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];

    // Cố gắng thực hiện câu lệnh đã chuẩn bị
    if(mysqli_stmt_execute($stmt)){
        echo "Chèn bản ghi thành cồng.";
    } else{
        echo "ERROR: Không thể thực thi truy vấn: $sql. " . mysqli_error($link);
    }
} else{
    echo "ERROR: Không thể chuẩn bị truy vấn: $sql. " . mysqli_error($link);
}
 
// Đóng câu lệnh
mysqli_stmt_close($stmt);
 
// Đóng kết nối
mysqli_close($link);
?>

Hướng dẫn nhận dữ liệu thông qua Form HTML và sử dụng Prepared Statement với MySQLi hướng đối tượng

<?php
/* Cố gắng kết nối máy chủ MySQL. Giả sử bạn đang chạy MySQL
Máy chủ có cài đặt mặc định (user là 'root' và không có mật khẩu) */
$mysqli = new mysqli("localhost", "root", "", "demo");
 
// Kiểm tra kết nối
if($mysqli === false){
    die("ERROR: Không thể kết nối. " . $mysqli->connect_error);
}
 
// Chuẩn bị câu lệnh INSERT
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = $mysqli->prepare($sql)){
    // Liên kết biến với tham số trong câu lệnh đã chuẩn bị
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    
    // Thiết lập tham số
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    
    // Cố gắng thực hiện câu lệnh đã chuẩn bị
    if($stmt->execute()){
        echo "Chèn bản ghi thành công.";
    } else{
        echo "ERROR: Không thể thực thi truy vấn: $sql. " . $mysqli->error;
    }
} else{
    echo "ERROR: Không thể chuẩn bị truy vấn: $sql. " . $mysqli->error;
}
 
// Đóng câu lệnh
$stmt->close();
 
// Đóng kết nối
$mysqli->close();
?>

Lưu ý: Mặc dù làm sạch dữ liệu đầu vào là không bắt buộc trong Prepared Statement, bạn phải luôn xác thực loại và kích thước của dữ liệu nhận được từ các nguồn bên ngoài và thực thi các giới hạn phù hợp để bảo vệ lại các hành động khai thác tài nguyên hệ thống có hại.

Hướng dẫn nhận dữ liệu thông qua Form HTML và sử dụng Prepared Statement với PDO

<?php
/* Cố gắng kết nối máy chủ MySQL. Giả sử bạn đang chạy MySQL
Máy chủ có cài đặt mặc định (user là 'root' và không có mật khẩu) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // Thiết lập PDO erorr thành Ngoại lệ
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Không thể kết nối. " . $e->getMessage());
}
 
// Cố gắng thực thi câu lệnh INSERT
try{
    // Chuẩn bị câu lệnh INSERT
    $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
    $stmt = $pdo->prepare($sql);
    
    // Ràng buộc tham số
   $stmt->bindParam(':first_name', $_REQUEST['first_name'], PDO::PARAM_STR);
    $stmt->bindParam(':last_name', $_REQUEST['last_name'], PDO::PARAM_STR);
    $stmt->bindParam(':email', $_REQUEST['email'], PDO::PARAM_STR);

    // Thực thi câu lệnh đã chuẩn bị
    $stmt->execute();    
    echo "Chèn bản ghi thành công.";
} catch(PDOException $e){
    die("ERROR: Không thể chuẩn bị / thực thi truy vấn: $sql. " . $e->getMessage());
}
 
// Đóng câu lệnh
unset($stmt);
 
// Đóng kết nối
unset($pdo);
?>

Bạn đã hiểu về Prepared Statement chưa?

Việc sử dụng Prepared Statement mang lại cho chúng ta nhiều lợi ích khi lập trình web. Đây là cách thực tế các lập trình viên hay làm để đảm bảo website đạt được sự an toàn và hiệu suất tốt.

Hãy ghi nhớ cách triển khai Prepared Statement bằng cả 3 cách, bạn chắc chắn sẽ cần nhiều đến chúng.

PHPDev