Providing PHP Programming Services to Design Professionals

Interested in Electronics?

Check out James' Embedded AVR/Arduino Development, and New Zealand Electronic Component Store

SQL Can Be Dangerous 

SQL, the Set Query Language used to communicate with relational databases such as MySQL is a carefully designed standard. But, it has a problem which with one, small, seemingly trivial mistake, your entire existence can come crashing to a earth shattering low point.

I present for your edification two queries, the right one, and the wrong one...

  1. update x set y = z where a = b
  2. update x set y = z

see the difference, the correct query has a "where clause" which specifies which items in the table 'x' should be updated (have their values changed), while in the incorrect query they (usually meticulous) programmer has accidentally omitted the where clause.

What happens when the innocently oblivious programmer executes query #2? Instead of changing only one or two rows of the database table, every single row gets modified. And the same applies to the following two queries, with no better results...

  1. delete from x where a = b
  2. delete from x

Even for a lay man it is plainly obvious the immediate and excruciating anguish felt when the unwitting programmer executes query #2 and in the matter of a few seconds (because it is a VERY fast operation when there is no comparison) the last year, or two, or ten's data has been relegated to the firey pits of hard drive hell.

The fact that this happens, that an operation with no where clause operates on all rows is not unknown, no, it's been part of SQL since the year dot, and every developer understands this. However, the number of times you actually want to update/delete with no where clause is very low, while the consequences of doing this accidentally, are severe indeed.

It's established that this is a bad thing, to allow so much damage, in so little time, with a very easy mistake. But how can we fix it? The answer is simplicity itself; disallow any modifying query that does not contain a where clause, and for those extremely rare cases where you actually want to operate over all records then a where clause of 'where 1 = 1' will do exactly the same thing (particularly if the database server optimizes it).

Unfortunatly popular database servers do not do this server side. I can't answer why, you'd have to ask the database server developers. However a client side fix is easy, most every developer uses a wrapper of some sort around database calls, for reasons of abstraction, or simplicity, or whatever, so a simple check of any executed query should be easy. For example in PHP for MySQL the following function used in place of mysql_query() will pick up the problem in most (but not all, you would have to properly parse the queries for that) situations and refuse to execute the query...

<?php function my_db_query($sql) { if(preg_match('/^s*(update|delete)/is', $sql) && !preg_match('/where/i', $sql)) { trigger_error('Cowardly refusing query', E_USER_WARNING); return FALSE; }
mysq_query($sql); } ?>

Such a simple solution to such a painfull problem.