Adam Johnson

Home | Blog | Training | Projects | Colophon

SQL's Implicit Type Conversion

2020-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:

SELECT * FROM session WHERE id = '929f9152-78aa-4a56-be59-df3241e4a16e';

The 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.

MySQL/MariaDB

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):

chainz@localhost [1]> select 22 = '22';
+-----------+
| 22 = '22' |
+-----------+
|         1 |
+-----------+
1 row in set (0.000 sec)

This is also the case when querying against a column:

chainz@localhost [4]> create table t(a int);
Query OK, 0 rows affected (0.045 sec)

chainz@localhost [5]> insert into t(a) values (22);
Query OK, 1 row affected (0.002 sec)

chainz@localhost [6]> select a from t where a = '22';
+------+
| a    |
+------+
|   22 |
+------+
1 row in set (0.001 sec)

However if not all characters are used in the conversion, MySQL does issue a warning:

chainz@localhost [2]> select 22 = '22a';
+------------+
| 22 = '22a' |
+------------+
|          1 |
+------------+
1 row in set, 1 warning (0.000 sec)

chainz@localhost [3]> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '22a' |
+---------+------+-----------------------------------------+
1 row in set (0.001 sec)

Converting warnings to errors could possibly have prevented Esau’s bug. However I haven’t ever seen an application configured to do this.

PostgreSQL

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’:

postgres=# select '22' = 22;
 ?column?
----------
 t
(1 row)

And whilst querying:

postgres=# create table t(a int);
CREATE TABLE
postgres=# insert into t(a) values (22);
INSERT 0 1
postgres=# select a from t where a = '22';
 a
----
 22
(1 row)

PostgreSQL is nice though and returns an error if the string doesn’t fully parse:

postgres=# select '22a' = 22;
ERROR:  invalid input syntax for integer: "22a"
LINE 1: select '22a' = 22;

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:

postgres=# select '22 ' = 22;
 ?column?
----------
 t
(1 row)

SQLite

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:

sqlite> select 22 = '22';
0

However using a table allows the column affinity to change the value:

sqlite> create table t(a int);
sqlite> insert into t(a) values (22);
sqlite> select a from t where a = '22';
22

SQLite just won’t find matches for converting a string with trailing letters in:

sqlite> select a from t where a = '22a';
sqlite>

So the bug also couldn’t happen here.

However it will trim spaces - and maybe other characters:

sqlite> select a from t where a = '22 ';
22

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.

Fin

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,

—Adam


Interested in Django or Python training? I'm taking bookings for workshops.


Subscribe via RSS, Twitter, or email:

One summary email a week, no spam, I pinky promise.

Related posts:

Tags: sql