SQL's Implicit Type Conversion2020-03-06
At yesterday’s DJUGL meetup I heard a talk from Esau Rodríguez about a buggy new system deployment he survived. His team were deploying a new version of a system that changed the way it connected to its MySQL database, whilst simultaneously moving session keys from integers to UUID’s. When they discovered the database connection change was flaky, they rolled back to the previous version. This quickly lead to a user reporting being able to see another’s data!
How did this happen?
Well the answer was in MySQL’s type conversion behaviour. Using the new UUID session key with the old integer-based table lead to queries like:
id column is an integer, but the query uses a string.
MySQL and MariaDB convert strings to integers by parsing until the first non-digit or sign character.
So in this case, this will match a user with ID 929.
I first thought this was due to MySQL’s famous string truncation strings or numerical value adjustments - now fixed by the strict mode (on by default). But this is implicit type conversion, and it happens on all the SQL databases I looked at. Below I’ve briefly investigated the behaviour on MySQL/MariaDB, PostgreSQL, and SQLite.
Both MySQL and MariaDB should have the exact same behaviour here, though I didn’t fully check. I find the MariaDB documentation easier to read than the equivalent MySQL page, but the MySQL one is a bit more exhaustive.
I checked some of the behaviour with MariaDB 10.4.
A conversion inside a simple
SELECT will convert and return true (1):
This is also the case when querying against a column:
However if not all characters are used in the conversion, MySQL does issue a warning:
Converting warnings to errors could possibly have prevented Esau’s bug. However I haven’t ever seen an application configured to do this.
PostgreSQL’s documentation on type conversion is quite academic.
The examples use less common operators, such as the factorial operator, but some conversion still happens with
Testing using PostgreSQL 11.7, getting ‘t’ for ‘true’:
And whilst querying:
PostgreSQL is nice though and returns an error if the string doesn’t fully parse:
So at least Esau’s bug couldn’t happen on PostgreSQL. PostgreSQL does allow spaces though - and maybe other characters - cases where MySQL/MariaDB would still issue its warning:
SQLite’s data type model is the loosest, with a column’s data type (“affinity”) not restricting the actual types stored in rows. This allows much more flexibility than you’d probably think.
I’m testing with SQLite 3.24.0.
Implicit conversion doesn’t actually happen on a plain
SELECT, returning 0 for false:
However using a table allows the column affinity to change the value:
SQLite just won’t find matches for converting a string with trailing letters in:
So the bug also couldn’t happen here.
However it will trim spaces - and maybe other characters:
SQLite has no warning or error for this conversion as far as I can tell. I guess that makes sense since a string could be stored in an integer column.
I guess the main takeaway is that we need to be careful with the types our applications send to databases. If you’re using MySQL it might make sense to convert its warnings into errors.
Thanks to Esau for the talk and reviewing this post,
Interested in Django or Python training? I'm taking bookings for workshops.
One summary email a week, no spam, I pinky promise.
© 2020 All rights reserved.