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?

Your code also looks prone to SQL injections at first seight.. — Raymond Nijland, Nov 12 '18 at 19:54

score 0 · Answer 1 · answered Nov 12 '18 at 21:32

Solving your SQL injection problem will also solve that some characters in a binary format don't expand nicely in a PHP interpolated string.

Using a PDO or mysqli solution will fix both of your problems.

Also MySQL-5.0.12 that is so far out of any maintained state. You will run into troubles where the only solution is to upgrade. Sooner the better.

score 0 · Accepted Answer · answered Nov 12 '18 at 22:08

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.

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

2 Answers2