Bug: CodeIgniter doesn't set the timezone in database sessions

[LenoilR]

PHP Version

8.2

CodeIgniter4 Version

4.4.6 (but current version has same issue)

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows, Linux

Which server did you use?

apache

Environment

production, development

Database

MariaDB 10.6

What happened?

CodeIgniter sets the timezone in PHP based on the setting of the variable $appTimezone in App\Config\App.php. It doesn't, however, set the timezone for database sessions. The result of this, if the database server is in a different timezone than the one set in $appTimezone, is that database functions like NOW() will return incorrect times.

Steps to Reproduce

1. Create a MySQL table with a column whose value is a time and whose default value is current_timestamp().
2. Insert a new record in the table using query builder's or model's insert method, not supplying a value for the column. Retrieve that value and display it alongside the output of Time::now();
3. change $appTimezone in App\Config\App.php to a different timezone string and rerun the test code.
4. The times will vary by however many hours difference there is between the $appTimezone setting and whatever the global timezone setting is on the database server.

Expected Output

The time displayed by Time::now() in CodeIgniter and the time added to a record in the database by a default value of current_timestamp() should be the same.

Anything else?

The only way to fix this is in the connect method of the database driver. For the MySQLi driver, this would entail including "SET SESSION time_zone=offset" in the call to mysqli->options(MYSQLI_INIT_COMMAND...), where offset is the time offset of the time zone in $appTimezone from UTC in the format +|-HH:MM.

I've successfully modified the code to do this, by adding a setSessionVars method to CodeIgniter\Database\MySQLi\Connection:

    public function setSessionVars(array $sessionVars) : void {
        $initString = '';
        foreach ($sessionVars as $var => $value) {
            $initString .= (!$initString? 'SET' : ',') . ' SESSION ' . $var . '=' . $value;
        }
        $this->mysqli->options( MYSQLI_INIT_COMMAND, $initString );
    }

I then modified these lines in the connect method:

        if ($this->strictOn !== null) {
            if ($this->strictOn) {
                $this->mysqli->options(
                    MYSQLI_INIT_COMMAND,
                    "SET SESSION sql_mode = CONCAT(@@sql_mode, ',', 'STRICT_ALL_TABLES')",
                );
            } else {
                $this->mysqli->options(
                    MYSQLI_INIT_COMMAND,
                    "SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                        @@sql_mode,
                                        'STRICT_ALL_TABLES,', ''),
                                    ',STRICT_ALL_TABLES', ''),
                                'STRICT_ALL_TABLES', ''),
                            'STRICT_TRANS_TABLES,', ''),
                        ',STRICT_TRANS_TABLES', ''),
                    'STRICT_TRANS_TABLES', '')",
                );
            }
        }

to this:

        $tzoffset = Time::now()->getOffset(); // seconds from GMT
        $tzoffset = sprintf("%+03d:%02d", floor($tzoffset/3600), abs(($tzoffset%3600)/60)); // +|-HH:MM
        $sessionVars = ['time_zone' => "'".$tzoffset."'" ];

        if (isset($this->strictOn)) {
            $sessionVars['sql_mode'] = ($this->strictOn)?
                    "CONCAT(@@sql_mode, ',', 'STRICT_ALL_TABLES')"
                    : "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                        @@sql_mode,
                                        'STRICT_ALL_TABLES,', ''),
                                    ',STRICT_ALL_TABLES', ''),
                                'STRICT_ALL_TABLES', ''),
                            'STRICT_TRANS_TABLES,', ''),
                        ',STRICT_TRANS_TABLES', ''),
                    'STRICT_TRANS_TABLES', '')"
            ;
        }

        $this->setSessionVars( $sessionVars );

Adding the setSessionVars method would allow users to easily set additional session variables by doing the following:

1. Create file App\Database\Connection.php and create a new Connection class that extends CodeIgniter\Database\MySQLi\Connection.
2. Override the setSessionVars method to add the additional variables and their values to the $sessionVars array and then call the parent method:

    public function setSessionVars(array $sessionVars) : void {
        // set the foo session variable to value 'bar'
        $sessionVars['foo'] = "'bar'";
        parent::setSessionVars($sessionVars);
    }

3. Tell CodeIgniter to use your new class by changing the DBDriver entry in the $default array in App\Config\Database.php from 'MySQLi' to 'App\Database'.