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.

The field is pid, not nid. I'm not sure if that's a typo on your part? — BenM
– BenM, Commented Jan 24, 2013 at 16:10
You should use nid = UNHEX('DE46C83E5A50CED70E6A525A7BE6D709') to use any indexes on nid — Stuart Carnie
– Stuart Carnie, Commented Aug 5, 2013 at 18:53

Scott Arciszewski · Accepted Answer · 2018-10-30 13:17:46Z

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

edited Oct 30, 2018 at 13:17

Scott Arciszewski

34.3k17 gold badges94 silver badges212 bronze badges

answered Jan 24, 2013 at 16:12

shadyyx

16.1k7 gold badges62 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2007877 Over a year ago

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

shadyyx Over a year ago

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?

user2007877 Over a year ago

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

shadyyx Over a year ago

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

user2007877 Over a year ago

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

|

Robert · Accepted Answer · 2014-05-08 10:34:57Z

4

try:

X''   --Hex Content

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

edited May 8, 2014 at 10:34

Robert

5,31843 gold badges70 silver badges115 bronze badges

answered May 8, 2014 at 10:17

jixiang

4501 gold badge4 silver badges11 bronze badges

Comments

Snow · Accepted Answer · 2016-02-24 19:23:17Z

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

answered Feb 24, 2016 at 19:23

Snow

1411 silver badge4 bronze badges

Comments

murilosandiego · Accepted Answer · 2020-04-13 16:30:13Z

3

For me it works without quotes in the binary field

SELECT * FROM `clients_addresses` WHERE client_id = 0x4f8472e23e63404fb8f9f56

answered Apr 13, 2020 at 16:30

murilosandiego

312 bronze badges

Collectives™ on Stack Overflow

SQL Query with binary data (PHP and MySQL)

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related