How a hidden MySQL ‘feature’ triggered a significant security review

Avoid an easy to make type-conversion mistakes when working with MySQL

Last week my VP of Engineering and I were trying to debug a thorny issue where invalid rows were being returned in a MySQL query set.

Instead of applying the WHERE filter as expected, we were getting hundreds of rows returned that did not appear to meet the filter criteria.

The SQL appeared valid, and all our SQL statements use parameter bindings, so we had a high degree of confidence the filters we were applying we valid. Yet still, unexpected rows appeared in the result set.

Working example
Let me walk you through a simplified example of what we saw.

Take the following schema containing an auto-incrementing ID and a customer reference which is a string (VARCHAR).

COLUMN           TYPE        LENGTH  OTHER
=============================================================
id               INTEGER     11      (PRIMARY,AUTO, UNSIGNED)
customer_ref     VARCHAR     100     (NULLABLE)

And then fill it with some sample data (assume 1,000’s of similar rows)

id      customer_ref   
====================
1	cus_HDeTaf	
2       cus_HDRRFn
3       cus_HDkjaE
4       cus_HDpAbl
5	cus_HDeWCd
...
10000   cus_HDpIye