How to design a secure MySQL table structure to implement password reset function?

How to design a secure MySQL table structure to implement the password reset function?

In modern software development, the password reset function of user accounts has become more and more important, because the risk of password leakage of user accounts also increases. In order to protect user privacy and data security, developers need to design a safe and reliable MySQL table structure to implement the password reset function.

The following is a feasible MySQL table structure design that can help implement the password reset function:

Table name: password_reset

##idINT(11)Primary key, auto-incrementuser_idINT(11)Foreign key associated with the user tabletokenVARCHAR(255)Unique identifier for resetting the password, using a randomly generated stringexpiration_timeTIMESTAMPValidity period of reset password linkcreation_timeTIMESTAMPRecord creation time

Column name	Type	Description

The steps to implement the password reset function are as follows:

Create the above table structure.

1. CREATE TABLE password_reset (
     id INT(11) NOT NULL AUTO_INCREMENT,
     user_id INT(11) NOT NULL,
     token VARCHAR(255) NOT NULL,
     expiration_time TIMESTAMP NOT NULL,
     creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY (id),
     FOREIGN KEY (user_id) REFERENCES users(id)
   );

When the user requests a password reset, generate a unique token and insert the relevant information into the password_reset table.

function generateToken() {
    $length = 32;
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $token = '';
    
    for ($i = 0; $i < $length; $i++) {
        $token .= $characters[rand(0, strlen($characters) - 1)];
    }
    
    return $token;
}

$userId = 123; // 用户的ID
$expirationTime = date('Y-m-d H:i:s', strtotime('+1 day')); // 链接的有效期为一天
$token = generateToken();

$query = "INSERT INTO password_reset (user_id, token, expiration_time) VALUES ($userId, '$token', '$expirationTime')";

// 执行SQL插入操作

Send the generated token to the user, and the user resets their password by clicking the link containing the token.
When the user clicks the link, check whether the token in the link exists and is valid in the password_reset table.

$token = $_GET['token']; // 从URL参数中获取token

$query = "SELECT * FROM password_reset WHERE token = '$token' AND expiration_time >= NOW()";
// 执行SQL查询操作

If the token is valid, the password reset form is displayed to the user, and the user enters the new password and submits the form.
After the password reset form is submitted, update the user's password in the user table.

$newPassword = $_POST['new_password']; // 从表单中获取新密码
$hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT); // 新密码进行哈希处理

$query = "UPDATE users SET password = '$hashedPassword' WHERE id = $userId";
// 执行SQL更新操作

At this time, delete the relevant data of the user in the password_reset table.

$query = "DELETE FROM password_reset WHERE user_id = $userId";
// 执行SQL删除操作

Through the above steps of MySQL table structure design and code examples, a secure password reset function can be implemented, which provides protection for the password security of user accounts. Of course, security is an ongoing process, and developers should also consider other security safeguards, such as proper input validation, use of HTTPS, etc.

The above is the detailed content of How to design a secure MySQL table structure to implement password reset function?. For more information, please follow other related articles on the PHP Chinese website!