MySQL in PHP – Everyone is doing it wrong

Ok, so maybe not everyone. But, for the mostpart, people are doing MySQL queries wrong in their PHP code. What’s even scarier is that 90% of PHP tutorials I read teach it wrong, too.

Here’s why:

  • 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.
  • The mysql_real_escape_string function 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:

$user_id = mysql_real_escape_string($_GET['id']);
$query = "SELECT * FROM users WHERE id = {$user_id}";
$result = mysql_query($query);

Seems legit? Nope. I can inject that.

page.php?id= 0 OR 1=1

Whoops! You just returned your entire user table. Now, let’s make it worse:

page.php?id= 0 OR 1=1 AND is_admin=1 LIMIT 1

Still unconvinced by how nasty this is?

$id = mysql_real_escape_string($_GET['id']); // 1 OR 1=1
$query = "DELETE FROM table WHERE id = {$id}";

Oh shit, you just lost all your table data. Ain’t that a bitch?

Here’s the sane way to do it with mysqli:

$db = new mysqli( /* database connection info here */ );
$statement = $db->prepare("SELECT * FROM users WHERE id = ?");
$statement = bind_param("i", $_GET['id']);

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!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s