mysql用户注册登录界面

-- 示例代码：使用SHA-256和加盐存储密码
DELIMITER //
CREATE FUNCTION hash_password(plaintext_password VARCHAR(255), salt VARCHAR(255))
RETURNS CHAR(64)
DETERMINISTIC
BEGIN
    DECLARE hashed_password CHAR(64);
    SET hashed_password = SHA2(CONCAT(plaintext_password, salt), 256);
    RETURN hashed_password;
END //
DELIMITER ;

INSERT INTO users (username, password_hash, salt)
VALUES ('user1', hash_password('password123', 'random_salt'), 'random_salt');

-- 示例代码：使用预处理语句防止SQL注入
SET @username = 'user1';
SET @password = 'password123';

PREPARE stmt FROM 'SELECT * FROM users WHERE username = ? AND password_hash = hash_password(?, ?)';
EXECUTE stmt USING @username, @password, 'random_salt';
DEALLOCATE PREPARE stmt;

-- 示例代码：记录登录失败次数并锁定账户
CREATE TABLE login_attempts (
    user_id INT,
    attempts INT DEFAULT 0,
    locked_until DATETIME
);

DELIMITER //
CREATE TRIGGER after_login_attempt
AFTER INSERT ON login_attempts
FOR EACH ROW
BEGIN
    IF NEW.attempts >= 5 THEN
        UPDATE users SET locked_until = NOW() + INTERVAL 30 MINUTE WHERE id = NEW.user_id;
    END IF;
END //
DELIMITER ;