Odtnhj

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?

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.

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.