CODEWITHSUNDEEP
mysqlpasswordssha
Maurice
Maurice

Reputation: 4940

MySQL SHA1 hash does not match

I have a weird problem with a MySQL users table. I have quickly created a simplified version as a testcase.

I have the following table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identity` varchar(255) NOT NULL,
  `credential` varchar(255) NOT NULL,
  `credentialSalt` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=ucs2 AUTO_INCREMENT=2 ;

INSERT INTO `users` (`id`, `identity`, `credential`, `credentialSalt`) VALUES
(1, 'test', '7288edd0fc3ffcbe93a0cf06e3568e28521687bc', '123');

And I run the following query

SELECT id,
       IF (credential = SHA1(CONCAT('test', credentialSalt)), 1, 0) AS dynamicSaltMatches,
       credentialSalt AS dynamicSalt,
       SHA1(CONCAT('test', credentialSalt)) AS dynamicSaltHash,
       IF (credential = SHA1(CONCAT('test', 123)), 1, 0) AS staticSaltMatches,
       123 AS staticSalt,
       SHA1(CONCAT('test', 123)) AS staticSaltHash
FROM   users
WHERE  identity = 'test'

Which gives me the following result

enter image description here

The dynamic salt does NOT match while the static salt DOES match.

This is blowing my mind. Can someone help me point out the cause of this?

My MySQL version is 5.5.29

Upvotes: 2

Views: 2291

Answers (1)

Ben
Ben

Reputation: 52903

It's because of the default character set of your table. You appear to be running this on a UTF8 database and something in SHA1() is having problems with the differing character sets.

If you change your table declaration to the following it will match again:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identity` varchar(255) NOT NULL,
  `credential` varchar(255) NOT NULL,
  `credentialSalt` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

SQL Fiddle

As robertklep commented explicitly casting your string to a character will also work, basically ensure you're using the same characterset when doing comparisons using SHA1()

As the encryption functions documentation says:

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

This was changed in version 5.5.3:

As of MySQL 5.5.3, the return value is a nonbinary string in the connection character set. Before 5.5.3, the return value is a binary string; see the notes at the beginning of this section about using the value as a nonbinary string.

Upvotes: 4

Related Questions