PBKDF2 as a function in Excel

Question

I've managed to get SHA256 hashing working in Excel but was looking for something a little more secure, I've come across PBKDF2 Hash Generation here: PBKDF2 Excel UDF and how to concatenate INT(i) But the code in the solution doesn't run:

Compile error: User-defined type not defined

With the following highlighted:

Function HMAC(ByVal plainText As String, _
    ByVal algoritm As hmacAlgorithm, _
    Optional ByVal key As String, _
    Optional ByVal decodeKey As keyDecoding = kdNone_String, _
    Optional ByVal encodeHash As hashEncoding = heBase64) As Variant

My end hope is to have a function similar to the SHA256 I have =SHA256hash(A2) with PBKDF2 keylength,salt and iterations as options =PBKDF2(A2,512,saltysalt,5000) For instance, so it's something simple for the team here to implement, as we all know, the more difficult it is to implement, the less likely people are to do it.

The code on the above link is:

Enums

Enum hmacAlgorithm
    HMAC_MD5
    HMAC_SHA1
    HMAC_SHA256
    HMAC_SHA384
    HMAC_SHA512
End Enum

Enum hashEncoding
    heBase64
    heHex
    heNone_Bytes
End Enum

PBKDF2 Function

Function PBKDF2(ByVal password As String, _
    ByVal salt As String, _
    ByVal hashIterations As Long, _
    ByVal algoritm As hmacAlgorithm, _
    Optional ByVal dkLen As Long, _
    Optional ByVal encodeHash As hashEncoding = heBase64) As Variant

'https://tools.ietf.org/html/rfc2898 - PKCS #5: Password-Based Cryptography Specification Version 2.0
'https://tools.ietf.org/html/rfc6070 - PKCS #5: Password-Based Key Derivation Function 2 (PBKDF2) Test Vectors
'https://en.wikipedia.org/wiki/PBKDF2

'DK = T1 || T2 || ... || Tdklen/hlen
'Ti = F(password, salt, c, i)
'
'F(Password, Salt, c, i) = U1 ^ U2 ^ ... ^ Uc
'
'U_1 = PRF (P, S || INT (i)) (INT (i) is a four-octet encoding of the integer i, most significant octet first.)
'U_2 = PRF (P, U_1)
'...
'U_c = PRF (P, U_{c-1})

Dim utf8Encoding As Object
Dim hashManager As Object

Dim hLen As Long
Dim noBlocks As Long
Dim noBlock As Long

Dim hmacKeyBytes() As Byte
Dim saltBytes() As Byte
Dim uboundSaltBytes As Long

Dim hmacBytes() As Byte
Dim tempBytes() As Byte
Dim outputBytes() As Byte

Dim i As Long
Dim j As Long

'Create utf8-encoding object
Set utf8Encoding = CreateObject("System.Text.UTF8Encoding")

'Create hmac object
Select Case algoritm
    Case HMAC_MD5
        Set hashManager = CreateObject("System.Security.Cryptography.HMACMD5")
    Case HMAC_SHA1
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA1")
    Case HMAC_SHA256
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA256")
    Case HMAC_SHA384
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA384")
    Case HMAC_SHA512
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA512")
End Select

'Check the length of the blocks to be generated
hLen = hashManager.HashSize / 8

'Calculate amount of blocks 'T'
If dkLen = 0 Then dkLen = hLen
noBlocks = Application.WorksheetFunction.Ceiling(dkLen / hLen, 1)

'Encode the key and salt to bytes
hmacKeyBytes = utf8Encoding.GetBytes_4(password)
saltBytes = utf8Encoding.GetBytes_4(salt)

'Set the key in the crypto class
hashManager.key = hmacKeyBytes

'Get the length of the salt, add 4 to concatenate INT(I)
uboundSaltBytes = UBound(saltBytes) + 4

'Loop T1 || T2 || ... || Tdklen/hlen
For i = 1 To noBlocks

    'Salt || INT(i)
    'INT (i) is a four-octet encoding of the integer i, most significant octet first.
    tempBytes = saltBytes
    ReDim Preserve tempBytes(uboundSaltBytes)
    noBlock = i

    'Calculate INT(i) of Salt || INT(i)
    For j = 3 To 0 Step -1
        tempBytes(uboundSaltBytes - j) = Int(noBlock / (255 ^ j))
        noBlock = noBlock - Int(noBlock / (255 ^ j)) * 255 ^ j
    Next j

    'Hash U1: Salt || INT(i)
    hmacBytes = hashManager.ComputeHash_2(tempBytes)
    tempBytes = hmacBytes

    'Hash, Xor: U1 ^ U2 ^ ... ^ Uc
    For j = 1 To hashIterations - 1
        hmacBytes = hashManager.ComputeHash_2(hmacBytes)
        tempBytes = XorBytes(tempBytes, hmacBytes)
    Next j

    'For the first block outputBytes() is empty
    If i = 1 Then
        outputBytes = tempBytes
    Else
        ConcatenateArrayInPlace outputBytes, tempBytes
    End If

Next i

'Extract the first dkLen octets to produce a derived key DK:
ReDim Preserve outputBytes(dkLen - 1)

'Base64, Hex, or Byte() output
If encodeHash = heBase64 Then
    PBKDF2 = Encode(outputBytes, edBase64)
ElseIf encodeHash = heHex Then
    PBKDF2 = Encode(outputBytes, edHex)
Else
    PBKDF2 = outputBytes
End If

Set hashManager = Nothing
Set utf8Encoding = Nothing

End Function

HMAC function

Function HMAC(ByVal plainText As String, _
    ByVal algoritm As hmacAlgorithm, _
    Optional ByVal key As String, _
    Optional ByVal decodeKey As keyDecoding = kdNone_String, _
    Optional ByVal encodeHash As hashEncoding = heBase64) As Variant

Dim hashManager As Object

Dim hashBytes() As Byte
Dim hmacKeyBytes() As Byte

'Create the specific hash manager based on the hash algoritm
Select Case algoritm
    Case HMAC_MD5
        Set hashManager = CreateObject("System.Security.Cryptography.HMACMD5") 'Returns 128 bits, 16 bytes
    Case HMAC_SHA1
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA1") 'Returns 160 bits, 20 bytes
    Case HMAC_SHA256
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA256") 'Returns 256 bits, 32 bytes
    Case HMAC_SHA384
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA384") 'Returns 384 bits, 48 bytes
    Case HMAC_SHA512
        Set hashManager = CreateObject("System.Security.Cryptography.HMACSHA512") 'Returns 512 bits, 64 bytes
End Select

'Encode the plaintText to bytes
hashBytes = UTF8_GetBytes(plainText)

If key = vbNullString Then

    'Get the key generated by the hashManager
    hmacKeyBytes = hashManager.key

    'Calculate the hash
    hashBytes = hashManager.ComputeHash_2(hashBytes)

    'Return encoded result
    If encodeHash = heBase64 Then
        HMAC = "<Key>" & Encode(hmacKeyBytes, edBase64) & "<Key>" & vbCrLf & Encode(hashBytes, edBase64)
    ElseIf encodeHash = heHex Then
        HMAC = "<Key>" & Encode(hmacKeyBytes, edHex) & "<Key>" & vbCrLf & Encode(hashBytes, edHex)
    End If

Else

    'Decode and set the key
    Select Case decodeKey
    Case kdBase64
        hashManager.key = Decode(key, edBase64)
    Case kdHex
        hashManager.key = Decode(key, edHex)
    Case Else
        hashManager.key = UTF8_GetBytes(key)
    End Select

    'Calculate the hash
    hashBytes = hashManager.ComputeHash_2(hashBytes)

    'Return encoded result
    If encodeHash = heBase64 Then
        HMAC = Encode(hashBytes, edBase64)
    ElseIf encodeHash = heHex Then
        HMAC = Encode(hashBytes, edHex)
    End If

End If

Set hashManager = Nothing

End Function

Test subroutine:

Sub PBKDF2_Test()

Dim testvector As String
Dim pbkdf2_result As String

pbkdf2_result = PBKDF2("password", "salt", 1, HMAC_SHA1, 20, heHex)
testvector = "0c60c80f961f0e71f3a9b524af6012062fe037a6"
If pbkdf2_result = testvector Then Debug.Print "TV1: OK" Else Debug.Print "TV1: FAULT"

pbkdf2_result = PBKDF2("password", "salt", 2, HMAC_SHA1, 20, heHex)
testvector = "ea6c014dc72d6f8ccd1ed92ace1d41f0d8de8957"
If pbkdf2_result = testvector Then Debug.Print "TV2: OK" Else Debug.Print "TV2: FAULT"

pbkdf2_result = PBKDF2("password", "salt", 4096, HMAC_SHA1, 20, heHex)
testvector = "4b007901b765489abead49d926f721d065a429c1"
If pbkdf2_result = testvector Then Debug.Print "TV3: OK" Else Debug.Print "TV3: FAULT"

pbkdf2_result = PBKDF2("passwordPASSWORDpassword", "saltSALTsaltSALTsaltSALTsaltSALTsalt", 4096, HMAC_SHA1, 25, heHex)
testvector = "3d2eec4fe41c849b80c8d83662c0e44a8b291a964cf2f07038"
If pbkdf2_result = testvector Then Debug.Print "TV4: OK" Else Debug.Print "TV4: FAULT"

End Sub

Any assistance would be greatly appreciated. Googling doesn't turn up much on PBKDF2 use in Excel.

You are missing the keyDecoding enum I think in ByVal decodeKey As keyDecoding - either you copied the core wrong, or it doesn't exist and you need to write your own — Greedo
Thanks - I've coped everything from the source (re-checked) maybe they missed it in their posting? (link in original post) Thanks for your help, though, I'm not quite sure how I'd write my own? = \ — le__bon
I took a quick look at the SO post you link; the Decode function is not included so you'll have to find your own I'm afraid. The Encode function is in the body of the question. The Enums are missing but not too hard to write. I suggest reading up on what the Decode function does, trying to write your own, and if you have difficulties, post that in a new question here. PS you only need to comment once, to tag someone in a post do @username at the start (see the help link in the bottom of the add comment box) — Greedo

Greedo Greedo · Accepted Answer · 2019-05-01T06:50:34

This whole section in your HMAC function is missing some enum definitions:

Select Case decodeKey
Case kdBase64
    hashManager.key = Decode(key, edBase64)
Case kdHex
    hashManager.key = Decode(key, edHex)
Case Else
    hashManager.key = UTF8_GetBytes(key)
End Select

decodeKey is declared in your function signature with type keyDecoding which I presume is an enum:

Optional ByVal decodeKey As keyDecoding = kdNone_String

That's what's giving your error, this keyDecoding enum is not defined anywhere. However it looks like it only has 3 members so you could probably roll your own.

The Decode and Encode functions meanwhile take some enum with edBase64 and edHex members. This enum also isn't defined anywhere (although this is not what gives you the error, it's the first point I mentioned).

You haven't included those two functions anyway so I can't say much more than that, but I imagine their signatures will tell you what the name of your second missing enum is. There maybe other issues with the code, but that's just what stood out to me. Make sure you've copied it from source correctly:)

PBKDF2 as a function in Excel

1 Answers