title: Dealing with a varbinary field in VB.NET
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here's the two competing attempts.  First, the parameterized version which fails:</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>And secondly, the dynamic query string version which works:</p>
  
  <pre><code>Dim scSelectThing As New SqlCommand
   scSelectThing.CommandText = "select thing from tokenstable where token=convert(varbinary, " + stringToken + " )"
  lbOutput2.Text = scSelectThing.ExecuteScalar()
  </code></pre>
  
  <p>When we run the SQL profiler, this is what is actually being executed against the DB:</p>
  
  <pre><code>exec sp_executesql N'select thing from tokenstable where token=@token',N'@token varbinary(68)',@token=0x3000780046003800380034004100450036003400430038003300440033004100380034003800460046004300380038004200390034003400330043004200370042004600
  </code></pre>
  
  <p>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.</p>
  
  <p>Any ideas what that would be?  Its obviously not acceptable to launch with a dynamic query in there.</p>
  
  <p>Edit:</p>
  
  <p>The string is an MD5 hash result.  To make it work in Query Analyzer we do this:</p>
  
  <pre><code>select * from tokenstable where 
  token=convert(varbinary, 0xF664AE32C83D3A848FFC88B9443CB7BF )
  </code></pre>
  
  <p>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.</p>