Encrypted file does not work with Office365

gggeek commented 1 month ago

PHP Version

PHP 8.1.2-1ubuntu2.18 (cli) (built: Jun 14 2024 15:52:55) (NTS)

Composer Version

2.7.2

Package Version

1.0.12

Bug description

After adding a password to an .xlsx file generated with phpspreadsheet, and uploading it to Office365, when trying to open it online, I get an error message. The same file works well when opened by LibreOffice - it asks for the password, and only accepts the correct one. Also, the non-encrypted file works fine in Office365.

Steps to reproduce

generate excel file with PhpSpreadsheet, using the xlsx writer
add a password with this tool
upload it to office365
try to view it on office365

Relevant code snippet

    ...
    $writer = new Writer\Xlsx($spreadsheet);
    $f = fopen('php://memory', 'r+');
    $writer->save($f);
    rewind($f);
    $out = stream_get_contents($f);

    $e = new Nick\SecureSpreadsheet\Encrypt(true);
    $out = $e->input($out)->password($_SERVER['PAYMENTS_REPORT_PASSWORD'])->output();

gggeek commented 1 month ago

PS: googling around, it is not very clear if Office365 even supports encrypted excel files - as in whole-document-encrypted. It might be that it only supports password-protecting single workbooks or cells against modification :-( If so, it might be worth adding to the readme file of this package...

nick322 commented 1 month ago

I tried this code with no problems :(

        $spreadsheet = new Spreadsheet();
        $activeWorksheet = $spreadsheet->getActiveSheet();
        $activeWorksheet->setCellValue('A1', 'Hello World !');

        $writer = new Xlsx($spreadsheet);
        $f = fopen('php://memory', 'r+');
        $writer->save($f);
        rewind($f);
        $out = stream_get_contents($f);

        $e = new Encrypt(true);
        $out = $e->input($out)->password('111a')->output();
        file_put_contents('111a.xlsx',$out);

upload it to office365 file https://1drv.ms/x/s!AtHN4N6NgQjViiL8NjFsXIYxmkZF?e=iLOJ8S

gggeek commented 1 month ago

I tested the exact same code snippet, uploaded the generated file, and it fails after uploading to the MS Sharepoint folder I use (same error dialog shown, after inputting the correct password). Otoh I downloaded your sample file, uploaded it, and that one works. Also, generating locally and uploading the same 'hello world' file without encryption does work as well.

So it seems that there is something different in the environment and/or Composer dependencies which makes the encryption part behave differently between my env and yours :-(

I am running php 8.1.2, in Ubuntu 22.04 (in a docker container)

Installed libs: nick322/secure-spreadsheet: 1.0.12 mnapoli/silly: 1.9.0 pear/ole: 1.0.0 pear/pear-core-minimal: 1.10.15

I am attaching as well the generated encrypted file as 111a_ko.xlsx (same pwd as the working one) 111a_ko.xlsx. Its binary form is quite different from your one...

nick322 commented 1 month ago

I tested the exact same code snippet, uploaded the generated file, and it fails after uploading to the MS Sharepoint folder I use (same error dialog shown, after inputting the correct password). Otoh I downloaded your sample file, uploaded it, and that one works. Also, generating locally and uploading the same 'hello world' file without encryption does work as well.

So it seems that there is something different in the environment and/or Composer dependencies which makes the encryption part behave differently between my env and yours :-(

I am running php 8.1.2, in Ubuntu 22.04 (in a docker container)

Installed libs: nick322/secure-spreadsheet: 1.0.12 mnapoli/silly: 1.9.0 pear/ole: 1.0.0 pear/pear-core-minimal: 1.10.15

I am attaching as well the generated encrypted file as 111a_ko.xlsx (same pwd as the working one) 111a_ko.xlsx. Its binary form is quite different from your one...

Maybe it's an environment problem. If possible, please provide the relevant dockerfile and execution steps so that I can reproduce the error.

gggeek commented 1 month ago

Its a complex env - I will try to put together a simpler dockerfile

On Tue, 30 Jul 2024, 03:02 Nick Huang, @.***> wrote:

I tested the exact same code snippet, uploaded the generated file, and it fails after uploading to the MS Sharepoint folder I use (same error dialog shown, after inputting the correct password). Otoh I downloaded your sample file, uploaded it, and that one works. Also, generating locally and uploading the same 'hello world' file without encryption does work as well.

So it seems that there is something different in the environment and/or Composer dependencies which makes the encryption part behave differently between my env and yours :-(

I am running php 8.1.2, in Ubuntu 22.04 (in a docker container)

Installed libs: nick322/secure-spreadsheet: 1.0.12 mnapoli/silly: 1.9.0 pear/ole: 1.0.0 pear/pear-core-minimal: 1.10.15

I am attaching as well the generated encrypted file as 111a_ko.xlsx (same pwd as the working one) 111a_ko.xlsx https://github.com/user-attachments/files/16415609/111a_ko.xlsx. Its binary form is quite different from your one...

Maybe it's an environment problem. If possible, please provide the relevant dockerfile and execution steps so that I can reproduce the error.

— Reply to this email directly, view it on GitHub https://github.com/nick322/secure-spreadsheet/issues/25#issuecomment-2257317571, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACLLGRNWG6N3LG5CFB2TVLZO3X3BAVCNFSM6AAAAABLQVB2ZSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENJXGMYTONJXGE . You are receiving this because you authored the thread.Message ID: @.***>

gggeek commented 1 month ago

Here it is: https://github.com/gggeek/secure_spreadsheet_issue

nfsecurity commented 1 month ago

It works fine in Office 365 (for me). I tried online (excel in the browser) and local mode (installed excel 365).

gggeek commented 1 month ago

A real Heisenbug :-(

Q: using the dockerfile I provided, do you get to produce an .xslx file which is 9728 bytes in size, as opposed to the 13312 one which you had generated before?

nick322 commented 4 weeks ago

After I generated the dockerfile you provided and uploaded the file to office 365, it still worked normally. 111a.xlsx

https://1drv.ms/x/c/d508818ddee0cdd1/EdoSoyMziQFOheRdybFMpvIBDgmCBt2D61pJqbcDbwbu-Q?e=mOGWag

gggeek commented 4 weeks ago

Lovely, thanks for putting in all this testing effort.

Recapitulating, it seems that:

the secure-spreadsheet codebase will generate encrypted versions of the same 'simple' original file which have a different binary layout, when run on different setups (ie. my docker setup vs. your standard dev env)
the encrypted file generated using my docker setup works fine when you upload it to office365 - both you and I successfully opened it with the correct password
the exact same file does not work when I upload it to anoffice365 account where I have "guest" role. I tested it this morning, and the results are always the same: an error dialog is shown after the password has been input and a fraction of a second has passed (which seems to imply successful decryption, as if I use a wrong password the rror message is both different and immediate)
otoh using the same guest account, I can successfully upload and open other encrypted excel files - even ones generated using secure-spreadsheet - albeit ones which have a different binary file layout

My conclusion is that is with great probability an MS f***up - somehow the fact that my access is 'guest' within my customer company's ms365 account, or some other unknown condition, does trigger some extra validation rules, and those rules do prevent the opening of "some" encrypted files.

I am satisfied with this knowledge, and do not require further testing nor fixes, at least for now.

Feel free to close this ticket, or keep it open as reminder for fellow users who might hit the same snag.

nick322 / secure-spreadsheet