Why should I use prepared statements if escaping is safe?
$mysqli = new mysqli("localhost", "root", "", "myDatabase");
$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.
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.