如何在Microsoft SQL Server中确保特定字符编码？

Question

我需要确保一个字符串被编码为已知的字符编码。到目前为止，我在 MS SQL Server 上的研究和测试表明，文档编码是 'UCS-2'，然而实际编码（在相关服务器上）是 'UCS-2LE'。

这似乎不太可靠。我希望能像 PERL、Node 或其他大多数语言中一样拥有一个 ENCODE 函数，以便无论升级或设置更改如何，我的哈希函数都能在已知输入上工作。

我们可以将哈希字符串限制为十六进制，因此在最坏的情况下，我们可以手动映射十六个可能的输入字符到正确的字节。有人对此有建议吗？

这是我正在使用的 PERL 代码：

use Digest::SHA qw/sha256/;
use Encode qw/encode/;

$seed = 'DDFF5D36-F14D-495D-BAA6-3688786D6CFA';
$string = '123456789';

$target = '57392CD6A5192B6185C5999EB23D240BB7CEFD26E377D904F6FEF262ED176F97';

$encoded = encode('UCS-2LE', $seed.$string);
$sha256 = uc(unpack("H*", sha256($encoded)));

print "$target\n$sha256\n";

这与 MS SQL 相匹配：

HASHBYTES('SHA_256', 'DDFF5D36-F14D-495D-BAA6-3688786D6CFA123456789')

但是我真正想要的是：

HASHBYTES('SHA_256', ENCODE('UCS2-LE', 'DDFF5D36-F14D-495D-BAA6-3688786D6CFA123456789'))

这样，无论 MS SQL 将输入字符串编码为什么，HASHBYTES 都将始终在已知的字节数组上操作。

- zanerock

Answer 1

SQL Server仅在声明为nvarchar的列、变量和文字上使用UCS-2。在所有其他情况下，它使用当前数据库的编码的8位ASCII，除非另有规定（例如使用collate子句）。

因此，您必须指定Unicode文字：

select HASHBYTES('SHA_256', N'DDFF5D36-F14D-495D-BAA6-3688786D6CFA123456789');

或者，您可以使用nvarchar数据类型的变量或表列：

-- Variable
declare @var nvarchar(128) = N'DDFF5D36-F14D-495D-BAA6-3688786D6CFA123456789';

select HASHBYTES('SHA_256', @var);

-- Table column
declare @t table(
  Value nvarchar(128)
);

insert into @t
select @var;

select HASHBYTES('SHA_256', t.Value)
from @t t;

顺便提一下，由于Wintel是小端平台，SQL Server使用与操作系统/硬件相同的编码版本。除非在SQL Server 2017中出现了新的东西，否则在本宇宙中无法本地获取大端表示。

- Roger Wolf