varbinary problem

I have a DB that started out on MySQL and I was using the SHA1 function to convert a password to a hash. I am now moving to SQLServer and must use HASHBYTES(‘SHA1’,’’,2).

My technique to determine if the password being input is correct is to put the value of the input password into the SQL SELECT as a literal surrounded by the correct hash function and return the stored value from the user table (when the user was setup) and then compare. If they are equal the the input password matches the db password since the hash functions match.

The actual syntax is this because HASHBYTES returns a varbinary and I need a regular string.


The ,2 in the convert outputs a string without a leading “x0” (a Microsoft SQLServer thing).

When I do the query in SQL Studio it returns the exact correct string.


In a XOJO query it returns a totally different hex string from the FIELD function. If I do not do the CONVERT function in SQL I get a binary string. I know it is the same EXACT query because I capture the SQL in a message box then copy/paste it into SQL Studio and the answers are different.

I am going to guess that it is something to do with encoding but since the DB returns the correct values from a table column that are VARCHAR(xx) then this should also return a string with the correct encoding … except for the fact that Microsoft may know better. ;-))

Any hints?

I think I have had a similar issue in the past. To get around it, I coded a stored procedure function that did the complete test on the SQL server and then just returned a true/false (or other value).

– Rob