Using PHP database sessions and needing to regenerate session id issue


I am utilising PHP sessions with a database rather than the traditional file storage. I currently am running into an issue with session regeneration. For the sign in, we regenerate the session id everytime and as expected the session handler destroys the session, makes a new session id and the the user_id column data will be missing but then i run a new update query to insert the user_id back in but it won’t work at all. I think i am being very thick as it’s been a long day but please can someone help.

I also think i may be going about this implementation wrong as regeneration needs to happen automatically every so often. Effectively, we require the sessions to be stored in a database so they can be remotely revoked by the user from any device they are signed into. But this session table is for any sessions, so the user_id can be null to support guest experiences. Any help would be greatly appreciated.

Please see laravel: HTTP Session - Laravel - The PHP Framework For Web Artisans

I also would utilise a framework but this is for a project where that isn’t possible… also tbh it’s shown i need to polish up on my vanilla PHP skills. Thanks :slight_smile:

The table has 4 columns:

user_id (optional)

My SessionHandler class looks like this:


class CustomSessionHandler implements \SessionHandlerInterface
    private $pdo;

    public function __construct($database)
        $this->pdo = $database;

    public function open($savePath, $sessionName): bool
        print "Session opened.\n";
        return true;

    public function close(): bool
        print "Session closed.\n";
        return true;

    public function read($sessionId): string|false
        $sql = $this->pdo->prepare("SELECT * FROM sessions WHERE session_id = :id");
        $sql->execute(['id' => $sessionId]);
        $data = $sql->fetch();

        if ($data) {
            $sql = "UPDATE sessions SET session_lastaccesstime = NOW() WHERE session_id = :id";
            $this->pdo->prepare($sql)->execute(['id' => $sessionId]);

            return $data['session_data'];
        } else {
            return '';
    public function write($sessionId, $data): bool
        print "Session value written.\n";
        print "Sess_ID: $sessionId\n";
        $sql = $this->pdo->prepare("INSERT INTO sessions SET session_id = :id, session_data = :session_data, session_lastaccesstime = NOW() ON DUPLICATE KEY UPDATE session_data = :session_data");
        $sql->execute(['id' => $sessionId, 'session_data' => $data]);
        return true;
    public function destroy($sessionId): bool
        print "Session destroyed.\n";
        print "Sess_ID: $sessionId\n";
        $sql = $this->pdo->prepare("DELETE FROM sessions WHERE session_id=:id");
        $sql->execute(['id' => $sessionId]);
        return true;
    public function gc($lifetime): int
        $this->pdo->query("DELETE FROM sessions WHERE session_lastaccesstime < DATE_SUB(NOW(), INTERVAL " . $lifetime . " SECOND)");
        return true;

I then initialise with this:

$dsn = 'mysql:host=localhost;dbname=artefact;charset=utf8';
$username = 'root';
$password = '';

try {
    $db = new PDO($dsn, $username, $password, [
    echo 'connected';
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());

$handler = new CustomSessionHandler($db);
session_set_save_handler($handler, true);

$_SESSION['chicken'] = "kfc";

Dummy sign in code:

    if ($_POST['action'] == 'login')
        // pretend submitted credentials, all good
        // db returns
        $user = [
            'id' => 1,
            'username' => "JohnDoe123",
        $_SESSION['user_id'] = $user['id'];
        $_SESSION['username'] = $user['username'];
        //session_regenerate_id(true); -- uncomment this and then the update query will not be executed
        $sql = "UPDATE sessions SET user_id = :user_id WHERE session_id = :id";
        $db->prepare($sql)->execute(['user_id' => $user['id'], 'id' => session_id()]);
    if ($_POST['action'] == 'logout') {
        $_SESSION = array();
        $cookie_par = session_get_cookie_params();
        setcookie(session_name(), '', time() - 86400, $cookie_par['path'], $cookie_par['domain'], $cookie_par['secure'], $cookie_par['httponly']);
<?php if (isset($_SESSION['user_id'])) { ?>
    logged in
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
        <input type="hidden" name="action" value="logout" />
        <button type="submit">fake sign out</button>

<?php } else { ?>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
        <input type="hidden" name="action" value="login" />
        <button type="submit">fake sign in</button>
<?php } ?>

Thanks in advance :slight_smile:

    public function verify_credentials($username, $password): bool
        $sql = "SELECT id, password FROM " . $this->table . " WHERE username =:username LIMIT 1";
        $user = $this->retrieve_credentials($sql, $username);
        if ($user && password_verify($password, $user['password'])) {
            session_regenerate_id(); // prevent session fixation attacks
            $_SESSION['user_id'] = $user['id'];
            return true;

        return false;

This is how I do session_regenerate_id(); and here’s my login script →

// Generate a CSRF token if it doesn't exist and store it in the session
if (!isset($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));

// Process the login form submission
    // Check if the submitted CSRF token matches the one stored in the session
    if (hash_equals($_SESSION['csrf_token'], $_POST['csrf_token'])) {
        // Sanitize the username and password input
        $username = strip_tags($_POST['username']);
        $password = $_POST['password'];

        // Verify the user's credentials
        if ($loginRepository->verify_credentials($username, $password)) {
            // Generate a secure login token
            $token = bin2hex(random_bytes(32));
            // Store the login token in the database
            $loginRepository->store_token_in_database($_SESSION['user_id'], $token);

            // Set a secure cookie with the login token
            setcookie('login_token', $token, [
                'expires' => strtotime('+6 months'),
                'path' => '/',
                'domain' => DOMAIN,
                'secure' => true,
                'httponly' => true,
                'samesite' => 'Lax'

            // Store the login token in the session
            $_SESSION['login_token'] = $token;

            // Redirect the user to the dashboard
            header('Location: ../dashboard.php');
        } else {
            // Display an error message for invalid username or password
            $error = 'Invalid username or password';
            error_log("Login error: " . $error);
    } else {
        // Display an error message
        $error = 'Invalid CSRF token';
        error_log("Login error: " . $error);
        $error = 'An error occurred. Please try again.';

That might help?

won’t work at all

This is not the most useful sort of error message. Having said that I don’t understand the point of your update in your login code regardless of if the session is regenerated or not. SessionHandlerInterface::write should end up being called for you.

Bottom line is that there should be no need to interact with your session table outside of the session handler class.


Please could you elaborate on what you mean? I like to follow best practices and right now my solution doesn’t follow them.

Would be amazing if you could provide me with a solution showcasing the best implementation.

I have however come to a solution by seeing if $_SESSION[‘user_id’] has been declared, if it has, then be sure to add it as part of the table row. It works with session regeneration.

Many thanks

The simple way of doing this is to only store the user’s id in a session variable in the login code, then query on each page request to get any other user data, such as the username, permissions, login state, … This allows these other values to be changed and they will take effect on the very next page request.

BTW - the session data can contain things other then who the logged in user is. The logout code should only clear that piece of data, not destroy the entire session.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.