0

I need to update a column that currently contains a plain text password with its own SHA256 base64 password. To do so I'm using a cursor to loop through each record and encode the password, but after execution all records have the same encoded password.

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;
ɐsɹǝʌ ǝɔıʌ
  • 4,440
  • 3
  • 35
  • 56

1 Answers1

2
  • Don't hash passwords without using a salt!
    • Ideally use a hash-function designed specifically for password hashing like bcrypt instead of the SHA family (as bcrypt has a configurable strength value and includes all parameters and fields in a single string value whereas hashing manually means needing to store the hash and salt separately).
      • However bcrypt is not natively supported by SQL Server.
      • Also avoid PWDENCRYPT as it's deprecated and doesn't let you specify the hashing algorithm used, use HASHBYTES instead.
    • You can still generate salts securely using CRYPT_GEN_RANDOM which is a cryptographically secure RNG (it uses values provided by the operating system which may be PRNG or hardware-based RNG).
      • Note that it is safe to use CRYPT_GEN_RANDOM in an UPDATE statement without a WHERE clause because it will generate a new number for each row.
  • You don't need a cursor - you can do this in a single UPDATE statement.
    • UPDATE statements behave the same whether they're used with a cursor or not - so if you want to update a single row then you must specify the WHERE [primaryKey] = pkValue clause.
  • Always avoid storing binary data as Base64-encoded strings - store binary data as binary(n) or varbinary(n).
    • This is because SQL uses case-insensitive collation by default but Base64 is case-sensitive (Base16 is case-insensitive), so doing a query on a Base64 column may return incorrect results.
    • Base64 values take 33% more space than binary values - and encoding/decoding makes each operation far more expensive.
    • Base64 values are not SARGable when used in a query already using binary values unless you Base64 encode all values - which is just silly.

Here is how I would do it (assuming I couldn't use 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

Notes on RAND vs CRYPT_GEN_RANDOM

As stated, CRYPT_GEN_RANDOM is a cryptographically secure RNG whereas RAND is not, so RAND must not be used for generating Cryptographic Salt values.

But I'd like to demonstrate another interesting difference: RAND() will return the same value for every row in a query whereas CRYPT_GEN_RANDOM always returns different values. See for yourself by running this query:

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;

Gives me this final output:

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

Notice how the cgr values are all unique while rng values are all the same - despite both columns being set in the same UPDATE statement. The rng2 column has distinct values but only because each row was set individually inside a WHILE loop.

(The rng and rng2 columns all start with 0x00...003F... because RAND() returns a float (IEEE-754) value which has a defined binary representation).

Dai
  • 141,631
  • 28
  • 261
  • 374