IP Blacklist in PHP+MySQL

Question

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.

Answer 1

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.