[Postfixadmin-devel] Bug in localized date display (v3.0/postgresql)

[Ullrich v. B. <uz...@mu...>]

The following bug description is for postfixadmin v3.0 with a postgres
database. The bug is visible in the following screenshot taken from a
postfixadmin web page in german language:

http://www.musoftware.de/Screenshot_2016-12-20_22-03-31.png


Bug description:
================

The conversion from a timestamp field in the postgres database to a
displayable date is wrong. It is just coincidence that it works in the english
version. The problematic line is in model/PFAHandler.php:

if (db_pgsql()) {
    $formatted_date = "TO_DATE(text(###KEY###), '" . escape_string(Config::Lang('dateformat_pgsql')) . "')";

TO_DATE() converts a string(!) to a date. The second argument specifies how to
parse the first argument. The text() function converts a timestamp to a string
using the default date format.

So what happens here is that text() converts the timestamp field to a string,
then to_date() is used to parse this date string. While it looks as if the
second argument (taken from the language specific files) specifies the result,
it does in fact specify how to parse the output of text().

Here is an example from an actual database:

---------------------------------------------------------------------
postfix=# SELECT modified from ALIAS limit 1;
           modified
-------------------------------
 2016-12-13 15:00:40.598107+01
(1 row)
---------------------------------------------------------------------

Actually, "modified" is converted to text automagically, so using text() has
no visible effect:

---------------------------------------------------------------------
postfix=# SELECT text(modified) from ALIAS limit 1;
             text
-------------------------------
 2016-12-13 15:00:40.598107+01
(1 row)
---------------------------------------------------------------------

Using to_date(), above output is parsed and converted to a date, which in turn
is converted back to text for output:

---------------------------------------------------------------------
postfix=# SELECT to_date(text(modified), 'YYYY-MM-DD') from ALIAS limit 1;
  to_date
------------
 2016-12-13
(1 row)
---------------------------------------------------------------------

However, the string 'YYYY-MM-DD' does not specify the output, but how to parse
the input, so using 'DD.MM.YYYY' gives garbage:

---------------------------------------------------------------------
postfix=# SELECT to_date(text(modified), 'DD.MM.YYYY') from ALIAS limit 1;
  to_date
------------
 0019-06-08
(1 row)
---------------------------------------------------------------------

If you look at the code in PFAHandler.php, the second argument is passed to
the translators. This doesn't work.


Solution:
=========

The solution for a localizable output is to use the postgresql function
to_char(), which expects a timestamp as first argument and an output format as
the second. Since the second argument specifies the output format, it can be
used by the translators to display localized dates:

        if (db_pgsql()) {
            $formatted_date = "TO_CHAR(###KEY###, '" . escape_string(Config::Lang('dateformat_pgsql')) . "')";

Or, as a diff:

------------------------------------------------------------------------------
--- PFAHandler.php.orig 2016-12-20 21:40:29.566967757 +0100
+++ PFAHandler.php      2016-12-20 21:18:28.383772774 +0100
@@ -565,7 +565,7 @@
         $no  = escape_string(Config::lang('NO'));

         if (db_pgsql()) {
-            $formatted_date = "TO_DATE(text(###KEY###), '" . escape_string(Config::Lang('dateformat_pgsql')) . "')";
+            $formatted_date = "TO_CHAR(###KEY###, '" . escape_string(Config::Lang('dateformat_pgsql')) . "')";
             # $base64_decode = "DECODE(###KEY###, 'base64')";
         } elseif (db_sqlite()) {
             $formatted_date = "strftime(###KEY###, '" . escape_string(Config::Lang('dateformat_mysql')) . "')";
------------------------------------------------------------------------------

This went probably unnoticed, because the date string in the english version

$PALANG['dateformat_pgsql'] = 'YYYY-mm-dd';

causes the date text to be parsed correctly because it matches the output date
format of text().


Another problem:
================

Besides that, there's an inconsistency in the output: The mailbox list is
displayed in list-virtual.php and does NOT use the date format specified in
the language file. So the date format in the "last change" columns differs. It
is localized in the alias domains and aliases list, and fixed/not localized in
the mailbox list. In my case, I had garbage in the first two lists and a
correct (but non german) date in the last.

Regards


        Uz


-- 
Ullrich von Bassewitz                                  uz...@mu...
Encrypted email preferred                          PGP Key-Id: 29D93B10