php - MySQL - 按时间间隔返回登录尝试

Question

我试图在给定的尝试次数后阻止用户访问。在因为之前有人问过这个问题而停止阅读之前，让我解释一下我的情况(如果已经有人问过，请指出重复的问题)。

我想要什么

我想要实现的是每个间隔的阻塞时间。所以，如果有:

• 5 分钟内尝试 3 次:用户阻塞 5 分钟
• 10 分钟内尝试 5 次:用户阻塞 10 分钟
• 15 分钟内尝试 10 次:用户阻塞 15 分钟

这考虑了哪些电子邮件试图登录以及来自哪个 IP。因此，如果用户尝试从不同的 IP 访问同一个电子邮件帐户，它可以这样做(如果同一个 IP 尝试另一个用户，它也可以这样做)。

拜托，我知道这不是安全问题的最佳解决方案 - 也许 - 但解决问题也会帮助我学习 MySQL，所以我想跳过安全建议并集中于如何实现这一点如果可能，在 MySQL 查询中。

我有什么

我的数据库中有一个包含登录尝试的表:

CREATE TABLE IF NOT EXISTS `er_login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `target_mail` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `attempt_date` datetime NOT NULL,
  `attempt_success` tinyint(4) DEFAULT '1',
  `ip_from` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

所以，我得到了登录尝试日期的记录。每次尝试都会创建一个新的寄存器。这是因为不同的间隔(所以我不能使用“尝试”字段，因为我不知道自上次尝试以来已经过去了多少时间)。

如果尝试最终成功，则此条目的“attempt_success”设置为 1。如果没有，则设置为 0。

我尝试过的

我尝试做的是编写一个 MySQL 查询，返回每个时间间隔的错误尝试次数。然后，我会在 PHP 中使用它并制作一些 IF 来检查每个时间间隔是否已达到限制。

所以，我试过的查询(请不要笑):

SELECT count(cinc.id) AS cinc_minuts, count(deu.id) AS deu_minuts, count(quinze.id) AS quinze_minuts 
    FROM er_login_attempts cinc, er_login_attempts deu, er_login_attempts quinze 
    WHERE 'mail@mail.com' IN (cinc.target_mail, deu.target_mail, quinze.target_mail) 
        AND '1.11.11.111' IN (cinc.ip_from, deu.ip_from, quinze.ip_from) 
        AND cinc.attempt_date BETWEEN MAX(cinc.attempt_date) AND DATE_SUB(MAX(cinc.attempt_date), INTERVAL 5 minutes)  
        AND deu.attempt_date BETWEEN MAX(deu.attempt_date) AND DATE_SUB(MAX(deu.attempt_date), INTERVAL 10 minutes)  
        AND quinze.attempt_date BETWEEN MAX(quinze.attempt_date) AND DATE_SUB(MAX(quinze.attempt_date), INTERVAL 5 minutes)

但是! 我收到“分组依据”错误。我尝试添加“Group By”表达式以按 target_mail、IP、INTERVALS 对其进行分组，但我一次又一次地遇到相同的错误。

但是，如您所见，此查询并未考虑是否已进行了成功的登录尝试，并且如果已成功尝试，则忽略所有较早的错误尝试会很棒 - 但未删除。

有了这一切

我希望您能了解我要实现的目标。再一次，如果之前有人问过我，我会感谢那些为我指明正确方向的人，如果是这样的话，我很抱歉重复这个问题。

如果您需要进一步的信息或者我跳过了一些重要的信息，请告诉我，我会尝试写下丢失的信息。

感谢大家抽出时间!

Answer 1

首先要注意的是不要将 IP 地址存储为字符串 - 它们是使大量数字可读的便捷方式。但是，如果您想开始对地址进行进一步处理(扫描子网中的登录、应用特定国家/地区的验证...)，那么您需要使用 IP 号。

SELECT 
  SUM(IF(secs_ago<=300, 1, 0)) as attempts_in_last_5,
  SUM(IF(secs_ago<=600, 1, 0)) as attempts_in_last_10,
  SUM(IF(secs_ago<=900, 1, 0)) as attempts_in_last_15
FROM
(
  SELECT
    UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(attempt_date) AS secs_ago
  FROM er_login_attempts
  WHERE target_email='mail@mail.com'
  AND INET_ATON('1.11.11.111')=ip_from
  AND attempt_date>NOW() - INTERVAL 15 MINUTES
) ilv;

....并且您的主键对于您所描述的目的几乎没有用 - 但您确实需要一个关于 attempt_date、target_email 和 ip_from 的索引(很可能按该顺序)。

这需要一些工作来适应阻塞期 - 最简单的解决方案是添加第二个表。