MySQL prepared statements trong PHP

Các câu lệnh được chuẩn bị (prepared statements) rất hữu ích để chống lại các cuộc tấn công SQL Injection.

Chuẩn bị câu lệnh và liên kết tham số

Một câu lệnh được chuẩn bị (prepared statement) là một tính năng được sử dụng để thực thi các câu lệnh SQL giống nhau (hoặc tương tự) lặp đi lặp lại với hiệu quả cao.

Một câu lệnh được chuẩn bị về cơ bản hoạt động như thế này:

1. Chuẩn bị: Một mẫu câu lệnh SQL được tạo và gửi đến cơ sở dữ liệu. Một số giá trị nhất định không được chỉ định mà thay bằng các ký tự ? (được gọi là tham số). Ví dụ: INSERT INTO MyGuests VALUES(?, ?, ?)
2. Cơ sở dữ liệu sẽ phân tích cú pháp, biên dịch và thực hiện tối ưu hóa truy vấn trên mẫu câu lệnh SQL và lưu trữ kết quả biên dịch mà không thực thi nó.
3. Thực thi: Sau đó, ứng dụng liên kết các giá trị với các tham số và cơ sở dữ liệu thực thi câu lệnh. Ứng dụng có thể thực thi câu lệnh bao nhiêu lần tùy ý với các giá trị khác nhau

So với việc thực thi trực tiếp các câu lệnh SQL, các câu lệnh được chuẩn bị có ba ưu điểm chính:

• Các câu lệnh được chuẩn bị giúp giảm thời gian phân tích cú pháp vì việc chuẩn bị truy vấn chỉ được thực hiện một lần (mặc dù câu lệnh được thực thi nhiều lần)
• Liên kết các tham số giúp thu nhỏ băng thông đến máy chủ vì bạn chỉ cần gửi các tham số mỗi lần truy vấn thay vì phải gửi toàn bộ truy vấn.
• Các câu lệnh được chuẩn bị rất hữu ích để chống lại SQL Injection. Bởi vì các giá trị tham số được truyền vào sau bằng một giao thức khác, không cần phải lọc các ký tự đặc biệt. Nếu mẫu câu lệnh gốc không xuất phát từ dữ liệu đầu vào từ bên ngoài, SQL Injection không thể xảy ra.

Chuẩn bị câu lệnh trong MySQLi

Ví dụ sau sử dụng các câu lệnh được chuẩn bị và liên kết các tham số trong MySQLi:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Như bạn thấy trong ví dụ trên có câu truy vấn SQL như sau:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"

Trong truy vấn SQL này chúng tôi chèn một dấu hỏi (?), đây là nơi chúng tôi muốn thay thế bằng một giá trị kiểu integer, string, double hoặc BLOB.

Sau đó, chúng tôi gọi hàm bind_param() như sau:

$stmt->bind_param("sss", $firstname, $lastname, $email);

Hàm này liên kết các tham số với truy vấn SQL và cho cơ sở dữ liệu biết các tham số là gì. Đối số “sss” liệt kê các kiểu dữ liệu của các tham số. Ký tự s nói với mysql rằng tham số là một chuỗi. Trong truy vấn chúng ta có 3 đối số kiểu string là $firstname, $lastname, $email nên chúng ta sẽ có 3 ký tự s (“sss”).

Đối số có thể là một trong bốn loại sau:

• i – integer
• d – double
• s – string
• b – BLOB

Chúng ta phải có một trong số này cho mỗi tham số.

Bằng cách nói cho mysql kiểu dữ liệu nào được yêu cầu, chúng ta đã giảm thiểu rủi ro của SQL Injection.

Lưu ý: Nếu chúng ta muốn chèn bất kỳ dữ liệu nào từ các nguồn bên ngoài (như dữ liệu đầu vào của người dùng), điều rất quan trọng là dữ liệu được lọc và xác thực.

Chuẩn bị câu lệnh trong PDO

Ví dụ sau sử dụng các câu lệnh được chuẩn bị và liên kết các tham số trong PDO:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // prepare sql and bind parameters
  $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
  VALUES (:firstname, :lastname, :email)");
  $stmt->bindParam(':firstname', $firstname);
  $stmt->bindParam(':lastname', $lastname);
  $stmt->bindParam(':email', $email);

  // insert a row
  $firstname = "John";
  $lastname = "Doe";
  $email = "[email protected]";
  $stmt->execute();

  // insert another row
  $firstname = "Mary";
  $lastname = "Moe";
  $email = "[email protected]";
  $stmt->execute();

  // insert another row
  $firstname = "Julie";
  $lastname = "Dooley";
  $email = "[email protected]";
  $stmt->execute();

  echo "New records created successfully";
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
?>