[ic] UserDB Last Login bug when running with mysql

[Kevin Walsh]

Mike Heins <mike at perusion.com> wrote:
> Quoting Carl Bailey (carl at triangleresearch.com):
> > Using IC 5.4, we noticed that the user's last login time in the UI was 
> > showing up as December 1969 for everyone.  We traced the issue to this:
> > 
> > The mysql column type is timestamp, which returns the data like 
> > YYYYMMDDHHMMSS.
> > However the UI page customer_view.html uses a [time] tag to display the 
> > value.  The problem is that the time tag only works correctly when 
> > given a "unix" time value, and fails when given the mysqlvalue above.
> > 
> > We solved it by changing the UI page as follows:
> >         [calc]
> >             my $dt = "[loop-param mod_time]";
> >             $dt =~ m/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/;
> >             return "$2/$3/$1 $4:$5:$6"
> >         [/calc]
> > 
> > However, this fix works only for mysql and does not allow such flexible 
> > formatting as the time tag.
> > A more general fix would be to adjust the time tag something along the 
> > lines that follow:
> > 
> > -- use POSIX qw(ceil strftime LC_CTYPE);
> > ++ use POSIX qw(ceil mktime strftime LC_CTYPE);
> > 
> > my $now = $opt->{time} || time;
> > ++ if (length($now) == 14) {
> > ++  # we have a mysql timestamp on our hands
> > ++  $now = m/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/;
> > ++  my $now = mktime($6,$5,$4,$3,$2 - 1,$1 - 1900);
> > ++}
> >
> Hmm. I am not sure about this, as it is quite possible that time values
> will be changing as UNIX moves to a 64-bit number. Also, MySQL could
> easily change the length of the return value, and it may even change
> based on locale or other settings.
> 
> However, I am willing to look at something that can reliably detect
> an iso time and do something with it. Any suggestions from the 
> group?
> 
The Date::Parse Perl module will reliably detect and parse various
date formats, but it's probably an overkill and would introduce another
dependency.

I agree that checking the length of the time value is probably a bad
idea, as MySQL may decide to use the standard ISO timestamp format one
day.  Perhaps something like the following would be better for now:

    my $now = $opt->{time} || time();

    if ($now = m/^(\d{4})-?(\d{2})-?(\d{2})[T\s]?(\d{2}):?(\d{2}):?(\d{2})/) {
        $now = POSIX::mktime($6,$5,$4,$3,$2 - 1,$1 - 1900);
    }

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin at cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/