Hashing in VBA using the Microsoft NG Cryptography (CNG) API

Introduction

Purpose of these articles

The Microsoft CNG APIs constitute a collection of more than a dozen APIs that handle all the aspects and supporting functions to calculate hash values and perform encryption and decryption meeting modern high demands and standards.

This series details one method to implement these features in applications supported by VBA (Visual Basic for Applications), for example Microsoft Access and Microsoft Excel.

The theory for and the clever mathematics behind hashing and encryption will not be touched; this is covered massively in books and articles published over a long period of time by dedicated experts. A search with Bing or Google will return a long list of resources to study.

Sections

The series has been split in five parts. This allows you to skip parts you are either familiar with or wish to implement later if at all.

1. Utilise Microsoft's Next Generation Cryptography (CNG) API in VBA
2. Hashing in VBA using the Microsoft NG Cryptography (CNG) API
3. Encryption in VBA using the Microsoft NG Cryptography (CNG) API
4. Using binary storage to serve the Microsoft NG Cryptography (CNG) API
5. Storing passwords in VBA using the Microsoft NG Cryptography (CNG) API

The three first deal, as stated, with hashing and encrypting/decrypting based on the CNG API, while the forth explains how to combine these techniques with the little known feature of Access, storing binary data, as this in many cases will represent the optimal storage method for hashed or encrypted data. The last demonstrates how to save and verify passwords totally safe using these tools.

Part 2. Hashing

Basic usage

Hashing, in this context, is the process of creating a non-reversible fixed length code that identifies the input.

Note, that the code is not unique as, in theory, the same code could be generated from different inputs, though - due to the extreme count of combinations for a given string length - this is unlikely to happen in real life.

This, however, is neither the point. The point is to be able to create a code based on an input, (later) to recreate that code, and then to check if the later code matches the first; if it does, the later input is assumed to match the original input.

The typical usage of hash values is for "storing" users' passwords. Instead of storing the password, the hash value is stored. To verify the password, its hash value is generated and compared with the stored hash value. If these values match, the password is accepted.

Hashing methods

The mathematical method used to hash a string is called an algorithm. These have evolved over time, and the oldest - also producing the shortest hash values - are now deprecated, though still widely used for less demanding purposes. For example, SHA1, and even the very old MD5, are often used when downloading files for verification of the integrity of the downloaded file. The expected hash value of the file is listed, and this must match the calculated hash value of the actual downloaded file.

The algorithms supported and their byte lengths are:

While MD5 and SHA1 can be well suited for some purposes, the general consensus is, that SHA256 is the minimum method to use for hashing passwords. For higher demands, use SHA512 or SHA384, but be aware, that SHA384 is not supported in all environments.

Working with hash values

Note, that the lengths listed above for the hash algorithms are the lengths of the generated byte arrays. As these bytes can take many other values than those for normal ASCII characters - even control characters and other non-printable characters - the byte array must for many practical purposes be converted to something printable.

The simple method would be to convert to the hexadecimal representation of the bytes, for example four bytes (here character <tab> is the third):

aX<tab>! -> 6158092D

but that consumes twice as many characters, thus a SHA256 hash would have a length of 64 characters - not very efficient.

A better method is to use Base64 encoding. It offers the same - a printable and portable string of ASCII characters - but consumes only 50% or less additional space then the byte count:

To conclude: For only a little overhead, the hash values can be treated as normal plain text.

Creating a hash

A set of functions is used to create hash values:

Hash
    HashData
        CngHash
    ByteBase64

The top function, Hash, the one that creates a Base64 encoded hash value from a passed text, contains less than a handful of code lines:

' Return a Base64 encoded hash of a string using the specified hash algorithm.
' By default, hash algorithm SHA256 is used.
'
' Example:
'   Text = "Get your filthy hands off my desert."
'   Value = Hash(Text)
'   Value -> "AIPgWDlQLv7bvLdg7Oa78dyRbC0tStuEXJRk0MMehOc="
'
' Length of the generated Base64 encoded hash string:
'
'   Encoding    Length
'   MD2         24
'   MD4         24
'   MD5         24
'   SHA1        28
'   SHA256      44      ' Default.
'   SHA384      64
'   SHA512      88
'
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Hash( _
    ByVal Text As String, _
    Optional ByVal BcryptHashAlgorithmId As BcHashAlgorithm = BcHashAlgorithm.bcSha256) _
    As String

    Dim HashBase64          As String

    If Text = "" Then
        ' No data. Nothing to do.
    Else
        HashBase64 = ByteBase64(HashData((Text), BcryptHashAlgorithmId))
    End If

    Hash = HashBase64

End Function

The only optional argument is which hash algorithm to use; by default, SHA256 is used.

The usage is extremely simple:

Dim Text    As String
Dim Value   As String

Text = "Get your filthy hands off my desert."
Value = Hash(Text)

Debug.Print "Text input:", Text
Debug.Print "Hash value:", Value

' Output:
' Text input:   Get your filthy hands off my desert.
' Hash value:   AIPgWDlQLv7bvLdg7Oa78dyRbC0tStuEXJRk0MMehOc=

The few lines of code in the function is possible, because it pulls data from the second level function, HashData, also taking an optional argument for the hash algorithm, which defaults to SHA256:

' Create and return the hash of a byte array as another byte array
' using the specified hash algorithm.
' By default, hash algorithm SHA256 is used.
'
' To be called from functions EncryptData, DecryptData, and Hash.
'
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function HashData( _
    ByRef TextData() As Byte, _
    Optional ByVal BcryptHashAlgorithmId As BcHashAlgorithm = BcHashAlgorithm.bcSha256) _
    As Byte()

    Dim Data()          As Byte
    Dim DataLength      As Long

    If StrPtr(TextData) = 0 Then
        ' Array TextData is empty.
    Else
        DataLength = UBound(TextData) - LBound(TextData) + 1
        Data = CngHash(VarPtr(TextData(LBound(TextData))), DataLength, BcryptHashAlgorithmId)
    End If

    HashData = Data

End Function

The important feature of the function is, that both input and output are byte arrays. However, you may have noticed, that function Hash above doesn't pass a byte array, but plain text:

HashData((Text), BcryptHashAlgorithmId)

That's because you can assign a text variable directly to a byte array; no conversion is needed.

The output is, in function Hash, fed directly to function ByteBase64, which converts the byte array to Base64 encoded text. Though quite convoluted, that function is trivial and won't be discussed here.

Function HashData also contains very few code lines. Essentially, it only wraps the function, that does "the real work", CngHash, which makes no less than eight calls to the CNG API to obtain the hash value.

The details of these calls will not be discussed here. However, each step is commented in-line, and links to the documentation at Microsoft are included, should you wish to study this further.

The usage of this function is also very straight:

Dim Data()  As Byte
Dim Value() As Byte

Data = "Text to hash"
Value = HashData(Data)

' Convert byte array to unicode.
Debug.Print StrConv(Value, vbUnicode)

' Output.
' ÌêÊž ”Þû§3k ¥ábÌ–t1Îôx[Œ’ö‡_šä

Part 4 of this series deals with various methods for storing hash values, where indeed these two functions can prove useful:

• SaveBinaryField
• ReadBinaryField

Supplemental functions

As you may have noticed, an enum is used for specifying the hash algorithm:

' Allowed BCrypt hash algorithms.
Public Enum BcHashAlgorithm
    [_First] = 1
    bcMd2 = 1
    bcMd4 = 2
    bcMd5 = 3
    bcSha1 = 4
    bcSha256 = 5
    bcSha384 = 6
    bcSha512 = 7
    [_Last] = 7
End Enum

This is to validate input and make it easy to always supply the rightly spelled and uppercased hash algorithm name to function CngHash.

Function BcryptHashAlgorithm serves this purpose:

' Return the literal hash algorithm name determined by
' the passed value of enum BcHashAlgorithm.
'
' To be called from functions CngHash and IsBcryptHashAlgorithm.
'
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function BcryptHashAlgorithm( _
    ByVal BcryptHashAlgorithmId As BcHashAlgorithm) _
    As String

    Dim HashAlgorithmName       As String

    ' Note: HashAlgorithmName must be in UPPERCASE.
    Select Case BcryptHashAlgorithmId
        Case BcHashAlgorithm.bcMd2
            HashAlgorithmName = "MD2"
        Case BcHashAlgorithm.bcMd4
            HashAlgorithmName = "MD4"
        Case BcHashAlgorithm.bcMd5
            HashAlgorithmName = "MD5"
        Case BcHashAlgorithm.bcSha1
            HashAlgorithmName = "SHA1"
        Case BcHashAlgorithm.bcSha256
            HashAlgorithmName = "SHA256"
        Case BcHashAlgorithm.bcSha384
            HashAlgorithmName = "SHA384"
        Case BcHashAlgorithm.bcSha512
            HashAlgorithmName = "SHA512"
    End Select

    BcryptHashAlgorithm = HashAlgorithmName

End Function

Likewise, functions are included to verify/validate either a literal algorithm name or an enum value for these names:

' Return True if the passed text value represents a value of
' enum BcHashAlgorithm.
' Note: To validate, HashAlgorithm must be in UPPERCASE.
'
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function IsBcryptHashAlgorithm( _
    ByVal HashAlgorithm As String) _
    As Boolean

    Dim Index           As BcHashAlgorithm
    Dim Result          As Boolean

    For Index = BcHashAlgorithm.[_First] To BcHashAlgorithm.[_Last]
        If BcryptHashAlgorithm(Index) = HashAlgorithm Then
            Result = True
            Exit For
        End If
    Next

    IsBcryptHashAlgorithm = Result

End Function

' Return True if the passed value of enum BcHashAlgorithm is valid.
'
' To be called from function CngHash.
'
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function IsBcryptHashAlgorithmId( _
    ByVal HashAlgorithmId As BcHashAlgorithm) _
    As Boolean

    Dim Result          As Boolean

    If BcHashAlgorithm.[_First] <= HashAlgorithmId And HashAlgorithmId <= BcHashAlgorithm.[_Last] Then
        Result = True
    End If

    IsBcryptHashAlgorithmId = Result

End Function

Finally, to determine the length of a hash value - either as a byte array or a Base64 encoded string - without actually hashing something, two simple functions, HashByteLength and HashTextLength, will return those values. These functions were discussed in Part 1.

These functions can come in handy for code that creates or alters table design where fields for hash values will be included.

Conclusion

A full set of functions for hashing meeting modern standards has been presented. The functions cover all common needs for hashing in typical applications written in VBA.

Code and download