Các câu lệnh soạn sẵn
Trong hướng dẫn này, bạn sẽ học cách sử dụng các câu lệnh đã chuẩn bị sẵn trong MySQL bằng PHP.
Câu lệnh soạn sẵn là gì?
Một câu lệnh chuẩn bị sẵn (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ại thời điểm thực thi câu lệnh.
MySQLi hỗ trợ việc sử dụng trình giữ chỗ vị trí ẩn danh (?), như được hiển thị bên dưới:
Trong khi, PDO hỗ trợ cả trình giữ chỗ vị trí ẩn danh (?), cũng như trình giữ chỗ được đặt tên. Phần giữ chỗ được đặt tên bắt đầu bằng dấu hai chấm (:) theo sau là số nhận dạng, như sau:
VALUES(:first_name, :last_name, :email);
Việc thực hiện câu lệnh đã chuẩn bị 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 cú pháp 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, đồng thời lưu trữ nó để sử dụng sau này.
- 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à các giá trị này để thực thi nó.
Các câu lệnh chuẩn bị sẵn 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ụ, một loạt câu lệnh INSERT. Phần sau đây mô tả một số lợi ích chính của việc sử dụng nó.
Ưu điểm của việc sử dụng các câu lệnh soạn sẵn
Một câu lệnh đã chuẩn bị có thể thực thi lặp lại cùng một câu lệnh với hiệu quả cao, vì câu lệnh chỉ được phân tích cú pháp một lần nữa, 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ì sau mỗi lần thực thi, chỉ các giá trị trình giữ chỗ cần được truyền đến máy chủ cơ sở dữ liệu thay vì câu lệnh SQL hoàn chỉnh.
Các câu lệnh chuẩn bị sẵn cũng cung cấp khả năng bảo vệ mạnh mẽ chống lại SQL injection , vì các giá trị tham số không được nhúng trực tiếp vào 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 riêng biệt với truy vấn bằng cách sử dụ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 thi, sau khi mẫu câu lệnh được phân tích cú pháp. Đó là lý do tại sao các câu lệnh chuẩn bị í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 các câu lệnh đã chuẩn bị thực sự hoạt động như thế nào:
Thủ tục
Ví dụ
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sss", $first_name, $last_name, $email);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
$stmt->execute();
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
$stmt->execute();
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
?>Hướng đối tượng
Ví dụ
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sss", $first_name, $last_name, $email);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
$stmt->execute();
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
$stmt->execute();
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
?>PDO
Ví dụ
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt insert query execution
try{
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);
// Bind parameters to statement
$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);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
$stmt->execute();
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
$stmt->execute();
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not prepare/execute query: $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>Như bạn có thể thấy trong ví dụ trên, chúng ta đã chuẩn bị câu lệnh INSERT chỉ một lần nhưng thực thi nó nhiều lần bằng cách chuyển các tập tham số khác nhau.
Giải thích mã (kiểu thủ tục)
Bên trong câu lệnh SQL INSERT ( dòng không-12 ) của ví dụ trên, dấu hỏi (?) được sử dụng như trình giữ chỗ cho giá trị first_name , last_name , email.
Hàm mysqli_stmt_bind_param() ( dòng không-16 ) ràng buộc biến để giữ chỗ ( ?) trong mẫu câu lệnh SQL. Các trình giữ chỗ ( ?) sẽ được thay thế bằng các giá trị thực tế đượ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 làm đố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 ràng buộc là chuỗi.
Chuỗi định nghĩa kiểu chỉ định kiểu dữ liệu của các biến ràng buộc tương ứng và chứa một hoặc nhiều trong bốn ký tự sau:
- b - nhị phân (chẳng hạn như hình ảnh, tệp PDF, v.v.)
- d - double (số thập phân)
- i - số nguyên (số nguyên)
- s - chuỗi (văn bản)
Số lượng biến liên kết và số ký tự trong chuỗi định nghĩa kiểu phải khớp với số lượng trình giữ chỗ trong mẫu câu lệnh SQL.
Sử dụng dữ liệu đầu vào nhận được thông qua biểu mẫu web
Ở chương trước, chúng ta đã tạo một biểu mẫu 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 triển khai câu lệnh đã chuẩn bị. Bạn có thể sử dụng cùng một biểu mẫu HTML để kiểm tra ví dụ chèn tập lệnh sau, nhưng chỉ cần đảm bảo rằng bạn đang sử dụng đúng tên tệp trong thuộc tính action của biểu mẫu.
Dưới đây là mã PHP được cập nhật để chèn dữ liệu. Nếu bạn xem kỹ ví dụ, bạn sẽ thấy chúng tôi đã không sử dụng mysqli_real_escape_string() để loại bỏ ký tự đặc biệt của dữ liệu đầu vào của người dùng, giống như chúng tôi đã làm trong ví dụ chương trước. Vì trong các câu lệnh đã chuẩn bị, đầu 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, vì vậy chúng không cần phải được loại bỏ một cách chính xác.
Thủ tục
Ví dụ
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
// Set parameters
$first_name = $_REQUEST['first_name'];
$last_name = $_REQUEST['last_name'];
$email = $_REQUEST['email'];
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not execute query: $sql. " . mysqli_error($link);
}
} else{
echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
?>Hướng đối tượng
Ví dụ
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sss", $first_name, $last_name, $email);
// Set parameters
$first_name = $_REQUEST['first_name'];
$last_name = $_REQUEST['last_name'];
$email = $_REQUEST['email'];
// Attempt to execute the prepared statement
if($stmt->execute()){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not execute query: $sql. " . $mysqli->error;
}
} else{
echo "ERROR: Could not prepare query: $sql. " . $mysqli->error;
}
// Close statement
$stmt->close();
// Close connection
$mysqli->close();
?>PDO
Ví dụ
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt insert query execution
try{
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmt = $pdo->prepare($sql);
// Bind parameters to statement
$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);
// Execute the prepared statement
$stmt->execute();
echo "Records inserted successfully.";
} catch(PDOException $e){
die("ERROR: Could not prepare/execute query: $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>Ghi chú: Mặc dù loại bỏ ký tự đặc biệt thông tin đầu vào của người dùng là không bắt buộc trong các câu lệnh đã chuẩn bị, nhưng 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 thích hợp để bảo vệ chống lại việc khai thác tài nguyên hệ thống.