PHP + MySQL中的IP黑名单 [英] IP Blacklist in PHP+MySQL

问题描述

我一直在尝试在PHP中实现某种IP黑名单，其中将失败的登录尝试存储在具有以下架构的MySQL表中:

I have been trying to implement a sort of IP blacklisting in PHP, where I store failed login attempts to a MySQL table with the following schema:

CREATE TABLE blacklist(
    `ip_address` VARCHAR(35) NOT NULL,
    `failures` INTEGER DEFAULT 0,
    `release_time` BIGINT DEFAULT -1,
    PRIMARY KEY(`ip_address`)
);

在登录检查中，我首先使用以下查询删除所有在当前时间(如果已经过了释放时间)之前具有释放时间的黑名单条目:

On my login check, I first delete any blacklist entries that have a release time before the current time (if the release time has already passed) with the following query:

/* I pass in time() */
DELETE FROM failures WHERE release_time < ?;

然后我执行以下操作:

/* I pass in $_SERVER['REMOTE_ADDR'] */
SELECT failures FROM blacklist WHERE ip_address=?

如果我没有检索到任何行，或者如果我获得的$ row ['failures']超过5，则允许检查用户名和密码.否则，我将完全拒绝登录.

If I retrieve no rows or if the $row['failures'] that I get back exceeds 5, I allow for checking of the username and password. Otherwise, I reject the login altogether.

对于每次失败的登录尝试(无论是通过黑名单规则还是通过无效的用户名/密码)，我都会执行:

For every failed login attempt (whether through a blacklist rule or through an invalid username/password), I execute:

/* IP address is $_SERVER['REMOTE_ADDR'],
   release_time is current time + (new failures * 90 minutes) */
INSERT INTO BLACKLIST(ip_address, failures, release_time) VALUES(?,?,?) 
ON DUPLICATE KEY UPDATE failures=failures+1, release_time=?;

不幸的是，我至少打了3次数据库(清除黑名单，获取IP地址，增加故障最少).有没有更好的方法来维护动态黑名单，也许每分钟写入一次缓存?

Unfortunately, I'm hitting the database at least 3 times (clearing the blacklist, getting an IP address, incrementing failures as the minimum). Is there a better way to maintain a dynamic blacklist, perhaps writing to a cache every minute?

我确实看到用ip/php/mysql禁止是相似的我的问题，但是如果有人长时间停止尝试登录，我可以将他们从黑名单中删除.这样，那些仅仅忘记自己的凭据的人受到的影响要比那些试图强行获取凭据的人受到的影响要小.

I did see that Banning by IP with php/mysql is similar to my question, but I'm allowing for people to be removed from the blacklist if they stop attempting logins for a good period of time. This way, people who simply forget their credentials are affected less than people attempting to brute force their way into gaining credentials.

推荐答案

以下查询不需要定期运行，可以移至cron作业:

The following query doesn't need run regularly and could be moved to a cron job:

DELETE FROM failures WHERE release_time < ?;

如果此人被列入黑名单，则此布尔"查询将返回1，否则返回0:

This "boolean" query will return 1 if the person is blacklisted, 0 otherwise:

SELECT
  COUNT(ip_address) as blacklisted
FROM blacklist
WHERE
  ip_address = ? AND
  release_time > ? AND
  failures > 5

由于您不使用PHP对行进行计数和比较数字，因此可能会加快速度:

It might speed things up as your not using PHP to count rows and compare numbers:

if ($row['blacklisted']) { /* ... */ }

我认为您真的不能避免最后一个.

I don't think you can avoid the last one really.

这篇关于PHP + MySQL中的IP黑名单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！