17

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.

dsolimano
  • 8,870
  • 3
  • 48
  • 63
user2007877
  • 173
  • 1
  • 1
  • 6

4 Answers4

11

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...

Scott Arciszewski
  • 33,610
  • 16
  • 89
  • 206
shadyyx
  • 15,825
  • 6
  • 60
  • 95
  • Tested them all and nothing. I did read the mentioned post, that's why I am asking it here. Strange stuff. :) – user2007877 Jan 24 '13 at 16:25
  • 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? – shadyyx Jan 24 '13 at 16:27
  • 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 – user2007877 Jan 24 '13 at 16:36
  • @user2007877 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... – shadyyx Jan 24 '13 at 16:38
  • SELECT id FROM test WHERE HEX(nid) = 'DE46C83E5A50CED70E6A525A7BE6D709'; works ... thank you thank you. This thing is strange. Isn't it? – user2007877 Jan 24 '13 at 16:42
  • 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. – shadyyx Jan 25 '13 at 10:02
  • 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. – user2007877 Jan 25 '13 at 17:10
4

try:

X''   --Hex Content

mysql> SELECT x'4D7953514C'；
    -> 'MySQL'
Robert
  • 5,278
  • 43
  • 65
  • 115
jixiang
  • 400
  • 1
  • 2
  • 10
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...

Snow
  • 141
  • 1
  • 4
2

For me it works without quotes in the binary field

SELECT * FROM `clients_addresses` WHERE client_id = 0x4f8472e23e63404fb8f9f56
murilosandiego
  • 21
  • 2