MySQL Workbench is a pile of shit

I’m sure there’s plenty of people who would beg to differ, but in my opinion MySQL Workbench is a huge festering pile of shit.

Those of you who have been dealing with MySQL for more than a few years will remember that the administrative applications used to be separate. We had MySQL Query Browser and MySQL Administrator as separate programs. This worked quite well because each program’s goal was individual, so they could strive for an application that did its job well.

The MySQL Query Browser was designed to provide the same sort of interface that phpMyAdmin. It was relatively lightweight and only really had issues when dealing with intensive or laggy queries. MySQL Administrator was also pretty good at its job. It worked out of the box and provided a fast way to monitor and tweak your MySQL server instance.

Now MySQL Workbench tries to combine the two along with a database modelling tool. Anyone with any design experience and sense will immediately realise the problem with this. It means that they can no longer tailor the design and structure of the application to suit a particular task. Instead, it all has to be a generic one-size-fits-all solution that doesn’t do any of its jobs in an optimal way. One would expect that anyone developing an SQL server would understand this problem, but sadly not. You need to put the work in to develop a tool that works well, rather than just grabbing a generic off-the-shelf solution and shipping it.

Here’s a few specific gripes:

  • Uses three times the memory the old apps did.
  • Leaks memory like a hippocampectomy patient.
  • Performs horribly and freezes frequently.
  • One tool causing a crash (this happens often) crashes the rest of the tools.
  • You have to give the administrator tool full access credentials to your remote machine, i.e. an administrative user on the machine (yes, I’m serious)
  • On Linux you have to provide SSH access to the administrator tool.
  • On Windows you have to enable remote WMI and allow it through the firewall.
  • Random bugs occur (e.g. broken fonts, UI glitches, freezes) and don’t tend to get patched explicitly, though they do randomly get fixed on some platforms.
  • Interface is cluttered.
  • Tab names and sub-tab names aren’t descriptive, making it difficult to do much at once.

Seems to be that the Oracle model of software development is “if it ain’t broke, fuck with it until it’s broken”.

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!