跳轉到內容

Visual Basic for Applications/VBA 中的字串雜湊

Add links
來自華夏公益教科書，開放的書籍，開放的世界

摘要

[編輯 | 編輯原始碼]
  • 以下 VBA 程式碼生成 MD5、SHA1、SHA2-256、SHA2-384 和 SHA2-512 雜湊的摘要；在本例中，用於字串。
  • 雜湊是一個輸出字串，它類似於偽隨機序列，並且對於用作其起始值的任何字串來說本質上是唯一的。雜湊無法輕易破解以找到用於製作它們的字串，並且它們對輸入更改非常敏感。也就是說，僅在開頭更改一個字元就會產生完全不同的輸出。雜湊可以用作偽隨機字元表的依據，儘管並非完全隨機，但這種方法可以產生至少與 VBA 的內建 Rnd() 函式一樣好的輸出質量。
  • 使用雜湊允許程式設計師避免將密碼字串嵌入到他們的程式碼中。
    • 應用程式佔用的記憶體空間可以使用特殊的實用程式讀取，因此密碼可能會在程式碼中找到，然後用於正常的使用者登入。相反，密碼的雜湊在程式碼中列出，並且僅在登入期間對密碼進行雜湊以進行比較。這避免了透過傳統使用者路徑訪問應用程式，因為找到的任何雜湊都無法進行逆向工程以獲得使用者介面所需的價值。這種方法假設程式碼不能由入侵者在登入裝置以外的任何位置執行，並且他們無法更改記憶體內容。
    • 如果駭客可以更改記憶體內容，那麼一個常見的漏洞是將記憶體中的雜湊更改為他們自己的雜湊；與他們可以在使用者登入介面使用的密碼相對應的雜湊。針對這種攻擊的反制措施是，所有登入檔案都使用使用者的官方頒發的密碼進行加密。然後，即使雜湊發生了變化，用於登入嘗試所需的檔案也無法解密使用。
  • 雜湊也可以從整個檔案生成，生成檔案雜湊的程式碼與下面給出的字串雜湊版本略有不同。檔案雜湊的主要區別在於，在使用傳統技術之前，首先將檔案轉換為字串。程式碼在其他地方本系列中給出了用於檔案雜湊的程式碼。字串雜湊即使在使用空字串作為起點時也會產生輸出，這與檔案雜湊不同，在檔案雜湊中，空文字檔案可能會引發錯誤。
  • 此 VBA 程式碼不特定於任何一個應用程式，因此它可以在任何地方工作，例如 MS Word、MS Excel 或 MS Access。這些程式碼版本包括 base-64 輸出或十六進位制的選項。

程式碼列表

[編輯 | 編輯原始碼]

關於程式碼的說明

[編輯 | 編輯原始碼]

重要. 發現雜湊例程在 Windows 10、64 位 Office 設定中出錯。但是，隨後的檢查揭示瞭解決方案。Windows 平臺必須安裝Net Framework 3.5（包含 .Net 2 和 .Net 3），這個舊版本，而不僅僅是Net Framework 4.8 高階服務，它在開啟或關閉 Windows 功能中啟用。當它在那裡被選中時，例程完美地工作。

以下 VBA 程式碼以十六進位制或 base-64 輸出格式生成字串的 MD5、SHA1、SHA2-256、SHA2-384 和 SHA2-512 雜湊的摘要。這些編碼都使用 MS Office 的內建函式，並提供一致的結果。值得注意的是，其他地方對相同摘要的原始實現可能在輸出方面存在很大差異。僅給出了一個具有種子或鹽引數（StrToSHA512Salt）的示例，應注意HMACSHA512 類輸出不同於在其餘部分給出的SHA*Managed 類雜湊。Managed 類提供了最廣泛報道的結果。請注意正確操作所需的 VBA 引用。在某些過程標題中給出了一個提醒。

在每種情況下，編碼人員都可以在 bytes() 陣列中找到未修改的雜湊值，此時它們位於 8 位位元組中，也就是說，表示 ASCI 程式碼的數字，因為它適用於完整的 8 位、256 個字元集。在每種情況下，填充 bytes() 陣列後的程式碼決定要提供哪種版本的 ASCI 字元集。對於十六進位制字元集，0-9 以及 A 到 F，總位集被分成四位位元組的兩倍，然後返回以供使用。對於 base-64 集，主要為小寫字母、大寫字母和整數，六位字元用於輸出。這兩個集合在這裡最有用，因為它們由常用的字元組成。128 和 256 ASCI 集合充滿了奇異字元和非列印字元，因此不實用。對於每個雜湊版本，其位數是一個常數，因此其輸出的長度會根據所選型別而異。

如果您的資料採用 ANSI 格式，則在使用 T-SQL HASHBYTES() 函式處理超過 128 的字元程式碼時，Excel/ACCESS 和 SQL Server 之間的結果將不同。要解決這些差異，請使用 StrConv() 而不是 .GetBytes_4()

'dont use for ANSI
Set oT = CreateObject("System.Text.UTF8Encoding")
TextToHash = oT.GetBytes_4(sIn)
'for ANSI data use StrConv instead
TextToHash = StrConv(sIn, vbFromUnicode)



Option Explicit

Sub Test()
    'run this to test md5, sha1, sha2/256, sha384, sha2/512 with salt, or sha2/512
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
    
    Dim sIn As String, sOut As String, b64 As Boolean
    Dim sH As String, sSecret As String
    
    'insert the text to hash within the sIn quotes
    'and for selected procedures a string for the secret key
    sIn = ""
    sSecret = "" 'secret key for StrToSHA512Salt only
    
    'select as required
    'b64 = False   'output hex
    b64 = True   'output base-64
    
    'enable any one
    'sH = MD5(sIn, b64)
    'sH = SHA1(sIn, b64)
    'sH = SHA256(sIn, b64)
    'sH = SHA384(sIn, b64)
    'sH = StrToSHA512Salt(sIn, sSecret, b64)
    sH = SHA512(sIn, b64)
    
    'message box and immediate window outputs
    Debug.Print sH & vbNewLine & Len(sH) & " characters in length"
    MsgBox sH & vbNewLine & Len(sH) & " characters in length"
    
    'de-comment this block to place the hash in first cell of sheet1
'    With ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)
'        .Font.Name = "Consolas"
'        .Select: Selection.NumberFormat = "@" 'make cell text
'        .Value = sH
'    End With

End Sub

Public Function MD5(ByVal sIn As String, Optional bB64 As Boolean = 0) As String
    'Set a reference to mscorlib 4.0 64-bit
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
        
    'Test with empty string input:
    'Hex:   d41d8cd98f00...etc
    'Base-64: 1B2M2Y8Asg...etc
        
    Dim oT As Object, oMD5 As Object
    Dim TextToHash() As Byte
    Dim bytes() As Byte
        
    Set oT = CreateObject("System.Text.UTF8Encoding")
    Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
 
    TextToHash = oT.GetBytes_4(sIn)
    bytes = oMD5.ComputeHash_2((TextToHash))
 
    If bB64 = True Then
       MD5 = ConvToBase64String(bytes)
    Else
       MD5 = ConvToHexString(bytes)
    End If
        
    Set oT = Nothing
    Set oMD5 = Nothing

End Function

Public Function SHA1(sIn As String, Optional bB64 As Boolean = 0) As String
    'Set a reference to mscorlib 4.0 64-bit
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
   
   'Test with empty string input:
    '40 Hex:   da39a3ee5e6...etc
    '28 Base-64:   2jmj7l5rSw0yVb...etc
    
    Dim oT As Object, oSHA1 As Object
    Dim TextToHash() As Byte
    Dim bytes() As Byte
            
    Set oT = CreateObject("System.Text.UTF8Encoding")
    Set oSHA1 = CreateObject("System.Security.Cryptography.SHA1Managed")
    
    TextToHash = oT.GetBytes_4(sIn)
    bytes = oSHA1.ComputeHash_2((TextToHash))
        
    If bB64 = True Then
       SHA1 = ConvToBase64String(bytes)
    Else
       SHA1 = ConvToHexString(bytes)
    End If
            
    Set oT = Nothing
    Set oSHA1 = Nothing
    
End Function

Public Function SHA256(sIn As String, Optional bB64 As Boolean = 0) As String
    'Set a reference to mscorlib 4.0 64-bit
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
    
    'Test with empty string input:
    '64 Hex:   e3b0c44298f...etc
    '44 Base-64:   47DEQpj8HBSa+/...etc
    
    Dim oT As Object, oSHA256 As Object
    Dim TextToHash() As Byte, bytes() As Byte
    
    Set oT = CreateObject("System.Text.UTF8Encoding")
    Set oSHA256 = CreateObject("System.Security.Cryptography.SHA256Managed")
    
    TextToHash = oT.GetBytes_4(sIn)
    bytes = oSHA256.ComputeHash_2((TextToHash))
    
    If bB64 = True Then
       SHA256 = ConvToBase64String(bytes)
    Else
       SHA256 = ConvToHexString(bytes)
    End If
    
    Set oT = Nothing
    Set oSHA256 = Nothing
    
End Function

Public Function SHA384(sIn As String, Optional bB64 As Boolean = 0) As String
    'Set a reference to mscorlib 4.0 64-bit
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
    
    'Test with empty string input:
    '96 Hex:   38b060a751ac...etc
    '64 Base-64:   OLBgp1GsljhM2T...etc
    
    Dim oT As Object, oSHA384 As Object
    Dim TextToHash() As Byte, bytes() As Byte
    
    Set oT = CreateObject("System.Text.UTF8Encoding")
    Set oSHA384 = CreateObject("System.Security.Cryptography.SHA384Managed")
    
    TextToHash = oT.GetBytes_4(sIn)
    bytes = oSHA384.ComputeHash_2((TextToHash))
    
    If bB64 = True Then
       SHA384 = ConvToBase64String(bytes)
    Else
       SHA384 = ConvToHexString(bytes)
    End If
    
    Set oT = Nothing
    Set oSHA384 = Nothing
    
End Function

Public Function SHA512(sIn As String, Optional bB64 As Boolean = 0) As String
    'Set a reference to mscorlib 4.0 64-bit
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
    
    'Test with empty string input:
    '128 Hex:   cf83e1357eefb8bd...etc
    '88 Base-64:   z4PhNX7vuL3xVChQ...etc
    
    Dim oT As Object, oSHA512 As Object
    Dim TextToHash() As Byte, bytes() As Byte
    
    Set oT = CreateObject("System.Text.UTF8Encoding")
    Set oSHA512 = CreateObject("System.Security.Cryptography.SHA512Managed")
    
    TextToHash = oT.GetBytes_4(sIn)
    bytes = oSHA512.ComputeHash_2((TextToHash))
    
    If bB64 = True Then
       SHA512 = ConvToBase64String(bytes)
    Else
       SHA512 = ConvToHexString(bytes)
    End If
    
    Set oT = Nothing
    Set oSHA512 = Nothing
    
End Function

Function StrToSHA512Salt(ByVal sIn As String, ByVal sSecretKey As String, _
                           Optional ByVal b64 As Boolean = False) As String
    'Returns a sha512 STRING HASH in function name, modified by the parameter sSecretKey.
    'This hash differs from that of SHA512 using the SHA512Managed class.
    'HMAC class inputs are hashed twice;first input and key are mixed before hashing,
    'then the key is mixed with the result and hashed again.
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
    
    Dim asc As Object, enc As Object
    Dim TextToHash() As Byte
    Dim SecretKey() As Byte
    Dim bytes() As Byte
    
    'Test results with both strings empty:
    '128 Hex:    b936cee86c9f...etc
    '88 Base-64:   uTbO6Gyfh6pd...etc
    
    'create text and crypto objects
    Set asc = CreateObject("System.Text.UTF8Encoding")
    
    'Any of HMACSHAMD5,HMACSHA1,HMACSHA256,HMACSHA384,or HMACSHA512 can be used
    'for corresponding hashes, albeit not matching those of Managed classes.
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA512")

    'make a byte array of the text to hash
    bytes = asc.Getbytes_4(sIn)
    'make a byte array of the private key
    SecretKey = asc.Getbytes_4(sSecretKey)
    'add the private key property to the encryption object
    enc.Key = SecretKey

    'make a byte array of the hash
    bytes = enc.ComputeHash_2((bytes))
    
    'convert the byte array to string
    If b64 = True Then
       StrToSHA512Salt = ConvToBase64String(bytes)
    Else
       StrToSHA512Salt = ConvToHexString(bytes)
    End If
    
    'release object variables
    Set asc = Nothing
    Set enc = Nothing

End Function

Private Function ConvToBase64String(vIn As Variant) As Variant
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
   
   Dim oD As Object
      
    Set oD = CreateObject("MSXML2.DOMDocument")
      With oD
        .LoadXML "<root />"
        .DocumentElement.DataType = "bin.base64"
        .DocumentElement.nodeTypedValue = vIn
      End With
    ConvToBase64String = Replace(oD.DocumentElement.Text, vbLf, "")
    
    Set oD = Nothing

End Function

Private Function ConvToHexString(vIn As Variant) As Variant
    'Check that Net Framework 3.5 (includes .Net 2 and .Net 3 is installed in windows
    'and not just Net Advanced Services 
    
    Dim oD As Object
      
    Set oD = CreateObject("MSXML2.DOMDocument")
      
      With oD
        .LoadXML "<root />"
        .DocumentElement.DataType = "bin.Hex"
        .DocumentElement.nodeTypedValue = vIn
      End With
    ConvToHexString = Replace(oD.DocumentElement.Text, vbLf, "")
    
    Set oD = Nothing

End Function

另請參閱

[編輯 | 編輯原始碼]
  • VBA 中的檔案雜湊  : 本系列中的一個配套頁面，其中列出了單個檔案雜湊的程式碼。將此與檔案列表程式碼結合起來，可以對檔案進行廣泛的雜湊。
  • VBA 中的資料夾雜湊 : 另一個配套頁面，它建立遞迴資料夾雜湊列表和日誌。使用最新的雜湊演算法，但僅限於不超過約 200MB 的檔案。
檢索自 "https://wikibook.tw/w/index.php?title=Visual_Basic_for_Applications/String_Hashing_in_VBA&oldid=4090333"
華夏公益教科書