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

问题描述

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

根据我对准备好的陈述的理解:

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

前面的代码应该使用我建议的查询在数据库中创建某种缓冲区.现在了解我的信息(我可能很错了)，前面的代码是不安全的，因为字符串$sql可以是任何内容，具体取决于$id的实际含义，如果$id = 1; DROP TABLE myTable;--，即使我有一个准备好的语句，我也会插入一个恶意查询.

从我的理解那里是绑定我的参数的地方.如果我改为执行以下操作:

$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，则查询失败.

有人告诉我我的理解是正确的，而有人告诉我我的理解是错误的，有人可以让我知道我是错的，正确的还是缺失的东西吗?并根据您的需要进行了详尽的说明，非常感谢所有反馈！

解决方案

您正确的认为第一种情况是不安全的.不过，重要的是要理解，只有在使用可变数据和/或重复执行相同查询的情况下，准备语句才有价值.如果您要执行无变量的 普通语句，则只需执行以下操作:

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

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

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

另外，为了使生活更轻松，您可以使用如下准备的语句进行隐式绑定:

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

甚至是这样，带有未命名的参数:

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

自然，大多数情况已经在我输入答案的评论中解释了！

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

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

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

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

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屋！