e107 session table

[Jimmi08]

Hi,
I noticed a lot of records on site that is almost not used. Table e107_session has over 3500 records with valid 24 hours sessions.

So I checked my other active site. The specific of this site is that 2 CMS are running for the same user set. One is very similar as old e107.

Records in old table looks correctly, new e107 session table looks wrong.

Could you explain me this, please? Thanks

[Deltik]

e107 currently saves the sessions of guest visitors. Bots tend not to persist cookies, so every time a bot hits your site, a row gets added. We could definitely improve this by not saving cookies for guests.

Unfortunately, one usage I can think of that would break from not saving cookies is /e107_images/secimg.php, which saves the answer to the CAPTCHA inside the session. A better approach for this would be to send the client a cryptographically signed string that only the server can decode so that the CAPTCHA answer would not depend on cookies and sessions.

[Jimmi08]

@Deltik Can you point me in your version where db records for guests are deleted? I found it only in logout process.
Thanks

[Deltik]

There is no special cleanup for unauthenticated visitors. The built-in PHP session garbage collection (SessionHandler::gc()) is used for all sessions.

[Jimmi08]

yes, and it is correct, Just, it should be called with close() but I couldn't find where this (close()| is called in e107.

[Deltik]

SessionHandler::close() is automatically called when the session is closed. e107 kinda does this with e_session::end(), but it happens quite late in e107_core/templates/footer_default.php due to #1446, which is a performance-harming workaround for the CAPTCHA depending on sessions (as I mentioned previously).

The current workaround for the performance loss of moving session closing to the end is not doing any locking per session when using e_session_db. This should be "okay" for the vast majority of visits, as we don't store that much stuff in sessions that could conflict from multiple visits.

[Jimmi08]

Thanks, finally I got it. This is used: https://www.php.net/manual/en/class.sessionhandlerinterface.php With my PHP limitation I couldn't get how it could work :)

[Jimmi08]

The code example from gitter discussion:

<?php
require_once('e107_config.php');

$gc_maxlifetime = (int)ini_get('session.gc_maxlifetime');
if (!$gc_maxlifetime) {
    $gc_maxlifetime = 3600;
}

$expiration_time = time() - $gc_maxlifetime + 3600; // One hour

$dsn = "mysql:host=$mySQLserver;dbname=$mySQLdefaultdb;charset=$mySQLcharset";
$pdo = new PDO($dsn, $mySQLuser, $mySQLpassword);

$table_name = $mySQLprefix . "session";

$query = "DELETE FROM `$table_name` WHERE (session_user = 0 OR session_user IS NULL) AND session_expires < $expiration_time LIMIT 1000";

$total_deleted = 0;
do {
    $stmt = $pdo->prepare($query);
    $stmt->execute();
    $deleted_rows = $stmt->rowCount();
    $total_deleted += $deleted_rows;
} while ($deleted_rows > 0);

echo "Deleted " . $total_deleted . " rows from the $table_name table.\n";