PHP MySQL 预处理语句

预处理语句是一种数据库操作技术，它可以显著提高数据库操作的安全性和性能。本教程将介绍预处理语句的概念、优势以及如何在PHP中使用MySQLi和PDO实现预处理语句。

什么是预处理语句？

预处理语句（Prepared Statements）是一种数据库操作机制，它将SQL查询模板与实际参数分离。预处理语句首先发送SQL模板到数据库服务器进行编译，然后再传入实际参数执行。

为什么需要预处理语句？

预处理语句提供了以下主要优势：

防止SQL注入攻击：这是预处理语句最重要的优势，它可以安全地处理用户输入的数据
提高性能：同一个SQL模板只需编译一次，可多次执行
代码清晰度：将SQL逻辑与数据分离，使代码更易于阅读和维护
类型安全：可以指定参数的数据类型

安全警告：

在处理用户输入数据时，始终使用预处理语句或适当的转义函数来防止SQL注入攻击。不要直接将用户输入拼接到SQL语句中！

SQL注入攻击示例

以下是一个不使用预处理语句可能导致SQL注入的示例：

<?php
// 不安全的代码 - 不要这样做！
$username = $_POST['username'];
$password = $_POST['password'];

// 假设一个恶意用户输入：username='admin' OR '1'='1' --
$sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";

// 生成的SQL将变成：
// SELECT * FROM users WHERE username='admin' OR '1'='1' --' AND password='输入的密码'
// 这个查询将返回所有用户记录，因为 '1'='1' 始终为真，-- 会注释掉后面的条件
?>

使用MySQLi预处理语句

MySQLi扩展提供了面向对象和过程式两种方式来使用预处理语句。

1. MySQLi面向对象方式

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

// 绑定参数
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 设置参数并执行
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

// 可以使用相同的预处理语句插入其他记录
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "新记录插入成功";

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

2. MySQLi过程式方式

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// 准备预处理语句
$stmt = mysqli_prepare($conn, "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

// 绑定参数
mysqli_stmt_bind_param($stmt, "sss", $firstname, $lastname, $email);

// 设置参数并执行
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
mysqli_stmt_execute($stmt);

// 可以使用相同的预处理语句插入其他记录
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
mysqli_stmt_execute($stmt);

echo "新记录插入成功";

// 关闭语句和连接
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

使用PDO预处理语句

PDO（PHP数据对象）也提供了预处理语句的支持，并且可以使用命名参数或问号占位符。

1. PDO使用问号占位符

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 准备预处理语句
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
    
    // 绑定参数
    $stmt->bindParam(1, $firstname);
    $stmt->bindParam(2, $lastname);
    $stmt->bindParam(3, $email);
    
    // 设置参数并执行
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
    
    // 可以使用相同的预处理语句插入其他记录
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();
    
    echo "新记录插入成功";
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

2. PDO使用命名参数

命名参数可以使代码更易读，特别是当SQL语句有很多参数时。

<?php
// 数据库连接参数
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 尝试连接数据库
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 准备预处理语句，使用命名参数
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
    
    // 方式1：绑定参数
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);
    
    // 设置参数并执行
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
    
    // 方式2：直接在execute中传递参数数组（不需要bindParam）
    $stmt->execute([
        ':firstname' => "Mary",
        ':lastname' => "Moe",
        ':email' => "mary@example.com"
    ]);
    
    echo "新记录插入成功";
} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

// 关闭连接
$conn = null;
?>

预处理语句的数据类型

在MySQLi中，绑定参数时可以指定数据类型。常用的数据类型如下：

i - integer（整数）
d - double（双精度浮点数）
s - string（字符串）
b - blob（二进制数据）

<?php
// 绑定不同类型的参数
$stmt = $conn->prepare("INSERT INTO products (name, price, stock, description) VALUES (?, ?, ?, ?)");
$stmt->bind_param("sdss", $name, $price, $stock, $description);

// s: string, d: double, s: string, s: string
$name = "产品名称";
$price = 99.99;
$stock = 100;
$description = "产品描述";
$stmt->execute();
?>

预处理语句获取结果

除了执行INSERT语句外，预处理语句也常用于执行SELECT查询并获取结果。

1. MySQLi获取结果

<?php
// 数据库连接代码...

// 准备预处理语句
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = ?");
$stmt->bind_param("s", $lastname);

// 设置参数并执行
$lastname = "Doe";
$stmt->execute();

// 绑定结果变量
$stmt->bind_result($id, $firstname, $lastname);

// 获取结果
while ($stmt->fetch()) {
    echo "ID: " . $id . " - 名: " . $firstname . " - 姓: " . $lastname . "<br>";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

2. PDO获取结果

<?php
// 数据库连接代码...

// 准备预处理语句
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = :lastname");

// 执行查询
$stmt->execute([
    ':lastname' => "Doe"
]);

// 设置获取模式
$stmt->setFetchMode(PDO::FETCH_ASSOC);

// 获取结果
while ($row = $stmt->fetch()) {
    echo "ID: " . $row['id'] . " - 名: " . $row['firstname'] . " - 姓: " . $row['lastname'] . "<br>";
}

// 或者一次性获取所有结果
$allRows = $stmt->fetchAll();
foreach ($allRows as row) {
    // 处理每一行数据
}

// 关闭连接
$conn = null;
?>

预处理语句的高级用法

1. 获取插入的ID

<?php
// MySQLi方式
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
$stmt->execute();
$last_id = $stmt->insert_id;
echo "新记录创建成功，ID为: " . $last_id;

// PDO方式
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->execute(["John", "Doe", "john@example.com"]);
$last_id = $conn->lastInsertId();
echo "新记录创建成功，ID为: " . $last_id;
?>

2. 获取受影响的行数

<?php
// MySQLi方式
$stmt = $conn->prepare("UPDATE MyGuests SET lastname=? WHERE id=?");
$stmt->bind_param("si", $lastname, $id);
$lastname = "Doe";
$id = 1;
$stmt->execute();
$affected_rows = $stmt->affected_rows;
echo "受影响的行数: " . $affected_rows;

// PDO方式
$stmt = $conn->prepare("UPDATE MyGuests SET lastname=? WHERE id=?");
$stmt->execute(["Doe", 1]);
$affected_rows = $stmt->rowCount();
echo "受影响的行数: " . $affected_rows;
?>

3. 使用LIKE语句

<?php
// MySQLi方式
$searchTerm = "%doe%"; // 注意通配符%应该在绑定参数前添加
$stmt = $conn->prepare("SELECT * FROM MyGuests WHERE lastname LIKE ?");
$stmt->bind_param("s", $searchTerm);
$stmt->execute();

// PDO方式
$searchTerm = "%doe%";
$stmt = $conn->prepare("SELECT * FROM MyGuests WHERE lastname LIKE :searchTerm");
$stmt->execute([
    ':searchTerm' => $searchTerm
]);
?>

预处理语句与事务

预处理语句和事务一起使用可以提高数据操作的安全性和性能。

<?php
// 数据库连接代码...

// 开始事务
$conn->begin_transaction();

try {
    // 准备多个预处理语句
    $stmt1 = $conn->prepare("INSERT INTO orders (customer_id, total) VALUES (?, ?)");
    $stmt1->bind_param("id", $customer_id, $total);
    
    $stmt2 = $conn->prepare("INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)");
    $stmt2->bind_param("iiid", $order_id, $product_id, $quantity, $price);
    
    $stmt3 = $conn->prepare("UPDATE products SET stock = stock - ? WHERE id = ?");
    $stmt3->bind_param("ii", $quantity, $product_id);
    
    // 执行第一个语句
    $customer_id = 1;
    $total = 199.98;
    $stmt1->execute();
    
    // 获取插入的订单ID
    $order_id = $stmt1->insert_id;
    
    // 执行第二个和第三个语句（订单项目和库存更新）
    $product_id = 101;
    $quantity = 2;
    $price = 99.99;
    $stmt2->execute();
    $stmt3->execute();
    
    // 提交事务
    $conn->commit();
    echo "订单处理成功";
} catch (Exception $e) {
    // 发生错误时回滚事务
    $conn->rollback();
    echo "错误: " . $e->getMessage();
}

// 关闭语句和连接
$stmt1->close();
$stmt2->close();
$stmt3->close();
$conn->close();
?>

预处理语句的性能考虑

预处理语句不仅提高了安全性，还可以提高性能，特别是当需要多次执行相同的SQL语句时。

编译一次，执行多次：数据库只需编译SQL模板一次，之后可以多次执行
减少网络传输：只传输参数数据，而不是完整的SQL语句
减少解析开销：数据库服务器不需要每次都解析整个SQL语句

<?php
// 性能比较示例
$start_time = microtime(true);

// 准备预处理语句
$stmt = $conn->prepare("INSERT INTO test_table (data) VALUES (?)");
$stmt->bind_param("s", $data);

// 执行1000次
for ($i = 0; $i < 1000; $i++) {
    $data = "测试数据 " . $i;
    $stmt->execute();
}

$end_time = microtime(true);
$execution_time = ($end_time - $start_time);
echo "预处理语句执行时间: " . $execution_time . " 秒";
?>

最佳实践

始终使用预处理语句：处理用户输入时，永远使用预处理语句或适当的转义函数
选择合适的参数类型：在MySQLi中，为参数指定正确的数据类型
重用预处理语句：对于需要多次执行的相同SQL模板，重用同一个预处理语句
与事务结合使用：处理多个相关的数据操作时，使用事务确保数据一致性
关闭语句和连接：操作完成后，及时关闭预处理语句和数据库连接
处理异常：使用try-catch块捕获和处理可能出现的错误
使用命名参数：在PDO中，使用命名参数可以提高代码可读性
避免SQL注入：记住，预处理语句是防止SQL注入的最有效方法之一

安全最佳实践：

预处理语句是防御SQL注入的强大工具，但它不能替代其他安全措施。始终对所有用户输入进行验证和过滤，特别是在显示给用户之前。