通过检查时间来锁定用户帐户

I'm trying to get my login system to check if 30 min have past time the last login attempt; However my issue is that when I check I want to let the user know how long is left until this ban will be removed. I was wondering how I would acheive this? I am using timestamps in the login_attempts database, with two rows user_id and time

我正在尝试让我的登录系统检查上次登录尝试是否有30分钟过去的时间;然而,我的问题是,当我检查时,我想让用户知道在此禁令被删除之前需要多长时间。我想知道如何实现这个目标?我在login_attempts数据库中使用时间戳,有两行user_id和time

Also in my users table I have a field called locked as once the account has been tried to login more than 5 times I will set the account to locked and require the user to unlock the account via email.

同样在我的用户表中,我有一个名为locked的字段,因为一旦尝试登录帐户超过5次,我将帐户设置为已锁定,并要求用户通过电子邮件解锁帐户。

I will leave the appropriate code below...

我将在下面留下适当的代码......

//CHANGE TO PDO ONCE WORKING.
function time_left($user_id) {
    $last_timestamp = mysql_result(mysql_query("SELECT `time` FROM `login_attempts` WHERE `user_id` = '$user_id' LIMIT 1"), 0, 'time');

    //$now = time();
    //$valid_attempts = $last_timestamp - $now;

    //return date('i', $last_timestamp);
    return $last_timestamp;
}
function login_attempts($user_id) {
    $now = time();
    $valid_attempts = $now - (30 * 60);

    return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `login_attempts` WHERE `user_id` = '$user_id' AND `time` > '$valid_attempts'"), 0);
}

    //Temp code update to PDO after working...
    if (user_locked($email) === true) {
        $user_id = user_id_from_email($email);
        $time_left = time_left($user_id);
        $errors[] = "This account has been temporarily locked, try again in $time_left minutes.";
    } else if (user_active($email) === false) {
        $errors[] = "Your account hasn't been activated yet.";
    } else {
        $login = login($email, $password);
        if ($login === false) {
            if(login_attempts($user_id) > 3) {
                $user_id = user_id_from_email($email);
                $time_left = time_left($user_id);
                $errors[] = "This account has been temporarily locked, try again in $time_left minutes.";
                //mysql_query("UPDATE `users` SET `locked` = '1' WHERE `user_id` = $user_id");
            } else {
                $errors[] = "Your password was incorrect.";
                mysql_query("INSERT INTO `login_attempts` (`user_id`, `time`) VALUES ('".$user_id."', '".time()."')");
            }
        } else {
            $_SESSION['user_id'] = $login;
            header("Location: ".APP_URL);
            exit();
        }
    }
}

If anyone could help me I would be really grateful, thanks!

如果有人能帮助我,我将非常感激,谢谢!

2 个解决方案

#1

I'm not sure I understand your question completely, but let's try:

我不确定我完全理解你的问题,但让我们试试:

 function time_left($user_id) {
    // Mind the ORDER BY
    $last_timestamp = mysql_result(mysql_query("SELECT `time` FROM `login_attempts` WHERE `user_id` = '$user_id' ORDER BY `time` DESC LIMIT 1"), 0, 'time');

    // Added constant to store "30 minutes"
    $remaining_seconds = $last_timestamp + LOGIN_COOLDOWN_PERIOD - time();

    // If you want to format result in minutes
    return round($remaining_seconds / 60);
}

Also, make sure to sanitize user input when using it in sql queries http://php.net/manual/en/security.database.sql-injection.php

此外,在sql查询中使用它时,请务必清理用户输入http://php.net/manual/en/security.database.sql-injection.php

#2

As requested, an example event with some additional bits n pieces. The event runs every minute and checks the login_attempts table for any user that is listed - for those records where the current time is greater than the lockout time the record is deleted if the account hasn't been totally locked.

根据要求,一个示例事件有一些额外的位n件。事件每分钟运行一次,并检查login_attempts表中列出的任何用户 - 对于当前时间大于锁定时间的记录,如果帐户尚未完全锁定,则删除记录。

basic sql event example
-----------------------

create event `evtchecklockouts`
    on schedule
        every 1 minute starts '2015-12-06 16:00:00'
    on completion preserve
    enable
    comment 'Every minute check for accounts that can be removed from the login_attempts. If any have more than 5 attempts, permanently lock account'
    do 
    begin
        declare cnt int default 5;

        /* Lock accounts for a hour,day,week,year etc if there are more than `cnt` attempts */
        update `login_attempts` set `locked`=1, `time`=timestampadd( year, 1, `time` ) where `count` >= cnt;

        /* delete records that are now ok */
        delete from `login_attempts` where `time` < now() and `locked`=0;
end


/* example table */
create table `login_attempts` (
    `id` int(10) unsigned not null auto_increment,
    `user_id` varchar(50) not null,
    `time` timestamp not null default current_timestamp,
    `count` tinyint(1) unsigned not null default '0',
    `locked` tinyint(1) unsigned not null default '0',
    primary key (`id`),
    unique index `user_id` (`user_id`)
)engine=innodb;

/* some dummy data */
insert into `login_attempts` (`id`, `user_id`, `time`, `count`, `locked`) values
    (36, 'cbf439dd2002a149d69818f16e5b7e48', '2015-12-06 16:39:16', 3, 0),
    (75, 'a0ff21a097c9e9c7e331dd67562bae9b', '2015-12-06 16:28:16', 1, 0),
    (76, '973bae9123afbdac6aa4de65b61c6c0c', '2015-12-06 16:48:30', 1, 0);





$uid='cbf439dd2002a149d69818f16e5b7e48';

function time_left( $uid ){
    $sql="select `user_id`, `count`, timestampdiff( minute, now(), `time` ) as 'timeleft' from `login_attempts` where `user_id`='{$uid}';";
    $res=mysql_query( $sql );
    $out=new StdClass;
    while( $rs=mysql_fetch_object( $res ) ){
        $out->uid=$rs->user_id;
        $out->count=$rs->count;
        $out->timeleft=$rs->timeleft;
    }
    return $out;
}

$res=calluser_func('time_left', $uid );

echo $res->uid.' attempts:'.$res->count.' Locked out for:'.$res->timeleft.'minutes';

#1