Page 1 of 1

storing session data in database

Posted: Sat Aug 16, 2008 1:29 pm
by ibolui
hi, i have implemented the storage of session data into mysql database, as described in 'essential php security'.
i would like to ask how do i 'clean up' both the 'sessions' and 'sessions_keys' tables?

Re: storing session data in database

Posted: Sun Aug 17, 2008 4:40 am
by Oren
It depends... what do you mean by "clean up"?

Re: storing session data in database

Posted: Sun Aug 17, 2008 4:48 am
by arjan.top
gc defined in session_set_save_handler should delete expired sessions

Re: storing session data in database

Posted: Sun Aug 17, 2008 6:04 am
by ibolui
yup i want to delete expired sessions from both the table but somehow it isnt working. below are my codes.. is there anything wrong with it??

session_set_save_handler('_open', '_close', '_read', '_write', '_destroy', '_clean');

function _open() {
global $db;
return $db;
}

function _close() {
global $db;
return mysql_close($db);
}

function _read($id) {
global $db;

$algorithm = MCRYPT_BLOWFISH;
$mode = MCRYPT_MODE_CBC;

$id = mysql_real_escape_string($id);

$sql = "SELECT session_data FROM sessions WHERE session_id = '$id'";

if ($result = mysql_query($sql, $db)) {
if (mysql_num_rows($result)) {
$record = mysql_fetch_assoc($result);
// return $record['session_data'];
$data = base64_decode($record['session_data']);

$iv_size = mcrypt_get_iv_size($algorithm, $mode);

$ciphertext = substr($data, $iv_size);
$iv = substr($data, 0, $iv_size);

$crypt = new crypt();

$crypt->iv = $iv;
$crypt->ciphertext = $ciphertext;
$crypt->decrypt();

return $crypt->cleartext;
}
}

return '';
}

function _write($id, $data) {
global $db;

$expires = time();

$crypt = new crypt();

$crypt->cleartext = $data;
$crypt->generate_iv();
$crypt->encrypt();

$ciphertext = $crypt->ciphertext;
$iv = $crypt->iv;

$data = base64_encode($iv . $ciphertext);

$id = mysql_real_escape_string($id);
$expires = mysql_real_escape_string($expires);
$data = mysql_real_escape_string($data);

$sql = "REPLACE INTO sessions VALUES ('$id', '$expires', '$data')";

return mysql_query($sql, $db);
}

function _destroy($id) {
global $db;

$id = mysql_real_escape_string($id);

$sql = "DELETE FROM sessions WHERE session_id = '$id'";

return mysql_query($sql, $db);
}

function _clean($max) {
global $db;

$old = time() - $max;
$old = mysql_real_escape_string($old);

$sql = "DELETE FROM sessions WHERE session_expires < '$old'";

return mysql_query($sql, $db);
}

Re: storing session data in database

Posted: Sun Aug 17, 2008 6:24 am
by arjan.top
expired sessions are deleted based on session.gc_divisor in php.ini, so if the value is 100, gc would delete expired sessions once for 100 requests

Re: storing session data in database

Posted: Sun Aug 17, 2008 9:42 am
by ibolui
so the cleaning up of expired sessions is automatically?

another thing.. i implemented the method of generating session key as follows.

function generate_session_key() {
global $db;
session_regenerate_id();
$_sess_id = session_id();
$ip = isset($_SERVER['REMOTE_ADDR']) ? htmlspecialchars($_SERVER['REMOTE_ADDR']) : '';
$sessionKey = sha1(uniqid(mt_rand(), TRUE) . $ip);

$sql = "REPLACE INTO sessions_keys VALUES ('$_sess_id', '$sessionKey')";
if (!mysql_query($sql, $db)) {
// error
}

$_SESSION['session_key'] = $sessionKey;
unset($sessionKey);
}


i realise that the sessions_keys tables has huge number of entries. how do i clean this up??

Re: storing session data in database

Posted: Sun Aug 17, 2008 9:51 am
by arjan.top
ibolui wrote:so the cleaning up of expired sessions is automatically?
yes
i realise that the sessions_keys tables has huge number of entries. how do i clean this up??
update _destory and clean, so that you delete from table session_keys too

Re: storing session data in database

Posted: Sun Aug 17, 2008 10:50 am
by ibolui
update _destory and clean, so that you delete from table session_keys too
sorry i dont get what you mean :?

Re: storing session data in database

Posted: Sun Aug 17, 2008 11:03 am
by arjan.top
delete session_key in _destroy and _clean

Re: storing session data in database

Posted: Sun Aug 17, 2008 8:59 pm
by ibolui
ohh.. but how do i identify which entries to delete?
sorry.. new to this :|