using mysql to track sessions instead of trusting the server?

Question

Some context...skip to the bottom for question if you are impatient...

I am trying to limit access to four pages on my (future) website to users with a valid username and password pair. To this end, I have a simple PHP/HTML form...in my PHP/HTML form the client types in a username and password, hits 'submit'...the data goes to POST and another PHP script validates the user/passwd pair by using a SELECT in my mySQL database...

userpassword table: uid (PRIMARY KEY,INT), username (varchar 32), password (char 128)

If the match works then it looks up the access table to see what page that particular username has access to (1 for access, 0 for no access):

useraccess table: uid (PRIMARY KEY,INT), securename0(TINYINT), securepage1(TINYINT)...

The PHP script then prints out links to the secure pages they have access to. If I understand them correctly, the articles and books I have read state that you normally store a cookie on the client side with a session ID that points to a session file on server that stores the username/password pair and whatever other session variables until it either times out or the user logs out.

I don't want to spend the money for a dedicated server. So all that PHP session info is saved all lumped together on the server, along with the other half dozen websites from other customers running on it. This strikes me as horribly insecure...

The question is...would it be any more secure to circumvent all that and store/track the per-user session information in my own mySQL table? ie. something like this:

session table: sessionkey (PRIMARYKEY, CHAR(128)), uid(INT), expiretimedate(DATETIME), accesstosecurepage0 (TINYINT), accesstosecurepage1(TINYINT)...

So when a user hits any "secure" page it would check their session id cookie (if present) and then do a SELECT on the session table to see if that particular "sessionkey" is present, then give them access depending on what accesstosecurepage0,1,2,etc. are set to.

Would this work better than the alternative or am I wasting my time?

Answer 1

Potentially more secure, yes -- after all, shared hosting is an infamous target for exactly the kind of security breaches you fear but, once again, the MySQL server is shared and accessible by other users just like all other resources so, worst case scenario, the damage is exactly the same.

The efficiency hit, however, would probably be unbearable and would almost certainly mitigate the extra peace of mind. To avoid the use of sessions or similar mechanisms completely, you wouldn't even have an easy way to cache the db results and a query per page load, per person - an unnecessary query - may well prove unacceptable.

Not to mention, you're replacing one class of vulnerability with a whole new one in the form of SQL injection.

Answer 2

I don't see this making your application any more secure. Session hijacking occurs when someone retrieves another user's session ID and pretends to be them. Your session table would not prevent this from happening. (I skipped to the bottom btw, hope I didn't miss any important details:)

It might even make it less secure since you are now giving hijackers two ways to steal session data: One through the file system and one through the DB. As to which one is more secure over the other, I'm not too sure, but I would think it depends on well you secure either one yourself.

Answer 3

This question is about as old as sessions themselves, although possibly for slightly different reasons than yours. Security is not the issue, as session hijacking occurs when someone gets hold of a user's session ID and sends that to the server. Therefore, using a database to store session data is as insecure as using a file on the machine - it essentially amounts to the same thing.

Database sessions tend to be used when multiple servers are required to host one site, or sessions need to be stored across different but related domains. However, it is considerably more work to set this up from scratch, if not using a pre-built framework.

If you don't need this functionality then using the standard session should be adequate.