MySQL in PHP – Everyone is doing it wrong
mysql_functions are to be deprecated in future and have been advised against by the PHP developers.
- Concatenating query parameters as strings will always introduce SQL injection holes.
mysql_real_escape_stringfunction doesn’t actually prevent SQL injection.
- Your custom validation is broken. It has always been broken and it always will be broken. Because you will always miss something.
If you’re doing any of the above, you’re doing it wrong.
Here’s an example:
Seems legit? Nope. I can inject that.
Whoops! You just returned your entire user table. Now, let’s make it worse:
Still unconvinced by how nasty this is?
Oh shit, you just lost all your table data. Ain’t that a bitch?
Here’s the sane way to do it with mysqli:
This causes the data (in this case the id) to be completely isolated from any query language.
Here’s a checklist of “doing it right”:
- Use PDO or mysqli to create parameterised queries.
- Don’t allow any string concatenation for values.
- Avoid string concatenation for query language, except where absolutely necessary.
- When concatenating, use patterns that isolate user input from the query, e.g.
$sort = ($_GET['sort'] == "asc" ? "ASC" : "DESC")
- Validate all inputs using cast functions (e.g.
intval) and regex to avoid bad data being accepted. This has two benefits – all your data is valid and you mitigate vulnerabilities.
Now you’ve got no excuse to be doing it wrong!