基于 .net ComputeHash 的 SQL CLR 函数不适用于 Cyrrilic

本文关键字：函数 不适用 适用于 Cyrrilic CLR SQL net ComputeHash 基于 | 更新日期: 2023-09-27 18:33:50

我编写了以下SQL CLR函数，以便对大于 8000 字节的字符串值进行哈希处理(T-SQL内置HASHBYTES函数的输入值限制(： 

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBinary HashBytes(SqlString algorithm, SqlString value)
{
    HashAlgorithm algorithmType = HashAlgorithm.Create(algorithm.Value);
    if (algorithmType == null || value.IsNull)
    {
        return new SqlBinary();
    }
    else
    {
        byte[] bytes = Encoding.UTF8.GetBytes(value.Value);
        return new SqlBinary(algorithmType.ComputeHash(bytes));
    }
}

它适用于拉丁字符串。例如，以下哈希是相同的：

SELECT dbo.fn_Utils_GetHashBytes ('MD5', 'test'); -- 0x098F6BCD4621D373CADE4E832627B4F6
SELECT HASHBYTES ('MD5', 'test');                 -- 0x098F6BCD4621D373CADE4E832627B4F6

问题是它不适用于西里尔字符串。例如：

SELECT dbo.fn_Utils_GetHashBytes ('MD5 ', N'даровете на влъхвите') -- NULL
SELECT HashBytes ('MD5 ',N'даровете на влъхвите') -- 0x838B1B625A6074B2BE55CDB7FCEA2832
SELECT dbo.fn_Utils_GetHashBytes ('SHA256', N'даровете на влъхвите') -- 0xA1D65374A0B954F8291E00BC3DD9DF655D8A4A6BF127CFB15BBE794D2A098844
SELECT HashBytes ('SHA2_256',N'даровете на влъхвите') -- 0x375F6993E0ECE1864336E565C8E14848F2A4BAFCF60BC0C8F5636101DD15B25A

我正在为MD5 NULL，尽管如果代码作为控制台应用程序执行，则代码返回值。谁能说出我做错了什么？

另外，我从这里获得了该功能，其中一条评论说：

小心将 CLR SP 参数静默截断为 8000 字节 - 我必须用 [SqlFacet(MaxSize = -1(] 标记参数，否则第 8000 个之后的字节将被忽略！

但是我已经对此进行了测试，并且工作正常。例如，如果我生成一个 8000 字节字符串的哈希和相同字符串的第二个哈希加上一个符号，则得到的哈希值不同。

DECLARE @A VARCHAR(MAX) = '8000 bytes string...'
DECLARE @B VARCHAR(MAX) = @A + '1'
SELECT LEN(@A), LEN(@B)
SELECT IIF(dbo.fn_Utils_GetHashBytes ('MD5', @A + '1') = dbo.fn_Utils_GetHashBytes ('MD5', @B), 1, 0) -- 0

我应该担心这个吗？

基于 .net ComputeHash 的 SQL CLR 函数不适用于 Cyrrilic

 Encoding.UTF8.GetBytes(...)

SQL Server没有UTF-8的概念。使用 UCS-2 (UTF-16( 或 ASCII。使用的编码必须与您传递给HASHBYTES的编码相匹配。您可以轻松看到HASHBYTES哈希VARCHAR与 NVARCHAR ：

select HASHBYTES('MD5', 'Foo')  -- 0x1356C67D7AD1638D816BFB822DD2C25D
select HASHBYTES('MD5', N'Foo') -- 0xB25FF0AD90D09D395090E8A29FF4C63C

最好是更改 SQLCLR 函数以接受字节而不是字符串，并处理要在调用方中VARBINARY的强制转换。

 SELECT dbo.fn_Utils_GetHashBytes ('MD5', CAST(N'даровете на влъхвите' AS VARBINARY(MAX));

仅供参考，SQL Server 2016已经取消了对HASHBYTES的8000字节限制：

对于 SQL Server 2014 及更早版本，允许的输入值限制为 8000 字节。

有关解释为什么您看到差异的详细演练，请参阅我对以下问题的回答：

TSQL md5 哈希不同于 C# .NET md5

对于任何不希望自己编译和部署它的人，这个函数可以在SQL#库的SQLCLR函数，存储过程等的免费版本中找到(我是它的创建者，但Util_Hash和Util_HashBinary，等等，都是免费的(。问题中显示的内容与 SQL# 中的两个 Util_Hash* 函数之间存在一个区别：问题中显示的函数采用NVARCHAR/SqlString 输入参数，而 SQL# 函数采用VARBINARY/SqlBinary输入。区别在于：

  • 接受VARBINARY输入也适用于二进制源数据(文件、图像、加密值等(
  • 虽然接受VARBINARY输入确实需要在函数调用中执行CONVERT(VARBINARY(MAX), source_string)的额外步骤，但这样做会保留用于VARCHAR数据的任何代码页。虽然不经常使用，但在处理非 Unicode 数据时会很方便。

关于其他帖子的警告：

小心将 CLR SP 参数静默截断为 8000 字节 - 我必须用 [SqlFacet(MaxSize = -1(] 标记参数，否则第 8000 个之后的字节将被忽略！

然而，您没有遇到同样的事情：这是由于 SSDT 为 SQLCLR 对象生成 T-SQL 包装器对象的方式发生了变化。在早期版本中(尤其是VS 2013之前随Visual Studio提供的版本(，默认行为是将NVARCHAR(MAX)用于SqlChars，NVARCHAR(4000)用于SqlString。但是在某个时候(我不想说从VS 2013开始，因为Visual Studio和SSDT是独立的产品，即使VS附带SSDT(默认设置更改为对SqlChars和SqlString都使用NVARCHAR(MAX)。发布警告的人员(2013 年 2 月 6 日(必须使用的是早期版本的 SSDT。尽管如此，明确并使用[SqlFacet(MaxSize = -1)]并没有什么坏处(甚至是一种很好的做法(。

关于if (algorithmType == null || value.IsNull)逻辑：由于NULL任何一个都应该返回一个NULL，因此最好删除该逻辑并使用CREATE FUNCTION语句的WITH RETURNS NULL ON NULL INPUT选项。但是，不幸的是，任何 SSDT 构造都不支持此选项(即没有SqlFacet(。因此，为了启用此选项，您可以创建一个部署后 SQL 脚本(将在主脚本之后自动部署(，该脚本发出具有所需定义的ALTER FUNCTION。投票支持我的连接建议以本机支持此选项并没有什么坏处：在 SqlFunctionAttribute 中实现 OnNullCall 属性，用于在空输入 SQLCLR 上返回空值。在实际层面上，性能提升主要体现在您为 @value 参数传入大量值但但不知何故@algorithm NULL，因此您最终不会使用 @value 的值。使用 RETURNS NULL ON NULL INPUT 选项的原因是，当您调用传入 SqlString 或 SqlBinary 的 SQLCLR 函数时，整个值将复制到应用程序域的内存中。那是时间、内存和 CPU，如果您提前知道您不会使用它，则无需浪费：-(。即使传入较小的值，您也可能看到非常频繁调用的函数的增益。

<小时 />

关于警告和测试的旁注：SQLCLR 不支持 VARCHAR ，仅支持 NVARCHAR 。因此，从来没有 8000 的限制，因为如果 SSDT 没有自动使用 NVARCHAR(MAX)，限制将是 4000 个字符。因此，如果存在差异，则首先会看到仅使用 4000 和 4001 个字符进行测试。

<小时 />

更新：从SQL Server 2019开始，现在可以通过_UTF8排序规则本机使用UTF-8。但是，您仍然无法将 UTF-8 字符串传入 SQLCLR 对象，因为 SQLCLR API 仅处理NVARCHAR而不处理VARCHAR。因此，尝试传入'UTF-8 encoded string'仍将作为 UTF-16 LE 通过，因为它将在传入过程中隐式转换。将 UTF-8 编码字符转换为 SQLCLR 的唯一方法是首先将它们转换为 VARBINARY 并将这些字节传递到 SQLCLR 对象中(如 VARBINARY -> SqlBinary/SqlBytes (。

相关文章：