关于PHP准备好的语句转义的清晰性 [英] Clarity on PHP Prepared Statement Escaping
问题描述
我知道这是一个非常普遍的问题，并且已经研究了许多小时，对于确定的答案，我还是有些不确定.我不是PHP的专业人士，并且已经从事自学了一段时间.最近，我开始关注MYSQLi准备的语句(已经习惯了旧习惯).
I know this is quite a popular question and, having researched for many hours now, I am still a little unsure on a definitive answer. I am no pro at PHP and have been self teaching for a little while now. I have just recently got my head around MYSQLi prepared statements (having been used to the old practice).
我的主要问题是尝试在使用准备好的语句时找到使用真实转义字符串(或任何其他安全性)的要求的明确答案.
My main question is trying to find a definitive answer on the requirement to use real escape string (or any other security) when using prepared statements.
我已经准备好解决以下问题:
I have ready through the following questions:
Do PHP PDO prepared statments need to be escaped?
但是使用准备好的语句时，似乎存在着支持和反对转义数据的论点.还有很多关于PDO的提及，对我来说，这很令人困惑，因为我不是PHP的天才.
But there seem to be arguments for and against escaping data when using prepared statements. There is also a lot of mention of PDO which, for me, is very confusing as I am no genius with PHP.
我希望这个伟大的社区能够帮助我全面理解并给我答案(希望能以某种方式理解)，以便我不断进步.
I am looking to this great community to help me understand completely and give me an answer (in a way I hopefully understand) in order for me to progress.
为此，我有以下示例，请问是否有人可以通俗易懂地解释使用哪个，不使用哪个，更重要的是为什么?
To that end, I have the following examples and ask if someone could, in lay-mans terms, explain which to use, which not to use and more importantly, WHY?
我目前在我的整个代码中都使用它:
I am currently using this throughout my code:
$id = $conn->real_escape_string($_POST['id']);
$name = $conn->real_escape_string($_POST['name']);
$message = $conn->real_escape_string($_POST['message']);
$qry = $conn->prepare('INSERT INTO status (id, name, message, date) VALUES (?, ?, ?, NOW())');
$qry->bind_param('iss', $id, $name, $message);
$qry->execute();
$qry->close();
但是，我对上述示例问题的有限理解是，告诉我使用以下代码是安全的/可以的:
But, my limited understanding of the example questions above is telling me that it is safe/ok to use the following code:
$qry = $conn->prepare('INSERT INTO status (id, name, message, date) VALUES (?, ?, ?, NOW())');
$qry->bind_param('iss', $_POST['id'], $_POST['name'], $_POST['message']);
$qry->execute();
$qry->close();
那么，最好的方法是什么?很抱歉这个长期的问题.经过研究并试图理解它，我只是想确定并理解原因.
So, which is the best method? Sorry for the long winded question. Having researched it and trying to understand it I just want to be sure and understand the reasons.
感谢大家的时间和支持，我们将非常感谢您提供的任何帮助.
Thank you all for your time and support, I would very much appreciate any help provided.
推荐答案
NB:此答案使用了一种过于简单化的转义和准备好的语句实际功能的模型.
NB: This answer uses an overly-simplistic model of what escaping and prepared statements actually do.
SQL是一种语言.其中的某些字符具有特殊含义.例如，'分隔字符串的开头和结尾.
SQL is a language. Some characters in it have special meaning. For instance ' delimits the beginning and end of a string.
转义数据时，将\放在具有特殊含义的字符前面.这导致它们的意思是(例如)撇号"而不是字符串的结尾".
When you escape data, you put a \ in front of the characters with special meaning. That causes them to mean (for example) "An apostrophe" instead of "The end of the string".
所以:
$id = $conn->real_escape_string($_POST['id']);
因此，现在，如果ID中有'，它将不会破坏SQL.
So now, if there was a ' in the ID, it won't break the SQL.
使用绑定变量时，它将自动为您转义.
When you use a bound variable, it will automatically be escaped for you.
$qry->bind_param('iss', $id, $name, $message);
因此，现在，如果ID中有'，它将不会破坏SQL.
So now, if there was a ' in the ID, it won't break the SQL.
…，除了您已经做过.
因此，现在您将'转换为\'，然后转至\\\'，因为'被转义，然后又与\一起从第一次转义再次转义.
So now you have the ' turned into \' and then in to \\\' because the ' was escaped and then it was escaped again along with the \ from the first escape.
因此，现在第一个\已被视为数据(而不是特殊的SQL字符)，并已插入数据库中.
So now the first \ has been treated as data (instead of as a special SQL character) and inserted into the database.
使用准备好的语句.仅使用 准备好的语句.
Use prepared statements. Use only prepared statements.
(例外是当您使用无法执行预准备语句的变量进行操作时，例如动态表名，这种情况不应该经常出现).
(The exception is when you are doing things with variables where a prepared statement can't go, such as dynamic table names, which shouldn't be too often).
这篇关于关于PHP准备好的语句转义的清晰性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
