CODEWITHSUNDEEP
sql-serverhashintegersha1
sadmicrowave
sadmicrowave

Reputation: 40932

How to use integer value with SQL Server query utilizing HashBytes

I'm trying to do a query on a username table where username and password match. The table is a pre-existing (created during company software installation) table and I don't have the ability to change encryption types or methods. A query with a string as the password works perfectly fine but when I try to query using an integer the query returns null.

"SELECT *
 FROM ITF_USER
 WHERE ITF_LOGIN = '$lcUserName'
 AND ITF_PASS = HashBytes('SHA1', '$lcPassword')";

if the password is something like 'helloworld' then the query works fine, but '1121321' does not return anything. Any suggestions?

UPDATE If I compare the table stored password with php's sha1($lcPassword) results I see a slight difference that is causing the null query results:

table -> 0x3FEEAC0B3A75CF1C12A8420CDE593FA275CCE584
sha1()->   8feeac0b3a75cf1c12a8420cde598fa275cce584

there are two 8's in the sha1() results that should be 3's

Upvotes: 2

Views: 2165

Answers (1)

James L.
James L.

Reputation: 9461

I tried this:

declare @vc varchar(255), @nvc nvarchar(255)
set @vc = '1111'
set @nvc = '1111'
select hashbytes('sha1', @vc)
select hashbytes('sha1', @nvc)

It returned different values:

varchar  = 0x011C945F30CE2CBAFC452F39840F025693339C42
nvarchar = 0x40C7BD210D05DBEA19402B952DD416E487450955

It seems that the datatype of the second parameters makes a difference when calling HashBytes(). Perhaps a varchar is being passed when you use a string and an int is converted to a nvarchar (or visa-versa).

It might work to force everything to one type: HashBytes('SHA1', cast('$lcPassword' as varchar(255))

Upvotes: 1

Related Questions