Clarity on PHP Prepared Statement Escaping

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

Answer 2

If you use prepared statements, you definitely don't need to escape parameters using real_escape_string() or any other similar function. In fact, if you escape it, you can end up with double-escaped values.

Your second example is completely valid.

Answer 3

The answer is stated in the article on Prepared Statements in the PHP docs:

Escaping and SQL injection

Bound variables are sent to the server separately from the query and thus cannot interfere with it. The server uses these values directly at the point of execution, after the statement template is parsed. Bound parameters do not need to be escaped as they are never substituted into the query string directly. [...]

Such a separation sometimes considered as the only security feature to prevent SQL injection, but the same degree of security can be achieved with non-prepared statements, if all the values are formatted correctly. It should be noted that correct formatting is not the same as escaping and involves more logic than simple escaping. Thus, prepared statements are simply a more convenient and less error-prone approach to this element of database security.

(emphasis mine).

Note that a prepared statement is compiled by the database engine before the parameters are bound. It really is the database engine that is capable of executing this query with arguments. At no time is an SQL string created that concatenates the string of the prepared SQL statement with the string version of the arguments.

Think of SQL as a language for us humans. The DB engine on the other hand does not need to have everything worded in a plain SQL string before it can do anything. On the contrary, it needs to parse SQL before it can do anything. So it can work with the two pieces of information (prepared statement, arguments) to produce the desired output without having to first build the complete SQL string. And because it does not build that string, it also does not need to escape anything. The arguments are not parsed again for SQL constructs (which would be the SQL injection risk). They are used as values, nothing more.