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.


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: