跳转到内容

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://wikibooks.cn/w/index.php?title=Visual_Basic_for_Applications/String_Hashing_in_VBA&oldid=4090333"
华夏公益教科书