Use more secure locking algorithms

[JanMarvin]

Inspired by this SO I requested Gemini to reproduce this code snippet in R and integrated it into the XML code used by openxlsx2. This is safer in comparison to the currently used older encryption code used by openxlsx and openxlsx2.

It requires the packages digest and base64enc. Both are neat, because they have no dependencies. Still adding two additional packages for a dubious functionality seems rather odd.

# Helper function to convert integer to raw bytes (little endian)
# This is a simplified version; proper handling for larger integers might be needed
# but for loop index up to typical spin counts (e.g., 100k), 4 bytes is sufficient.
int_to_raw_le <- function(n, nbytes = 4) {
  if (n < 0) stop("Negative integers not supported")
  if (nbytes <= 0) stop("nbytes must be positive")
  
  raw_bytes <- raw(nbytes)
  for (i in 0:(nbytes - 1)) {
    byte_val <- bitwAnd(bitwShiftR(n, i * 8), 0xFF)
    raw_bytes[i + 1] <- as.raw(byte_val)
  }
  # R's raw vectors are typically printed MSB first, but the order in memory/bytes is what matters.
  # We constructed it little-endian (LSB first) in the loop.
  raw_bytes
}


docx_sha512_hash_r <- function(password, spin_count = 0) {
  # Password encoding to UTF-16LE
  # R's internal string representation is typically UTF-8 or native.
  # Encoding to UTF-16LE for hashing input requires careful handling.
  # We can't directly rely on R's encode strings for raw byte output needed here.
  # A common way is to treat it as a sequence of 16-bit integers.
  # This is an approximation and might need refinement based on exact OOXML specs
  # for UTF-16LE byte representation including BOM if any (usually none for hashing input).
  password_chars <- strsplit(password, "")[[1]]
  password_utf16le_raw <- raw()
  for (char in password_chars) {
    # Get the Unicode code point (assuming input is valid Unicode)
    code_point <- utf8ToInt(char) # Convert UTF-8 char to integer code point
    # Convert code point to 2 bytes little endian for UTF-16LE (assuming basic multilingual plane)
    if (code_point > 0xFFFF) {
      # This simple approach doesn't handle surrogate pairs for code points > 0xFFFF
      warning("Characters outside BMP not fully supported by this simple UTF-16LE encoding")
      # For full support, need to implement surrogate pair logic
    }
    byte1 <- as.raw(bitwAnd(code_point, 0xFF))
    byte2 <- as.raw(bitwShiftR(code_point, 8))
    password_utf16le_raw <- c(password_utf16le_raw, byte1, byte2)
  }
  # UTF-16LE does not typically include a BOM when used in contexts like hashing input
  
  # Generate Salt (16 random bytes)
  salt <- as.raw(sample(0:255, 16, replace = TRUE))
  
  # Combine Salt and Password Bytes
  combined_bytes <- c(salt, password_utf16le_raw)
  
  # Initial Hash (SHA-512)
  # digest package works on raw vectors
  hashed_password <- digest::digest(combined_bytes, algo = "sha512", serialize = FALSE, raw = TRUE)
  
  # Iteration (Spin Count)
  for (i in 0:(spin_count - 1)) {
    # Convert loop index to 4 bytes little endian
    index_bytes <- int_to_raw_le(i, 4)
    
    # Append index bytes to the current hash
    hashed_password_with_index <- c(hashed_password, index_bytes)
    
    # Hash the combined value (previous hash + index bytes)
    hashed_password <- digest::digest(hashed_password_with_index, algo = "sha512", serialize = FALSE, raw = TRUE)
  }
  
  # Base64 Encode Hash and Salt
  b64_hash <- base64enc::base64encode(hashed_password)
  b64_salt <- base64enc::base64encode(salt)
  
  # Return as a list or vector
  list(hash = b64_hash, salt = b64_salt)
}

# Example usage:
result <- docx_sha512_hash_r("openxlsx2", spin_count = 100000)

fs_xml <- sprintf(
  '<fileSharing userName="Test" algorithmName="SHA-512" spinCount="100000" hashValue="%s" saltValue="%s"/>',
  result$hash, result$salt
)

ws_xml <- sprintf(
  '<sheetProtection sheet="1" edit="readOnly" enforcement="1" algorithmName="SHA-512" spinCount="100000" hashValue="%s" saltValue="%s"/>',
  result$hash, result$salt
)

library(openxlsx2)
wb <- wb_workbook()$add_worksheet("protected")$add_worksheet("unprotected")
wb$workbook$fileSharing <- fs_xml
wb$worksheets[[1]]$sheetProtection <- ws_xml
if (interactive()) wb$open()

[JanMarvin]

The same with openssl:

# Helper function to convert integer to raw bytes (little endian)
# This is a simplified version; proper handling for larger integers might be needed
# but for loop index up to typical spin counts (e.g., 100k), 4 bytes is sufficient.
int_to_raw_le <- function(n, nbytes = 4) {
  if (n < 0) stop("Negative integers not supported")
  if (nbytes <= 0) stop("nbytes must be positive")
  
  raw_bytes <- raw(nbytes)
  for (i in 0:(nbytes - 1)) {
    byte_val <- bitwAnd(bitwShiftR(n, i * 8), 0xFF)
    raw_bytes[i + 1] <- as.raw(byte_val)
  }
  # R's raw vectors are typically printed MSB first, but the order in memory/bytes is what matters.
  # We constructed it little-endian (LSB first) in the loop.
  raw_bytes
}


docx_sha512_hash_r <- function(password, spin_count = 0) {
  # Password encoding to UTF-16LE
  # R's internal string representation is typically UTF-8 or native.
  # Encoding to UTF-16LE for hashing input requires careful handling.
  # We can't directly rely on R's encode strings for raw byte output needed here.
  # A common way is to treat it as a sequence of 16-bit integers.
  # This is an approximation and might need refinement based on exact OOXML specs
  # for UTF-16LE byte representation including BOM if any (usually none for hashing input).
  password_chars <- strsplit(password, "")[[1]]
  password_utf16le_raw <- raw()
  for (char in password_chars) {
    # Get the Unicode code point (assuming input is valid Unicode)
    code_point <- utf8ToInt(char) # Convert UTF-8 char to integer code point
    # Convert code point to 2 bytes little endian for UTF-16LE (assuming basic multilingual plane)
    if (code_point > 0xFFFF) {
      # This simple approach doesn't handle surrogate pairs for code points > 0xFFFF
      warning("Characters outside BMP not fully supported by this simple UTF-16LE encoding")
      # For full support, need to implement surrogate pair logic
    }
    byte1 <- as.raw(bitwAnd(code_point, 0xFF))
    byte2 <- as.raw(bitwShiftR(code_point, 8))
    password_utf16le_raw <- c(password_utf16le_raw, byte1, byte2)
  }
  # UTF-16LE does not typically include a BOM when used in contexts like hashing input
  
  # Generate Salt (16 random bytes)
  salt <- as.raw(sample(0:255, 16, replace = TRUE))
  
  # Combine Salt and Password Bytes
  combined_bytes <- c(salt, password_utf16le_raw)
  
  # Initial Hash (SHA-512)
  # digest package works on raw vectors
  hashed_password <- openssl::sha512(combined_bytes)
  
  # Iteration (Spin Count)
  for (i in 0:(spin_count - 1)) {
    # Convert loop index to 4 bytes little endian
    index_bytes <- int_to_raw_le(i, 4)
    
    # Append index bytes to the current hash
    hashed_password_with_index <- c(hashed_password, index_bytes)
    
    # Hash the combined value (previous hash + index bytes)
    hashed_password <- openssl::sha512(hashed_password_with_index)
  }
  
  # Base64 Encode Hash and Salt
  b64_hash <- openssl::base64_encode(hashed_password)
  b64_salt <- openssl::base64_encode(salt)
  
  # Return as a list or vector
  list(hash = b64_hash, salt = b64_salt)
}

# Example usage:
result <- docx_sha512_hash_r("openxlsx2", spin_count = 100000)

fs_xml <- sprintf(
  '<fileSharing userName="Test" algorithmName="SHA-512" spinCount="100000" hashValue="%s" saltValue="%s"/>',
  result$hash, result$salt
)

ws_xml <- sprintf(
  '<sheetProtection sheet="1" edit="readOnly" enforcement="1" algorithmName="SHA-512" spinCount="100000" hashValue="%s" saltValue="%s"/>',
  result$hash, result$salt
)

library(openxlsx2)
wb <- wb_workbook()$add_worksheet("protected")$add_worksheet("unprotected")
wb$workbook$fileSharing <- fs_xml
wb$worksheets[[1]]$sheetProtection <- ws_xml
if (interactive()) wb$open()

[mschilli87]

IMHO, implementing crypto-code from scratch using basic functions like sample (which is not cryptographically safe) and hash functions that even Wikipedia lists as vulnerable to specific attacks (SHA-256) is a good idea, especially not based on AI-generated code.
Personally, I prefer no crypto over bad crypto as at least then as a user I can't have a false sense of security and simply won't use the tool for sensitive data.
I haven't looked into it specifically, but I am sure there are packages available that implement this functionality in a better way, written by programmers with a focus on crypto. I know for certain that there are packages for cryptographically safe RNG. Regarding password handling, I'd look into packages used by Shiny Apps for user management und follow their dependencies.

[JanMarvin]

Thanks for your input, but I was just toying around with a better alternative to the currently implemented password encryption method which is known to be rather weak. Other than that I have no interest in dripping my feet into cryptography.

Still the entire task here is to store a hash and a password as xml string with the file. The only security question for the task at hand is if the password can easily be cracked. Modifications to the file can be applied with e.g. openxlsx2 irrespective of the password and if one wants to get rid of it, it’s possible to simply remove it from the XML and zip the file again. This also does not prevent anyone from reading the file. It only applies to (un)locking the file with spreadsheet software that cares about this type of protection (first and foremost Excel).

File encryption is implemented with msoc and I’m sure you don’t like this either :)

[mschilli87]

I'm totdally fine with that. I would just rather put a big warning on this 'security' 'feature' instead of doing work towards looking more secure while that's not even a design goal. Just keep in mind that those half-hearted attempts at 'security' is exactly what LLM have been trained on which is why I do not trust them at all when security is a real concern. So if anything, I'd ask you to include a big warning to the users explaining why there is no proper data security in spreadsheets and another big warning as a comment targeting developers that search the internet for 'cryto' code in R to make sure they don't copy/paste this as a 'good and trusted' solution. 😉