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

• the connection character set is properly set
• the processed value is surrounded by single quotes
• no weird SQL modes such as NO_BACKSLASH_ESCAPES used

-- 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 simplified 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 more complex, consisting of large distinct modules. And escaping is done in one in one module while quoting 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 escaping characters 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?

Or I know that the data has been escaped already when it was entering the application, so I won't have to escape it during some internal manipulations sometime later (when moving into another table for example). And have a first class second order SQL injection as a result.

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 the whole world better. Not because escaping is somewhat "unsafe". But because prepared statement does the complete formatting, in a single place.