Trouble with database sessions

[Chistaen]

Hiya,

 

Embarassing question here. I'm writing a forum software, and this is kind of the first time I've used database sessions instead of 'normal' sessions. It doesn't work as badly as I feared, but I'm still having some problems figuring out how to deal with the double quotes my session function adds to the database.

 

Okay, so my sessions table has three columns: session_id, session_data, session_date. Session_id is the session id, session_data contains some kind of session name (not of the session itself, it's the name used to start the session ($_SESSION['this part'] = 'meh')), a random part PHP adds, the user id and the encrypted password used to login. Session_date just contains the exact date and time the session was created. The problem is, session_data looks like this:

drasession123|s:45:"1|60|passwordencryption";

drasession123 is the session name, 1 is the user id, 60 is the session length in minutes, and passwordencryption is the encrypted password (I just replaced it with passwordencryption for safety reasons). There are several things I don't understand,  and I would greatly appreciate any help:

 

1. What is s:45:? Is that some kind of session name?
2. How do I get rid of the quotes? I don't even use htmlspecialchars (using doesn't make much of a difference), and I think it must be PHP adding that part. Please don't laugh too hard, I've always had problems like this.
3. Can I just call sessions like I normally do, i.e. like this: $_SESSION['drasession123']?
4. Unrelated, I know, but is it even wise to add the user's password (even if it's hashed) to a session? The only reason I added it was to verify the session is authentic.

This is the function that adds the session to the database:

// Write new sessions
function drasession_write($sessionid = '', $sessiondata = '') {

	global $draseim;

	// Prepare the variables we need in our query
	$sessionid = mysqli_real_escape_string($draseim['connection'], $sessionid);
	$sessiondata = mysqli_real_escape_string($draseim['connection'], $sessiondata);
	
	// Run a query
	$result = draseim_query($draseim['connection'], "
		REPLACE INTO " . db_pref . "sessions
		VALUES (
			'$sessionid',
			'$sessiondata',
			'" . $draseim['time'] . "'
		)
	", true);
	
	// Did it actually work?
	if($result == true)
		return true;
	else
		return false;
}

And this is the part that tells PHP it should use my functions rather than its own.

// Let's establish a connection to the database
$draseim['connection'] = draseim_connect($setting['db_server'], $setting['db_user'], $setting['db_password'], $setting['db_name']);

// Database sessions
session_set_save_handler(
	'drasession_open',
	'drasession_close',
	'drasession_read',
	'drasession_write',
	'drasession_destroy',
	'drasession_cleaner'
);

// Otherwise the sessions won't work
session_start();

... and the piece that sets the session.

$_SESSION[$setting['session_name']] = $userid . '|' . $sessionlength . '|' . $login['password'];

Any help would be most appreciated.

 

Thank you!

[scootstah]

What is s:45:? Is that some kind of session name?

I think that is a serialized array. serialize()

 

How do I get rid of the quotes? I don't even use htmlspecialchars (using doesn't make much of a difference), and I think it must be PHP adding that part. Please don't laugh too hard, I've always had problems like this.

PHP will not add those by itself. You must be calling html_entities() or htmlspecialchars() somewhere. What are you displaying the database results with?

 

Can I just call sessions like I normally do, i.e. like this: $_SESSION['drasession123']?

Yes.

 

Unrelated, I know, but is it even wise to add the user's password (even if it's hashed) to a session? The only reason I added it was to verify the session is authentic.

No. Do not store any sensitive information in a session. Ever.

[mac_gyver]

php internally serializes and unserializes the session data it passes to and retrieves from the write and read session data handlers, though it's not (according to the documentation) the same serialization that the serialize() and unserialize() functions do.

[scootstah]

Does it? Huh, thought you had to do that yourself in the write handler. It's been a while since I've done that myself.

[Chistaen]

Thank you for your fast reply.

 

I think that is a serialized array. serialize()

Oh, okay, so I can just ignore it?
 

PHP will not add those by itself. You must be calling html_entities() or htmlspecialchars() somewhere. What are you displaying the database results with?

My sincerest apologies, it seems that I had an html_entities() in my query function. The quotes now display like they should. Thanks for the tip. ;p Just phpMyAdmin.

 

No. Do not store any sensitive information in a session. Ever.

Lol, I feel so stupid, haha, thanks for the advice. Would you recommend getting rid of the user id as well, or just the password?

 

 

Thanks again for your help.

[scootstah]

Would you recommend getting rid of the user id as well, or just the password?

You can leave the user ID, that's normal to have in a session. You just don't want anything that the general public shouldn't see. If someone sees a user ID... well, it's not a big deal. They shouldn't be able to do anything with a user ID. But, a password hash on the other hand, they could attempt to recover the password. Not good.

[mac_gyver]

further to the above, as of php5.5.4, you can specify that php uses standard serialization for the data.

 

as to the serialization, here's the read handler documentation -

The read callback must always return a session encoded (serialized) string, or an empty string if there is no data to read.

This callback is called internally by PHP when the session starts or when session_start() is called. Before this callback is invoked PHP will invoke the open callback.

The value this callback returns must be in exactly the same serialized format that was originally passed for storage to the write callback. The value returned will be unserialized automatically by PHP and used to populate the $_SESSION superglobal. While the data looks similar to serialize() please note it is a different format which is speficied in the session.serialize_handler ini setting.

 

 

the write callback doc is similarly worded.

[Chistaen]

Thanks again for the replies. I've removed the password from the session, however I'm now facing another problem: as soon as I leave the login page after logging in (the page that sets the session), PHP empties the 'session_data' column and updates the 'session_date' (the one with the current time) column. I have absolutely NO idea what's causing the problem. Cookies can be changed, so if I use cookies to keep the session data in the database intact, there's a huge security risk. Right?

 

Sorry for being so stupid, I'm sure there's a very simple solution for it, but I just don't even remotely know what that solution might be. Since this is the first time I'm using database sessions, it's still all very new to me.

 

How do you guys handle login systems? I'm not asking for code (I'm here to learn, not to steal your code ), I'm just curious whether you guys use cookies + sessions, custom sessions, sessions, etc., because creating a decent login system can't be too difficult (right?). Since I updated some of the code, here are the relevant pieces of code:

 

The function that writes the session to the database, called automatically by PHP (so it's not the login system that calls this function).

function drasession_write($sessionid = '', $sessiondata = '') {

	global $draseim;

	// Prepare the variables we need in our query
	$sessionid = mysqli_real_escape_string($draseim['connection'], $sessionid);
	$sessiondata = mysqli_real_escape_string($draseim['connection'], $sessiondata);

	// Run a query
	$result = draseim_query($draseim['connection'], "
		REPLACE INTO " . db_pref . "sessions
		VALUES (
			'$sessionid',
			'$sessiondata',
			'" . $draseim['time'] . "'
		)
	", true, false);
	
	// Did it actually work?
	if($result == true)
		return true;
	else
		return false;
}

This is a part of the login function, the part that sets the session.

			// The user agent
			$login['user_agent'] = mysqli_real_escape_string($draseim['connection'], $_SERVER['HTTP_USER_AGENT']);
			
			// The IP address
			$login['user_ip'] = addslashes($_SERVER['REMOTE_ADDR']);
		
			// How long should we keep the session active?
			$sessionlength = (!empty($_POST['draseim_forever']) ? '0' : '60');
		
			// Set the session
			$_SESSION[$setting['session_name']] = array(
				'user' => $userid,
				'agent' => $login['user_agent'],
				'ip' => $login['user_ip'],
				'sessionlength' => $sessionlength
			);

Oh, to confirm that I am calling session_start();, here's all code from the header comment block to that function.

// Let's not jump the gun by accessing files directly. Index.php (which, in case you haven't noticed, is this file) is the only file that should be accessed directly
if(defined('Draseim'))
	die('No. Just... no.');
else
	define('Draseim', 1);
	
// Let's create an array
$draseim = array();

// What time is it?
$draseim['time'] = time();
	
// We need these files.
require 'Config.php';
require $setting['dir_resources'] . '/Start.php';
require $setting['dir_resources'] . '/Menu.php';
require $setting['dir_resources'] . '/Session.php';
require $setting['dir_apps'] . '/User.app.php';

// Define db_pref, which contains the database prefix
define('db_pref', $setting['db_pref']);

// Let's establish a connection to the database
$draseim['connection'] = draseim_connect($setting['db_server'], $setting['db_user'], $setting['db_password'], $setting['db_name']);

// Database sessions
session_set_save_handler(
	'drasession_open',
	'drasession_close',
	'drasession_read',
	'drasession_write',
	'drasession_destroy',
	'drasession_cleaner'
);

// Otherwise the sessions won't work
session_start();

Thanks again for your replies and help.

[scootstah]

You have to have a session cookie, otherwise the script doesn't know which session you belong to.

 

Yes, cookies can be modified client side. And yes, there is inherent risk there. If someone else got hold of your session cookie, they could use it to impersonate you. But, there are ways to mitigate that - HTTP only cookies, HTTPS everywhere, no XSS vulnerabilities, etc.

 

Also, any time a user gains elevated permissions they should re-authenticate using their username and password. For example, if a user goes from a normal page to a protected page, they should re-enter their credentials. You could be more strict on these protected pages, like limiting the elevated session duration to something small like 10-15 minutes, thus reducing the window that an attacker could gain a valid session.

 

If you have to use this system for something mission-critical, your best bet is to use an existing framework/library where all of these things have already been battle tested.

[mac_gyver]

as soon as I leave the login page after logging in (the page that sets the session), PHP empties the 'session_data' column and updates the 'session_date' (the one with the current time) column.

 

 

1) do your database functions have error checking logic in them so that you would know if there are any database errors?

 

2) do you have php's error_reporting set to E_ALL and either display_errors set to ON or log_errors set to ON so that any php detected errors would be displayed/logged?

 

3) when you change pages, are you changing the host-name/sub-domain part of the url and/or the path in the url and your session cookie parameters are not set up to match variations of these that are different from where the session was originally started? it may be that sessions are not working between pages, regardless of the session save handler, due to how you are getting between pages and the session cookie settings (see item #5 in this list.)

 

4) are you calling a database function to close the database connection anywhere in your main php code? this would prevent php from saving the session data during the shut-down process since there wouldn't be a database connection for your session write handler to use. upon the next session_start() you would be getting a new empty session.

 

5) for debugging purposes, temporarily comment out the session_set_save_handler(...) statement so that php will use file based session data handling. this will help pin down the problem to either your main program logic or your session handler code.

 

6) why are you or why do you think you should be using a database session save handler? there's very few reasons for doing so and since the code must be 100% correct and is dependent on there being a database connection (see item #4 in this list) it has restrictions that you must take into account when writing your main code or you must set up a second dedicated database connection that is only used by the session handler and not your main code (or you can remember to always call session_write_close() before calling any database connection close function.)

Edited July 12, 2015 by mac_gyver

[Chistaen]

Thanks for the replies. =)

 

Also, any time a user gains elevated permissions they should re-authenticate using their username and password. For example, if a user goes from a normal page to a protected page, they should re-enter their credentials. You could be more strict on these protected pages, like limiting the elevated session duration to something small like 10-15 minutes, thus reducing the window that an attacker could gain a valid session.

Great idea, I'll definitely keep that in mind.
 

If you have to use this system for something mission-critical, your best bet is to use an existing framework/library where all of these things have already been battle tested.

Yeah, I agree, but unfortunately, using a framework proved to be more difficult than writing everything myself. But that's probably because I need to get used to working with them.

 

1) do your database functions have error checking logic in them so that you would know if there are any database errors?

At the moment, not yet. Right now I just add mysqli_error's whenever something doesn't work, but good idea, I'll change add error checking functions immediately. =)

 

 

2) do you have php's error_reporting set to E_ALL and either display_errors set to ON or log_errors set to ON so that any php detected errors would be displayed/logged?

Yep.
 

3) when you change pages, are you changing the host-name/sub-domain part of the url and/or the path in the url and your session cookie parameters are not set up to match variations of these that are different from where the session was originally started? it may be that sessions are not working between pages, regardless of the session save handler, due to how you are getting between pages and the session cookie settings (see item #5 in this list.)

I just have one file (index.php) which handles all the other pages, like this: index.php?page=hi. The sessions are updated in the database, but for some reason the session data just isn't updated. I'm not sure why, sessions should continue to hold the data I create them with, right?

 

4) are you calling a database function to close the database connection anywhere in your main php code? this would prevent php from saving the session data during the shut-down process since there wouldn't be a database connection for your session write handler to use. upon the next session_start() you would be getting a new empty session.

Nope, although I did add a database close function to the session handler, but only because I thought it was required. Apart from that, I don't have a function that closes the database connection.

 

5) for debugging purposes, temporarily comment out the session_set_save_handler(...) statement so that php will use file based session data handling. this will help pin down the problem to either your main program logic or your session handler code.

Will do, I'll let you know the results as soon as possible.

 

6) why are you or why do you think you should be using a database session save handler? there's very few reasons for doing so and since the code must be 100% correct and is dependent on there being a database connection (see item #4 in this list) it has restrictions that you must take into account when writing your main code or you must set up a second dedicated database connection that is only used by the session handler and not your main code (or you can remember to always call session_write_close() before calling any database connection close function.)

I was under the impression it was safer, and wouldn't cause problems with multiple servers. But I assume there are more ways to do this?

 

 

I was actually considering another option: creating a custom session system by using cookies. When logging in, I would use the session table to add a 'session' that would hold a randomly-generated id, user id, the user agent, IP address, and the time. Then I would place a cookie, with said IDs, the user agent and the IP address. Couldn't I just verify that information? It seems to me that shouldn't be too much of a security risk. Right?

[mac_gyver]

I just have one file (index.php) which handles all the other pages, like this: index.php?page=hi.

 

 

if you are doing a header() or javascript or meta tag redirect/refresh of the page, you could still be modifying the host-name/sub-domain (or the path) in the url, which can cause the session to not match, since the default session 'domain' setting causes a session to only match the variation of the host-name/sub-domain where it was set at.

 

I was under the impression it was safer

 

 

if you are on a shared web server, using the database is actually less secure, since there's no limit/flood checking for login attempts against the database server. shared accounts can sit there 24/7 trying database usernames/passwords until they break in to your database. using the default file handling for session data (assuming that the web server permissions are set up correctly) would mean that your session data files are owned by your account and cannot be read by the other accounts.

 

if you are on a vps or dedicated web server, the session data files or database would be equally secure (assuming you don't have any database code that allows sql injection and you are using the same database connection for the session data and the web based data.)

 

i recommend that until you have a proven need to use a database for session data, to stick with the default file based session data.

 

edit:

 

Yep.

 

 

and if you are displaying the php errors, are you (in your code) or php (in the php.ini) using output buffering so that you won't actually see any displayed errors if your page is doing a redirect/refresh?

Edited July 13, 2015 by mac_gyver

[scootstah]

I was under the impression it was safer, and wouldn't cause problems with multiple servers. But I assume there are more ways to do this?

It's "safer" on shared hosting because often times shared hosting stores all PHP sessions in the same place. So if somebody was able to get access to that directory, they could steal everybody's sessions, basically.

 

There are many ways to store sessions, and you don't necessarily have to use databases to circumvent this problem. You could simply change the session save directory to a directory that is part of your hosting. Usually shared hosts have chroot'ed directories that cannot be accessed by anyone except your account user.

 

Storing sessions in a database is an easy fix for when your servers sit behind a load balancer. It can be a pit of a pain to get the default file-based setup working that way. Another way is to use a cache like Redis or Memcached.