4

I'm using MySql 5.5.32 and trying to reproduce some code in a stored procedure that we have in the business layer so I can set passwords for people using sql. It appears that there is something wrong with the SHA2 function though, but maybe I'm missing something:

SELECT length(SHA2("bob", 512))

Returns 128. Shouldn't it be 64?

SELECT length(SHA2("bob", 256))

Which returns 64, so it appears that either I missing something, or there is a bug in SHA2. Any ideas?

prolificslacker
  • 49
  • 11
bpeikes
  • 3,495
  • 9
  • 42
  • 80
  • 7
    The length is specified in bits, and the returned *string* is encoded in hexadecimal. aka 4 bits = 1 char, so 512/4=128, 256/4=64. – Sammitch Feb 28 '14 at 20:10

3 Answers3

6

Comment was correct, even though the docs say that it returns binary string, it returns hex encoded string. To get the correct length use:

SELECT length(unhex(SHA2("bob", 512)));
Nae
  • 14,209
  • 7
  • 52
  • 79
bpeikes
  • 3,495
  • 9
  • 42
  • 80
6

I coded the patch for the SHA2() function in 2005 and contributed it to MySQL (the developers then edited my code a bit to match their coding standards).

The function always returned a string of hex digits, just like all the other hash functions in MySQL.

You are probably reading this statement in the documentation:

As of MySQL 5.5.6, the return value is a nonbinary string in the connection character set. Before 5.5.6, the return value is a binary string.

I can see how a reader would think this means it returns binary bytes, but that's a misinterpretation.

What is actually meant by that is that the string had a binary character set. It's still a plaintext string of hex digits. In fact, none of the hash functions in MySQL return a string of bytes as if you ran UNHEX() on it, they all return strings of hex digits. The length of a string of hex digits is twice the length of the equivalent binary bytes.

If you don't know I mean by a binary character set, see What is binary character set?

SHA2() was changed to use the connection character set in 5.5.6, whereas other hash functions were changed in the same way in 5.5.3.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yeah, the documentation should be more clear. I understood what they meant by "binary charater set", but still thought that they meant it was actually the bytes of the hash function, i.e. a binary blob of sorts. The docs should state: SHA2 returns the hash as a hex encoded string representing the hash. As of MySQL 5.5.6, the return value is a nonbinary string in the connection character set. Before 5.5.6, the return value is a binary string. – bpeikes Mar 03 '14 at 17:20
  • Feel free to submit a documentation bug report at bugs.mysql.com. – Bill Karwin Mar 03 '14 at 17:53
4

The SHA2(str, hash_length) function returns back a nonbinary string. Previously the SHA2 function in MySQL returned back a value of a binary string.

From the docs in the link I provided:

As of MySQL 5.5.6, the return value is a nonbinary string in the connection character set.

We can dissect all of this via the following SELECT statement:

SELECT SHA2("bob", 256) AS 'Hashed String', 
    UNHEX(SHA2("bob", 256)) AS 'Binary String',
    LENGTH(UNHEX(SHA2("bob", 256))) AS 'Byte Count'

Which via MySQL Workbench the output will result in this:

enter image description here

UPDATE Per Comment:

The UNHEX(str) function states in the documentation that it interprets each pair of characters in the inputted string as hexadecimal numbers. As for whether the input string is binary or nonbinary, the documentation answers that question for us here:

A NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval.

Derek W
  • 9,708
  • 5
  • 58
  • 67
  • BTW, SHA2 is not doing any encryption, it's hashing. Also, the comment in the docs about pre 5.5.6 doesn't pertain to the issue. The sample you provided will return the same value in either version. The documentation doesn't even state what type of string unhex expects as the argument. Does UNHEX take a binary string or a non-binary string? The docs don't say. – bpeikes Mar 03 '14 at 17:26
  • Yes, Hashing =/= Encryption. Bad habit. – Derek W Mar 03 '14 at 17:48
  • I updated my answer to address your last concern regarding the `UNHEX` argument. – Derek W Mar 03 '14 at 18:47