PHP MySQL Saving Binary string into BLOB Field can't retrieve binary value

Question

I am trying to save a string in binary to save space in my MySQL DB. However, I am unable to retrieve the binary data correctly, so what am I doing wrong?

I have a database like:

CREATE TABLE `Test` (
    ID INT NOT,
    Value BLOB
    );

I insert the data with statements like:

INSERT INTO `Test` (ID, Value) VALUES(1, b'10000001');

However, I can't retrieve the binary string when I select the value from the inserted row with the underneath query:

SELECT * FROM `Test`; //NOT WORKING. Returns Value=0
SELECT ID, BIN(Value) FROM `Test`; //NOT WORKING. Returns Value=0

What should I use? I have already tried using BIN(Value) in my query as shown above, but without any success. Thanks in advance.

VALUES(1, b'10000001' if that's your actual query, it failed. Why is there a b in there and outside the quoted value? — Funk Forty Niner
– Funk Forty Niner, Commented Sep 7, 2017 at 21:53
@Fred-ii-, to tell the Database it's a binary value? I read that on the internet, not sure or it is correct but I can't get any solution to work..? — TVA van Hesteren
– TVA van Hesteren, Commented Sep 7, 2017 at 21:55
you may be right. You tagged as php though, is there relevant code for it? When you run this in phpmyadmin (or similar), what error(s) does it throw? and this is unclear to me ID INT NOT, with the NOT, I've never seen that before. — Funk Forty Niner
– Funk Forty Niner, Commented Sep 7, 2017 at 22:02
@TVAvanHesteren not in mysql. Either use a prepared statement and bind in the unmolested binary data, or hex-encode the data like 0x81. — Sammitch
– Sammitch, Commented Sep 7, 2017 at 22:03

Sammitch · Accepted Answer · 2017-09-07 22:26:31Z

Starting with a literal string of ones and zeroes is incredibly odd, but:

$ones_and_zeroes = '10000001';

// If you have a literal string of ones and zeroes that you need to convert
// to an actual number there's base_convert()

$hex_string = base_convert($ones_and_zeroes, 2, 16); 

// $hex_string is now "81"

// you can convert this to an *actual* binary string with hex2bin()
$bin = hex2bin($hex_string);

// $bin now contains the byte 0x81

// now if you want to do it right:
$dbh = new PDO( ... );
$stmt = $dbh->prepare('INSERT INTO `Test` (ID, Value) VALUES(1, :bin)');
$stmt->execute(['bin'=>$bin]);

// or whatever the gross mysqli equivalent is.

I'm not going to show you how to do it wrong in PHP, but of you wanted to hand-write this query it would be:

INSERT INTO `Test` (ID, Value) VALUES(1, 0x81);

Hex-encoded data prefixed with '0x' being the only safe way [that I am aware of] to embed binary data in a text query, though it is EMINENTLY preferable to use prepared statements whenever possible as hex-encoding data like this doubles the length.

Kudehinbu Oluwaponle · Accepted Answer · 2017-09-07 22:23:40Z

0

The best advice is to check if the insert query ran successfully i.e. with expected data inserted into DB using any MySQL admin tool.
Only then can you isolate whether the problem is with the retrieval or the insets itself

answered Sep 7, 2017 at 22:23

Kudehinbu Oluwaponle

1,18513 silver badges11 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Saving Binary string into BLOB Field can't retrieve binary value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related