Java uses poi to set the allow edit ranges password for excel, and the generated excel prompts that-CodePudding

I am using java to set the Excel ‘allow edit ranges’ password, allow edit ranges

Below is my core code

    String pwd = "AB";
    byte[] decode = Hex.decodeHex(pwd.toCharArray());
    CTProtectedRange ctProtectedRange = sheet.getCTWorksheet().addNewProtectedRanges().addNewProtectedRange();
    ctProtectedRange.setName("protect");
    ctProtectedRange.setPassword(decode);
    System.out.println(ctProtectedRange); //get this string:<xml-fragment name="protect" password="AB"/>
    ctProtectedRange.setSqref(List.of("A1:D1"));
    sheet.protectSheet("1");

When I downloaded excel from the browser, I clicked on the protected cell and entered the password: ‘AB’, but I receive a prompt that the password is incorrect

Can i get any advice？

CodePudding user response：

The byte[]s used in CTProtectedRange.setPassword are bytes of an 16-bit password verifier as described in 2.3.7.1 Binary Document Password Verifier Derivation Method 1.

This can be created using CryptoFunctions.createXorVerifier1. Note, as ist is 16 bit only, this actually returns an int in short range only.

The byte[]s then can be created using java.nio.ByteBuffer.

Example:

...
  String pwd = "AB";
  short pwdHash = (short)org.apache.poi.poifs.crypt.CryptoFunctions.createXorVerifier1(pwd);
  byte[] pwdHashBytes = java.nio.ByteBuffer.allocate(2).putShort(pwdHash).array();
  ctProtectedRange.setPassword(pwdHashBytes);
...

This is an obfuscating method only and it is not very safe against brute force attacks as there are only 65,536 different password hashs. It was the only method using Excel 2007. Later Excel versions provide methods using hash algorithms using hash value and salt. Those are safer then.

Setting a password for protected range using hash algorithm SHA-512, hash value and salt could look like so:

...
  String pwd = "AB";
  org.apache.poi.poifs.crypt.HashAlgorithm hashAlgorithm = org.apache.poi.poifs.crypt.HashAlgorithm.sha512;
  byte[] saltValue = new byte[]{123, 64, 89, 23, -123, -34, 89, -3};
  int spinCount = 10000;
  byte[] hashValue = org.apache.poi.poifs.crypt.CryptoFunctions.hashPassword(pwd, hashAlgorithm, saltValue, spinCount, false);
  ctProtectedRange.setAlgorithmName(hashAlgorithm.jceId);
  ctProtectedRange.setSaltValue(saltValue);
  ctProtectedRange.setHashValue(hashValue);
  ctProtectedRange.setSpinCount(spinCount);
...