了解 PDO 准备好的语句和绑定参数 [英] Understanding PDO Prepared Statements and Binding Parameters

问题描述

根据经验以及不断被告知使用准备好的语句和绑定参数的好处，我一直在我的代码中使用这两种技术，但是我想确切地了解这两种技术中的每一种的目的:

From experience and also having been told constantly the benefits of using prepared statements and binding my parameters, I have constantly used those two techniques in my code, however I would like to understand exactly the purpose of each of those two techiques:

根据我对准备好的语句的理解:

From my understanding of prepared statements:

$sql = "SELECT * FROM myTable WHERE id = ".$id;
$stmt = $conn->prepare($sql);
$stmt->execute();

前面的代码应该使用我提出的查询在数据库中创建一种缓冲区.现在根据我的理解(我可能是非常错误的)，以前的代码是不安全的，因为字符串 $sql 可以是任何取决于 $id 实际上是，如果 $id = 1;DROP TABLE myTable;--，即使我有准备好的语句，我也会插入恶意查询.

The previous code should create a sort of a buffer in the database with the query I proposed. Now FROM MY UNDERSTANDING (and I could be very wrong), the previous code is insecure, because the string $sql could be anything depending on what $id actually is, and if $id = 1; DROP TABLE myTable;--, I would be inserting a malicious query even though I have a prepared statement.

根据我的理解这是绑定我的参数的地方.如果我改为执行以下操作:

FROM MY UNDERSTANDING this is where binding my parameters com in. If I do the following instead:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();

数据库应该事先知道sql语句的所有部分:SELECT 这些列: * FROM myTable 和 WHERE id = 用户输入的变量"，如果 "a variable that was input by the user" != a variable，则查询失败.

The database should know exactly all the parts of the sql statement before hand: SELECT these columns: * FROM myTable and WHERE id = "a variable that was input by the user", and if "a variable that was input by the user" != a variable, the query fails.

有人告诉我我的理解是正确的，有人告诉我这是错误的，如果我错了、正确或遗漏了什么，有人可以告诉我吗?并根据需要详细说明，非常感谢所有反馈！

I have been told by some my understanding is correct, and by others that it is false, could someone please let me know if I am wrong, correct, or missing something? And elaborate as much as you want, all feedback is greatly appreciated!

推荐答案

您说得对，第一种情况是不安全的.但是，重要的是要了解，准备语句仅在您使用可变数据和/或重复执行相同查询时才有价值.如果您正在执行没有变量的简单语句，您可以简单地这样做:

You're correct that the first case is insecure. It's important to understand though, that preparing a statement only has value if you are using variable data, and/or executing the same query repeatedly. If you are executing plain statements with no variables, you could simply do this:

$sql = "SELECT * from myTable WHERE this_column IS NOT NULL";
$result = $conn->query($sql);

最后得到一个 PDOStatement 对象，就像你使用 PDO::exec() 一样.

And end up with a PDOStatement object to work with, just like when you use PDO::exec().

对于你的第二种情况，你基本上是正确的.发生的事情是传递给数据库的变量被转义和引用(除非你用 PDOStatement::bindParam() 的第三个参数另外指定，它作为一个字符串发送，这在大多数情况下都很好.)因此，如果发送了错误数据，查询将不会失败".它的行为就像您传递了一个在数据库中不作为 ID 存在的有效数字一样.当然，有一些边缘情况，其中即使有正确准备的声明，您仍然容易受到攻击.

For your second case, again, you're largely correct. What's happening is the variable passed to the database is escaped and quoted (unless you specify otherwise with the third argument to PDOStatement::bindParam(), it's sent as a string which is fine for most cases.) So, the query won't "fail" if bad data is sent. It behaves exactly as if you had passed a valid number that didn't exist as an ID in the database. There are, of course, some edge cases where you are still vulnerable even with a correctly prepared statement.

此外，为了让生活更轻松，您可以使用这样的准备好的语句来进行隐式绑定:

Also, to make life easier, you can use prepared statements like this, to do implicit binding:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([":id"=>$id]);

或者甚至像这样，带有未命名的参数:

Or even like this, with un-named parameters:

$sql = "SELECT * FROM myTable WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);

当然，我在输入答案时已在评论中解释了大部分内容！

Naturally, most of this has been explained in the comments while I was typing up the answer!

这篇关于了解 PDO 准备好的语句和绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！