Storing the binary SHA1 hash in the mySQL BINARY column (20)

Question

I want to save the SHA1 hash in the BINARY column (20). I tried this by preparing INSERT INTO foo SET ( hash=? ) , And then binding the statement to a variable containing a 20-byte binary value, but received a runtime syntax error of "... hash = '\ 0 \ 0 # * $ ^! ... "". (I'm confused why executing a prepared statement will represent such values.) This post does not indicate that something is wrong with saving SHA1 in BINARY (20), but does not indicate how it done with SQL.

UPDATE : "Why binary and not hex?" There will be about a billion lines, so 20 extra bytes is significant, and they also tell me that numeric queries are twice as fast as string searches (and that BINARY fields will be treated as numeric data)

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

Answer 1

Use the UNHEX function to translate it before inserting:

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

You will have space requirements, but you can get some performance hits by converting your hash from binary to hex back to binary.

Answer 2

I would say that you do not need 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) 

Answer 3

If you are collecting the final SQL from strings, then the hexadecimal string prefix SHA1 0x . Here I assume that you are calculating or getting the given string SHA1, say in the variable strSha1

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

Or, if you are using parameterized or prepared SQL queries, 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 a plain string provided to you, not SHA1.