MySQL how to make value expire?

Question

So I'm currently designing a Forgot Password feature for a website. Basically, when the user clicks forgot password it sends them an email with a reset token. I want the reset token to expire in 48 hours(for security reasons). How would I do this in MySQL and PHP. I have a column in my table called resetkey and when the 48 hours is up I want the system to clear the resetkey/token associated with the user's username.

Thanks!

Query the table for the expiration date when they try to use the key. If it is older than 48 hours (or whatever time you set) throw the proper error or have them ask for another reset. — Jay Blanchard, Mar 23 '15 at 21:03

O. Jones · Accepted Answer · 2020-06-29T09:56:22.350

Next to your resetkey column place a DATETIME column called, maybe, expires.

Then, whenever you insert a new reset key, also insert a value into expires:

INSERT INTO forgot (resetkey, expires) VALUES (whatever, NOW() + INTERVAL 48 HOUR)

Right before you read any reset key from the table, do this:

DELETE FROM forgot WHERE expires < NOW()

Then you'll never see an expired key; they'll always get wiped out if they have expired.

Now, you could choose to do something with looking up a user-furnished reset key. If it's expired you could announce that to the user: "Your reset key has expired." But that's a bad idea ... for security's sake you shouldn't help users understand why a security token like a reset key is invalid. You should just say "that reset key is not correct."

Does this leave open the possibility that some rows containing expired token will persist in the table? Yes. But it won't be possible for your app to actually read them and used them if you follow the procedure of wiping out the expired ones before using any tokens. If you had a reason to avoid keeping expired tokens in the table even though they're useless, you could set up an EVENT or some other kind of regularly scheduled job to run the DELETE statement I mentioned.

Wont this solution keep the tokens when userbase is small and password is not reset for long time , it seems that it isn't covering the edge cases? — Anas khan, Jun 29 '20 at 05:06
It covers the edge case you mention just fine. Please see my edit. — O. Jones, Jun 29 '20 at 09:56

score 0 · Answer 2 · answered Mar 23 '15 at 21:09

In addition to your column resetkey you will also have a column resetDeadline. When they get a reset token you will set the deadline for some datetime in the future. When they try to log in with the resetkey always check that NOW() < resetDeadline. If it is then check the resetkey and allow the reset. If it isn't then don't bother checking the resetkey - just don't allow it.

score 0 · Answer 3 · answered Sep 24 '20 at 18:38

I'd use MySQL Events for that:

CREATE EVENT IF NOT EXISTS `dbName`.`eventName`
ON SCHEDULE
EVERY 1 DAY // or 1 HOUR
COMMENT 'Description'
DO
BEGIN

DELETE FROM `dbName`.`TableName` WHERE `expireDateCol` < NOW();

END

and enable the MySQL Event Scheduler , see original source.

source: https://stackoverflow.com/a/24568566/2069181

MySQL how to make value expire?

3 Answers3

Linked