mysql自动拉入黑名单,PHP + MySQL中的IP黑名单

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 then execute the following:

/* I pass in $_SERVER['REMOTE_ADDR'] */

SELECT failures FROM blacklist WHERE ip_address=?

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=?;

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?

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.

解决方案

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

DELETE FROM failures WHERE release_time < ?;

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

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.