CodeIgniter4: Bug: Session Library

Describe the bug Timestamp of session data, saved in database, is always set to 0000-00-00 00:00:00

CodeIgniter 4 version CI 4.1.3

Affected module(s) system/Session/Handlers/DatabaseHandler.php

write() (line 201 & line 222, ‘timestamp’ => ‘now()’)

Expected behavior, and steps to reproduce if appropriate When inserting values into the session table (default ‘ci_sessions’), the timestamp is always set to 0000-00-00 00:00:00. The session table is set up as described in: https://codeigniter.com/user_guide/libraries/sessions.html#databasehandler-driver

The frameworks internal query builder composed this query (gives a timestamp of 0000-00-00 00:00:00): INSERT INTO ci_sessions (id, ip_address, timestamp, data) VALUES (‘123456789012345678901234567890’, ‘123.12.0.0’, ‘now()’, ‘<blob_string>’);

If I do a manual insert into the database table, I get a correct result (e.g. timestamp of 2021-06-09 09:56:12): INSERT INTO ci_sessions (id, ip_address, timestamp, data) VALUES (‘123456789012345678901234567890’, ‘123.12.0.0’, now(), ‘<blob_string>’);

The difference being: ‘now()’ -> now()

My main concern is that the garbage collector (gc() on line 318) will delete all session data.

Additional question: Am I overlooking something? If not, how would I be able to do a temporary fix for this.

Context

PHP version 7.4.20
MySQL 5.7.34

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 23 (14 by maintainers)

Most upvoted comments

Just to get a common understanding: both MySQL and Postgres can interpret these constructs: CURRENT_TIMESTAMP and NOW(), so there lies not the problem.

The problem is the fact that when the actual database insertion happens, the query builder (certainly in the case of MySQL), does not place the function now() in the final query string, but instead the string ‘now()’. I don’t know how the query builder handles this in the case of Postgres or if Postgres itself maybe can interpret the string ‘now()’. [Solution A] , could be: 'timestamp' => $this->platform === 'postgre' ? 'now()' : date('Y-m-d H:i:s'),
Avoiding the ‘now()’ string (not the function per se) altogether. [Solution B] , could be: 'timestamp' => date('Y-m-d H:i:s'),
As @paulbalandan suggested. [Solution C] , could be: Handling the insertion and update of the timestamp in the database table itself (see above comments).
Or we might use a bit more raw SQL, below a untested/unfinished example. [Solution D] , could be:

$insertData = [
    'id'         => $sessionID,
    'ip_address' => $this->ipAddress,
    'data'       => $this->platform === 'postgre' ? '\x' . bin2hex($sessionData) : $sessionData,
];

$sql = 'INSERT INTO ' . $this->table . ' (id, ip_address, timestamp, data) VALUES (:id:, :ip_address:, now(), :data:)';

$this->db->query($sql, $insertData);

Any ideas about above 4 possible solutions, or maybe something else can help us?

gmeister2 on Jun 16, 2021

That commit you referenced is from 2 years ago.

@MGatner Correct, but it is only recently merged into develop. (MGatner merged commit ad8aa07 into codeigniter4:develop on Feb 24)

To me this feels like a real serious bug, i.e. the garbage collector cleaning all sessions at random moments, due to the fact that the timestamp is incorrectly set (see my previous posts in this thread).

Some background: I am eager to try out the Myth Auth authentication library, but currently still use Ion Auth 4, which uses the CI4 session library.

gmeister2 on Jun 15, 2021

‘now()’ is string. It is not now(). It is not a bug of MySQL.

I am not suggesting this is a bug in MySQL. This ‘issue thread’ is about reporting a bug in the session library, i.e. 'now()' on line 201 & 222 not getting set in the query builder as now().

This might be a possible solution (on line 201 & 222): 'timestamp' => date('Y-m-d H:i:s'),

gmeister2 on Jun 11, 2021

In my system MySQL doesn’t seem to parse the ‘now()’ into NOW() and this might be a bug.

'now()' is string. It is not now(). It is not a bug of MySQL.

kenjis on Jun 11, 2021