PHP MySQL 預處理語句

在本教程中，你將學習如何使用 PHP 在 MySQL 中使用預處理語句。

什麼是預處理的宣告

預處理語句（也稱為引數化語句）只是一個包含佔位符而不是實際引數值的 SQL 查詢模板。這些佔位符將由執行語句時的實際值替換。

MySQLi 支援使用匿名位置佔位符（?），如下所示：

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

而 PDO 既支援匿名位置佔位符（?），也支援命名佔位符。命名佔位符以冒號（:）後跟識別符號開頭，如下所示：

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

預處理的語句執行包括兩個階段：準備和執行。

• 準備 - 在準備階段，建立 SQL 語句模板並將其傳送到資料庫伺服器。伺服器解析語句模板，執行語法檢查和查詢優化，並將其儲存以供以後使用。
• 執行 - 執行期間，引數值將傳送到伺服器。伺服器從語句模板建立一個語句，並使用這些值來執行它。

預處理語句非常有用，特別是在使用不同值多次執行特定語句的情況下，例如，一系列 INSERT 語句。以下部分介紹了使用它的一些主要好處。

使用預處理語句的優點

預處理語句可以高效率地重複執行相同的語句，因為語句只能再次解析，而它可以多次執行。它還最大限度地減少了頻寬使用，因為每次執行時只需要將佔位符值傳輸到資料庫伺服器而不是完整的 SQL 語句。

預處理語句還提供強大的 SQL 注入 保護，因為引數值不直接嵌入 SQL 查詢字串中。引數值使用不同的協議與查詢分開傳送到資料庫伺服器，因此不會干擾它。在解析語句模板之後，伺服器直接在執行點使用這些值。這就是為什麼預處理的語句不易出錯，因此被認為是資料庫安全中最關鍵的元素之一。

以下示例將向你展示預處理語句的實際工作方式：

程序導向式

<?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 the parameters values and execute
    the statement again to insert another row */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* Set the parameters values and execute
    the statement to insert a row */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    mysqli_stmt_execute($stmt);
    
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
 
// Close statement
mysqli_stmt_close($stmt);
 
// Close connection
mysqli_close($link);
?>

物件導向式

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

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

正如你在上面的示例中所看到的，我們只准備了一次 INSERT 語句，但通過傳遞不同的引數集執行了多次。

程式碼解釋

在上面示例的 SQL INSERT 語句（第 12 行）中，問號用作 first_name，last_name，email 欄位值的佔位符。

該 mysqli_stmt_bind_param() 函式（行號-16）在 SQL 語句中的模板中繫結變數到佔位符（?）。佔位符（?）將替換為執行時變數中儲存的實際值。作為第二個引數提供的 type 定義字串，即 sss 字串指定每個繫結變數的資料型別是字串。

型別定義字串指定相應繫結變數的資料型別，幷包含以下四個字元中的一個或多個：

• b - 二進位制（如影象，PDF 檔案等）
• d - 雙（浮點數）
• i - 整數（整數）
• s - 字串（文字）

繫結變數的數量和型別定義字串中的字元數必須與 SQL 語句模板中的佔位符數相匹配。

使用通過 Web 表單接收的輸入

如果你還記得上一章，我們已經建立了一個 HTML 表單來將資料插入到資料庫中。在這裡，我們將通過實現預處理語句來擴充套件該示例。你可以使用相同的 HTML 表單來測試以下插入指令碼示例，但只需確保你在 action 表單的屬性中使用正確的檔名。

這是用於插入資料的更新的 PHP 程式碼。如果你仔細看到這個例子，你會發現我們沒有使用它 mysqli_real_escape_string() 來轉義使用者輸入，就像我們在上一章的例子中所做的那樣。由於在預處理語句中，使用者輸入永遠不會直接替換為查詢字串，因此不需要正確轉義它們。

程序導向式

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

物件導向式

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

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

注意： 雖然預處理語句中不需要轉義使用者輸入，但應始終驗證從外部源接收的資料的型別和大小，並強制執行適當的限制以防止系統資源利用。