replaced http://stackoverflow.com/ with https://stackoverflow.com/

Source Link

edited May 23, 2017 at 12:41

Community Bot

1

I'm trying to implement an Hmac in T-SQL using this question this question as an guide. Please critique this:

edited title

Link

edited Oct 17, 2015 at 20:38

Kittoes0124

1.9k
2
18
24

T-SQL Hmac (SHA2_256Rfc2104 SHA2_256) Implementation

Tweeted twitter.com/StackCodeReview/status/652978891934535680

occurred Oct 10, 2015 at 22:47

Stricter type sizing: should prevent silent previous truncation errors.

Source Link

edited Oct 5, 2015 at 15:25

Kittoes0124

1.9k
2
18
24

create function dbo.Hmac (
    @key varbinary(8000)
  , @message varbinary(80007936)
)   
returns table
with schemabinding as
return (
    select HashBytes('SHA2_256', Pads.o + HashBytes('SHA2_256', Pads.i + @message)) as [Hash]
    from (values(
        Cast( -- keys longer than blocksize are shortened and keys shorter than blocksize are zero-padded
            case
                when DataLength(@key) > 64 then HashBytes('SHA2_256', @key)
                else @key 
 end as binary(64)) -- keys longer than blocksize are shortened and keysend
 shorter than blocksize are zero-padded   as binary(64))
      , Cast(0x3636363636363636 as bigint) -- Rfc2104 inner padding constant
      , Cast(0x5C5C5C5C5C5C5C5C as bigint) -- Rfc2104 outer padding constant
    )) as Vars ([key], innerPadding, outerPadding)
    cross apply (values(
      -- unrolled loop to XOR key and iPad
        Cast((Substring(Vars.[key], 1, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.innerPadding) as binary(8))
      -- unrolled loop to XOR key and oPad
      , Cast((Substring(Vars.[key], 1, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.outerPadding) as binary(8))
    )) as Pads (i, o)
);

create function dbo.Hmac (
    @key varbinary(8000)
  , @message varbinary(8000)
)   
returns table
with schemabinding as
return (
    select HashBytes('SHA2_256', Pads.o + HashBytes('SHA2_256', Pads.i + @message)) as [Hash]
    from (values(
        Cast(case when DataLength(@key) > 64 then HashBytes('SHA2_256', @key) else @key end as binary(64)) -- keys longer than blocksize are shortened and keys shorter than blocksize are zero-padded
      , Cast(0x3636363636363636 as bigint) -- Rfc2104 inner padding constant
      , Cast(0x5C5C5C5C5C5C5C5C as bigint) -- Rfc2104 outer padding constant
    )) as Vars ([key], innerPadding, outerPadding)
    cross apply (values(
      -- unrolled loop to XOR key and iPad
        Cast((Substring(Vars.[key], 1, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.innerPadding) as binary(8))
      -- unrolled loop to XOR key and oPad
      , Cast((Substring(Vars.[key], 1, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.outerPadding) as binary(8))
    )) as Pads (i, o)
);

create function dbo.Hmac (
    @key varbinary(8000)
  , @message varbinary(7936)
)   
returns table
with schemabinding as
return (
    select HashBytes('SHA2_256', Pads.o + HashBytes('SHA2_256', Pads.i + @message)) as [Hash]
    from (values(
        Cast( -- keys longer than blocksize are shortened and keys shorter than blocksize are zero-padded
            case
                when DataLength(@key) > 64 then HashBytes('SHA2_256', @key)
                else @key 
            end
        as binary(64))
      , Cast(0x3636363636363636 as bigint) -- Rfc2104 inner padding constant
      , Cast(0x5C5C5C5C5C5C5C5C as bigint) -- Rfc2104 outer padding constant
    )) as Vars ([key], innerPadding, outerPadding)
    cross apply (values(
      -- unrolled loop to XOR key and iPad
        Cast((Substring(Vars.[key], 1, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.innerPadding) as binary(8))
      -- unrolled loop to XOR key and oPad
      , Cast((Substring(Vars.[key], 1, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.outerPadding) as binary(8))
    )) as Pads (i, o)
);