Doing temporary bans right

Question

Ok, you have login form and you want to ban users for 5 minutes if they get password/user wrong. User can try few times in a row until banned.

LOGIN_FAILS table:

• id PK, serial/auto_increment
• ipaddress int/text/whatever you prefer
• added DATETIME/TIMESTAMP

SQL:

SELECT IF(COUNT(id) < 3, 'false', 'true') AS is_banned FROM LOGIN_FAILS WHERE ipaddress='1.2.3.4' AND added BETWEEN (NOW() - INTERVAL '5 minutes') AND NOW();

This can be visualized as

Ban check timeblock: #####

Failures: 1,2,3

Failures are set to time range:

Time ->
========1==2=3==========
========######==========

Now you could try again once in a row:

========1######=========

Now you could try again twice in a row:

========1==2######======

But the correct approach would be holding this time block for 5 minutes

========######==========

Now you can try again three times in a row

========1==2=3######====
        ^^^^^^ Ignored

But you don't want to use current time as blocks (0:00-0:05, 0:05-0:10, etc) because if ban happens at 0:04 the next ban check time block is 0:05 (= only 1 minute ban).

So what has to be added/modified to given SQL statement so that user can always try N times in a row and then hold in temporary ban for X minutes?

Edit

So something like

SELECT
  IF(COUNT(id) < 3, 'false', 'true') AS is_banned
FROM 
  LOGIN_FAILS
WHERE 
  ipaddress='1.2.3.4' AND
  added 
  BETWEEN
  (
    SELECT
      MIN(added) AS min
    FROM
      LOGIN_FAILS
    WHERE
      ipaddress='1.2.3.4' AND
      added <= (NOW() - INTERVAL 5 minutes)
    ORDER BY
      added
    LIMIT 3
  )
  AND
  NOW()

Answer 1

Your question is long, and I confess that I'm struggling to puzzle out what you're asking. I believe you're asking: If I check "how many failures have there been in the last X minutes?" then the lockout will only last until the least-recent failure falls off; how do I make sure it always lasts 5 minutes?

If that's what you're asking, then here is a fairly simple approach: On each login failure, ask the database "how many failures in the last X minutes?". If those, plus the one that just failed, is 3 or more, then set a column—say, locked_until—on the user's database row to now + 5 minutes.

Now, modify your login code to first check if an account is locked.

Answer 2

as i have understood from your long question, you need to know that an ip is to be ban or not for its failure login try within last $time_for_ban_try. then you can try following-

$time_for_ban_try = 10;//mins, for example

$sql = "SELECT IF(COUNT(id) < 3, 'false', 'true') AS is_banned FROM LOGIN_FAILS WHERE ipaddress='1.2.3.4' AND added BETWEEN (NOW() - INTERVAL $time_for_ban_try MINUTE) AND NOW()";

If i am wrong to understand please let me know.

Answer 3

Memcache is perfectly suited for this kind of thing, and will bring far less overhead (and much less code) to your system than a database table. You can count up login failures by setting key->value pair of IP->#_of_failures, and just cache it for as many seconds as you want to keep track of login failures. If the value (# of failures) is too high, you cache a unique "banned" value for that IP, for 5 minutes.

Please be aware of the fact that banning users for login failures is extremely annoying and if you do this, you should set the allowed number of failures to be very high. Users typically have a set of passwords (in order of "importance") that they will attempt to use on your site if they have forgotten it.

Also be aware of the fact that multiple people may use the same IP (as on a college campus, or private network).