Monthly Archives: February 2013

Why not MySQL

I’ve been a happy postgresql user for some years now. Just a quick reminder why I prefer PostgreSQL way of doing things (the proper way):

$ psql -c "select ''::text - ''::text"
ERROR:  operator is not unique: text - text
LINE 1: select '' - ''
                  ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Sounds about right, minus operator isn’t defined for two strings in general. I was horrified when going through my rss reader and stumbled upon Abusing MySQL string arithmetic for tiny SQL injections by Krzysztof Kotowicz:

mysql> select ''-'';
+-------+
| ''-'' |
+-------+
|     0 |
+-------+

The above works because when faced with minus operator, MySQL thinks that you are, by mistake passing it two doubles in strings and that an empty string defaults to zero.

PostgreSQL’s solution for above is superior as it let’s you know, hey, this is undefined. MySQL’s solution thinking it can/should fix this for you leads to security (or at least lingering hard to find bugs) problems, as shown in the article.