SQL server中的拉丁字符与雪花数据库的SHA1不匹配的SHA1值

Question

我正在尝试匹配SQL Server和Snowflake表中某些数据的sha1值。

我已经得到了sha1的拉丁字符在SQL服务器在以下方式-

select  sys.fn_varbintohexsubstring(0, HASHBYTES('SHA1',cast('á'  as varchar(1))),1,0) 

返回b753d636f6ee46bb9242d01ff8b61f715e9a88c3

对于相同的字符，Snowflake中的sha1函数返回不同的值。

select sha1(cast('á' as varchar))
Result - 2b9cc8d86a48fd3e4e76e117b1bd08884ec9691d

注意- SQL Server中的数据类型是nvarchar，而雪花中的数据类型是带有默认排序规则的varchar。对于英文字符，sha1值在将nvarchar转换为varchar后匹配。但是，对于拉丁字符则不是这样。

是否有一种方法来匹配非英语字符的sha1值?我需要在SQL Server 2017中获得值'2b9cc8d86a48fd3e4e76e117b1bd08884ec9691d' &

感谢

Answer 1

TL;在计算哈希值时不要使用varchar。在这个过程中，你可以踩到太多的耙子。

只是作为一个例子，我改编了你的代码，更容易理解，并在一个数据库的上下文中运行它有Latin1_General_100_CI_AS默认排序:

declare @a nchar(1) = N'á';
declare @b char(1) = cast(@a as char(1));
select @b as [Char], ascii(@b) as [A], unicode(@b) as [U], HASHBYTES('SHA1',@b) as [Hash]
union all
select @a, ascii(@a), unicode(@a), HASHBYTES('SHA1',@a);

结果是:

Char    A    U Hash
---- ---- ---- ------------------------------------------
á     225  225 0xB753D636F6EE46BB9242D01FF8B61F715E9A88C3
á     225  225 0xA4BCF633D5ECCD3F2A55CD0AD3D109A108A45F02

但是，如果我将数据库上下文更改为另一个DB，使用Cyrillic_General_100_CI_AS排序，相同的代码突然返回不同的值:

Char    A    U Hash
---- ---- ---- ------------------------------------------
a      97   97 0x86F7E437FAA5A7FCE15D1DDCB9EAEAEA377667B8
á      97  225 0xA4BCF633D5ECCD3F2A55CD0AD3D109A108A45F02

可以看到，第一行中的[Char]现在是一个不同的字符(小写拉丁语";这种隐式的代码页调整是无法避免的，除非您的数据是Unicode或二进制形式的。

---

选择

1. 升级到MS SQL Server 2019，或迁移到Azure SQL数据库。从这个版本开始，您实际上可以使用UTF-8编码存储字符串，尽管您可能会因此而受到性能影响(是否明显，取决于您的使用模式)。
2. 计算哈希外部(意思，不是在SQL中)。您可以用c#编写CLR函数，或者用Java编写类似的函数(参见Elliott Brossard的回答)。这将增加解决方案的复杂性，例如，公司的政策可能不允许将外部代码放入数据库。此外，维护外部组件通常是一件麻烦事。

Answer 2

可以使用Java UDF计算Latin-1字符串的SHA1哈希值。下面是一个例子:

create function latin1sha1(str varchar)
returns varbinary language java handler = 'Latin1Sha1.compute' as $$
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
class Latin1Sha1 {
public byte[] compute(String str) throws NoSuchAlgorithmException, UnsupportedEncodingException {
MessageDigest hash = MessageDigest.getInstance("SHA-1");
hash.update(str.getBytes("ISO-8859-1"));  // AKA Latin-1
return hash.digest();
}
}
$$;
select hex_encode(latin1sha1('á'));

返回B753D636F6EE46BB9242D01FF8B61F715E9A88C3。