ODS export: incorrect zip files time

[Enurhe]

Describe the bug

phpmyadmin/libraries/classes/ZipExtension.php

Line 200 in adef7b6

foreach ($data as $table => $dump) {

function createFile in file libraries/classes/ZipExtension.php

In the foreach loop the $time option is reused for each iteration resulting in wrong dates in archive.
First iteration is OK, but on next $time has the value of previous iteration, and thus getdate($time) result in some strange dates.

         ...
        foreach ($data as $table => $dump) {
         ...
            /* Convert Unix timestamp to DOS timestamp */
            $timearray = ($time == 0) ? getdate() : getdate($time);
         ...
            $time = (($timearray['year'] - 1980) << 25)
         ...
       }

To Reproduce

Steps to reproduce the behavior:

1. Click on a table to view data
2. Click on Export
3. Select "OpenDocument Spreadsheet"
4. Click Go
5. List files in resulting archive, e.g. 7z l export.ods

$ 7z l export.ods

Listing archive: ../export.ods

--
Path = ../export.ods
Type = zip
Physical Size = 3286

   Date      Time    Attr         Size   Compressed  Name
------------------- ----- ------------ ------------  ------------------------
2019-01-14 00:14:22 ....A           46           44  mimetype
2011-07-26 01:52:42 ....A        13864         1199  content.xml
2003-06-25 23:05:24 ....A          425          199  meta.xml
1994-11-28 12:02:04 ....A         4711         1136  styles.xml
1985-09-04 15:59:30 ....A          513          190  META-INF/manifest.xml
------------------- ----- ------------ ------------  ------------------------

Expected behavior

Consistent dates.

Server configuration

• phpMyAdmin version: 4.8.4

Not a severe bug, but none the less.

[bahl24]

Hi @Enurhe ,
Can you share the structure of your table as I was unable to reproduce the issue, basically what is the type of the column -(timestamp, datetime, date, time or year)?
I found no issue in type-timestamp default-current_timestamp or even when type-time and I manually entered values.

[Enurhe]

Hi @bahl24 ,

The issue is with the content of the produced OpenDocument file.

An ODS file is practically a ZIP archive. As you can see from the example in original post the produced files in the ZIP archive has dates ranging from 1985 to 2019 - which is wrong. Reason being re-use of the $time variable in each iteration when packing files. They should all have same date. 2019-01-14 00:14:22 as per example.

Here is the relevant code extracted from the code linked in original post, with comments:

phpmyadmin/libraries/classes/ZipExtension.php

Line 160 in adef7b6

* Creates a zip file.

/* Note option $time */
public function createFile($data, $name, $time = 0)
{
    foreach ($data as $table => $dump) {

        /* On first iteration $time == 0 or provided option value. 
         *
         * On second+ iteration, $time == DOS timestamp from previous
         * iteration as the variable $time is re-used.
         * */
        $timearray = ($time == 0) ? getdate() : getdate($time);

        /* Here $time is re-used, thus:
         * On next iteration $time has the value set below
         *
         * The above line will result in:
         *      $timearray = getdate($time)
         * where $time is the DOS timestamp produced below.
         * */
        $time = (($timearray['year'] - 1980) << 25)
            | ($timearray['mon'] << 21)
            | ($timearray['mday'] << 16)
            | ($timearray['hours'] << 11)
            | ($timearray['minutes'] << 5)
            | ($timearray['seconds'] >> 1);
    }
}

From PHP manual:

getdate ([ int $timestamp = time() ] ) : array

timestamp

The optional timestamp parameter is an integer Unix timestamp that defaults to the current local time if a timestamp is not given. In other words, it defaults to the value of time().

Providing a DOS timestamp to getdate() does not produce the desired result.

[Enurhe]

Easiest solution would be to simply move the code on line 203 to 222 and place it before the foreach loop as it is strictly not necessary to re-generate this value for each file being packed.

From:

phpmyadmin/libraries/classes/ZipExtension.php

Line 203 in adef7b6

/* Convert Unix timestamp to DOS timestamp */

Down to line 222 inclusively.

Even better, perhaps, add a new function named unix_to_dostime or the like.

$dostime = unix_to_dostime($time);
$hexdtime = pack('V', $dostime);

foreach ($data as $table => $dump) {
…

[kartik1000]

@Enurhe Hey, i think that the date and time should have been same in ods file as per me. Am I correct or wrong do let me know..

[kartik1000]

@williamdes, Hey I tried making some changes and this was the result

Is it ok?

[kartik1000]

@williamdes, Hey I did this with multiple tables

[williamdes]

@kartik1000 Can you try to use the "Zip" option in "Custom" mode please ?

[kartik1000]

@williamdes Hey, I tried with zip option this time,

[williamdes]

@kartik1000 Thanks, It seems that you sent the wrong second screeshot
Can I have a screenshot of the times in the .zip file (because I now I had the issue) ?

[kartik1000]

Ok @williamdes,

This is of the zipped file only

[kartik1000]

@williamdes, can you confirm about the above screenshot?

[williamdes]

@kartik1000 Yes, can you open a PR, could not find one for this issue :)

[kartik1000]

@williamdes, Thanks a lot :)