SQL Query with binary data (PHP and MySQL)

Question

This site had helped me a lot in the past, but now I am lost. Thanks in advance for your guidance.

I have a MySQL table that contains a Binary value, like the example below. I cannot change the table.

CREATE TABLE `test` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `nid` binary(16) NOT NULL,
   `test` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`))

This an example value of nid: ÞFÈ>ZPÎ×jRZ{æ× (not all showing, but all 16 are there)

Now I want to create a SQL Query to look for the id of the row where this value is true.

SELECT id FROM test WHERE nid = 'ÞFÈ>ZPÎ×jRZ{æ×';

... does not work. Any idea?

SOLUTION Obtaining the nid in HEX format did the trick. It results in DE46C83E5A50CED70E6A525A7BE6D709 and when I use this in the query like this ...

SELECT id FROM test WHERE HEX(nid) = 'DE46C83E5A50CED70E6A525A7BE6D709';

I am getting the right result.

Rad

The field is pid, not nid. I'm not sure if that's a typo on your part? – Sachiko

Yep, that was a typo, sorry. Good catch :) – Rad

You should use nid = UNHEX('DE46C83E5A50CED70E6A525A7BE6D709') to use any indexes on nid – Violoncellist

Answer 1

Note: This addresses binary data, but not encrypted data. See this answer for searching on encrypted data.

Try adding X, x or 0x in front of binary data used for search:

SELECT id FROM test WHERE pid = '0xÞFÈ>ZPÎ×jRZ{æ×';

EDIT: try also this:

SELECT id FROM test WHERE BINARY pid = 'ÞFÈ>ZPÎ×jRZ{æ×';

OR

SELECT id FROM test WHERE HEX(pid) = BIN2HEX('0xÞFÈ>ZPÎ×jRZ{æ×');

as supposed here: How to select with a binary field ? (php,mysql)

IF NOTHING FROM ABOVE WORKS: Try obtaining the pid in HEX format, like

SELECT id, HEX(pid) pid, test FROM test

and then when searching try only:

SELECT id, test FROM test WHERE HEX(pid) = '{$my_pid}'

But I'm not sure how do You obtain the pid data to PHP or even whether You pass the binary data into Your select - where query... Just guessing due to the php tag...

Vibrant

Tested them all and nothing. I did read the mentioned post, that's why I am asking it here. Strange stuff. :) – Rad

The binary data You use in the query is comming out from PHP? How did You obtained that data? Are You sure the data from PHP is really a binary data? – Vibrant

Data is already in DB. I am using PHP for my app. I am seeing this in MySQLWorkbench's value editor: BINARY de 46 c8 3e 5a 50 ce d7 0e 6a 52 5a 7b e6 d7 09 – Rad

@Rad And how do the data appear when printed from PHP? I am quite sure that in MySQl they are represented as BINARY, but about PHP? Probably PHP is messing the data up... – Vibrant

SELECT id FROM test WHERE HEX(nid) = 'DE46C83E5A50CED70E6A525A7BE6D709'; works ... thank you thank you. This thing is strange. Isn't it? – Rad

You are welcome! Not so strange. The PHP was messsing the binary data (though I do not know the exact reason why that happened). If You obtain the binary data as string (in HEX format), then everything is OK - no data messed up. – Vibrant

I do not think it is PHP, as when I use MySQLWorkbench to run the query, I am getting the same result. I see the value in the DB, I copy it to my SQL Query, and it does not find the row. Only the trick with HEX that you proposed works. I find this strange. – Rad

Answer 2

try:

X''   --Hex Content

mysql> SELECT x'4D7953514C'；
    -> 'MySQL'

Savoy

Answer 3

The last posting from jixiang pointed me into the right direction for searching a binary field:

SELECT * FROM test WHERE yourBinaryColumn = x'binarystuffdata';

This works for me...

Benedic

Answer 4

For me it works without quotes in the binary field

SELECT * FROM `clients_addresses` WHERE client_id = 0x4f8472e23e63404fb8f9f56

Richmound