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

注意： 虽然预处理语句中不需要转义用户输入，但应始终验证从外部源接收的数据的类型和大小，并强制执行适当的限制以防止系统资源利用。