Sessions login - Destory session if user no longer exists in database

Question

Problem Explanation

I have created my first session's login. Basically I have 3 inputs, 2 fields, one submit.

First field will be the username, second will be the password.

And then I used PDO to SELECT username,password from users table WHERE = username, to validate the details.

Then I count the rows, to see if there is a row with that specify username & password.

Great, I am now logged in..

But there's the problem.. When I delete the user from the database, I can keep browsing in my system with the same user, until session ends or I log out.

Question

How can I instantly check if user is still in the database? What are the best ways to do so everytime user gets to a new page or refreshes?

Thanks!

The code for creating session (login validation):

        # Selecting the entered username + password from our admins database & making sure field
        # with that specify username and password exists.
        $hashed = hash('sha512', md5($password));           
        $check = $CONNECT_TO_DATABASE->prepare("SELECT * FROM admin WHERE username = :username AND password = :password LIMIT 1");
        $check->bindValue(':username', $username);
        $check->bindValue(':password', $hashed);
        $check->execute();  

        # We check if that row exists, if it exists - We will create a new session with that entered
        # username, that means administrator has sucessfuly logged in.

        if ($check->rowCount()) {
            $_SESSION['user'] = $username;
            header ('Location: index.php');
        } else {

        # If login failed, because the details are wrong, we will store the error message into
        # our errors array and then use a loop to fetch the error.
            if (!empty($username) && !empty($password)) {
                $errors[] = 'Wrong username or password.';
            }
        }

and then I use this to see if user is logged in:

if (isset($_SESSION['user'])) {

Answer 1

You'll have to write a checking algorithm that will be run on every page. This algorithm will perform a query in the database to check if the user still exists (remember that you can't trust user input, and sessions are such). If the query results are negative then you'll call session_destroy().

Depending on your application design and patterns you are following, you can either include a validate.php file in all pages or call a specific AuthHelper class in the controller's constructor that will do that for you.

I'll stress this out a little bit more: you cannot trust session as they are user input. A solution that will automatically check if the user exists is the following algorithm:

1. When the user is logged in you store a login key (random alphanumeric value) in both the session and the user row in the database.
2. On every protected page of your website you check if a row with the session's login key exists in the database. If it does not than either the user doesn't exists any more or the session login key has been corrupted. In both cases you want to destroy the session ang log the user out.

Answer 2

If this is a concern, don't use sessions. Sounds like you need to check the DB every time you perform an action to validate that the user still exists, so you might as well pull their data each time. This will probably start an argument over load, but you can cache things that need to be cached and re-pull crucial pieces and validate others.

This will also help you if you ever have to scale, so you won't have to worry about session persistence.

Answer 3

You literally passed and stored userinfo to session so you can use the $_SESSION variables you stored. Then Use a SELECT statement to execute to your database to check if the stored user in the session if still on your database then if not execute a condition to destroy session and to log them out.

example.

$sql='SELECT * FROM USERS WHERE ID='.$_SESSION['user_id'].'';
$result = mysql_query($sql);

if(count($result)>0){
    echo 'User at database';

}
else{
   echo 'User not at database';
   session_destroy();
   // then redirect to homepage or login page.
}