VB/VBScript -比较WHERE语句中的二进制值(SQL Server)

Question

当我尝试运行这样的SQL语句时，我得到一个类型不匹配错误:

Dim s
s = "test"
"SELECT id FROM mytable WHERE sha1bin = " & SHA1bin(s)

下面是我的SHA1bin函数:

Private Function SHA1Bin(s)
    Dim asc, enc, bytes
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.SHA1CryptoServiceProvider")
    bytes = asc.GetBytes_4(s)
    SHA1Bin = enc.ComputeHash_2((bytes))
    Set asc = Nothing
    Set enc = Nothing
End Function

据我所知，VBScript并没有真正的二进制数据类型。我需要做些什么来进行比较，还是应该将其存储为40个字符的十六进制值，以便我可以进行文本比较?

Answer 1

computeash_2返回一个字节数组。

首先，将字节数组转换为十六进制字符串:

Dim pos,hexs
For pos = 1 To Lenb(bytes) ' bytes is your resulting byte array '
 hexs = hexs & LCase(Right("0" & Hex(Ascb(Midb(bytes, pos, 1))), 2))
Next

然后与T-SQL的CONVERT:

进行比较

Dim Query
Query="SELECT id FROM mytable WHERE sha1bin = CONVERT(varbinary(max), 0x" & hexs & ")"