mySQL Database - Reset certain column to '0' every 15 minutes

Question

Okay so if I have a login database:

id | email | pass | attempts
----------------------------
 1 |  ""   |  ""  |    4
 2 |  ""   |  ""  |    2

Attempts marks the number of attempts made at logging in, per user. So user 1 has attempted to log in 4 times and user 2 has attempted to log in 2 times.

I was wondering if there was a method* that I could add to my login database, which would reset all user attempts to 0? So after the reset, the table would look like this:

id | email | pass | attempts
----------------------------
 1 |  ""   |  ""  |    0
 2 |  ""   |  ""  |    0

Thanks !

EDIT: from SQL Trigger to Method

Why a trigger? When are the attempts values supposed to be reset? — jarlh, Apr 07 '16 at 12:38
Don't know why a trigger, just thought there might be a way to use a trigger. It doesn't have to be! I'd want to set the 'attempts' value to reset to 0 in each row, every 15 minutes. — J. Doe, Apr 07 '16 at 12:40

score 0 · Answer 1 · answered Apr 07 '16 at 13:03

0

You can use EVENT as described here

e.g.

CREATE EVENT clear_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 15 MINUTE
DO
  UPDATE myschema.the_table SET attempts = 0;

answered Apr 07 '16 at 13:03

StanislavL

56,971
9
68
98

score 0 · Answer 2 · edited May 23 '17 at 11:50

Is the aim of this to prevent a brute force attack? If so there are better ways than what you are proposing.

This StackOverflow post is very informative about ways to stop it, and mentions that you could do a ask a 'simple' question, to ensure that the user isn't a robot.

However, if you don't want something like that and you're more set on logging how many times someone has entered incorrect details, then in your PHP login script you could replicate something like the following.

if($UserInput == $Password){
//let the user in, they're correct
}
else{
//Relay Incorrect password text
    if($i > 4) //So 5 tries at an incorrect password
    {
        //Disable the button for x unix time
    }
    else
    {
        $i++; //Add one to the count
    }
}

mySQL Database - Reset certain column to '0' every 15 minutes

2 Answers2