Storing a binary SHA1 hash into a mySQL BINARY(20) column

Question

I want to store a SHA1 hash into a BINARY(20) column. I tried it by preparing INSERT INTO foo SET ( hash=? ) followed by executing the statement binding to a variable containing the 20-byte binary value, but got a runtime syntax error "... hash='\0\0#*$^!...'". (I'm baffled why executing a prepared statement would represent values like this.) This post doesn't indicate there's anything wrong with storing a SHA1 into a BINARY(20) column, but doesn't indicate how it's done with SQL.

UPDATE: "Why binary and not hex?" There will be about a billion rows, so 20 extra bytes is significant, and also I'm told that numeric lookups are twice as fast as string lookups (and that BINARY fields will be treated like numerics)

UPDATE 2: The error message was not complaining about the representation of the binary value, but about the parentheses around the SET list.

Yes: there will be about a billion rows, so 20 extra bytes is significant, and also I'm told that numeric lookups are twice as fast as string lookups (and that BINARY fields will be treated like numerics). — Chap
– Chap, Commented Jan 30, 2013 at 16:11
Right - I've read various posts about "it's easier to use the hex value", etc. We're experts. We can handle binary. ;-) — Chap
– Chap, Commented Jan 30, 2013 at 16:13

Bart Friederichs · Accepted Answer · 2013-01-30 16:21:52Z

4

Use the UNHEX function to translate it before insertion:

 INSERT INTO foo SET hash=UNHEX('0beec7b5ea3f0fdbc95d0dd47f3c5bc275da8a33');

You will have the space requirements tackled, but you could get some performance hits translating your hash from binary to hex back to binary.

answered Jan 30, 2013 at 16:21

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

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

7 Comments

Chap Over a year ago

I'm not sure what's causing the syntax error here...

execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(name_id='1', name_sha1=UNHEX('00000001823453423a34ec426f109497436924d9'), title=N')

Those single-quotes around '1' were apparently put there by mysql, although name_id is a bigint. does the name_sha1 assignment look correct?

Bart Friederichs Over a year ago

What's the complete query? I tested and tried the UNHEX method on the MySQL command line. Also HEX to get it back, worked.

Chap Over a year ago

INSERT INTO dw_name SET (name_id=?,name_sha1=UNHEX(?),title=?,prefix=?,first_name=?,middle_name=?,last_name=?,maturity_suffix=?,professional_suffix=?,company_name=?,gender_id=?,insert_date=?,update_date=?)

is the prepared stmt.

Bart Friederichs Over a year ago

Lose the parentheses around the field list.

Chap Over a year ago

Where'd those come from?? Thanks! Still having other issues, but I'm past the binary problem.

|

Álvaro González · Accepted Answer · 2013-01-31 15:14:56Z

1

I'd say you don't need to do anything special:

mysql> CREATE TABLE binary_test (
    ->     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     hash BINARY(20) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE binary_insert(IN hash BINARY(20))
    -> BEGIN
    ->     INSERT INTO binary_test (hash) VALUES (hash);
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL binary_insert( UNHEX(SHA1('password')) );
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM binary_test;
+----+----------------------+
| id | hash                 |
+----+----------------------+
|  1 | [¬aõ╔╣??♠é%♂l°3←~µÅÏ |
+----+----------------------+
1 row in set (0.00 sec)

edited Jan 31, 2013 at 15:14

answered Jan 30, 2013 at 16:31

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Comments

ϹοδεMεδιϲ · Accepted Answer · 2013-05-07 12:42:17Z

0

If you are piecing together the final SQL from strings, then prefix the SHA1 hex string with 0x. Here I assume that you compute or get given the SHA1 string, say in a variable strSha1

strSQL = "INSERT INTO TableName SET BinaryHash=0x";
strSQL .= strSha1;
DbConn.Query(strSQL);

Or if you are using parametrised or prepared SQLs, then see the pseudo code below.

preparedStmt = DbConn.Prepare("INSERT INTO TableName SET BinaryHash = UNHEX( ? )");
DbConn.Query(preparedStmt, strSha1);

Obviously, you can use the SHA1() function from MySQL in the latter case, if you have the string in plain given to you, rather than the SHA1 of it.

answered May 7, 2013 at 12:42

ϹοδεMεδιϲ

2,9384 gold badges34 silver badges54 bronze badges

Collectives™ on Stack Overflow

Storing a binary SHA1 hash into a mySQL BINARY(20) column

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related