Handling encrypted spreadsheets?

[revcom]

Hi there,

I'm assuming you can't use this library to read encrypted sheets with a password.
Is this feature easy to add?

Thanks...

Robert

[MaxDesiatov]

Hi @revcom, the library currently doesn't support it, but thanks for the feature request, it definitely puts it higher on the roadmap 👍 I need to investigate how it would work and I'll post any updates here when any are available

[MaxDesiatov]

The most comprehensive description of how it works I found so far is this one: https://blog.didierstevens.com/2018/06/07/encrypted-ooxml-documents/

Please feel free to post if you find more information about the internals

[revcom]

Thanks Max Appreciate your prompt reply. Look forward to updates when and if you get to add this feature to your excellent library Keep up the good work... Robert

[revcom]

Hi Max My app is now finished using an unencrypted spreadsheet for now. I had no trouble with your library - it was excellent. Any rough idea when you might get around to looking at the encryption support? I looked at the docs you sent and I think its a bit beyond my capabilities. Just wanted to know if you think you could get to it this year? Next year? Later than that? I won’t hold you to any dates, just need to plan alternatives if necessary. By the way my app was my first attempt at using SwiftUI and I must say I am impressed. As far as it goes its very smooth and a joy to use Look forward to hearing from you Robert

…

On 6 Apr 2020, at 8:48 pm, Max Desiatov ***@***.***> wrote: The most comprehensive description of how it works I found so far is this one: https://blog.didierstevens.com/2018/06/07/encrypted-ooxml-documents/ <https://blog.didierstevens.com/2018/06/07/encrypted-ooxml-documents/> Please feel free to post if you find more information about the internals — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#101 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAM5XUKE3DF7AZMVFT4SBSDRLGXQNANCNFSM4MCBRN2Q>.

[MaxDesiatov]

Hey @revcom, I'm glad that it worked for you, I'll have another look at the encryption support and I'll see what I can do, will keep you updated.

[MaxDesiatov]

@revcom do you have a sample file you could attach here or send to hello@corexlsx.org by any chance? It doesn't have to contain any real data, just empty worksheets would work. I just need to understand which of the encryption types you're likely to work with, and I also would hope it can be attached to the test suite.

[revcom]

Hi Max

Sample spreadsheets sent to you privately. Thanks again for your great support

Robert

[MaxDesiatov]

Thanks, I'll keep the issue open and will close it after the feature is implemented and merged, if you don't mind.

[MaxDesiatov]

Due to the amount of new dependencies the encryption feature requires, this was implemented in a separate library. Could you have a look at CryptoOffice and let me know how that works for you?

[revcom]

Wow Max, you’re a quick worker 😀 I was just yesterday thinking of checking in with you to see if you were still ok and didn’t quite know how to do it without looking like I was hassling you to get the encryption stuff done. I’ll pull it down now and see if it works. Thanks again for your excellent work. I’m sure it will perform superbly. Stay safe...

…

On 31 May 2020, at 10:53 pm, Max Desiatov ***@***.***> wrote: Assigned #101 <#101> to @revcom <https://github.com/revcom>. — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#101 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAM5XUOVAQ2GZIZ5YKHTXZTRUJHONANCNFSM4MCBRN2Q>.

[revcom]

Hi Max Having a bit of trouble importing CryptoOffice - it says "/Users/macmini/Documents/FindPassword/FindPassword.xcodeproj Missing package product ‘XMLCoder’" Do I have to download that separately? Thanks…(got to get ready for shopping now)

…

On 31 May 2020, at 10:53 pm, Max Desiatov ***@***.***> wrote: Assigned #101 <#101> to @revcom <https://github.com/revcom>. — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub <#101 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAM5XUOVAQ2GZIZ5YKHTXZTRUJHONANCNFSM4MCBRN2Q>.

[MaxDesiatov]

Thanks for having a look at this so quickly. You don't have to download XMLCoder separately. If you add CryptoOffice as any other package depedency in Xcode to your application's target, it should be pulled automatically. I know that error could appear due to Xcode bugs, maybe restarting Xcode and rebuilding could help?

[revcom]

Hi Max Restarted everything (Mac, Xcode etc) but still no joy I don’t see the XML library anywhere in the dependencies listed in Xcode No hurry - I’ll work on it whilst you sleep Robert

[MaxDesiatov]

Hey Robert, is the package added via the Xcode SwiftPM integration as described here? If so, is this issue reproducible just for your single app or any other apps too? I'm not able to reproduce the issue when I create a new app in Xcode and add both CryptoOffice and CoreXLSX to the dependencies list as shown on that Apple's documentation page.

[revcom]

Hi Max, I got it building correctly today (whilst you were sleeping) but I DID have to add XMLCoder specifically to get it going. I used Swift package Manager for everything (CoreXLSX, CryptoOffice AND XMLCoder). I did have two targets in my project (OSX and iOS) so that may have confused me as maybe I added CryptoOffice to the wrong project initially. But its all good now - tomorrow I’ll temporarily remove XMLCoder and see if it still works without it specifically. Thanks for trying to reproduce the problem. I must add that your new library is decrypting my spreadsheet perfectly now. So you’ve done a great job with it. However, I had some issues to start with and CoreSLSX was throwing the NotAnArchive exception. CryptoOffice was working perfectly by the way. So I spent the whole day pretty much trying to narrow down the bug for you. It was strange because CoreXLSX has been working with the same (unencrypted) spreadsheet for months and it seemed strange that it should start to fail when adding a de-encryption step to begin with. So I was trying to simplify my spreadsheet to just a few rows to zero in on the problem. But I couldn’t seem to get a conclusive answer as to which portion of it was failing. Finally in desperation, I copied the entire spreadsheet into another tab and deleted the initial tab. Been working perfectly ever since. Go figure. I’ll blame Microsoft. Thanks again for: 1. Making the changes so quickly for me, AND 2. All your help in getting it working Its very much appreciated Robert

…

On 1 Jun 2020, at 6:09 pm, Max Desiatov ***@***.***> wrote: Hey Robert, is the package added via the Xcode SwiftPM integration as described here <https://developer.apple.com/documentation/xcode/adding_package_dependencies_to_your_app>? If so, is this issue reproducible just for your single app or any other apps too? I'm not able to reproduce the issue when I create a new app in Xcode and add both CryptoOffice and CoreXLSX to the dependencies list as shown on that Apple's documentation page. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#101 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAM5XUMHQRBTMTZF43LZIJTRUNOZ5ANCNFSM4MCBRN2Q>.

[MaxDesiatov]

@revcom awesome, glad to hear that the issue is fixed!

In the last 0.11 version of CoreXLSX, which you already use, as it's required to make CryptoOffice working, there's a new parseWorksheetPathsAndNames function. You no longer need to call parseWorkbooks separately, here's an example::

let filteredName = "Passwords"
guard 
  let (name, worksheetPath) = try file.parseWorksheetPathsAndNames()
    .first(where: { $0.name == filteredName })
else { fatalError("sheet with name \(filteredName) not found") }

let worksheet = try file.parseWorksheet(at: worksheetPath)

[revcom]

Thanks Max Will implement that in the morning. Thanks again

…

On 1 Jun 2020, at 8:42 pm, Max Desiatov ***@***.***> wrote: @revcom awesome, glad to hear that the issue is fixed! In the last 0.11 version of CoreXLSX, which you already use, as it's required to make CryptoOffice working, there's a new parseWorksheetPathsAndNames function. You no longer need to call parseWorkbooks separately, here's an example:: let filteredName = "Passwords" guard let (name, worksheetPath) = try file.parseWorksheetPathsAndNames() .first(where: { $0.name == filteredName }) else { fatalError("sheet with name \(filteredName) not found") } let worksheet = try file.parseWorksheet(at: worksheetPath) — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[MaxDesiatov]

I don't think there's a more concise way, there are already a few implicit loops in my version with a few flatMaps and maps around:

  /// Parse and return an array of worksheets in this XLSX file with their corresponding names.
  public func parseWorksheetPathsAndNames(
    workbook: Workbook
  ) throws -> [(name: String?, path: String)] {
    return try parseDocumentPaths().map {
      try parseDocumentRelationships(path: $0)
    }.flatMap { (path, relationships) -> [(name: String?, path: String)] in
      let worksheets = relationships.items.filter { $0.type == .worksheet }

      guard !path.isRoot else { return worksheets.map { (name: nil, path: $0.target) } }

      // .rels file has paths relative to its directory,
      // storing that path in `pathPrefix`
      let pathPrefix = path.components.dropLast().joined(separator: "/")

      let sheetIDs = Dictionary(uniqueKeysWithValues: workbook.sheets.items.compactMap { sheet in
        sheet.name.flatMap { (sheet.relationship, $0) }
      })

      return worksheets.map { (name: sheetIDs[$0.id], path: "\(pathPrefix)/\($0.target)") }
    }
  }

And it depends on whether you'd like to optimise for conciseness, readability, correctness or efficiency. My version is certainly not very efficient if you know there's only a single woksheet with such name. But this is probably not always the case with all different varieties of .xlsx files 🙂

[revcom]

Thanks Max Last night before going to bed, I realised that I should have looked at your implementation of parseWorksheetPathsAndNames to see how you did it before sending my last email. Sorry about that. One small point - is there any way of throwing an “Invalid Password” exception if the decryption fails? I looked at the code and it seems like thee is no indication back to you when decryption fails. If this is the case, is there some piece of information contained in every spreadsheet you could check for first to see if its decrypted correctly and throw an exception if not? Like a Microsoft header or copyright or something? Just a thought as the ataIsNotAnArchive exception could mean lots of different things, not just that the password is invalid. Thanks again… Robert

…

On 2 Jun 2020, at 3:06 am, Max Desiatov ***@***.***> wrote: I don't think there's a more concise way, there are already a few implicit loops in my version with a few flatMaps and maps around: /// Parse and return an array of worksheets in this XLSX file with their corresponding names. public func parseWorksheetPathsAndNames( workbook: Workbook ) throws -> [(name: String?, path: String)] { return try parseDocumentPaths().map { try parseDocumentRelationships(path: $0) }.flatMap { (path, relationships) -> [(name: String?, path: String)] in let worksheets = relationships.items.filter { $0.type == .worksheet } guard !path.isRoot else { return worksheets.map { (name: nil, path: $0.target) } } // .rels file has paths relative to its directory, // storing that path in `pathPrefix` let pathPrefix = path.components.dropLast().joined(separator: "/") let sheetIDs = Dictionary(uniqueKeysWithValues: workbook.sheets.items.compactMap { sheet in sheet.name.flatMap { (sheet.relationship, $0) } }) return worksheets.map { (name: sheetIDs[$0.id], path: "\(pathPrefix)/\($0.target)") } } } And it depends on whether you'd like to optimise for conciseness, readability, correctness or efficiency. My version is certainly not very efficient if you know there's only a single woksheet with such name. But this is probably not always the case with all different varieties of .xlsx files 🙂 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#101 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAM5XUIEYSD3SN5CI76OI2TRUPN27ANCNFSM4MCBRN2Q>.

[MaxDesiatov]

CoreXLSXError.dataIsNotAnArchive is probably the best indicator unfortunately. It doesn't look like the cryptography library raises an error when the password is incorrect, it just tries to decrypt with a wrong password, which leads to a garbage output. Maybe there's some other way, but at least currently it doesn't look like the CryptoSwift depedency that CryptoOffice uses can give a direct indication that a password input is incorrect.

[revcom]

Hi again Sorry to disturb you again but I managed to find an indication if the decryption has worked or not. This is what I implemented in my code, not sure if its best in my code or perhaps you might consider incorporating something like it into CryptoOffice if its not too specific or likely to fail with a future version of MS Office. let encryptedFile = try CryptoOfficeFile(path: path) let decryptedData = try encryptedFile.decrypt(password: password) let str = String(decoding: decryptedData, as: UTF8.self) if !str.contains("[Content_Types].xml") { print ("Bad password") } else { let file = try XLSXFile(data: decryptedData) print ("File decrypted") What do you think? Robert

…

On 2 Jun 2020, at 3:06 am, Max Desiatov ***@***.***> wrote: I don't think there's a more concise way, there are already a few implicit loops in my version with a few flatMaps and maps around: /// Parse and return an array of worksheets in this XLSX file with their corresponding names. public func parseWorksheetPathsAndNames( workbook: Workbook ) throws -> [(name: String?, path: String)] { return try parseDocumentPaths().map { try parseDocumentRelationships(path: $0) }.flatMap { (path, relationships) -> [(name: String?, path: String)] in let worksheets = relationships.items.filter { $0.type == .worksheet } guard !path.isRoot else { return worksheets.map { (name: nil, path: $0.target) } } // .rels file has paths relative to its directory, // storing that path in `pathPrefix` let pathPrefix = path.components.dropLast().joined(separator: "/") let sheetIDs = Dictionary(uniqueKeysWithValues: workbook.sheets.items.compactMap { sheet in sheet.name.flatMap { (sheet.relationship, $0) } }) return worksheets.map { (name: sheetIDs[$0.id], path: "\(pathPrefix)/\($0.target)") } } } And it depends on whether you'd like to optimise for conciseness, readability, correctness or efficiency. My version is certainly not very efficient if you know there's only a single woksheet with such name. But this is probably not always the case with all different varieties of .xlsx files 🙂 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#101 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAM5XUIEYSD3SN5CI76OI2TRUPN27ANCNFSM4MCBRN2Q>.

[MaxDesiatov]

Thanks for sharing, that's certainly another possible option, I think it would be equivalent to CoreXLSXError.dataIsNotAnArchive in behaviour. Although catching that error probably would be more efficient, since str.contains would traverse the whole decrypted data blob in search of a substring, while .dataIsNotAnArchive error is thrown when ZIPFoundation fails to open the zip archive, which is what decrypted .xlsx essentialy is. And ZIPFoundation already does some tricks to efficiently check whether a given data blob is a valid zip archive.

[revcom]

Hi Max

Code has been working well but there seems to be a bug when adding additional rows with Excel. After a number of additions the library suddenly throws ArchiveEntryNotFound and refuses to allow access to the encoded spreadsheet data.
Excel has no problem opening and editing the same spreadsheet.

I've send you a minimal app and spreadsheet that exhibits the problem privately.

Thanks again for your help...

Robert

[revcom]

Thanks Max, the latest version seems to fix my ArchiveEntryNotFound problem. Appreciate your help (again)

Robert