I don't know if it's the best solution to my problem, but it's what I'm trying to do at the moment. If you have a better solution, I would be grateful if you could help me. Let's get to the problem:

I'm creating a system to change the password for the user who forgot the password, sending a temporary link to his email, containing a key in the url to allow the password change (as if it were a token).

The problem is with the temporary part. I already restrict the user to send only one email to recover password, check this key to see if it's valid... However, I don't know how to make the link expire.

In my table, I have a column called "recover_password", which creates the token, and a column "expiration", which inserts the date the email was sent, and with it I wanted to create an event to clear the column "recover_password" and the column "expiration", after 15 minutes. However, I don't know how to create this event through PHP for MySQL.

Is there a better method or do I just have to learn how to create this event?

For now, I have the following PHP code:

<?php
 use PHPMailer\PHPMailer\PHPMailer;
 use PHPMailer\PHPMailer\Exception;

 require 'lib/vendor/phpmailer/phpmailer/src/Exception.php';
 require 'lib/vendor/phpmailer/phpmailer/src/PHPMailer.php';
 require 'lib/vendor/phpmailer/phpmailer/src/SMTP.php';
    if(isset($email_esqueceu_senha)){

        $query = "SELECT * FROM login WHERE email=:email";
        $enviar = $conn->prepare($query);
        $enviar->bindParam(':email', $email_esqueceu_senha, PDO::PARAM_STR);
        $enviar->execute();

        if($enviar->rowCount()){
            $enviar = $enviar->fetch(PDO::FETCH_ASSOC);
            if($enviar['recuperar_senha'] !== ''){

                $erro_enviar_email = "<span class='errologin mt-3'>E-mail já enviado. Por favor, verifique sua caixa de e-mail!</span>";

            }else{
            $token = password_hash($enviar['id'], PASSWORD_DEFAULT).password_hash($enviar['senha'], PASSWORD_DEFAULT);
            
            $query_recuperar_senha = "UPDATE login SET recuperar_senha=:recuperar_senha, expiracao=NOW() WHERE id=:id LIMIT 1";
            $recuperar_senha = $conn->prepare($query_recuperar_senha);
            $recuperar_senha->bindParam(':recuperar_senha', $token, PDO::PARAM_STR);
            $recuperar_senha->bindParam(':id', $enviar['id'], PDO::PARAM_STR);

            if($recuperar_senha->execute()){

                $query_expiracao = "CREATE EVENT deletar_recuperar_senha ON SCHEDULE EVERY 15 MINUTE STARTS CURRENT_TIMESTAMP   INTERVAL 3 SECOND DO UPDATE login SET recuperar_senha='', expiracao='' WHERE expiracao= NOW()   15 MINUTE AND id=:id";
                $expiracao = $conn->prepare($query_expiracao);
                $expiracao->bindParam(':id', $enviar['id'], PDO::PARAM_INT);
                
                if($expiracao->execute()){

                }else{
                    die("<script>
                    window.alert('Erro no evento!');
                    setTimeout(function(){
                        
                        window.history.go(-1);
                    }, 10000000);
                </script>");
                }
                
                $query_smtp = "SELECT * FROM smtp";
                $smtp = $conn->prepare($query_smtp);
                $smtp->execute();
                
                while($smtp_banco = $smtp->fetch(PDO::FETCH_ASSOC)){
                
                if($smtp_banco['autenticacao'] == '1'){
                    $auth = 'TRUE';
                }else{
                    $auth = 'FALSE';
                }
            
            
                if($smtp_banco['seguranca'] == 'tls'){
                    $seg = 'PHPMailer::ENCRYPTION_STARTTLS';
                }elseif($seguranca == 'ssl'){
                    $seg = 'PHPMailer::ENCRYPTION_SMTPS;';
                }
                
                $mail = new PHPMailer();
                $mail->IsSMTP();
                $mail->Mailer = "smtp";
            
                $mail->SMTPDebug = 0;  
                $mail->SMTPAuth = $auth;
                $mail->CharSet = 'UTF-8';
                $mail->Port = $smtp_banco['porta'];
                $mail->SMTPSecure = $seg;
                $mail->Host = $smtp_banco['endereco_smtp'];
                $mail->Username  = $smtp_banco['usuario_autenticacao'];
                $mail->Password  = $smtp_banco['senha_autenticacao'];
                
                $mail->IsHTML(true); 
                $mail->AddAddress($email_esqueceu_senha); 
                $mail->SetFrom($smtp_banco['endereco_envio'], $smtp_banco['nome_remetente']); 
                $mail->Subject = "Atualizar senha"; 
                $content = "Segue o link para a atualização de senha do Sistema de Controle Copa Studio(<strong>ESTE LINK É ÚNICO E SÓ PODERÁ SER ULTILIZADO UMA ÚNICA VEZ</strong>:<br><br>
                http://localhost/sistema/sistemadecontrole/atualizar_senha.php?token=".$token;
            
                $mail->MsgHTML($content); 
                if(!$mail->Send()) {
                echo "<script>
                window.alert('E-mail não enviado!');
                setTimeout(function(){
                    
                    window.history.go(-1);
                }, 10000000);
            </script>";
                }else{
                    echo "<script>
                window.alert('Verifique sua caixa de e-mail para continuar. (Lembrando que o e-mail pode ir para caixa de Spam)');
                setTimeout(function(){
                    
                    window.history.go(-1);
                }, 0);
            </script>";
                }
            }
            }else{
                $erroemail_esqueceu_senha = "Erro ao criar token. Por favor, entre em contato com a equipe de T.I.";
            }

        }}else{
            $erroemail_esqueceu_senha = "Email não cadastrado";
        }
    }
?>

CodePudding user response：

One way to implement this would be to not remove the data from the column, but to validate the time instead.

When a user clicks on the recovery link, you check if the token is correct, and if the current time is still within 15 minutes.

This way you don't have to clear anything.

$recoveryTime = strtotime($expiration);
if (time() - $recoveryTime < 15 * 60) {
    // Less than 15 minutes ago
}