Check if user is logged in with session instead of database query

Question

I'm wondering if it would be better to check if a user is logged into a website with his session instead of querying my database.

Isn't it a little bit too much to query the database on every subpage for every user (think of thousand of user) if his ID exists in the database?

Wouldn't it be better, faster and not so database-heavy to just check the values stored in the user session, with some conditions (like the ID has to be at least 10 digits long, surname has to exist and a string which is just known by the website operater should exist). Couldn't that be seen as a (99%) safe way to check if a user is logged in legitimately? I've read that PHP saves the session content in an encoded way, so it is not readable for people who wants to grab the content in order to simulate another user.

Is your database so slow that it actually costs you a lot to look this up? It usually shouldn't be. — Pekka, Jun 06 '13 at 08:53
99% of all applications require to fetch some user specific stuff from the database anyway. So this is not an additional lookup but one single query you cannot avoid anyway. — arkascha, Jun 06 '13 at 08:54
@Pekka: No not really but I think it is better to build a stable and future proof basis so I don't have to worry in future. Because over time there will be more and more database queries... — John Brunner, Jun 06 '13 at 08:55
It depends on your use case, really. If you take precautions and secure your sessions properly, I don't think that relying on sessions is necessary a bad thing. Check this question: http://stackoverflow.com/questions/328/php-session-security — Filippos Karapetis, Jun 06 '13 at 08:55
One big argument in favour of querying the database every time is that your data is guaranteed to be fresh. What if you need to disable a user account, or the user changes some setting (like their use name)? Seeing the old data for the rest of your session because your app doesn't think to refresh its information isn't terribly elegant, and usually there is no real performance argument here (as you'll usually query *lots* more stuff from the database anyway) — Pekka, Jun 06 '13 at 08:57
You need a session anyway to store the user-id, otherwise you don't know how to query the database. That means you can spare the database query for just testing if the user is logged in. Another question would be, whether you store the session-data itself in a database or in a file. — martinstoeckli, Jun 06 '13 at 09:12

Mehran · Accepted Answer · 2017-06-11T21:57:30.800

I believe there's a misunderstanding in your question. Sessions and databases play different roles, even though they are both data storage. The session is a temporary storage while the database is a permanent one. Data remain in the database until you remove it explicitly but sessions come with an expiration date. There's one another major difference between databases and sessions as well, and it's called session-id. Session-id is a mean to associate an HTTP request with the appropriate session data on the server. Session-ids usually are transferred back and forth between web servers and browsers as a cookie. Here's a typical scenario on how sessions work:

The very first request of a browser has arrived on a web server. The software on server processes the incoming request and sees that it includes no session-id (as it's the first request). So a randomly generated unique session-id is created for this request and sent back to the client with the response (whatever it might be). There's also a storage created on the server in association the newly created session-id.
User asks for another page on the same server. This time when the request has arrived on the server, it comes with a session-id so instead of creating a new session-id for this request, the data associated with it is loaded. If the data is changed by the software on the server, it is stored back to the storage when is the response is sent back to the browser.
From now on each request that is sent to web server loads the same session data. This process continues unless the session data or session-id is removed from the server.

In the scenario explained, sessions are used to keep data associated with requests. One of the major functions of session data is to store credential data of a user. Here's another scenario:

A user opens the first page of a website. A session-id is created for him and sent back to his browser.
User goes to the login page and fills in the form and presses the submit button.
The login request has arrived on the server. Username and password are checked against one another and if verified it is mentioned in the session data that this session-id belongs to which user.
From now on, each request that is arrived on server loads the session data containing who this request comes from and it has nothing to do with the database (unless you use the database as session data storage).

This late scenario is called authentication, which means verifying if the requests come from who they are claiming they are coming from. In ordinary cases, once a user is authenticated there's no need to authenticate him again (unless the session is destroyed). As far as authentication goes the only part database use is mandatory is when you want to check the username and password.

Moreover, there's another scenario called authorization. In this scenario, you know who is asking what and the only thing that remains is to check if he is allowed to do it or not. You know who is asking because you have his validated credentials in session data loaded with incoming request's session-id. Authorization can be categorized into two types. First, you can check and see if the user is allowed to perform the requested action. Second, you may want to go further and check and see if the user is allowed to do the requested action on the requested data. The first type is the purpose of libraries called ACL (Access Control List) and second ones are usually implemented within each project individually.

ACL is a function (to put it simply) which takes in the requester user and the requested action (called resource) and returns a boolean indicating if the user is allowed to do the action or not. To be precise, resources can be compound (like Files_Delete or Files_Read). The ACL function requires instruction on who can do what. Most developers load this data from a permanent storage (like a database) as the user is authenticated and store it in session data to prevent reloading it from the database. This is possible since ACL data is not that big and it is possible to store it in session. So usually authorizing using ACL requires no database access either (after they are created).

The only discussion left is for when you want to check if the requested action on the requested data is allowed for the requester. Usually here by data, we mean records of the database and usually, there are a lot of them. So it is illogical to store this huge amount of data anywhere other than the database itself. And since it is already in the database there's no tool more suitable better than SQL to query who can do what on which record. This is where you need to access database for verifying user request's authorization. But in all other scenarios session data is sufficient.

In conclusion, in all of the scenarios explained, there's only one that requires database access. Others can be done using session data only.

thanks for the in-depth-view. your answer seems logical to me. — John Brunner, Jun 11 '13 at 12:15

score 1 · Answer 2 · answered Jun 06 '13 at 09:00

Checking if a user is logged in via the session is perfectly acceptable, and is how most do it anyway. In your login code, when the user successfully authenticates against the database, you can set a flag in the session to say that the user is logged in. On subsequent pages, you check this flag before showing protected content for example.

// username and password match against the db
$_SESSION['user'] = array(
    'username' => 'xxx',
    'userId' => 'xxx',
    'loggedIn' => true;
);

Check if they're logged in:

if(isset($_SESSION['user']) && $_SESSION['user']['loggedIn'])
{
    // good to show protected content
}

Doing this does not necessarily mean you have to dump all user details into the session, you can still do live lookups for that.

hek2mgl · Answer 3 · 2013-06-06T09:36:46.960

While fetching session data from a local file might be a little bit faster, using a database is better for scalability.

Imagine a scenario where you use a load balancer to separate traffic to multiple instances of your web server. If data is stored in a database (or cluster) the load balancer doesn't need to work worry about which web server contains the session data for a certain request. All servers have them. This makes things much easier.

Btw, you can easily switch between two methods using session_set_save_handler()

score 0 · Answer 4 · answered Jun 06 '13 at 08:58

If your objective is only to check that the user is logged in or not, and if that 99% safety (according to you) is adequate, then sure, you can do this. However, the surname check you mention is not possible without loading from an external resource (or an internal map, but that is unlikely).

Typically, you do more than just check if the user is logged in or not. You load the user's profile, roles, permissions, etc... You will have to hit the database anyway for this. In most cases, it is better to load the user while checking the session and cache the info for the rest of the page request so that you don't hit the database too often.

Or, use something like APC user cache store or Memcache in case of distributed environment.

score 0 · Answer 5 · edited May 23 '17 at 11:57

Your session data is stored in the server at a location defined by session.save_path in the php.ini file or at run time using session_save_path the session data stored in the server is stored in a serialize text which is readable by human if they have access to the server.

In order for someone else to hijack a users session they need to know the session_id. You can check How unique is the php session id for more information about the session id uniqueness.

To answer your original question, Yes it's best to use the session variable to test to see if the user has been authenticated or not. This can simply be done by checking an existence of a session variable like isset($_SESSION['auth_id']). Be sure to session_destroy() and session_regenerateid()` when the user logs out to destroy the session data.

score 0 · Answer 6 · answered Jun 06 '13 at 08:59

When you're performing the login you could assign some values to the session.

<?php
// this should go right at the top of all pages using the session
session_start();

// other code

// your login procedure
if (login_condition == true)
{
    $_SESSION['logged_in'] = true;
    // other session values you want to store
}

Then, in your login only pages you can do this

<?php
session_start();

if ( ! isset($_SESSION['logged_in']))
{
    header("Location: /path/to/login.php\r\n");
    exit;
}

As mentioned in the comments, what if you need to delete the user while they're on the session? You will still need some way to check the session against the database.

Of course if this is not a concern use something similar to the above :)

Check if user is logged in with session instead of database query

6 Answers6