dinsdag 25 november 2014

Realworld problems with MySQL, #1.

I am very aware of all the shortcomings in MySQL, but they still bite me in the behind almost daily. Today was one of those days.

Case in point: some records were accidentally removed from the database. These things happen, people click the wrong button every now and again. Restore from backup is not an option, so I restored a backup to a temporary database so I could copy the records over.

Obviously I ran the insert queries from the command line tool (using the query-editor) inside a transaction to make sure that all queries would work and I would not be left with a broken set of data.


And I was right to do so because one of the id's had changed since the backup was made. No worries, there is no COMMIT statement so all the data  is still unchanged. Just fix the query and try again. This time I got a bunch of unique constraint violations.


I had not given any commits yet and the block ends with a ROLLBACK so why did the data suddenly already exist?

And then I remembered...

Yes I put the queries in a transaction but when a query fails in the commandline tool. It stops executing, so it never executed the  ROLLBACK and in MySQL, a BEGIN is executed as "COMMIT;BEGIN;", so when I fixed the first error and tried again, it had secretly committed the queries that did work during the first run.

And now my data really was fscked.

Any other database will give you an error telling you to close the current transaction before opening a new one, saving you from destroying your data by *NOT COMMITTING*.

I don't really have words for just how stupid it is that you can commit data by *not* doing a commit... it's just... argh!

maandag 3 november 2014

A story of fail that never had to be.

Some time ago there was an SQL-injection exploit in Drupal. The issue was tracked to a combination of issues including PDO. The problem is typical for  PHP/MySQL developers attitude towards security, which seems to be "f*ck it".

One reason why prepared statements exist is that they split the parsing of the query syntax from the data. Parsing them separately means they cannot influence eachother and nothing you can put into the data will ever change the way the query is parsed.

This all must have seemed quite silly to the creators of PDO, who decided to implement their own version of prepared statements, which did not suffer from the roundtrip delay or the one-query-at-a-time limit. Unfortunately that does mean that the values are not validated, and that you can still inject anything you want into the query.

The article I refer to mentions that everybody makes mistakes, which is true, but what the PDO guys did is not a mistake, they went out of their way to make a feature that did not implement any of the safety features that people use prepared statements for. And not because they thought that they were not safe enough, but because they thought it was too slow and inconvenient.

That's stupidity of a whole other level.