在本教程中,您将学习如何使用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语句模板中的占位符数匹配。
如果您还记得上一章,我们已经创建了一个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); ?>
注意:尽管在预处理语句中不需要转义用户输入,但是您应始终验证从外部源接收到的数据的类型和大小,并实施适当的限制以防止系统资源的利用。