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!
Advertisement
Answer
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.