Converting a binary variable to a hex representation for mySQL statement in PHP

Question

I'm coding a tool to insert test data into a database. Some of the fields are blobs which are the (mcrypt) encrypted representations of strings.

I'm creating binary variables, but can't find a way to properly output it in the format I see in PHPmyAdmin when I export (known good) data as a reference.

For example:

I used PHPmyAdmin to export a known string. It produces a value of 0xe07861bbcaf39ad54a0b85389a9f08886997f8cafffe871b8569c2fcf3293bcc in the VALUES list.

Running bin2hex on my binary field (which I've confirmed contains the same contents as known good data) results in a representation of 7a49e1b3d7c6357cab6b4f9c61bc4d8535c23cbc8789e28ce9321993e9372c80

I can't find any documentation on how to properly convert binary PHP data to the (hex) format that mySQL uses. I've read the similar questions that seem related.

How can I get from a binary field to the 0x.... value that PHPmyAdmin makes?

Take a look a HEX() and UNHEX() mysql functions dev.mysql.com/doc/refman/5.0/en/… dev.mysql.com/doc/refman/5.0/en/… — Babblo
– Babblo, Commented Jun 7, 2013 at 15:44
«Running hex2bin on my binary...» - hex2bin stands for "from hexadecimal to binary", not the other way round. — Álvaro González
– Álvaro González, Commented Jun 10, 2013 at 11:57
I mentioned this too and got a downvote. So i deleted my answer, because i think that i don't understand this question... — larrydahooster
– larrydahooster, Commented Jun 10, 2013 at 12:02
Side note: there're of course valid usages for hexadecimal dumps but both PHP and MySQL can handle raw binary data just fine. Just in case... — Álvaro González
– Álvaro González, Commented Jun 10, 2013 at 12:04

Alix Axel · Accepted Answer · 2013-06-10 11:52:28Z

2

It's as simple as:

'0x' . bin2hex($bin)

As for the different outputs, my bet is that you are mixing up the original data.

edited Jun 10, 2013 at 11:52

answered Jun 10, 2013 at 11:42

Alix Axel

155k100 gold badges406 silver badges509 bronze badges

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

1 Comment

Andy Over a year ago

Thanks for confirming this - I thought I was going crazy. Your tip that I was mixing up the original data led me to double check the way the target package writes data. Although my code was correct, the reason it was spitting different values out was because the program the tool is for performs an additional function on the field just before writing to the database.

Collectives™ on Stack Overflow

Converting a binary variable to a hex representation for mySQL statement in PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related