Bug #72856 SHA2(x, NULL) yields SHA2(x, 0) instead of NULL

• Submitted: 3 Jun 2014 21:01
• Modified: 19 Aug 2014 14:49
• Reporter: Arthur O'Dwyer
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.5.8, 5.5.31, 5.5.38, 5.6.20, 5.7.5
• OS: Any
• Assigned to: Tor Didriksen
• CPU Architecture: Any

[3 Jun 2014 21:01] Arthur O'Dwyer

Description:
SHA2 was implemented here: http://bugs.mysql.com/bug.php?id=13174
It's documented here: http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_sha2

The documented behavior is:

> "The second argument indicates the desired bit length of the result,
> which must have a value of 224, 256, 384, 512, or 0 (which is equivalent
> to 256). If either argument is NULL or the hash length is not one of
> the permitted values, the return value is NULL."

However, the actual implemented behavior is that NULL in the second position is treated as equivalent to 0.

This feels like a bug, because it is inconsistent with the way NULL is handled everywhere else in MySQL, and also it is inconsistent with MySQL's own documentation.

How to repeat:
SELECT SHA2("hello", 1), SHA2("hello", 0), SHA2("hello", NULL);

+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
| SHA2("hello", 1) | SHA2("hello", 0)                                                 | SHA2("hello", NULL)                                              |
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
| NULL             | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 |
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+

Suggested fix:
SHA2("hello", NULL) should return NULL as documented.

Alternatively, it should be explicitly documented as a synonym for SHA2("hello", 256).

[4 Jun 2014 4:43] MySQL Verification Team

Hello Arthur,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

[4 Jun 2014 4:44] MySQL Verification Team

// 5.6.20

mysql> SELECT SHA2("hello", 1), SHA2("hello", 0), SHA2("hello", NULL);
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
| SHA2("hello", 1) | SHA2("hello", 0)                                                 | SHA2("hello", NULL)                                              |
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
| NULL             | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 |
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1583 | Incorrect parameters in the call to native function 'sha2' |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

[4 Jun 2014 4:55] MySQL Verification Team

// 5.7.5

mysql> SELECT SHA2("hello", 1), SHA2("hello", 0), SHA2("hello", NULL);
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
| SHA2("hello", 1) | SHA2("hello", 0)                                                 | SHA2("hello", NULL)                                              |
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
| NULL             | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824 |
+------------------+------------------------------------------------------------------+------------------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1583 | Incorrect parameters in the call to native function 'sha2' |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

[19 Aug 2014 14:49] Paul DuBois

Noted in 5.7.5 changelog.

SHA2() failed to return NULL if the hash-length argument was NULL or
not one of the permitted values.