Very early support for .xlsx protection [PATCH] lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- shobits1 · 2017-03-03 23:07 I was trying to keep the Workbook, Worksheet and Cell protection information from the read .xlsx file to the saved one. I'm happy with the result so far,, the saved file retains all the information from the original one ( except for the share file protection/revision). also you protect unprotected files but no password support for now. Anyone interested the patch is attached. BTW; wp, if I want to add password support what hash library you prefer? ---------------------------------------------------------------------- wp · 2017-03-04 00:00 Wow, that's a surprise. Thank you. I'll have to look into the patch in more detail. For the moment, just a few remarks: Maybe one thing to consider: FPSpreadsheet should not be an Excel-only library. Could you find out how LibreOffice handles protection? Even if you would not want to write a reader/writer for protection in ods files the data types must be general enough to be compatible with both cases. But I think it will be similar. How about removing the sheet protection enumerations of unsupported items from TsWorksheetProtection? We don't need spAutoFilter, spPivotTables or spScenario because being unsupported they will be dropped in reading anyway. They can be added later if these features should be available in some time. In InitFormatRecord you initialize the ProtectionMode with [prLockCell]. Doesn't this mean that cells are locked by default? Or does it mean that cells are locked by default if the sheet is protected - that weird way of thinking which always confused me... Does Calc of LibreOffice follow the same strategy? As for password hashing, most preferably I would not want to add a dependence on another package or an external dll. If absolutely necessary, the password stuff must be IFDEF'ed in order to be able to make fpspreadsheet standalone again. Maybe you should have a look at the fpc folder packages/hash/src - if you find anything suitable here this would be the way to go. ---------------------------------------------------------------------- shobits1 · 2017-03-04 01:25 Thank you for the quick reply . To tell the truth, I don't have deep experience with FPSpreadsheet nor with xlsx/ods file format.. I just implemented this when I saw a need for the modified file to be kept protected from user. Anyway, I was reading ECMA-376, Fifth Edition, Part 1 - Fundamentals And Markup Language Reference.zip.. which .xlsx files are based on and that's why I add all the sheet protection enumerations (you may remove any unsupported enumeration,, I would've done that if I have known FPSpreadsheet very well). the InitFormatRecord initialize the ProtectionMode with [prLockCell] because it's the default with Excel, each cell has Locked checked.. and the correct behavior (according to Excel) are non protection are enforced until the sheet is protected (once again I don't know how LibreOffice be haves). Quote Locking cells or hiding formulas has no effect until you protect the worksheet ... from Excel As for hashing algorithm, the format says you can use MD2, MD4, MD5, RIPEMD-128, RIPEMD-160, SHA-1, SHA-256, SHA-384, SHA-512, WHIRLPOOL,, but I found that Excel 2010 generate files that uses none of those (maybe it is using legacy algorithm) ---------------------------------------------------------------------- marcov · 2017-03-04 04:25 16-bits sha1 according to http://www.spreadsheet1.com/sheet- protection.html ---------------------------------------------------------------------- wp · 2017-03-04 21:53 Please test r5783 in which I applied your patch with some modifications: I renamed some identifiersSince Excel has that strange system of some protection options being on and others being off by default it is not possible to have the worksheet protection empty for an unprotected sheet. Hence I added another option, woProtected, which must be included in the workbook Options to enable protection. Simply call worksheet.Protect(true) to do this (the parameter false removes the option again and unprotects the sheet).I wrote some unit tests to check integrity after writing and reading. All these tests are passed. In this context I realized that some access methods should be helpful. Therefore, the worksheet now has methods WriteCellProtection and ReadCellProtection.I did not test the crypto parameters. My Excel 2007 does not show them, possibly they came in later. Could you test this please?Also see the wiki (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Protection) for a first version of the documentation. Something strange: When I define a password for the workbook protection in Excel the file cannot be opened by fpspreadsheet any more, and the zip file signature (PK) is gone. Therefore, I assume that this password encrypts the file. But what is the difference to the "Encrypt document" command in the menu? ---------------------------------------------------------------------- shobits1 · 2017-03-05 00:46 I'll try and do some tests tomorrow, but I have some remarks: Quote from: wp on March 04, 2017, 10:53:44 pm...it is not possible to have the worksheet protection empty for an unprotected sheet... Code: XML  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i The attribute sheet under sheetProtection is the one controlling if the worksheet is protected or not. if sheet="0",, it will ignore all other protection. Quote from: wp on March 04, 2017, 10:53:44 pmI did not test the crypto parameters. My Excel 2007 does not show them, possibly they came in laterI think Excel 2010 and 2007, and older format uses the same password hashing algorithm,, Excel 2013 and later uses the more secure hashes. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TsCryptoInfo = record  Password: string; // this will hold the password hash for older Excel version (2010 and Earlier)   // This is for the later versions  AlgorithmName: string;  HashValue: string;  SaltValue: string;  SpinCount: Integer;end I intentionally add the password field so to make distinguishing between old and new hashing bit easier; maybe removing it and assigning AlgorithmName:=EXCELOLDHASH will make more sense Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // This is the code for generating Excel 2010 and earlier password's hashfunction Hash_Password( const APassword: string ): string;const  Key = $CE4B;var  i : Integer;  HashValue: Word = 0;begin  for i := Length( APassword ) downto 1 do  begin    HashValue := ord(APassword[i]) xor HashValue;    HashValue := HashValue shl 1;  end;  HashValue := HashValue xor Length( APassword ) xor Key;   Result := IntToHex(HashValue, 4);end;  Quote from: wp on March 04, 2017, 10:53:44 pmSomething strange: When I define a password for the workbook protection in Excel the file cannot be opened by fpspreadsheet any more, and the zip file signature (PK) is gone. Therefore, I assume that this password encrypts the file. But what is the difference to the "Encrypt document" command in the menu? Never happened to me under Excel 2010 (I assume it's the same with 2007),, only when Encrypting the document the files loses its signature; I usually protect the workbook from the Review tab. ---------------------------------------------------------------------- shobits1 · 2017-03-05 10:23 OK,, I did some testing and I think everything works as it should be. one things though, according to the documentation : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i cellprot := worksheet.ReadCellProtection(cell); this should work but there is no ReadCellProtection, although the ReadProtection is there (maybe it needs renaming). ---------------------------------------------------------------------- wp · 2017-03-05 11:20 Quote from: shobits1 on March 05, 2017, 01:46:00 amI'll try and do some tests tomorrow, but I have some remarks: Quote from: wp on March 04, 2017, 10:53:44 pm...it is not possible to have the worksheet protection empty for an unprotected sheet... Code: XML  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i The attribute sheet under sheetProtection is the one controlling if the worksheet is protected or not. if sheet="0",, it will ignore all other protection.I think this is a very Excel-specific way to do it. I think that the elements of the sheet Protection set should behave like those of the cell protection: they need another "event" to become active - in case of the cell protection the sheet protection must be activated before the cell protection items become active. And in case of the sheet protection? The event to activate this in Excel's implementation is an element of the set itself. This concept is very confusing, and it took me a long time to understand what spSheet really means, and maybe some of the words in my previous post are wrong for this reason. In my implementation sheet protection is activated by adding soProtected to the all-purpose Options of the worksheet. I left spSheet in the set, but renamed it to spCells because I think there might be concepts in which all cells can be allowed for editing but anything else is frozen. (Probably "spEdit" would be a better word?). Next, I'll look at how LibreOffice handles security. If they do it like Excel, I'll probably remove spCells altogether. Quote from: shobits1 on March 05, 2017, 01:46:00 amI intentionally add the password field so to make distinguishing between old and new hashing bit easier; maybe removing it and assigning AlgorithmName:=EXCELOLDHASH will make more sense [...] This is the code for generating Excel 2010 and earlier password's hash Thanks for posting the Excel hashing algorithm. Since also the old Excel versions have the encrypted password in the file I would prefer to write it to the field "Hashvalue" of the TsCryptoInfo, yes, with EXCELHASH (or similar) in the AlgorithmName field. Maybe a better word for "HashValue" would be "PasswordHash" Quote from: shobits1 on March 05, 2017, 01:46:00 amNever happened to me under Excel 2010 (I assume it's the same with 2007),, only when Encrypting the document the files loses its signature; I usually protect the workbook from the Review tab. My Excel experience with protection and encryption is very limited. Here's what I do: I open Excel 2007, it comes up with an empty worksheet. I go to "Review", select "Protect workbook" and enter a password ("test") in the edit box (leave the two checkboxes alone). I confirm the password in the next dialog, and then I save as xlsx. I open the file in a hex editor. The first two bytes in the file are $D0 $CF which is not the signature of a zip file which would be $50 $4B ('PK'). Therefore, fpspreadsheet cannot read the file. Even if we had an implementation of decryption it would not be possible to read the workbook cryptoinfo field for the algorithm to be applied for decryption. Really bad implementation by MS unless they assume that the file is always encrypted using their own algorithm... Is there an Excel setting somewhere which automaticlly causes file encryption when workbook protection w/ password is activated? ---------------------------------------------------------------------- shobits1 · 2017-03-05 17:18 I end-up downloading office/excel 2007; unfortunately, indeed it encrypts the file once the workbook protection is activated unlike Excel 2010.. I'll try searching and see if I can find a way to decrypt the file (no promises though). ---------------------------------------------------------------------- shobits1 · 2017-03-05 21:30 While I'm searching, I found that the algorithm for calculating the password's hash (I previously posted), doesn't work well and can't handle special characters and characters from other languages (ex. the calculated hash of ok1YES3!@ is not the same as excel).. the following one should return the correct hash value (tested with Excel 2010/2007 for protected worksheet) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function ExcelPasswordHash( const APassword: string ): string;var  i : Integer;  PassLen : Integer;  Password: string;  PassHash: Word = 0;begin  // we are needed to work with single byte character.  Password:= UTF8ToWinCP( APassword );  PassLen := Length(Password);   if PassLen = 0 then  begin    Result := '';    exit;  end;   for i:= PassLen downto 1 do  begin    PassHash:= ((PassHash shr 14) and  $0001) or ((PassHash shl  1) and  $7fff);    PassHash:= PassHash xor ord(Password[i]);  end;   PassHash:= ((PassHash shr 14) and  $0001) or ((PassHash shl  1) and  $7fff);  PassHash:= PassHash xor PassLen xor $CE4B;   Result := IntToHex(PassHash, 4);end;  sorry for any inconvenient. ---------------------------------------------------------------------- wp · 2017-03-05 23:33 Thank you, no problem. Although we don't have a usage for this function at the moment I put it into the new unit fpscrypto which will be supposed to contain all encryption/decryption related stuff. Today I wrote readers for the old xls formats (biff2, biff5, biff8) and the ods format. All follow the same philosophy as the xlsx reader, but at a much coarser level of control. One exception, maybe, is that xls has additional records for file write protection and file read/write protection, each of of them with their own passwords, this means that the workbook is protected by three passwords here. While the read/write protection is something like file encryption I still don't know what's the difference between write protection and the combination of cell/sheet/book protection. In ods I learned what the passwords for sheet and workbook protection are good for: they do not cause any encryption of any file content, they are just requested when the user wants to change the related protection settings. Is this the same with Excel2010+? ---------------------------------------------------------------------- shobits1 · 2017-03-06 07:13 Quote from: wp on March 06, 2017, 12:33:02 amIn ods I learned what the passwords for sheet and workbook protection are good for: they do not cause any encryption of any file content, they are just requested when the user wants to change the related protection settings. Is this the same with Excel2010+? yes it is the same at least for Excel 2010 (I don't have excel 2013/2016 atm), the attached file created with excel 2010 have the workbook and worksheet protected. ---------------------------------------------------------------------- shobits1 · 2017-03-11 23:59 I finally decrypted the office 2007 protected workbook successfully. for those whose intersted the attached file is all you need; Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses   ....., xlsxdecrypter;  procedure TForm1.Button3Click(Sender: TObject);const  EncryptedFile = 'PATH TO ENCRYPTED FILE';  DecryptedFile = 'PATH TO DECRYPT THE FILE'; var ExcelDecrypt :TExcelFileDecryptor; DecryptedStream : TFileStream; begin  ExcelDecrypt := TExcelFileDecryptor.Create;   if ExcelDecrypt.isEncryptedAndSupported(EncryptedFile) then  begin    DecryptedStream := TFileStream.Create(DecryptedFile, fmCreate + fmOpenReadWrite);    ExcelDecrypt.Decrypt(EncryptedFile, DecryptedStream);    DecryptedStream.free;  end;   ExcelDecrypt.Free;end;  if you want to make fpspreadsheet opens the file automatically just apply the patch then copy the `xlsxdecrypter.pas` to `[fpspreadsheet path]\source\common` ---------------------------------------------------------------------- wp · 2017-03-12 10:24 Very interesting. Thank you. I have a problem, though. I suppose that, regarding encryption/decryption, you already have looked through what comes with fpc and Lazarus. There is sha1 support (in fpc/source/packages/hash), but I did not find anything for aes. Therefore, I think that usage on DCPCrypt is unavoidable. On the other hand, since encryption/decryption will not be needed in most cases I want to keep fpspreadsheet independent of this package. So, let me think about how this useful unit could be added without adding a dependence on DCPCrypt. Maybe by adding another package laz_fpspreadsheet_crypto? Anyway, do you know if your decrypter is applicable also to the standard file protection? How would a password be applied to your class? ---------------------------------------------------------------------- shobits1 · 2017-03-12 13:14 Quote from: wp on March 12, 2017, 11:24:08 amSo, let me think about how this useful unit could be added without adding a dependence on DCPCrypt. Maybe by adding another package laz_fpspreadsheet_crypto? or may be adding a AES support to `fpscrypto.pas` Quote from: wp on March 12, 2017, 11:24:08 amAnyway, do you know if your decrypter is applicable also to the standard file protection? How would a password be applied to your class? 1.I don't know if it'll work with other encrypted files created with excel 2007 and earlier but I do know it won't work with excel 2010 encrypted files since it uses agile encryption (the EncryptionInfo is a sort of xml file). 2.there are: Decrypt(inFileName: string; outStream: TStream; APassword: UnicodeString):string; Decrypt(inStream: TStream; outStream: TStream; APassword: UnicodeString) :string; I made them private since I didn't have the time to test files encrypted with office 2007 and other than default password... but theoretically it should work. [EDIT] I just tried an encrypted excel 2007 with random password.. it worked fine. ---------------------------------------------------------------------- wp · 2017-03-14 22:17 r5806 contains the xlsx decryption. I added a new package laz_fpspreadsheet_crypto which depends on DCPCrypt as the main encryption/decryption engine. It will contain the dedicated units for the fpspreadsheet file formats, as wel as specialized reader/writer units which will have to do the decryption work and pass the stream to the standard reader; the same shouild happen with the writer (not available yet). The new reader/writer is registered as being user- defined, it can be selected by its ID - sifidOOXML instead of sfOOXML. All the file/stream reading routines of fpspreadsheet needing the format id were extended to accept an optional password. The readers in the basic package laz_fpspreadsheet ignore the password, but those in laz_fpspreadsheet_crypto have to use it to do their work. In the directory examples/read_write there is a new folder ooxmldemo_crypto which contains a simple console program to read an encrypted and a protected workbook written by Excel2007 (the protected workbook is also encrypted by means of an Excel-internal password). Both files are provided, their password is "test". The basic procedure to load an encrypted xlsx file is as follows: Add package laz_fpspreadsheet_crypto as a requirement to the programAdd unit xlsxooxml_crypto to the uses clause, instead of xlsxooxml.In the workbook's call to ReadFromFile or ReadFromStream use the format ID sfidOOXML (instead of sfOOXML).In case of the file- encrypted workbook, specify the password in ReadFromStream after the format ID. No password is needed to decrypt the protected workbook. The decrypting reader accepts also normal, non-encrypted files.As shobits1 wrote above, it must be expected that the decryption only works for files written by Excel 2007 and 2010.