关于PHP准备语句转义的清晰度

Question

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:

Prepared Statements, escape variables

IF I use mysqli prepared statements do i need to escape

Do PHP PDO prepared statments need to be escaped?

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.

Answer 1

NB: This answer uses an overly-simplistic model of what escaping and prepared statements actually do.

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".

So:

$id = $conn->real_escape_string($_POST['id']);

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

So now, if there was a ' in the ID, it won't break the SQL.

… except you have already done that.

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.

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