Dealing with a varbinary field in VB.NET

Question

A partner of ours is making a call to a web service that includes a parameter called token. Token is the result of an MD5 hash of two numbers, and helps us authenticate that the user using our partners system. Our partner asks the user for two strings, concatenates them, runs them through MD5, and then calls our web service. The result of the MD5 is the token, and it is submitted to us as a string.

We store valid tokens in a DB - since we use the SQL Server to compute the tokens on our end, SQL seemed to be happiest storing the tokens as a varbinary, which is its native result for an MD5 computation.

We have two pieces of code that attempt to do the same thing - run a select query that pulls out a value based on the token submitted. One uses a dynamic query (which is a problem), but it works. The other one attempts to do the same thing in a parameterized (safer) fashion, it does not work - it is unable to locate the token.

Here's the two competing attempts. First, the parameterized version which fails:

byteArrayToken = System.Text.UnicodeEncoding.Unicode.GetBytes(stringToken)
scSelectThing.CommandText = "select thing from tokenstable where token=@token"
Dim param As SqlParameter = scSelectThing.Parameters.Add("@token", SqlDbType.VarBinary)
param.Value = byteArrayToken
lbOutput2.Text = scSelectThing.ExecuteScalar()

And secondly, the dynamic query string version which works:

Dim scSelectThing As New SqlCommand
 scSelectThing.CommandText = "select thing from tokenstable where token=convert(varbinary, " + stringToken + " )"
lbOutput2.Text = scSelectThing.ExecuteScalar()

When we run the SQL profiler, this is what is actually being executed against the DB:

exec sp_executesql N'select thing from tokenstable where token=@token',N'@token varbinary(68)',@token=0x3000780046003800380034004100450036003400430038003300440033004100380034003800460046004300380038004200390034003400330043004200370042004600

This doesn't look right to me, it looks as though we're doing something that leads something in the stack to do the wrong conversion somewhere.

Any ideas what that would be? Its obviously not acceptable to launch with a dynamic query in there.

Edit:

The string is an MD5 hash result. To make it work in Query Analyzer we do this:

select * from tokenstable where 
token=convert(varbinary, 0xF664AE32C83D3A848FFC88B9443CB7BF )

Note the lack of quotes, if we quote it the query fails. The field that we are comparing to is a varbinary, where SQL Server has stored the results of the MD5 computations on our end.

Solution

Your issue is likely that the string->binary conversions are using different encodings. Try using System.Text.Encoding.ASCII instead of Unicode and see if that does the trick for you. My guess is that convert() is considering your string to be a varchar instead of an nvarchar and is using ASCII instead of Unicode for its own string->binary conversion.

Edit

Also, is this string an actual binary equivalent to the hash, or is it a hexadecimal representation?

Edit 2

Then your issue is that you're passing the binary representation of the hexadecimal representation of your binary data. That confusing enough?

You just need to convert the hex string to a byte array before you add it as a value for your parameter. You can do that with the following code, taken from the answer to this question (and translated to VB.NET):

Public Shared Function StringToByteArray(ByVal hex As String) As Byte()
    Dim NumberChars As Integer = hex.Length

    Dim bytes(NumberChars / 2) As Byte

    For i As Integer = 0 To NumberChars - 1 Step 2
        bytes(i / 2) = Convert.ToByte(hex.Substring(i, 2), 16)
    Next

    Return bytes
End Function

So your code will look something like this...

byteArrayToken = StringToByteArray(stringToken)
scSelectThing.CommandText = "select thing from tokenstable where token=@token"
Dim param As SqlParameter = scSelectThing.Parameters.Add("@token", SqlDbType.VarBinary)
param.Value = byteArrayToken
lbOutput2.Text = scSelectThing.ExecuteScalar()