MySQL: Query with UUID (bytestring) as primary key not working

Question

I have a table in a MySQL database, that uses UUID v1 as the primary key. The UUIDs are stored optimized, as a 16-byte string, as described in https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/ . The MYSQL data type for the column is binary(16). All UUIDs are generated the same way, using a PHP library. MySQL is v5.0.12.

I retrieve rows from the table like this:

$where = sprintf("'%s'", $bytestring_uuid);

$wpdb->get_results(
                            "
                                SELECT *
                                FROM $my_table
                                WHERE id = $where 
                            "
            );

Now this usually works, but oddly, with some UUIDs the query fails and i can't figure out why.

Here are some UUIDs the query works with (in the original format, so you can read them) :

c80615fc-e441-11e8-b328-002522a6b241

d4c94f0c-e441-11e8-9316-002522a6b241

df11cade-e441-11e8-b3a5-002522a6b241

This one fails:

27c049c4-e67f-11e8-9e6f-002522a6b241

Error log:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?IÄžo' at line 3 for query SELECT * FROM my_table WHERE id = 'èäAÈü³(

Any ideas what's happening?

Answer 1

27 is single quote, 22 is double quote. Hence a serious need to escape the binary string. Yes, that link tells you how to take only 16 bytes for storing it.

Or... Instead of saying

WHERE uuid = '?IÄžo...'

capture the hex and say

WHERE uuid = UNHEX('27c049c4e67f11...');

That is, have $byte_string be 32 hex digits.