UNHEX() binary UUID comparisons silently return no rows (breaks Staatic) #538

Activity

1. ingipingi commented on Oct 22, 2026

   

   ingipingi

   on Oct 22, 2026

   More actions

   We hit the same bug with WPML 5.x, which keys its URL resolution cache (wp_icl_url_resolution_cache) and media lookup (wp_icl_media_url_lookup) by UNHEX(MD5(...)). Its background worker could never claim a queued row, so it posted to admin-ajax.php again on every request, forever (about 110 requests a minute per site).

   We've run the fix proposed here, the BLOB cast plus the decoder fixes, patched into 3.1.0 on local copies of our client sites since October 19. The WPML worker emptied its queue in one request and stopped. It still applies to 3.1.1.

   On the mixed comparison you raised: with the cast, UNHEX('4142') = 'AB' returns 0 instead of 1. That's how hexadecimal literals already behave on trunk (SELECT 0x4142 = 'AB' returns 0), since the driver translates them to SQLite BLOB literals. So the cast makes UNHEX() consistent with 0x… and X'…', and a general fix for binary vs. non-binary comparisons would cover both.

   Query	MySQL	trunk	With the cast
   SELECT UNHEX('4142') = 0x4142	1	0	1
   SELECT UNHEX('4142') = 'AB'	1	1	0
   SELECT 0x4142 = 'AB'	1	0	0

   Our change, against trunk (b5dc0cc)

   diff --git a/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php b/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
   index 57d72f7..f7b9f49 100644
   --- a/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
   +++ b/packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
   @@ -5305,6 +5305,17 @@ class WP_MySQL_On_SQLite extends PDO {
    				return 'LENGTH(' . $args[0] . ')';
    			case 'CONCAT':
    				return '(' . implode( ' || ', $args ) . ')';
   +			case 'UNHEX':
   +				/*
   +				 * The UNHEX() user-defined function returns a PHP string, which
   +				 * PDO SQLite hands back as TEXT. Binary columns hold BLOBs, and
   +				 * SQLite never considers a TEXT value equal to a BLOB, so lookups
   +				 * like "WHERE binary_col = UNHEX('...')" would never match.
   +				 * Casting the result to BLOB keeps the bytes and fixes that.
   +				 * It also makes UNHEX() consistent with hexadecimal literals,
   +				 * which are translated to SQLite BLOB literals (x'...').
   +				 */
   +				return sprintf( 'CAST(UNHEX(%s) AS BLOB)', implode( ', ', $args ) );
    			case 'FOUND_ROWS':
    				$found_rows = $this->found_rows;
    				if ( is_int( $found_rows ) ) {
   diff --git a/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-pdo-user-defined-functions.php b/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-pdo-user-defined-functions.php
   index 7b8828e..67ee401 100644
   --- a/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-pdo-user-defined-functions.php
   +++ b/packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-pdo-user-defined-functions.php
   @@ -748,11 +748,24 @@ class WP_SQLite_PDO_User_Defined_Functions {
    	 * in the argument as a hexadecimal number and converts it to the byte represented
    	 * by the number. The return value is a binary string.
    	 *
   -	 * @param string $number Number to be unhexed.
   +	 * Like MySQL, it returns NULL for NULL and for invalid hex digits, and reads
   +	 * an odd-length argument as if it had a leading zero (UNHEX('123') = 0x0123).
    	 *
   -	 * @return string Binary string
   +	 * @param string|null $number Number to be unhexed.
   +	 *
   +	 * @return string|null Binary string, or NULL.
    	 */
    	public function unhex( $number ) {
   +		if ( null === $number ) {
   +			return null;
   +		}
   +		$number = (string) $number;
   +		if ( ! preg_match( '/^[0-9a-fA-F]*$/D', $number ) ) {
   +			return null;
   +		}
   +		if ( 1 === strlen( $number ) % 2 ) {
   +			$number = '0' . $number;
   +		}
    		return pack( 'H*', $number );
    	}
    

   Tested with the mysql-on-sqlite suite: equality in both directions, a UUID via REPLACE(), IN, NOT IN, a column argument, a self-join, UPDATE, DELETE, the UNHEX(MD5(...)) cache key, and NULL, invalid, odd-length, and numeric input. The full suite passes, and check-cs is clean.