时间戳

首页 > 解决方案 > 遍历表并使用 SHA256 base64 哈希对字段进行编码

sql-server - 遍历表并使用 SHA256 base64 哈希对字段进行编码

问题描述

我需要使用自己的 SHA256 base64 密码更新当前包含纯文本密码的列。为此，我使用游标循环遍历每条记录并对密码进行编码，但在执行后，所有记录都具有相同的编码密码。

DECLARE @hash AS VARBINARY(128); 
DECLARE @h64 AS VARCHAR(128);
DECLARE @pass AS VARCHAR(500);
DECLARE @id AS INTEGER;

DECLARE cursor1 CURSOR 
    FOR SELECT [ID] FROM dbo.Table
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @id
WHILE @@FETCH_STATUS = 0  
    BEGIN   
        SET @pass = (SELECT [Password] FROM dbo.Table WHERE ID = @id);
        SET @hash = HASHBYTES('SHA2_256', @pass);
        SET @h64 = CAST(N'' AS xml).value('xs:base64Binary(sql:variable("@hash"))', 'varchar(128)');
        UPDATE dbo.Table SET [Password] = @h64;         
        FETCH NEXT FROM cursor1 INTO @id;  
    END;
DEALLOCATE cursor1;

标签： sql-serverbase64sha256database-cursor

解决方案

  • 不要在不使用盐的情况下散列密码！
    • 理想情况下，使用专门为密码散列设计的散列函数，如 bcrypt 而不是 SHA 系列（因为 bcrypt 具有可配置的强度值，并将所有参数和字段包含在单个字符串值中，而手动散列意味着需要分别存储散列和盐） .
      • 但是 SQL Server 本身并不支持 bcrypt。
      • 还要避免PWDENCRYPT，因为它已被弃用并且不允许您指定使用的散列算法，HASHBYTES而是使用它。
    • 您仍然可以使用CRYPT_GEN_RANDOM加密安全的 RNG 安全地生成盐（它使用操作系统提供的值，可能是 PRNG 或基于硬件的 RNG）。
      • 请注意，在没有子句CRYPT_GEN_RANDOM的UPDATE语句中使用是安全的，WHERE因为它将为每一行生成一个新数字。
  • 您不需要游标 - 您可以在单个UPDATE语句中执行此操作。
    • UPDATE无论是否与游标一起使用，语句的行为都相同 - 因此，如果要更新单行，则必须指定WHERE [primaryKey] = pkValue子句。
  • 始终避免将二进制数据存储为 Base64 编码的字符串- 将二进制数据存储为binary(n)或varbinary(n).
    • 这是因为 SQL 默认使用不区分大小写的排序规则，但 Base64 区分大小写（Base16 不区分大小写），因此对 Base64 列进行查询可能会返回不正确的结果。
    • Base64 值占用的空间比值多 33% binary- 编码/解码使每个操作的成本更高。
    • Base64 值在已经使用binary值的查询中使用时不是 SARGable，除非您对所有值进行 Base64 编码 - 这很愚蠢。

这是我的做法（假设我不能使用 bcrypt）：

ALTER TABLE
    dbo.Table
ADD
    [Salt] binary(16) NULL;

GO -- `GO` is necessary when using ALTER TABLE statements in the same query-batch as UPDATE statements.

ALTER TABLE
    dbo.Table
ADD
    [PasswordHash] binary(32) NULL; -- store hashes as binary values, not strings. SHA-256 produces a 256-bit (32-byte) long hash, so use a fixed-length `binary(32)` column.

GO

-- The [Salt] and [PasswordHash] columns need to be set in separate `UPDATE` queries (or using a single `UPDATE FROM` query) because of how `CRYPT_GEN_RANDOM` works.

UPDATE
    dbo.Table
SET
    [Salt] = CRYPT_GEN_RANDOM( 16 );

UPDATE
    dbo.Table
SET
    [PasswordHash] = HASHBYTES( 'SHA_256', [Password] + [Salt] );
    -- T-SQL uses `+` to concatenate binary values. Don't use `CONCAT` because it will return a `varchar` value with undefined conversion semantics from `binary` values.

GO

-- Finally, remove the old password information and make the new columns non-NULLable:

ALTER TABLE dbo.Table DROP COLUMN [Password];
GO

ALTER TABLE dbo.Table ALTER COLUMN [Salt] binary(16) NOT NULL;
GO

ALTER TABLE dbo.Table ALTER COLUMN [PasswordHash] binary(32) NOT NULL;
GO

关于RANDvs的注释CRYPT_GEN_RANDOM

如前所述，CRYPT_GEN_RANDOM它是一个加密安全的 RNG，RAND但不是，因此RAND不得用于生成加密盐值。

但我想展示另一个有趣的区别：RAND()将在查询中为每一行返回相同的值，而CRYPT_GEN_RANDOM总是返回不同的值。通过运行此查询亲自查看：

DECLARE @foo TABLE (
    rowId int        NOT NULL IDENTITY PRIMARY KEY,
    cgr   binary(16)     NULL,
    rng   binary(16)     NULL,
    rng2  binary(16)     NULL
);

INSERT INTO @foo ( cgr, rng, rng2 ) VALUES
    ( NULL, NULL, NULL ),
    ( NULL, NULL, NULL ),
    ( NULL, NULL, NULL ),
    ( NULL, NULL, NULL ),
    ( NULL, NULL, NULL );

SELECT * FROM @foo;

UPDATE
    @foo
SET
    cgr = CRYPT_GEN_RANDOM( /*length:*/ 10 ),
    rng = RAND();

--

SELECT * FROM @foo;

--

DECLARE @i int = 1;
WHILE @i <= 5
BEGIN

    UPDATE
        @foo
    SET
        rng2 = RAND()
    WHERE
        rowId = @i;

    SET @i = @i + 1;
END;

SELECT * FROM @foo;

给我这个最终输出：

id   cgr                                   rng                                   rng2
1    0x2DEB1D8A8DAB1F65373E000000000000    0x00000000000000003FC75AD042AE086F    0x00000000000000003FE2C5C607959DFF
2    0x4F7F050C335330AF43E6000000000000    0x00000000000000003FC75AD042AE086F    0x00000000000000003FEB46BAA0391C3E
3    0xB23F1C1C4C860A9652EE000000000000    0x00000000000000003FC75AD042AE086F    0x00000000000000003FDA62960990C897
4    0x44C604D79B0BB19167F9000000000000    0x00000000000000003FC75AD042AE086F    0x00000000000000003FC04FEA23759748
5    0xCF7F9A4FA4EDD605ECC2000000000000    0x00000000000000003FC75AD042AE086F    0x00000000000000003FE3A8FA18BD83A9

请注意，cgr值是如何唯一的，而rng值是相同的——尽管两列都在同一个UPDATE语句中设置。该列具有不同的值，但这只是因为每一行都是在循环rng2内单独设置的。WHILE

（rng和rng2列都以0x00...003F...因为RAND()返回float具有定义的二进制表示的（IEEE-754）值开始）。

推荐阅读