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, Jan 30 '13 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, Jan 30 '13 at 16:13
@ÁlvaroG.Vicario of course. The choices you make when implementing systems can impact the functionality/readability/etc — Bart Friederichs, Jan 30 '13 at 16:13
I just want to know whether it can be done, and how to do it. — Chap, Jan 30 '13 at 16:15
The `UNHEX` function can translate back to binary data from a HEX string. But I reckon you have it binary already. — Bart Friederichs, Jan 30 '13 at 16:17
I do have it in binary, but I'm unable to successfully bind that to the placeholder in the SQL INSERT state,ment. — Chap, Jan 30 '13 at 16:22

score 4 · Accepted Answer · answered Jan 30 '13 at 16:21

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 '13 at 16:21

Bart Friederichs

33,050
15
95
195

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? – Chap Jan 30 '13 at 16:29
What's the complete query? I tested and tried the `UNHEX` method on the MySQL command line. Also `HEX` to get it back, worked. – Bart Friederichs Jan 30 '13 at 16:30
`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. – Chap Jan 30 '13 at 16:33
2

Lose the parentheses around the field list. – Bart Friederichs Jan 30 '13 at 16:37
Where'd those come from?? Thanks! Still having other issues, but I'm past the binary problem. – Chap Jan 30 '13 at 16:53
With your permission I'll edit your answer to include showing how to do it in a prepared statement, since that was part of my questoin. – Chap Jan 30 '13 at 17:27
Apparently my edit was never "accepted." But UNHEX() is the way to solve this question, and it looks like you were the first with this answer. – Chap May 08 '13 at 00:18

Álvaro González · Answer 2 · 2013-01-31T15:14:56.140

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)

score 0 · Answer 3 · answered May 07 '13 at 12:42

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.

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

3 Answers3