問題描述
根據(jù)經(jīng)驗以及不斷被告知使用準備好的語句和綁定參數(shù)的好處，我一直在我的代碼中使用這兩種技術(shù)，但是我想確切地了解這兩種技術(shù)中的每一種的目的:
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:
根據(jù)我對準備好的語句的理解:
From my understanding of prepared statements:
$sql = "SELECT * FROM myTable WHERE id = ".$id;
$stmt = $conn->prepare($sql);
$stmt->execute();
前面的代碼應(yīng)該使用我提出的查詢在數(shù)據(jù)庫中創(chuàng)建一種緩沖區(qū).現(xiàn)在根據(jù)我的理解(我可能是非常錯誤的)，以前的代碼是不安全的，因為字符串 $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.
根據(jù)我的理解這是綁定我的參數(shù)的地方.如果我改為執(zhí)行以下操作:
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();
數(shù)據(jù)庫應(yīng)該事先知道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.
有人告訴我我的理解是正確的，有人告訴我這是錯誤的，如果我錯了、正確或遺漏了什么，有人可以告訴我嗎?并根據(jù)需要詳細說明，非常感謝所有反饋！
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!
推薦答案
您說得對，第一種情況是不安全的.但是，重要的是要了解，準備語句僅在您使用可變數(shù)據(jù)和/或重復(fù)執(zhí)行相同查詢時才有價值.如果您正在執(zhí)行沒有變量的簡單語句，您可以簡單地這樣做:
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().
對于你的第二種情況，你基本上是正確的.發(fā)生的事情是傳遞給數(shù)據(jù)庫的變量被轉(zhuǎn)義和引用(除非你用 PDOStatement::bindParam() 的第三個參數(shù)另外指定，它作為一個字符串發(fā)送，這在大多數(shù)情況下都很好.)因此，如果發(fā)送了錯誤數(shù)據(jù)，查詢將不會失敗".它的行為就像您傳遞了一個在數(shù)據(jù)庫中不作為 ID 存在的有效數(shù)字一樣.當然，有一些邊緣情況，其中即使有正確準備的聲明，您仍然容易受到攻擊.
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]);
或者甚至像這樣，帶有未命名的參數(shù):
Or even like this, with un-named parameters:
$sql = "SELECT * FROM myTable WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);
當然，我在輸入答案時已在評論中解釋了大部分內(nèi)容！
Naturally, most of this has been explained in the comments while I was typing up the answer!
這篇關(guān)于了解 PDO 準備好的語句和綁定參數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！