Understanding hashing algorithms in VBA and how to avoid collisions.

[kelly mort]

I found this code at stackoverflow.com and I am falling in love with the code. I am now learning about the hashing algorithms in VBA and I wish someone with a deeper understanding could help guide me and advise me on a few things:
1. Is there a possibility that two or more different strings or input will produce same output?
2. If the above can occur, is there a way to prevent that and how?

Sub tester()
    Dim myInput$
    myInput = "money"
    'MsgBox hash4(myInput)
    MsgBox hash12(myInput)
End Sub


Function hash12(s$)
    Dim l%, l3%, s1$, s2$, s3$
    l = Len(s)
    l3 = Int(l / 3)
    s1 = Mid(s, 1, l3)
    s2 = Mid(s, l3 + 1, l3)
    s3 = Mid(s, 2 * l3 + 1)
    hash12 = hash4(s1) + hash4(s2) + hash4(s3)
End Function

Function hash4(txt)
    Dim x&, mask, i, j, nC, crc%, c$
    crc = &HFFFF
    For nC = 1 To Len(txt)
        j = Asc(Mid(txt, nC))
        crc = crc Xor j
        For j = 1 To 8
            mask = 0
            If crc / 2 <> Int(crc / 2) Then mask = &HA001
            crc = Int(crc / 2) And &H7FFF: crc = crc Xor mask
        Next j
    Next nC
    
    c = Hex$(crc)

    While Len(c) < 4
      c = "0" & c
    Wend
    
    hash4 = c
End Function

 

[Eric W]

Any hashing algorithm can create duplicate hashes for different input values. The best algorithms manage to minimize the number of collisions, but it's always a possibility. Typically a website might store the hash value of a password instead of the password itself, to prevent saving the password in a readable form. But if the hashing algorithm is weak, then there could actually be many passwords with the same hash value, and the security on that site is weak too.

If you're using the hash value for something else, like creating a lookup key in an internal table, there are several options. One of which is chaining. If multiple records generate the same hash, create a list of matching records, and if you create a hash that has multiple records, go down the list until you find the one you want.

Anyway, this is a huge subject, too big to really cover here. Google "Hashing algorithms collisions" and you should find a lot of links.

 

[kelly mort]

@Eric W,

Thanks for the quick and timely reply.

I have done a few readings already and have come across a few examples.

Example :

Public Function Base64Sha1(inputText As String, Optional secretKey = "") As String
 
    Dim asc As Object
    Dim enc As Object
    Dim textToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    
    If secretKey = "" Then secretKey = inputText
    
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
 
    textToHash = asc.GetBytes_4(inputText)
    SharedSecretKey = asc.GetBytes_4(secretKey)
    enc.Key = SharedSecretKey
 
    bytes = enc.ComputeHash_2((textToHash))
    Base64Sha1 = EncodeBase64(bytes)
 
End Function
 
Private Function EncodeBase64(arrData() As Byte) As String
 
    Dim objXML As Object
    Dim objNode As Object
 
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")
 
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text
 
End Function
 
Sub TestMe()
    
    Debug.Print Base64Sha1("asdf", "ThisIsTheSecretKey") = "DSmGEC8dUW9xRs+YfAPji59dxCM="
    Debug.Print Base64Sha1("asdf") = "qIQmNGgreJRqJroWUUu0MxLq2oo="
    Debug.Print Base64Sha1("asdf", "asdf") = "qIQmNGgreJRqJroWUUu0MxLq2oo="
    
End Sub

This code has appeared in multiple searches that I have conducted.

My question now is, can this be implemented in Excel VBA and how does it work?

A little bit of expert advise will be great.

 

[Eric W]

It appears to be standard VBA code, so it should work. It relies on some system libraries, which may or may not be present on your PC. To use it from a worksheet,

=Base64Sha1("clear text to be hashed","hash key")

or from another procedure:

HashValue = Base64Sha1("clear text to be hashed","hash key")

 

[kelly mort]

It appears to be standard VBA code, so it should work. It relies on some system libraries, which may or may not be present on your PC. To use it from a worksheet,

=Base64Sha1("clear text to be hashed","hash key")

or from another procedure:

HashValue = Base64Sha1("clear text to be hashed","hash key")

I am getting "automation error"

So how do I get those libraries on my pc?

 

[Marc L]

Hi,​

​

maybe using some 'hashing algorithm' in pure VBA rather than using any external library …​

 

[kelly mort]

Hi,​

​

maybe using some 'hashing algorithm' in pure VBA rather than using any external library …​

Do you have any examples in mind?

I would like to have a look.

 

[Marc L]

I have only some cryptographing samples rather than hashing ones …​

 

[kelly mort]

​

I have only some cryptographing samples rather than hashing ones …​

Okay you can share it for me to have a look. I love to learn new things.

 

[kelly mort]

​

I have only some cryptographing samples rather than hashing ones …​

Okay you can share it for me to have a look. I love to learn new things.