(SQL Server) Hashing Encoded Data

This example explains how it's important to know exactly what bytes are getting hashed when working with encoded data, such as hex, base64, etc.

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
    -- 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

    -- Imagine we want to SHA-1 hash this A9133F6DC63E85BE0619763F8975CA076FF91B95CB6F121CD21C016143315E11B9ED4374F174B2CC
    DECLARE @strToHash nvarchar(4000)
    SELECT @strToHash = 'A9133F6DC63E85BE0619763F8975CA076FF91B95CB6F121CD21C016143315E11B9ED4374F174B2CC'

    -- The question is:  Do we want to hash the bytes 0xA9, 0x13, 0x3F...
    -- or do we want to literally hash each us-ascii byte:  'A', '9', '1', '3'.

    -- The results will obviously be different depending on what bytes are actually hashed.

    -- To hash the decoded bytes (i.e. 0xA9, 0x13, 0x3F...) we do this:

    -- "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...
    EXEC sp_OASetProperty @crypt, 'Charset', 'hex'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha1'
    -- Get the hash result in lowercase hex
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex_lower'
    DECLARE @hashValue nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashStringENC', @hashValue OUT, @strToHash

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

    -- The result is 7a4a2ba73811fab98b4baaee2df164cbdd0d8e8e

    -- This is how to hash the 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', 'sha1'

    EXEC sp_OAMethod @crypt, 'HashStringENC', @hashValue OUT, @strToHash

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

    -- The result is:  2A7500F88734F4B19C79852CA0FF3E1C561F31C8

    -- 

    EXEC @hr = sp_OADestroy @crypt


END
GO