vba Excel 的工作表密码保护是如何工作的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12852095/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does Excel's worksheet password protection work
提问by Andrew White
This code has been floating around the net for quite a few years - it's apparently able to provide a password for decrypting an excel spreadsheet that you don't know the password to.
这段代码已经在网上流传了好几年——它显然能够提供一个密码来解密你不知道密码的 Excel 电子表格。
http://www.theofficeexperts.com/VBASamples/Excel02.htm
http://www.theofficeexperts.com/VBASamples/Excel02.htm
I'm interested in how this works but I don't seem to be able to work it out. I'm assuming it's encrypted under a certain value that can be gotten to in a number of ways (a few places on the net say it'll give you the original password or another one that will work) sort of like a public key - you can have 100 public keys that all work with a single private one.
我对它的工作原理很感兴趣，但我似乎无法解决。我假设它是在某个值下加密的，可以通过多种方式获得（网络上的一些地方说它会给你原始密码或另一个有效的密码）有点像公钥 -您可以拥有 100 个公钥，所有公钥都可以与一个私钥一起使用。
To me it seems like it's creating integer variables and filling them with a specific number, before turning that number into the relevant characters. Wouldn't these always be the same? If so, is there "master password" for Excel protection?
对我来说，它似乎在创建整数变量并用特定数字填充它们，然后将该数字转换为相关字符。这些不会总是一样的吗？如果是这样，是否有用于 Excel 保护的“主密码”？
Thanks all!
谢谢大家！
EDIT: I've noticed the For n = 32 To 126in the example code I posted. Cross referencing with an ASCII table that seems to be all characters from space through to tilde. Is this some sort of dictionary attack I'm not understanding due to syntax?
编辑：我注意到我发布的示例代码中的For n = 32 To 126。与一个 ASCII 表交叉引用，该表似乎是从空格到波浪号的所有字符。这是某种由于语法而我不理解的字典攻击吗？
回答by brettdj
It is a small world, as the code indicates I posted that code at another forum around 10 years ago having seen it somewhere else - I think on John Walkenbach'sold web site
这是一个小世界，因为代码表明我在大约 10 年前在另一个论坛上发布了该代码，在其他地方看到了它——我想是在约翰沃肯巴赫的旧网站上
It is important to note that this code protection applies to worksheet protection only - not to Excel's file open or VBA passwords.
请务必注意，此代码保护仅适用于工作表保护 - 不适用于 Excel 的文件打开或 VBA 密码。
- One example of this write-up in full is here(screenshot below)
- googling
excel sheet protection “test” and “zzyw”gives other references such as this from Tom Urtis
回答by Torben Klein
Edit (2020): From Excel 2013 on, apparently the protection scheme has changed. So the original answer only has historical significance anymore.
编辑（2020 年）：从 Excel 2013 开始，显然保护方案已更改。所以原来的答案只剩下历史意义了。
The new protection makes it near-impossible to retrieve the password by using state-of-the-art SHA-512 hashing. But why break it if you can simply pluck it out within seconds.
新的保护使得通过使用最先进的 SHA-512 哈希几乎不可能检索密码。但是，如果您可以在几秒钟内将其拔出，为什么要打破它。
- unzip the
.xlsxor.xlsmfile - edit
xl/worksheets/sheet<num>.xml - search and remove
<sheetProtection... />tag - save, zip again, enjoy
- 解压缩
.xlsx或.xlsm文件 - 编辑
xl/worksheets/sheet<num>.xml - 搜索和删除
<sheetProtection... />标签 - 保存，再次压缩，享受
Original answer (up to Excel 2010)
原始答案（至 Excel 2010）
Fascinating - I knew the code snippet before, but not the explanation that brettdj posted. As the others explained, it is a brute-force search for hash collisions. Actually it seems to have been made by trial and error, since it does much more work than necessary (194560 combinations are generated, but there are only 32768 hashvalues possible.)
令人着迷 - 我之前知道代码片段，但不知道 brettdj 发布的解释。正如其他人所解释的，这是对哈希冲突的蛮力搜索。实际上它似乎是通过反复试验做出的，因为它做了比必要更多的工作（生成了 194560 个组合，但只有 32768 个可能的哈希值。）
Excel's hash algorithm in short (as explained in http://chicago.sourceforge.net/devel/docs/excel/encrypt.html):
Excel 的哈希算法简而言之（如http://chicago.sourceforge.net/devel/docs/excel/encrypt.html 中所述）：
- Take the ascii code of each character of the passwort.
- Treat it as a 16-bit signed number. Shift its bits to the left, based on the position of the character (1 bit for first character, 2 for 2nd and so on)
- XOR all the characters together, giving a 16-bit signed int >=0.
- XOR that result with the length of the password and a magic number.
- 取密码每个字符的ascii码。
- 将其视为 16 位有符号数。根据字符的位置将其位向左移动（第一个字符为 1 位，第二个字符为 2 位，依此类推）
- 将所有字符异或在一起，得到一个 16 位有符号整数 >=0。
- 结果与密码长度和幻数异或。
Knowing this, one can devise a brute-force search as follows:
知道了这一点，就可以设计出如下的蛮力搜索：
- The highest bit is always zero, so there are 15 bits to test.
- Split them up into three counters each covering 5 bits. That way each counter can represent a printable ascii char.
- Pack the ascii representation of those counters in a password string, in a way so that they do not affect each other.
- 最高位始终为零，因此有 15 位要测试。
- 将它们分成三个计数器，每个计数器覆盖 5 位。这样每个计数器都可以代表一个可打印的 ascii 字符。
- 将这些计数器的 ascii 表示打包在密码字符串中，以使它们不会相互影响。
The simplest way is to use a 11-character password and put the counters at position 1, 6 and 11. The bit-shifting in step 2 aligns the counter bits the right way: the first counter ("x") is shifted 1 bit, the second one ("y") 6 bits, the third one ("z") 11 bits. In a bitwise representation of the hash, the counters affect the following bits:
最简单的方法是使用 11 个字符的密码并将计数器放在位置 1、6 和 11。步骤 2 中的位移位以正确的方式对齐计数器位：第一个计数器（“x”）移位 1 位，第二个（“y”）6位，第三个（“z”）11位。在散列的按位表示中，计数器影响以下位：
bit: 76543210 76543210
cnt: -zzzzyyy yyxxxxxz
The XOR operations can be ignored since the XOR argument is constant all the time. For the same reason, a constant offset (e.g. 64) can be added. Also it does not matter what character is used on the other password bytes (2-5, 7-10).
由于 XOR 参数始终是常量，因此可以忽略 XOR 操作。出于同样的原因，可以添加一个恒定的偏移量（例如 64）。此外，在其他密码字节（2-5、7-10）上使用什么字符也无关紧要。
By iterating over all possible combinations of x, y, z you eventually find a password that gives the same hash value as the original one.
通过迭代 x、y、z 的所有可能组合，您最终会找到一个与原始密码具有相同哈希值的密码。
Public Sub demo()
' http://stackoverflow.com/questions/12852095/how-does-excels-worksheet-password-protection-work
Dim x As Integer, y as Integer, z as Integer
Dim part1 As String, part12 As String
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets(1)
sh.Protect "$ome_Insanely_Long_and_c0mplex_password! [(which i$ imp*ssible t0 re-member)]"
For x = 64 To 95
' pad with dots, so that x, y and z affect nonoverlapping bits of the hash.
part1 = Chr(x) + "...."
For y = 64 To 95
part12 = part1 + Chr(y) + "...."
For z = 64 To 95
On Error Resume Next
sh.Unprotect part12 + Chr(z)
If Err.Number = 0 Then
Debug.Print "Password: '" & part12 + Chr(z) & "'"
Exit Sub
End If
On Error GoTo 0
Next
Next
Next
End Sub
回答by mkingston
Just a guess, but it looks to me like Excel tests if the password is valid by running it through some sort of hash function which produces a pretty small range of results and compares it with the hashed value stored.
只是一个猜测，但在我看来，它就像 Excel 通过某种散列函数运行密码来测试密码是否有效，该函数会产生很小范围的结果并将其与存储的散列值进行比较。
Presumedly what this function is doing is testing all those values until it finds one that works. Judging by the values used, the hash function produces 2^11*(126-31) different values, all of which can be produced by the values generated in this code.
据推测，这个函数正在做的是测试所有这些值，直到找到一个有效的值。从使用的值来看，哈希函数产生了2^11*(126-31)个不同的值，所有这些值都可以由这段代码中生成的值产生。
My analysis assumes this routine works. I haven't tested it.
我的分析假设这个例程有效。我没有测试过。
回答by vy32
The code does a brute force search using the encryption passwords AAAAAAAAAAA(SPACE) through BBBBBBBBBBB(~) where (SPACE) is the space character (CHR(32)) and (~) is of course character 126. When the password is found it displays the password in a message box.
该代码使用加密密码 AAAAAAAAAAA(SPACE) 到 BBBBBBBBBBB(~) 进行蛮力搜索，其中 (SPACE) 是空格字符 (CHR(32)) 而 (~) 是字符 126。当找到密码时在消息框中显示密码。
Of course, this means that it is only checking passwords that are precisely 12 characters long and that only consist of upper-case letters A (ASCII 65) and B (ASCII 66) followed by one of the printable ASCII characters. @mkingston is correct that it tests 2^11*(126-31) different values. But there is no hash function. I don't think that this will crack many spreadsheets. You would be better off using One of these programs from AccessData.
当然，这意味着它只检查长度恰好为 12 个字符且仅由大写字母 A (ASCII 65) 和 B (ASCII 66) 后跟可打印 ASCII 字符之一组成的密码。@mkingston 是正确的，它测试了 2^11*(126-31) 个不同的值。但是没有哈希函数。我认为这不会破解许多电子表格。您最好使用AccessData 中的这些程序之一。
For more details on ActiveSheet.Protect and ActiveSheet.Unprotect, see http://msdn.microsoft.com/en-us/library/office/aa191957(v=office.10).aspx.
有关 ActiveSheet.Protect 和 ActiveSheet.Unprotect 的更多详细信息，请参阅http://msdn.microsoft.com/en-us/library/office/aa191957(v=office.10).aspx。
回答by user3349074
Sub FindPassword()
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
