VB/VBScript - Comparing a binary value in a WHERE statement (SQL Server)

Question

I'm getting a type mismatch error when I try to run an SQL statement like this:

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

Here's my SHA1bin function:

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

As I understand it VBScript doesn't really have a binary data type. What do I need to do to make this comparison or should I store it as a 40 Character Hex value instead so I can do a text comparison?

Which version of SQL Server are you using? Also, did you try "SELECT id FROM mytable WHERE sha1bin = '" & SHA1bin(s) & "'"? — stealthyninja
– stealthyninja, Commented Apr 25, 2011 at 20:57
SQL Server 2008 Express. I tried it with the quotes, didn't work. SQL Server 2008 does have a HashBytes function. I suppose I could use it if I would change my code to use a stored procedure. Any way to use that function in my SQL Statement? — HK1
– HK1, Commented Apr 25, 2011 at 21:14

magma · Accepted Answer · 2011-04-25 21:12:10Z

2

ComputeHash_2 returns a byte array.

First, convert your byte array to a hex string:

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

Then, compare with T-SQL's CONVERT:

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

answered Apr 25, 2011 at 21:12

magma

8,5551 gold badge38 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HK1 Over a year ago

Your solution works well. I actually just changed my function above so that it returns hex instead of binary. In other words, I inserted your first bit of code in my SHA1 function. I'm unable to do any real scientific testing but it appears that my code is now running faster by being able to lookup rows by their hash value instead of comparing the full text string.

Collectives™ on Stack Overflow

VB/VBScript - Comparing a binary value in a WHERE statement (SQL Server)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related