SHA256 in stored procedure

[ASP.Confused]

I have a Snitz forum on my site that uses SHA256 to do passwords. I have
another table with all the users and passwords stored in it, for another
area of our site. I would like to create a query to pass to the Snitz forum
applicaiton, so that the passwords in my table will transfer to it.

Basically, I don't want to maintain two areas with the exact same
information. I have no problem generating the query I need, but I do have a
problem with converting the password from my current encryption format to
SHA256. My encryption format is reversable, thankfully :D, so I should be
able to decrypt the password, and then encrypt it in SHA256.

How would I go about creating a stored procedure to do this?

[Steve Kass]

I don't know the SHA256 algorithm, but if it's similar to SHA-1, maybe you
can modify this T-SQL function to compute SHA256. It only takes input of up
to 55 bytes, but I think that's the only way in which it is different from
the published SHA1. It's worth some careful testing in any case.

create function S(
@N int,
@x bigint
) returns binary(4) as begin

declare @two_N bigint
set @two_N = power(cast(2 as bigint), @N)

declare @two_32_N bigint
set @two_32_N = power(cast(2 as bigint), 32-@N)

return cast(@x%@two_32_N*@two_N + @x/@two_32_N as binary(4))
end
go

create function f(
@t bigint,
@B bigint,
@C bigint,
@D bigint
) returns bigint as begin

declare @2_32 bigint set @2_32 = power(cast(2 as bigint),32)

if @t between 0 and 19
return (@B & @C) | ((@2_32-@B-1) & @D)
if @t between 20 and 39
return @B ^ @C ^ @D
if @t between 40 and 59
return (@B & @C) | (@B & @D) | (@C & @D)
return @B ^ @C ^ @D
end
go

create function SHA1 (
@s varchar(55)
) returns binary(20) as begin

declare @b varbinary(55)
set @b = cast(@s as varbinary(55))

declare @zeros binary(64)
set @zeros = 0x

declare @padded binary(64)
set @padded =
@b + 0x80 + substring(@zeros,1,55-datalength(@b))
+ cast(8*datalength(@b) as binary(8))

declare @H5 binary(20)
set @H5 = 0x67452301EFCDAB8998BADCFE10325476C3D2E1F0
declare @K4 binary(16)
set @K4 = 0x5A8279996ED9EBA18F1BBCDCCA62C1D6
declare @ABCDE binary(20) set @ABCDE = @H5
declare @W80 varbinary(320) set @W80 = @padded

declare @TEMP binary(4) set @TEMP = 0x
declare @2_32 bigint set @2_32 = power(cast(2 as bigint),32)

declare @t int
set @t = 16
while @t < 80 begin
set @W80 = @W80 +
dbo.S(1,cast(substring(@W80,(@t-3)*4+1,4) as bigint)
^cast(substring(@W80,(@t-8)*4+1,4) as bigint)
^cast(substring(@W80,(@t-14)*4+1,4) as bigint)
^cast(substring(@W80,(@t-16)*4+1,4) as bigint))
set @t = @t + 1
end

set @t = 0
while @t < 80 begin
set @TEMP = cast((cast(dbo.S(5,substring(@ABCDE,1,4)) as bigint)
+ dbo.f(@t,substring(@ABCDE,5,4)
,substring(@ABCDE,9,4)
,substring(@ABCDE,13,4))
+ cast(substring(@ABCDE,17,4) as bigint)
+ cast(substring(@W80,4*@t+1,4) as bigint)
+ cast(substring(@K4,4*(@t/20)+1,4) as bigint))%@2_32 as
binary(4))

set @ABCDE = @TEMP+substring(@ABCDE,1,4)
+dbo.S(30,substring(@ABCDE,5,4))
+substring(@ABCDE,9,8)
set @t = @t + 1
end

set @H5
= cast((cast(substring(@H5, 1,4) as bigint) + cast(substring(@ABCDE, 1,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5, 5,4) as bigint) + cast(substring(@ABCDE, 5,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5, 9,4) as bigint) + cast(substring(@ABCDE, 9,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5,13,4) as bigint) + cast(substring(@ABCDE,13,4)
as bigint))% @2_32 as binary(4))
+ cast((cast(substring(@H5,17,4) as bigint) + cast(substring(@ABCDE,17,4)
as bigint))% @2_32 as binary(4))

return @H5
end
go
select dbo.SHA1(0xff)
go

go
--drop function SHA1
--drop function f, S
go

Steve Kass
Drew University
Ref: 6089E0EC-3298-4287-B658-E7ADEA79360F

"ASP.Confused" <anonymous@> wrote in message
news:uyB4%239VVE...@TK2MSFTNGP11.phx.gbl...