(SQL Server) Hash a Hex String

Demonstrates common pitfalls in hashing a hex string..

Note: This example requires Chilkat v9.5.0.70 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- We have a hex string to be SHA-256 hashed:  "b08538d832bf"
    -- The result we expect to receive is "8a9f04cb1adfbd7f59c5918635f92f6c847e5b15f9828519d2fdbd6ead0918fc"

    DECLARE @strToHash nvarchar(4000)
    SELECT @strToHash = 'b08538d832bf'

    -- How do we get this result?

    -- The 1st question to be answered is:  What bytes are getting hashed?  
    -- There are two possibilities:
    -- 1) Hash 12 bytes, namely the us-ascii values for 'b', '0', '8', '5', .... 'b', 'f'
    -- or
    -- 2) Hash 6 bytes:  0xb0, 0x85, ... 0xbf
    -- 

    -- This is how to hash the 12 us-ascii byte values:
    -- The Charset property defines the byte representation of the string passed to the hash algorithm:
    EXEC sp_OASetProperty @crypt, 'Charset', 'us-ascii'
    -- The EncodingMode property defines the binary encoding (hex, base64, etc.) of the hash returned as an encoded string.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

    DECLARE @hashValue nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashStringENC', @hashValue OUT, @strToHash
    EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    PRINT 'hash of 12 us-ascii bytes: ' + @hashValue

    -- The result is:  327F2B33A0F0580D09840B0D7CEE54514CA33E9A  (not what we were hoping).

    -- -------------------------------------------
    -- This is how to hash the 6 bytes
    -- 
    -- "hex" is not an actual character encoding.  It's a special value to be used to tell Chilkat to hex decode
    -- the string and pass the decoded bytes to the hash algorithm...
    -- Note: This example requires Chilkat v9.5.0.70 or greater for the "hex" Charset to work properly.
    EXEC sp_OASetProperty @crypt, 'Charset', 'hex'
    -- The EncodingMode and HashAlgorithm remain the same..
    EXEC sp_OAMethod @crypt, 'HashStringENC', @hashValue OUT, @strToHash
    EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    PRINT 'hash of 6 hex bytes: ' + @hashValue

    -- The result is 8A9F04CB1ADFBD7F59C5918635F92F6C847E5B15F9828519D2FDBD6EAD0918FC (which equals the hash we were expecting)

    EXEC @hr = sp_OADestroy @crypt


END
GO