T-SQL Pbdfk2 (Rfc2898 SHA2_256)实现

Question

使用我的前一函数作为基础，我得到了以下内容：

create function dbo.Pbkdf2 (
    @password varbinary(8000)
  , @salt varbinary(7996)
  , @iterations int = 1000
  , @derivedKeyLength int = 32
)
returns varbinary(max)
as
begin;    
    declare @hmacLength int = 32;    
    declare @i int = 1;
    declare @l int = (@derivedKeyLength + @hmacLength - 1) / @hmacLength;
    declare @r int = @derivedKeyLength - (@l - 1) * @hmacLength;            
    declare @derivedKey varbinary(max) = Cast('' as varbinary(max));
    declare @u varbinary(8000);
    declare @uA binary(32);
    declare @uB binary(32);        

    while(@i <= @l)
    begin;
        declare @j int = 1;

        select @u = @salt + Cast(@i as binary(4));  
        select @uA = uA.[Hash]
             , @uB = uA.[Hash]
        from dbo.Hmac(@password, @u) as uA;

        while @j < @iterations
        begin;
            select @uA = tA.[Hash] from dbo.Hmac(@password, @uA) as tA;
            select @uB = tB.[Hash]
            from (values(
              -- unrolled loop to XOR uA and uB
                Cast(Substring(@uA, 1, 8) ^ Cast(Substring(@uB, 1, 8)  as bigint) as binary(8))
              + Cast(Substring(@uA, 9, 8) ^ Cast(Substring(@uB, 9, 8)  as bigint) as binary(8))
              + Cast(Substring(@uA, 17, 8) ^ Cast(Substring(@uB, 17, 8)  as bigint) as binary(8))
              + Cast(Substring(@uA, 25, 8) ^ Cast(Substring(@uB, 25, 8)  as bigint) as binary(8))
            )) tB ([Hash]);

            select @j = @j + 1;
        end;

        select @derivedKey = @derivedKey + Cast(case when @i = @l then Left(@uB, @r) else @uB end as varbinary(32));

        select @i = @i + 1;
    end;

    return @derivedKey;
end;

我真的很想用一些基于设置的东西来代替循环，这样我就可以把它变成一个内嵌的TVF，但是似乎不能把我的头绕在它周围……

Answer 1

我对此做了非常非常疯狂的尝试。我也不能把它归结为完全基于设置的东西，但是我能够用递归的CTE (不幸的是其中的两个)将它带到一个内联的TVF中。通过1000次迭代，我需要至少998的MAXRECURSION才能完成它。

我不相信这是一个加密安全的实现。我强烈怀疑优化器在这里可能会做一些有趣的事情，从而启用定时攻击；可能需要一些非常积极的查询暗示，以确保稳定的连接顺序和方法。通过深入挖掘，我认为我们必须做以下工作(至少)，才能使其具有密码安全性。所有这些都是为了防止定时攻击：

1. 强制LOOP联接；HASH联接使用哈希表(已知时间攻击漏洞)，MERGE联接将排序，这将受到可用数据的影响。
2. 强制连接顺序(OPTION( FORCE ORDER ))；如果优化器知道数据集和重新排序的基数，那么我们也会看到不同的数字。
3. 禁用并行性(OPTION( MAXDOP 1 ))

在所有这些过程中，我都严重依赖于APPLY操作符的行为，在这里，它计算左侧每一行的右侧。这对于获得一些复杂的计算是至关重要的，我怀疑这是通向非递归实现的路径(如果存在的话)。

我要做的第一件事是重新创建你的外循环计算，这是我用这样的方法做的。您可能需要更多在计数器中间(只需保持CROSS APPLY到sys.all_objects)，但这对我的测试用例是好的。

SELECT uAOuter.Hash UA,
       uAOuter.Hash UB,
       OuterLooper.Counter
  FROM ( SELECT TOP (( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength )
                ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) [Counter],
                @salt + CAST(ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS binary(4)) U
           FROM sys.all_objects
           ORDER BY [Counter] ASC ) OuterLooper
    CROSS APPLY dbo.Hmac( @password, OuterLooper.U ) uAOuter

从那以后，我就陷入了如何从自身角度计算@uA的问题；我仍然认为可能有一些非常非常聪明的APPLY魔法可以得到这个，但是我花了几个小时去做这个。相反，我最终确定了一个递归的CTE，以获得基本情况，然后进行计算。

WITH InnerLoopRecursive AS
(
  SELECT tA.Hash uA,
         tB.Hash uB,
         OuterLoop.Counter OuterCount,
         1 [Counter]
    FROM ( SELECT uAOuter.Hash UA,
                  uAOuter.Hash UB,
                  OuterLooper.Counter
             FROM ( SELECT TOP (( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength )
                           ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) [Counter],
                           @salt + CAST(ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS binary(4)) U
                      FROM sys.all_objects
                      ORDER BY [Counter] ASC ) OuterLooper
               CROSS APPLY dbo.Hmac( @password, OuterLooper.U ) uAOuter ) OuterLoop
      CROSS APPLY dbo.Hmac( @password, OuterLoop.UA ) tA
      CROSS APPLY ( VALUES (
                      -- unrolled loop to XOR uA and uB
                      CAST(SUBSTRING( tA.Hash, 1, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 1, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 9, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 9, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 17, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 17, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 25, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 25, 8 ) AS bigint) AS binary(8)))) tB ( [Hash] )
  UNION ALL
  SELECT tA.Hash uA,
         tB.Hash uB,
         InnerLoopRecursive.OuterCount,
         InnerLoopRecursive.Counter + 1
    FROM InnerLoopRecursive
      CROSS APPLY dbo.Hmac( @password, InnerLoopRecursive.uA ) tA
      CROSS APPLY ( VALUES (
                      -- unrolled loop to XOR uA and uB
                      CAST(SUBSTRING( tA.Hash, 1, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 1, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 9, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 9, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 17, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 17, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 25, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 25, 8 ) AS bigint) AS binary(8)))) tB ( [Hash] )
    WHERE InnerLoopRecursive.Counter + 1 < @iterations
)

在那之后，我真的希望只使用一个聚合(或者可能是一个窗口函数)，但是显然您不能用varbinary(MAX)来完成，所以我不得不执行另一个递归的CTE。

, DerivedKeyPerOuterLoop AS
(
  SELECT CONVERT( varbinary(MAX), '' ) DerivedKey,
         0 [Counter]
  UNION ALL
  SELECT DerivedKeyPerOuterLoop.DerivedKey + CONVERT( varbinary(MAX),
                                                      CASE WHEN DerivedKeyPerOuterLoop.Counter = (( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength ) THEN LEFT(InnerLoopRecursive.uB, @derivedKeyLength - ((( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength ) - 1 ) * @hmacLength)
                                                           ELSE InnerLoopRecursive.uB END ) DerivedKey,
         DerivedKeyPerOuterLoop.Counter + 1
    FROM DerivedKeyPerOuterLoop
      INNER JOIN InnerLoopRecursive
        ON ( DerivedKeyPerOuterLoop.Counter + 1 ) = InnerLoopRecursive.OuterCount
    WHERE InnerLoopRecursive.Counter = @iterations - 1
)

在递归的CTEs过程中，JOINs变得相当古怪，因为我们试图处理一个潜在的粒度爆炸，但它应该可以正常工作。

一旦我们得到了最后的CTE，那么我们只需要从最后一次迭代中得到结果：

CREATE OR ALTER FUNCTION dbo.Pbkdf2_modified
(
  @password         varbinary(8000),
  @salt             varbinary(7996),
  @iterations       int = 1000,
  @derivedKeyLength int = 32,
  @hmacLength       int = 32
)
RETURNS table
AS
  RETURN ( WITH InnerLoopRecursive AS
           (
             SELECT tA.Hash uA,
                    tB.Hash uB,
                    OuterLoop.Counter OuterCount,
                    1 [Counter]
               FROM ( SELECT uAOuter.Hash UA,
                             uAOuter.Hash UB,
                             OuterLooper.Counter
                        FROM ( SELECT TOP (( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength )
                                      ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) [Counter],
                                      @salt + CAST(ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS binary(4)) U
                                 FROM sys.all_objects
                                 ORDER BY [Counter] ASC ) OuterLooper
                          CROSS APPLY dbo.Hmac( @password, OuterLooper.U ) uAOuter ) OuterLoop
                 CROSS APPLY dbo.Hmac( @password, OuterLoop.UA ) tA
                 CROSS APPLY ( VALUES (
                                 -- unrolled loop to XOR uA and uB
                                 CAST(SUBSTRING( tA.Hash, 1, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 1, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 9, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 9, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 17, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 17, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 25, 8 ) ^ CAST(SUBSTRING( OuterLoop.UB, 25, 8 ) AS bigint) AS binary(8)))) tB ( [Hash] )
             UNION ALL
             SELECT tA.Hash uA,
                    tB.Hash uB,
                    InnerLoopRecursive.OuterCount,
                    InnerLoopRecursive.Counter + 1
               FROM InnerLoopRecursive
                 CROSS APPLY dbo.Hmac( @password, InnerLoopRecursive.uA ) tA
                 CROSS APPLY ( VALUES (
                                 -- unrolled loop to XOR uA and uB
                                 CAST(SUBSTRING( tA.Hash, 1, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 1, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 9, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 9, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 17, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 17, 8 ) AS bigint) AS binary(8)) + CAST(SUBSTRING( tA.Hash, 25, 8 ) ^ CAST(SUBSTRING( InnerLoopRecursive.uB, 25, 8 ) AS bigint) AS binary(8)))) tB ( [Hash] )
               WHERE InnerLoopRecursive.Counter + 1 < @iterations
           ),
                DerivedKeyPerOuterLoop AS
           (
             SELECT CONVERT( varbinary(MAX), '' ) DerivedKey,
                    0 [Counter]
             UNION ALL
             SELECT DerivedKeyPerOuterLoop.DerivedKey + CONVERT( varbinary(MAX),
                                                                 CASE WHEN DerivedKeyPerOuterLoop.Counter = (( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength ) THEN LEFT(InnerLoopRecursive.uB, @derivedKeyLength - ((( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength ) - 1 ) * @hmacLength)
                                                                      ELSE InnerLoopRecursive.uB END ) DerivedKey,
                    DerivedKeyPerOuterLoop.Counter + 1
               FROM DerivedKeyPerOuterLoop
                 INNER JOIN InnerLoopRecursive
                   ON ( DerivedKeyPerOuterLoop.Counter + 1 ) = InnerLoopRecursive.OuterCount
               WHERE InnerLoopRecursive.Counter = @iterations - 1
           )
           SELECT DerivedKeyPerOuterLoop.DerivedKey
             FROM DerivedKeyPerOuterLoop
             WHERE DerivedKeyPerOuterLoop.Counter = (( @derivedKeyLength + @hmacLength - 1 ) / @hmacLength ));