MySQL SHA1 散列不匹配

Question

我对 MySQL 用户表有一个奇怪的问题。我很快创建了一个简化版本作为测试用例。

我有下表

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');

然后我运行以下查询

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'

这给了我以下结果

动态盐不匹配，而静态盐会匹配。

这让我大吃一惊。谁能帮我指出这是什么原因？

我的MySQL版本是5.5.29

Answer 1

这是因为你的表的默认字符集。您似乎是在 UTF8 数据库上运行它，SHA1() 中的某些内容在不同字符集方面存在问题。

如果您将表声明更改为以下内容，它将再次匹配:

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

作为robertklep commented将字符串显式转换为字符也可以，基本上确保在使用 SHA1()

进行比较时使用相同的字符集

作为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).

这是 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.