Why should I use prepared statements if escaping is safe?

  1. Comments (2)

 $mysqli = new mysqli("localhost""root""""myDatabase");
$mysqli->set_charset("utf8");
$id mysqli_real_escape_string($mysqli,$_POST['id']);
$username mysqli_real_escape_string($mysqli,$_POST['username']);

$sql "SELECT * FROM users WHERE id='$id' AND username='$username'";
$result $mysqli->query($sql);

This oversimplified example, provided --

-- is technically safe.

It would be a nonsense, if some mechanism would fail to provide the functionality it is intended for, when used properly.

However

The problem here is not technical but sort of a methodological one.

As you can see, formatting a value for a database is effectively split into two parts, escaping variables and quoting values in the query. And this is where all the magic happens lies the cause for innumerable real life cases of SQL injections.

With your simple example, where all the code is bound together, it is hard to overlook the proper routine. But in the real life the code is much bigger. And these parts, escaping is done in one place and quoting is done in another. Or not. Nobody can tell actually. I'd just trust that this value has been escaped already. Or I will escape it just to be sure, and introduce extra backslashes in the data. Or I am a new dev, who don't understand the example you posted here, and I was watching a youtube video that said escaping prevents SQL injection. I know the value has been escaped already, so I can put in the query safely. And as it is an integer, why would I waste quotes on it?

Do you see where I am getting at?
Trust me, I've seen all these cases in the wild. Such a separated formatting introduces a total mess and a wast opportunity for injections.

This is why prepared statements are better. Not because escaping is somewhat "unsafe". But because prepared statement does the complete formatting, in a single place.


Related articles: