Updating a record on login with PHP


#1

I have a simple login system that I am trying to develop further by adding the ability to store the last login time of a given user. It appears I can't quite get my update query correct.

The code i am using in PDO is as follows;

<?php 

           
    // This variable will be used to re-display the user's username to them in the 
    // login form if they fail to enter the correct password.  It is initialized here 
    // to an empty value, which will be shown if the user has not submitted the form. 
    $submitted_username = ''; 
   

    // This if statement checks to determine whether the login form has been submitted 
    // If it has, then the login code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // This query retreives the user's information from the database using 
        // their username. 
        $query = " 
            SELECT 
                id, 
                username, 
                password, 
                salt, 
                email,
				user_type
            FROM users 
            WHERE 
                username = :username 
        "; 
         
        // The parameter values 
        $query_params = array( 
            ':username' => $_POST['username'] 
        ); 
         
        try 
        { 
            // Execute the query against the database 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        } 
         
        // This variable tells us whether the user has successfully logged in or not. 
        // We initialize it to false, assuming they have not. 
        // If we determine that they have entered the right details, then we switch it to true. 
        $login_ok = false; 
         
        // Retrieve the user data from the database.  If $row is false, then the username 
        // they entered is not registered. 
        $row = $stmt->fetch(); 
        if($row) 
        { 
            // Using the password submitted by the user and the salt stored in the database, 
            // we now check to see whether the passwords match by hashing the submitted password 
            // and comparing it to the hashed version already stored in the database. 
            $check_password = hash('sha256', $_POST['password'] . $row['salt']); 
            for($round = 0; $round < 65536; $round++) 
            { 
                $check_password = hash('sha256', $check_password . $row['salt']); 
            } 
             
            if($check_password === $row['password']) 
            { 
                // If they do, then we flip this to true 
                $login_ok = true; 
            } 
        } 
         
        // If the user logged in successfully, then we send them to the private members-only page 
        // Otherwise, we display a login failed message and show the login form again 
        if($login_ok) 
        { 
            // Here I am preparing to store the $row array into the $_SESSION by 
            // removing the salt and password values from it.  Although $_SESSION is 
            // stored on the server-side, there is no reason to store sensitive values 
            // in it unless you have to.  Thus, it is best practice to remove these 
            // sensitive values first. 
            unset($row['salt']); 
            unset($row['password']); 
             
            // This stores the user's data into the session at the index 'user'. 
            // We will check this index on the private members-only page to determine whether 
            // or not the user is logged in.  We can also use it to retrieve 
            // the user's details. 
            $_SESSION['user'] = $row; 
             
            // Redirect the user to the private members-only page. 
            header("Location: home.php"); 
            die("Redirecting to: home.php"); 
        } 
        else 
        { 
            // Tell the user they failed 
            print("Login Failed."); 
             
            // Show them their username again so all they have to do is enter a new 
            // password.  The use of htmlentities prevents XSS attacks.  You should 
            // always use htmlentities on user submitted values before displaying them 
            // to any users (including the user that submitted them).  For more information: 
            // http://en.wikipedia.org/wiki/XSS_attack 
            $submitted_username = htmlentities($_POST['username'], ENT_QUOTES, 'UTF-8'); 
        } 
    } 
     
?>

Ideally I'd like the user to be logged in and then just before they are directed to 'members only' page the current date/time logged as a timestamp in the MySQL DB.

How can I update the record relating to their user ID with this data?


#2

Well you'd need to add a last_login column in your DB and then upon login_ok, update last_login with current timestamp.

So set last_login as a timestamp and the value to update is 'NOW()' (if i'm not mistaken).


#3

Thanks for the reply. That is what I have been trying to do, but can't seem to construct the query correctly.

The only success I have had is inserting a new record with no data, but not matching an existing an id and then updating the last login column.

The query I constructed that inserts a blank record is;

// query
            $sql = "REPLACE INTO users(user_lastlogin) VALUES('$user_lastlogin')";
            $q = $db->prepare($sql);
            $q->execute(array($user_lastlogin));

But have also tried - which writes nothing to the DB;

$lastlogintime = date("Y-m-d H:i:s");

            // query
            $sql = "REPLACE INTO users(user_lastlogin) VALUES('$lastlogintime'";
            $q = $db->prepare($sql);
            $q->execute(array($lastlogintime));

#4

And the following code is sucessfully updating the necessary record but is clearing all existing data and just writing the user_lastlogin value.

            $lastlogintime = date("Y-m-d H:i:s");
            $id = $_SESSION['user']['id'];

            // query
            $sql = "REPLACE INTO users(id,username,password,salt,email,created,user_type,user_lastlogin) VALUES('$id','$username','$password','$salt','$email','$created','$user_type','$lastlogintime')";
            $q = $db->prepare($sql);
            $q->execute(array($id,$username,$password,$salt,$email,$created,$user_type,$lastlogintime));

#5

I'd have done something like UPDATE users SET user_lastlogin = :lastlogintime WHERE username = :username

You need to link that lastlogintime to the specific user trying to log in.