Jump to content

Easily store PHP session data in a MySQL database

Recommended Posts

Hey guys,


I wanted to share something with everyone. I hope this is helpful. This is basically a native solution to easily store PHP session data in a MySQL database. 


Session variables contain data that is saved for a specific user by associating the user with a unique identity. Typically, PHP would store session variables in a local file system on the server by default. While this may be acceptable to many people who are running small to moderate PHP applications, some larger applications that require load balancing would need to be run on multiple servers with a load balancer. In such cases, each server running PHP would need a way to ensure that sessions continue to work properly. One common way to achieve this is to override where PHP opens, reads, writes, and destroys the session variables so that it can perform these operations on a table inside of a MySQL database. When this is performed, the web application can gain advantages such as session management, session logging, and session interactions.


I have provided my source code for your reference here:




Hope this helps someone out!

Link to post
Share on other sites

The code has severe problems and is far from production-ready, let alone ready for big applications.


First, there's no synchronisation whatsoever, which means concurrent requests accessing the same session will trample each other. You can actually see that:


// include your classes

$session = new Session();

if (isset($_GET['inc']))
elseif (isset($_GET['show']))
    echo $_SESSION['counter'];
    $_SESSION['counter'] = 0;

<!DOCTYPE html>
<html lang="en">
        <meta charset="utf-8">
        <script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
            for (let i = 0; i < 100; i++)
            $.get('?show', function (counter) {alert("Final counter value: " + counter)});

This is a simple Ajax counter which makes 100 requests to the script, and each request increments a counter in the session. You would expect a final result of 100, but you'll get less than that (I had 60), because the session operations overwrite each other, and so some are completely lost.


In an actual application, this is a nightmare. Not only can the error screw up important data. It will also occur randomly. Good luck to anybody trying to debug that.


Then your ID field is too short. A session ID may be up to 256 character long, so storing it in a VARCHAR(32) can lead to truncation.


The database class doesn't even make sense. For some strange reason, it only supports a single database server (because the credentials are hard-coded) and one query/statement at a time. How is that supposed for work for large applications? And what's the whole point of the class? It doesn't add any new features to PDO and is extremely confusing (for example, query() doesn't actually send a query).


Long story short:

  • Stop marketing this as a solution for big applications. It's an experiment at best.
  • You must synchronize session operations. This can be done with advisory locks.
  • Fix the length of the ID field.
  • Throw away the database class and use plain PDO.
Edited by Jacques1
Link to post
Share on other sites

The approach to security is also somewhat naive.


Sessions stored in a database are much more vulnerable than file-based sessions due to the prevalence of SQL injection attacks. With your current approach, an attacker can

  • obtain all session IDs if they find a vulnerable SELECT query
  • manipulate the session (e. g. make themselves an admin) or perform an object injection attack through the serialized data if they find a vulnerable UPDATE or INSERT query

That's pretty bad.


The sessions should definitely be protected with a cryptographic layer:

  • Only store hashes of the IDs instead of the plaintext IDs. SHA-256 is enough. This prevents the IDs from being read and simultaneously fixes the length problem.
  • Protect the integrity of the serialized data with a message authentication code. For example:
    $session_mac = hmac('sha256', "$hashed_id:$access_time:$data" , $session_auth_key);

    This will detect any manipulations.


Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.