Auto delete from Mysql database

Question

I am currently trying to create a 'reset password' token. A token that gets generated when someone on my site wants to reset their password. Currently, once the token is created I save it in my database. I want to make the token exist in the database for only 24 hours and then automatically delete itself but I have no idea how to do this. Any help would be appreciated.

I generate the token pretty simply, by creating a string of random characters. Then I save the token and the email of the forgotten password in the DB as follows

'INSERT INTO reset_token (token, email) VALUES ("'.token.'", "'.email.'")'

You could just add `expiration_date` column. After that token will be automatically invalidated (no one can use it) but still in table. — Lukasz Szozda, Oct 20 '17 at 14:52
Possible duplicate of [PHP help with password reset and token expiry](https://stackoverflow.com/questions/3164978/php-help-with-password-reset-and-token-expiry) — Dezza, Oct 20 '17 at 14:54
add `expiration_date` column. then you can use something like `if ($expiration_date < date("Y-m-d")) echo "token is valid!";` — Stranger, Oct 20 '17 at 14:58
Thanks for the help guys, I was able to get it done with the help of some documentation on the MYSQL website https://dev.mysql.com/doc/refman/5.7/en/create-event.html — arthur27, Oct 20 '17 at 15:08

Xpleria · Answer 1 · 2018-04-10T11:13:15.837

Edit

This can be done using the Event Scheduler. You need to create an event using the CREATE EVENT syntax.

To delete an entry you can do the following:

public function schedule_delete($table, $column, $value, $hours = 24) {
    $sql = "CREATE EVENT `delete_{$table}_{$column}_$value` 
                ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL $hours HOUR
                DO DELETE FROM `$table` WHERE `$column` = '$value'";
    $db->query($sql);
}

Note that the name has to be unique. Which means delete_{$table}_{$column}_$value has to be unique, otherwise you will get an error.

For MySQL Events to work, the Event Scheduler has to be ON. You can turn it on using the following query:

SET GLOBAL event_scheduler = ON;

~~When you say "Auto Delete", YOU need to "Auto Delete" it.~~

In cases where you cannot turn on the scheduler because you're on a shared hosting server and you do not have enough privileges, you can manually delete the entries by checking their timestamps.

So somewhere in the code there needs to be a query performing the delete operation. To delete expired entries, you need to know if they are expired. For this you need to store the timestamp along with the token.

Now where you put this, depends on how often you want to delete expired entries. In OP's case, it can be done just before the lines of code where tokens are checked for validity. i.e., if there is a function validate_token that would see if the token exists in the database and is valid, you can put it at the beginning of that function.

function validate_token($token) {
    $expiry = time() - 60*60*24;
    // perform query to delete all tokens that have a timestamp less than $expiry

    // Now run the query to check if the token exists in the database. 
    // You have only valid tokens left!
}

@N.B. I did come across it some time after answering this question. Updated the answer appropriately. — Xpleria, Apr 09 '18 at 08:24

Auto delete from Mysql database

1 Answers1

Edit