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
/* 尝试MySQL服务器连接。 假设您正在运行MySQL。
具有默认设置的服务器(没有密码的用户“root”) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
//检查连接
if($link === false){
    die("错误：无法连接。 " . mysqli_connect_error());
}
 
//使用预处理语句
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    //将变量作为参数绑定到预处理语句
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    /* 设置参数值并执行,该语句再次插入另一行 */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* 设置参数值并执行插入行的语句 */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    mysqli_stmt_execute($stmt);
    
    echo "记录插入成功。";
} else{
    echo "错误：无法准备查询： $sql. " . mysqli_error($link);
}
 
//关闭语句
mysqli_stmt_close($stmt);
 
//关闭连接
mysqli_close($link);
?>

<?php
/* 尝试MySQL服务器连接。 假设您正在运行MySQL。
具有默认设置的服务器(没有密码的用户“root”) */
$mysqli = new mysqli("localhost", "root", "", "demo");
 
//检查连接
if($mysqli === false){
    die("错误：无法连接。 " . $mysqli->connect_error);
}
 
// 使用预处理语句
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = $mysqli->prepare($sql)){
    // 将变量作为参数绑定到预处理语句
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    
    /* 设置参数值并执行。
    再次执行该语句以插入另一行 */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    $stmt->execute();
    
    /* 设置参数值并执行
        要插入行的语句 */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    $stmt->execute();
    
    echo "已成功插入记录。";
} else{
    echo "错误：无法准备查询：$sql. " . $mysqli->error;
}
 
//关闭语句
$stmt->close();
 
//关闭连接
$mysqli->close();
?>

<?php
/* 尝试MySQL服务器连接。 假设您正在运行MySQL。
具有默认设置的服务器(没有密码的用户“root”) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    // 将PDO错误模式设置为异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("错误：无法连接。 " . $e->getMessage());
}
 
//尝试执行插入查询
try{
    //使用预处理语句
    $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
    $stmt = $pdo->prepare($sql);
    
    //将参数绑定到语句
    $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);
    
    /* 设置参数值并执行,
      再次执行该语句以插入另一行 */
    $first_name = "Hermione";
    $last_name = "Granger";
    $email = "hermionegranger@mail.com";
    $stmt->execute();
    
    /* 设置参数值并执行
        要插入行的语句 */
    $first_name = "Ron";
    $last_name = "Weasley";
    $email = "ronweasley@mail.com";
    $stmt->execute();
    
    echo "记录插入成功。";
} catch(PDOException $e){
    die("错误：无法准备/执行查询: $sql. " . $e->getMessage());
}
 
// 关闭语句
unset($stmt);
 
//关闭连接
unset($pdo);
?>

如您在上面的示例中看到的，我们INSERT仅准备了一次语句，但是通过传递不同的参数集多次执行了该语句。

代码的用法（程序风格）

在上面示例的SQL INSERT语句中，问号用作first_name，last_name和email字段值的占位符。

mysqli_stmt_bind_param()函数将变量绑定到占位符（？）在SQL语句模板中。占位符（？）将替换为执行时变量中保存的实际值。作为第二个参数提供的类型定义字符串，即“sss”字符串指定每个绑定变量的数据类型为string(字符串)。

类型定义字符串指定相应绑定变量的数据类型，参数有以下四种类型:

• i - integer（整型）

• d - double（双精度浮点型）

• s - string（字符串）

• b - BLOB（binary large object:二进制大对象）

类型定义字符串中的绑定变量数和字符数必须与SQL语句模板中的占位符数匹配。

使用通过Web表单接收的输入

如果您还记得上一章，我们已经创建了一个HTML表单，用于将数据插入数据库。在这里，我们将通过执行预处理语句来扩展该示例。您可以使用相同的HTML表单来测试以下插入脚本示例，但只需确保action在表单的属性中使用了正确的文件名即可。

这是用于插入数据的更新的PHP代码。如果仔细查看示例，您会发现我们没有mysqli_real_escape_string()像上一章中的示例那样使用来转义用户输入。由于在预处理语句中，用户输入永远不会直接替换为查询字符串，因此不需要正确地对它们进行转义。

<?php
/* 尝试MySQL服务器连接。 假设您正在运行MySQL。
具有默认设置的服务器(没有密码的用户“root”) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
//检查连接
if($link === false){
    die("错误：无法连接。 " . mysqli_connect_error());
}
 
//使用预处理语句
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($link, $sql)){
    //将变量绑定到准备好的语句作为参数
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    //设定参数
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    
    //尝试执行预处理语句
    if(mysqli_stmt_execute($stmt)){
        echo "记录插入成功。";
    } else{
        echo "错误：无法执行查询： $sql. " . mysqli_error($link);
    }
} else{
    echo "错误：无法执行查询： $sql. " . mysqli_error($link);
}
 
// 关闭语句
mysqli_stmt_close($stmt);
 
//关闭连接
mysqli_close($link);
?>

<?php
/* 尝试MySQL服务器连接。 假设您正在运行MySQL。
具有默认设置的服务器(没有密码的用户“root”) */
$mysqli = new mysqli("localhost", "root", "", "demo");
 
//检查连接
if($mysqli === false){
    die("错误：无法连接。 " . $mysqli->connect_error);
}
 
//使用预处理语句
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = $mysqli->prepare($sql)){
    //将变量作为参数绑定到预处理语句
    $stmt->bind_param("sss", $first_name, $last_name, $email);
    
    //设置参数
    $first_name = $_REQUEST['first_name'];
    $last_name = $_REQUEST['last_name'];
    $email = $_REQUEST['email'];
    
    //尝试执行预处理语句
    if($stmt->execute()){
        echo "记录插入成功。";
    } else{
        echo "错误：无法执行查询： $sql. " . $mysqli->error;
    }
} else{
    echo "错误：无法执行查询： $sql. " . $mysqli->error;
}
 
//关闭语句
$stmt->close();
 
//关闭连接
$mysqli->close();
?>

<?php
/* 尝试MySQL服务器连接。 假设您正在运行MySQL。
具有默认设置的服务器(没有密码的用户“root”) */
try{
    $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
    //将PDO错误模式设置为异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("错误：无法连接。 " . $e->getMessage());
}
 
//尝试执行插入查询
try{
    //使用预处理语句
    $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
    $stmt = $pdo->prepare($sql);
    
    // 将参数绑定到语句
    $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);
    
    // 执行预处理语句
    $stmt->execute();
    echo "记录插入成功。";
} catch(PDOException $e){
    die("错误：无法准备/执行查询 $sql. " . $e->getMessage());
}
 
//关闭语句
unset($stmt);
 
//关闭连接
unset($pdo);
?>