How to insert hex /binary ? into a mysql database?

Question

i am trying to insert some hex values into a field of a mysql db

this is the kind of value i need to insert :

['D\x93\xb4s\xa5\x9eM\\\x14\xf3*\x95\xf9\x83\x1d*%P\xdb\xa2', 'D\xbf\xef\xb0\xc8\xff\x17\xc6Y6\xc6\xb4,p\xaa\xb1\xf2V\xdaa', 'D\xd7~~\x02\xd3|}\xfcN\xc1\x03\x97\x07\xb5<U\x16Y\x9e', '\xf3\xb6\xc2,Y/[i\x98\x93\x9d\xb2R\x93\x84\x12W\x1a3\x19', '\xf3\xb7\xce\x1f-n\x89\xb6\x87K\x9dsf\xcb=w\xab\x1a\xa0<', '\xf3\xbf7\x04d\xe6\xdf\xf8"9\x1d\x05\x01\xe4\xd4\xb0\xad\x80\xc0\xf5']

this is my table

 +--------------+--------------+------+-----+---------+-------+
    | Field        | Type         | Null | Key | Default | Extra |
    +--------------+--------------+------+-----+---------+-------+
    | consensus    | char(40)     | NO   |     | NULL    |       |
    | identityb32  | char(40)     | NO   |     | NULL    |       |
    | pubdate      | char(40)     | NO   |     | NULL    |       |
    | dirport      | char(6)      | NO   |     | NULL    |       |
    | ip           | char(40)     | NO   |     | NULL    |       |
    | orport       | char(40)     | NO   |     | NULL    |       |
    | identityhash | char(40)     | NO   |     | NULL    |       |
    | nick         | char(40)     | NO   |     | NULL    |       |
    | version      | char(40)     | NO   |     | NULL    |       |
    | flags        | varchar(500) | NO   |     | NULL    |       |
    | identity     | char(40)     | NO   |     | NULL    |       |
    | digest       | char(40)     | NO   |     | NULL    |       |
    | pubtime      | char(40)     | NO   |     | NULL    |       |
    +--------------+--------------+------+-----+---------+-------+
    13 rows in set (0.00 sec)

currently i am adding the hex data as i would do a normal string but this results in a non readable input being added:

D??s??M?*???*%P?

how can the hex data be added?

Search for UNHEX() and HEX(). The usage is pretty straight forward. — Andrei P.
– Andrei P., Commented Mar 13, 2015 at 13:38
Thanks i have tried (UNHEX(HEX(identity[c]))) but get the error NameError: name 'UNHEX' is not defined — user2065929
– user2065929, Commented Mar 13, 2015 at 13:41
I don't think it is either latin1 or utf8. Perhaps it is binary or some other BLOB type of stuff? — Rick James
– Rick James, Commented Mar 14, 2015 at 3:38

Ravi Chauhan · Accepted Answer · 2015-03-13 13:43:38Z

0

Check CHARSET

   CREATE TABLE `t` (
      `id` varchar(32) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

answered Mar 13, 2015 at 13:43

Ravi Chauhan

1,54513 silver badges30 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:51:12Z

0

Maybe the way really is HEX() and UNHEX() functions. But, this post maybe help Inserting hex value mysql

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Mar 13, 2015 at 13:47

Pablo Lopes

1382 silver badges10 bronze badges

Comments

Andrei P. · Accepted Answer · 2015-03-13 14:54:57Z

0

Those aren't hex values.

I may be way off on this, but the only way I found to insert your values, was like this:

$string = 'D\x93\xb4s\xa5\x9eM\\\x14\xf3*\x95\xf9\x83\x1d*%P\xdb\xa2';
$pattern = '#\\\#';
$replacement = '\\\\\\';
$insert_value = preg_replace($pattern, $replacement, $string);

After which proceed to insert. You could use VARCHAR(256) for the column.

I'm positive there are a lot of better ways to go about this.

Apparently I'm on the python part of SO. I thought it was PHP. This is what I get for subscribing to multiple tags.

edited Mar 13, 2015 at 14:54

answered Mar 13, 2015 at 14:34

Andrei P.

3124 silver badges11 bronze badges

2 Comments

user2065929 Over a year ago

I think you are right about them not being hex values, are they binary string values instead ?

Andrei P. Over a year ago

I honestly have no idea. Deciphering hex is a bit beyond my pay grade. If you want a quality answer head over to the C part of stackoverflow, those guys will help you out.

Collectives™ on Stack Overflow

How to insert hex /binary ? into a mysql database?

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related