Why should I use prepared statements if escaping is safe?
$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 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:
- Relative and absolute paths, in the file system and on the web server.
- Articles
- MVC in simpler terms or the structure of a modern web-application
- PHP error reporting
- How to get a single player's rank based on the score
- Do you really need to check for both isset() and empty() at the same time?
- Operator precedence or how does 'or die()' work.
- Numerical strings comparison
- What's wrong with popular articles telling you that foo is faster than bar?
- Do you abuse the null coalescing operator (and isset/empty as well)?
Got a question?
I am the only person to hold a gold badge in
,
and
on Stack Overflow and I am eager to show the right way for PHP developers.
Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you got.
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>before and an empty line after for a quote