How to code the forgot password functions when the registration details are stored in two separate tables?

Question

I have a forgot password form with an email field. When a user enters an email and clicks the "receive reset link" button, it should generate a token and token expiration date.

The email and token details are in two separate tables called users and persistences respectively. The persistences table has a FOREIGN KEY named user_id.

This is what the two tables look like:

CREATE TABLE IF NOT EXISTS `users` (   
   `id` int(11) NOT NULL AUTO_INCREMENT,   
   `email` varchar(255) NOT NULL,   
   `password` varchar(255) NOT NULL,   
   `token` varchar(255) DEFAULT NULL,   
   `expires_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`id`) 
);

CREATE TABLE IF NOT EXISTS `persistences` (   
   `id` int(11) NOT NULL AUTO_INCREMENT,   
   `user_id` int(11) DEFAULT NULL,   
   `token` varchar(255) DEFAULT NULL,   
   `expires_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   
   PRIMARY KEY (`id`),   
   FOREIGN KEY (`user_id`) References users(`id`) 
);

I need to know how to write the code for the following functions.

forgot_password()
reset_password()
update new password()

It's hard for me to figure it out because the email and token details are stored in two separate tables.

Here's what I coded so far. I managed to get the user's id from the users table and insert it into the persistences table.

// register new user 
public function register($fname, $lname, $email, $password)  
{         
    $sql = "INSERT INTO users (first_name, last_name, email, password) VALUES (:fname, :lname, :email, :password)"; 
    $stmt = $this->conn->prepare($sql);         
    $stmt->execute(["fname" => $fname, "lname" => $lname, "email" => $email, "password" => $password]); 
    $new_user_id = $this->conn->lastInsertId();                  
    
    $sql2 = "INSERT INTO persistences (user_id) VALUES (:user_id)";         
    $stmt2 = $this->conn->prepare($sql2);         
    $stmt2->execute(["user_id" => $new_user_id]);          
    return true;     
}

Here's what the forgot_password(), reset_password(), and update_new_password() functions would look like if the email and token details were stored in a single users table (instead of two separate tables).

//forgot password 
public function forgot_password($token, $email)     
{         
    $sql = "UPDATE users SET token = :token, expires_at = DATE_ADD(NOW(), INTERVAL 10 MINUTE) WHERE email = :email";         
    $stmt = $this->conn->prepare($sql);         
    $stmt->execute(['token' => $token, 'email' => $email]);           
    return true;     
}      

//reset password 
public function reset_pass_auth($email, $token)     
{         
    $sql = "SELECT id FROM users WHERE email = :email AND token = :token AND token != '' AND expires_at > NOW() AND deleted != 0";         
    $stmt = $this->conn->prepare($sql);         
    $stmt->execute(['email' => $email, 'token' => $token]);          
    $row = $stmt->fetch(PDO::FETCH_ASSOC);          
    return $row;     
}      

//update new password  
public function update_new_pass($password, $email)     
{         
    $sql = "UPDATE users SET token = '', password = :password WHERE email = :email AND deleted != 0";          
    $stmt = $this->conn->prepare($sql);         
    $stmt->execute(['password' => $password, 'email' => $email]);          
    return true;     
}

How should this code be changed when there are two separate tables storing the email and token details?

I'm using PHP PDO and MySQL.

Why does it matter that they're different tables? Just join the tables using the foreign key. — Barmar
Honestly, I don't see how this question is different from the previous one — Your Common Sense
Please do not repost questions. I don't even understand why you decided to repost it. — Dharman

SIR SIR · Accepted Answer · 2022-04-21T17:05:13

Seems like mixing two tables. Do you need all of the columns?

Try to split the functionality first:

Create a table where all needed information about the user is stored (email, password, id)
A user can now be logged in. On a registration, you fill only information into this table.
Create a reset-password functionality, using token, using new table (user_id, token, expiration_at).
If a user submits a form to reset his/her pass (filling an email), you firstly get the user's id based on his email (from first table). Then you check that the user has not requested password reset yet. After that, you generate a token and insert a new record into the new table (incl. expiration timestamp). You might send an email to the user about the request including the token and an information when the token is valid until.

If the user uses the token, you verify the token (and if an email is provided, you verify the email based on the user_id as well) from the second table. If all matches, and the expiration is still valid, you save the inserted password by the user to the first table (now you can get the ID of the user by the token - simple select form second table)

On change password request you do not need to use the second table at all. simple verify that the "old password" matches the current password, and if so, save the new password into the first table. (crypted ofc)

How to code the forgot password functions when the registration details are stored in two separate tables?

1 Answers