HotXLS XLS XOR Obfuscation: Key Derivation and XorRor

HotXLS writes Excel 5.0/95 (BIFF5) XOR-obfuscated workbooks that Excel 16 opens only when three details match [MS-OFFCRYPTO] exactly: the FILEPASS key must be CreateXorKey_Method1(password), the 16-byte XOR array must be built with XorRor (rotate right by one bit), and each byte must use XorArrayIndex = (stream offset + record length) mod 16. HotXLS got the index right in v2.384.47 and the key and rotation right in v2.384.54. Before that, every password-protected BIFF5 file it produced opened fine in HotXLS and failed in Excel

That last sentence is the whole story in miniature. A reader and a writer that share the same wrong idea agree with each other perfectly, so round-trip tests stay green while the only consumer that matters says no. Excel 16 said no twice, with two different messages, and each message pointed at a different layer of the scheme. This article walks through those layers in the order Excel checks them, with byte-level detail you can use whether you call HotXLS or write your own BIFF reader

What does BIFF XOR obfuscation actually store?

BIFF XOR obfuscation stores only two 16-bit words in the file, and everything else is recomputed from the password. The FILEPASS record ($002F) sits immediately after the workbook globals BOF, and in a BIFF5 file its body is exactly 4 bytes: the XOR key followed by the password verifier. There is no salt, no algorithm identifier and no encrypted verifier blob of the kind RC4 and AES schemes carry

From those two words a reader rebuilds three things:

• The verifier, a 16-bit hash of the password bytes XORed with $CE4B. Comparing it with the stored word is the password check, and the only one
• The XOR key, a 16-bit value from CreateXorKey_Method1 in [MS-OFFCRYPTO] §2.3.7.2, driven by two constant tables (InitialCode, 15 words, and XorMatrix, 105 words)
• The XOR array, 16 bytes made of the password bytes padded with a fixed 16-byte pad, each XORed with the low key byte (even positions) or the high key byte (odd positions), then rotated right by one bit

Record headers stay in plain text, and so do a handful of whole records that the scheme exempts, among them BOF, FILEPASS and INTERFACEHDR. Every other record body is transformed byte by byte: rotate left by 5 bits, then XOR with one entry of the 16-byte array. Decryption, which [MS-OFFCRYPTO] §2.3.7.3 spells out as DecryptData_Method1, is the mirror image: XOR first, then rotate right by 5

In HotXLS you never touch any of this directly. Set a password, pick the format, and SaveAs emits FILEPASS and transforms the stream:

uses
  SysUtils, lxHandle;

procedure SaveLegacyProtectedBook(const FileName: string);
var
  Wb: IXLSWorkbook;
begin
  Wb := TXLSWorkbook.Create;
  Wb.Sheets.Add.Name := 'Ledger';
  Wb.Sheets[1].Range['A1', 'A1'].Value := 'Account';
  Wb.Sheets[1].Range['B1', 'B1'].Value := 1250.75;

  // BIFF5 only supports XOR obfuscation; xletAuto would pick it as well
  Wb.EncryptionType := xletXor;
  // Keep it ASCII and at most 15 characters (see below)
  Wb.EncryptionPassword := 'secret';

  if Wb.SaveAs(FileName, xlExcel5) <> 1 then
    raise Exception.Create('BIFF5 save failed');
end;

Why does Excel say the password is wrong when the verifier matches?

Excel rejects the password because it does not trust the stored key: Excel derives the key from the typed password with CreateXorKey_Method1 and compares it with the FILEPASS key word, so a file whose key is anything else fails the password check even when the verifier is correct. The specification describes the key as an output of the password, not as a free parameter, and Excel 16 enforces that reading

The HotXLS writer before v2.384.54 filled the key word with two random bytes. That looks harmless on paper, since the verifier is the documented password check and the array is built from whatever key the file declares. HotXLS itself read those files without trouble, because its reader took the key from FILEPASS as given. Excel 16, handed the same file and the correct password, answered that the password was not correct. Since v2.384.54 the key is derived, so FILEPASS for the password secret always holds key $014D and verifier $DAA7, values cross-checked against an independent implementation of the specification

The derivation itself is short once the two tables are in place. Walk the password backwards, look at bit 6 of each byte seven times while shifting it left, and XOR in one XorMatrix entry each time the bit is set. The following is a principle sketch that reproduces the specification algorithm and matches the HotXLS implementation; it is not a HotXLS API:

// Principle sketch of [MS-OFFCRYPTO] 2.3.7.2 CreateXorKey_Method1
// and CreateXorArray_Method1 (illustration only, not a HotXLS API)
type
  TXorArray = array [0..15] of Byte;

function DemoCreateXorKey(const Password: AnsiString): Word;
const
  InitialCode: array [0..14] of Word = ($E1F0, $1D0F, $CC9C, $84C0, $110C,
    $0E10, $F1CE, $313E, $1872, $E139, $D40F, $84F9, $280C, $A96A, $4EC3);
  XorMatrix: array [0..104] of Word = (
    $AEFC, $4DD9, $9BB2, $2745, $4E8A, $9D14, $2A09,
    $7B61, $F6C2, $FDA5, $EB6B, $C6F7, $9DCF, $2BBF,
    $4563, $8AC6, $05AD, $0B5A, $16B4, $2D68, $5AD0,
    $0375, $06EA, $0DD4, $1BA8, $3750, $6EA0, $DD40,
    $D849, $A0B3, $5147, $A28E, $553D, $AA7A, $44D5,
    $6F45, $DE8A, $AD35, $4A4B, $9496, $390D, $721A,
    $EB23, $C667, $9CEF, $29FF, $53FE, $A7FC, $5FD9,
    $47D3, $8FA6, $0F6D, $1EDA, $3DB4, $7B68, $F6D0,
    $B861, $60E3, $C1C6, $93AD, $377B, $6EF6, $DDEC,
    $45A0, $8B40, $06A1, $0D42, $1A84, $3508, $6A10,
    $AA51, $4483, $8906, $022D, $045A, $08B4, $1168,
    $76B4, $ED68, $CAF1, $85C3, $1BA7, $374E, $6E9C,
    $3730, $6E60, $DCC0, $A9A1, $4363, $86C6, $1DAD,
    $3331, $6662, $CCC4, $89A9, $0373, $06E6, $0DCC,
    $1021, $2042, $4084, $8108, $1231, $2462, $48C4);
var
  Len, I, Bit, Element: Integer;
  Ch: Byte;
begin
  Result := 0;
  Len := Length(Password);
  if Len > 15 then
    Len := 15;                       // the key only sees 15 bytes
  if Len = 0 then
    Exit;
  Result := InitialCode[Len - 1];
  Element := $68;                    // last XorMatrix entry
  for I := Len downto 1 do
  begin
    Ch := Ord(Password[I]);
    for Bit := 1 to 7 do
    begin
      if (Ch and $40) <> 0 then
        Result := Result xor XorMatrix[Element];
      Ch := Byte(Ch shl 1);
      Dec(Element);
    end;
  end;
end;

function XorRor(B, KeyByte: Byte): Byte;
begin
  B := B xor KeyByte;
  Result := Byte((B shr 1) or (B shl 7));   // rotate right by one bit
end;

procedure DemoCreateXorArray(const Password: AnsiString; out Arr: TXorArray);
const
  PadArray: TXorArray = ($BB, $FF, $FF, $BA, $FF, $FF, $B9, $80,
    $00, $BE, $0F, $00, $BF, $0F, $00, $00);
var
  Key: Word;
  Len, I: Integer;
begin
  Key := DemoCreateXorKey(Password);
  Len := Length(Password);
  if Len > 16 then
    Len := 16;
  for I := 0 to Len - 1 do
    Arr[I] := Ord(Password[I + 1]);
  for I := Len to 15 do
    Arr[I] := PadArray[I - Len];
  for I := 0 to 15 do
    if Odd(I) then
      Arr[I] := XorRor(Arr[I], Byte(Key shr 8))
    else
      Arr[I] := XorRor(Arr[I], Byte(Key and $FF));
end;

For secret this produces the array 1F 32 17 B9 14 BA 7B 7F 59 DD 59 7F 7A C0 A6 DF, which is a handy fixture if you are testing your own reader

Why does the right key still produce a damaged file?

A correct key still produces a damaged file when the XOR array is rotated the wrong way: [MS-OFFCRYPTO] defines the array step as XorRor, a rotate right by one bit, and an array rotated left by two decrypts every record body to noise. Fixing the key moved Excel 16 past the password prompt and straight into a different error, a report that the file has a problem and cannot be opened

The old HotXLS code rotated each array byte left by 2 bits, a form that circulates in several BIFF implementations. Because HotXLS used the same rotation on both sides, its own reader never noticed. Excel 16 can no longer save Excel 5.0/95 files, and it does not offer XOR when saving BIFF8, so there was no native Excel sample to diff against. The evidence had to come from the other direction: write one plain-text BIFF5 stream, re-encode it eight ways, and let Excel 16 open each variant. The eight variants crossed three independent choices:

Choice	Option A	Option B
Array rotation	XorRor (rotate right 1)	Rotate left 2
Array index	(offset + record length) mod 16	offset mod 16
Byte transform order	Rotate left 5, then XOR	XOR, then rotate left 5

Excel 16 opened exactly two of the eight: XorRor with rotate-then-XOR and the record-length index, and one variant that only looks different. Rotate-left-2 with XOR-then-rotate and the same index is the same function in disguise. Rotation distributes over XOR, so rol5(p xor rol2(b)) equals rol5(p) xor rol7(b), and on an 8-bit value a rotate left by 7 is a rotate right by 1. In short, rol5 ∘ rol2 = ror1, which is why the rotate-left-2 array looks plausible in isolation: it is correct only together with the opposite transform order. Paired with the specification order, it corrupts every transformed byte

The same experiment settled a second question. The variants that dropped the record length from the index all failed, which confirmed the index rule HotXLS had adopted a release earlier on the strength of the specification text alone

How is XorArrayIndex calculated for each byte?

XorArrayIndex for a byte is its offset in the workbook stream plus the length of the whole record data it belongs to, mod 16. The index therefore restarts at a record-dependent value for every record and increments by one per byte inside it. The specification pseudocode names the inputs FileOffset and Data.Length, which is easy to misread as the record start offset alone, and that misreading is exactly what HotXLS shipped until v2.384.47

Three details decide whether your indices line up with Excel:

• The 4-byte record header is never transformed, but it still occupies stream positions, so the first body byte of a record sits at header offset + 4
• The length term is the full record data length, not the number of bytes actually transformed
• BOUNDSHEET is partly plain: its first 4 bytes, lbPlyPos, the stream offset of the sheet BOF, stay readable so a parser can locate sheets. Those 4 bytes are skipped by the transform but still count toward both the offset and the record length

Put together, the per-record transform is a few lines. Again, this is a sketch of the rule, not something you need to call:

function Rol8(B: Byte; N: Integer): Byte;
begin
  Result := Byte((B shl N) or (B shr (8 - N)));
end;

// Obfuscate one record body in place. BodyPos is the stream offset of
// Body[0], i.e. the record header offset + 4. PlainPrefix is 4 for
// BOUNDSHEET, the full length for BOF / FILEPASS, 0 for most records
procedure DemoObfuscateRecord(var Body: array of Byte; RecordLength: Word;
  PlainPrefix: Integer; BodyPos: LongWord; const Arr: TXorArray);
var
  I: Integer;
begin
  for I := PlainPrefix to RecordLength - 1 do
    Body[I] := Rol8(Body[I], 5) xor
      Arr[(BodyPos + LongWord(I) + RecordLength) mod 16];
end;

// Reading is the mirror image: B := Body[I] xor Arr[...];
// then rotate right by 5, i.e. Rol8(B, 3)

Before v2.384.47 the HotXLS reader computed the index from the stream position alone and the writer used the plain byte offset. Both ignored the record length, so again the two halves agreed with each other and with nobody else. An independently written decoder read the v2.384.47 output correctly and the older output as garbage, and the eight-variant Excel 16 test later confirmed the rule against the real target

What happens to XOR files written by older HotXLS versions?

HotXLS keeps reading its own pre-v2.384.54 XOR files by checking the FILEPASS key: when the stored key equals the key derived from the password, the reader builds the specification XorRor array, and when it differs, the reader treats the file as an older HotXLS file and rebuilds the rotate-left-2 array. Excel-written files always carry the derived key, so they always take the specification path

The test is a heuristic with a precise failure rate. An old file whose random key happened to equal the derived key would be read with the wrong array, and the chance of that is 1 in 65,536. The fallback covers the array rotation only; the index rule is not switched, so the files it rescues are the ones written between v2.384.47 and v2.384.53. If you still hold BIFF5 XOR files from that window, open them with the current HotXLS and save them again to get a file Excel accepts

Two password details apply to every file, old or new:

• Length. CreateXorKey_Method1 only reads the first 15 password bytes, which is the specification limit. HotXLS applies that cap to the key and keeps the verifier and array on their usual full-length and 16-byte rules, consistently on both sides. Excel itself refuses passwords longer than 15 characters for this format, so treat 15 as the real maximum
• Character set. HotXLS converts the password to bytes through the system ANSI code page. The specification describes taking the low byte of each UTF-16 character, which agrees for ASCII. Without Excel samples protected by non-ASCII passwords there is no ground truth for the rest, so stick to ASCII passwords for XOR files

On the reading side, TXLSWorkbook.OnPassword lets you prompt for a password when Open meets a FILEPASS record. The event is a TXLSPasswordEvent with a var PassWord: WideString and a var Retry: Boolean; set Retry to True to try again, up to three retries:

procedure TImportForm.WorkbookPassword(Sender: TObject;
  var PassWord: WideString; var Retry: Boolean);
var
  S: string;
begin
  S := '';
  Retry := InputQuery('Protected workbook', 'Password:', S);
  PassWord := S;
end;

procedure TImportForm.ImportLegacyFile(const FileName: string);
var
  Wb: IXLSWorkbook;
  Rc: Integer;
begin
  Wb := TXLSWorkbook.Create;
  Wb.OnPassword := WorkbookPassword;
  Rc := Wb.Open(FileName);
  // -1003: password required but none supplied; -1005: wrong password
  if Rc <> 1 then
    raise Exception.CreateFmt('Cannot open %s (code %d)', [FileName, Rc]);
  ShowMessage(VarToStr(Wb.Sheets[1].Range['A1', 'A1'].Value));
end;

If you already know the password, Open(FileName, APassWord) skips the event entirely

Is XOR obfuscation secure enough for anything?

BIFF XOR obfuscation is not encryption and protects nothing against a motivated reader. The password check is a 16-bit verifier, the key is 16 bits, and the 16-byte array repeats across the whole stream, so predictable BIFF record contents expose array bytes without any password at all. HotXLS writes XOR only because Excel 5.0/95 files have no other option, and the reason to produce such files today is a legacy consumer that cannot read anything newer

The classic engine selects the scheme through TXLSWorkbook.EncryptionType, and the combination with the save format is checked strictly:

• xletAuto (default) writes RC4 CryptoAPI for xlExcel97 and XOR for xlExcel5, matching what Excel itself wrote for each format
• xletXor is valid for BIFF5 only; with xlExcel97 the save raises an exception instead of silently falling back
• xletRC4 and xletRC4CryptoAPI are BIFF8 only, and asking for them on a BIFF5 save raises an exception as well

RC4 is dated too, and the details of making it interoperate are covered in why Excel rejects an encrypted workbook with a correct password. If the recipient can read XLSX, use the XLSX engine instead: TXLSXWorkbook.SaveAsEncryptedAgile writes Agile Encryption (SHA-512 password hashing with a 100,000-iteration spin count and AES-256-CBC), the format Excel 2010 and later write by default, while SaveAsEncrypted writes the older AES-128 Standard Encryption. The trade-offs between the two are in encrypting XLSX files with AES in Delphi, and the read side is covered in reading Agile-encrypted Excel files with HotXLS

Quick reference: BIFF XOR obfuscation that Excel 16 accepts

• FILEPASS ($002F) follows the globals BOF; in BIFF5 its body is 4 bytes: key, then verifier
• Key = CreateXorKey_Method1(password) per [MS-OFFCRYPTO] §2.3.7.2, never random; for secret it is $014D
• Array = password bytes + pad, XOR low key byte at even positions and high key byte at odd positions, then XorRor (rotate right 1)
• Encrypt a byte: rotate left 5, then XOR; decrypt: XOR, then rotate right 5 (§2.3.7.3)
• XorArrayIndex = (byte stream offset + record data length) mod 16; headers and plain prefixes count toward the offset
• BOUNDSHEET keeps its first 4 bytes plain; BOF, FILEPASS and INTERFACEHDR stay fully plain
• Passwords: ASCII, at most 15 characters
• HotXLS: index fixed in v2.384.47, key and XorRor fixed in v2.384.54, older HotXLS XOR files detected by key mismatch
• For real protection use BIFF8 RC4 CryptoAPI at minimum, or XLSX Agile Encryption

HotXLS handles BIFF5 and BIFF8 password protection, XLSX Standard and Agile Encryption, and the read-side password callback from one Delphi and C++Builder library, with the interop details above handled for you. See the HotXLS Delphi spreadsheet component for editions, platforms and a trial download