Rate Limiting Login Attempts Without Locking Anyone Out

Two failure modes bracket this problem. No limiting: a botnet works through a password list at thousands of guesses per minute, and some accounts will fall. Naive lockout ("5 failures = account locked 24h"): an attacker now locks anyone out of their account on purpose - congratulations, your security feature is a weapon. The workable middle is throttling that slows attackers exponentially while barely touching real users.

The storage

CREATE TABLE login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    attempt_key VARCHAR(191) NOT NULL,      -- 'user:carl@...' or 'ip:203.0.113.5'
    attempted_at DATETIME NOT NULL,
    INDEX (attempt_key, attempted_at)
);

The logic

function throttle_check(PDO $pdo, string $email, string $ip): int
{
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM login_attempts
         WHERE attempt_key IN (?, ?) AND attempted_at > NOW() - INTERVAL 15 MINUTE'
    );
    $stmt->execute(["user:$email", "ip:$ip"]);
    $failures = (int) $stmt->fetchColumn();

    // 0-3 free, then 2^n seconds: 8s, 16s, 32s... capped at 15 min
    return $failures <= 3 ? 0 : min(2 ** min($failures, 13), 900);
}

// In the login handler:
$wait = throttle_check($pdo, $email, $ip);
if ($wait > 0 && !window_elapsed($pdo, $email, $ip, $wait)) {
    header('Retry-After: ' . $wait);
    http_response_code(429);
    exit(json_encode(['error' => "Too many attempts. Try again in {$wait}s."]));
}

if (!password_verify($password, $hash)) {
    record_failure($pdo, "user:$email");
    record_failure($pdo, "ip:$ip");
    // fail with the SAME message & similar timing as unknown-email - no user enumeration
} else {
    clear_failures($pdo, "user:$email");    // success resets the account key
}

The decisions that matter

Two keys, both counted. Per-account catches distributed attacks on one user (many IPs, one target); per-IP catches spraying (one IP, many targets). Either alone has an obvious bypass. Weight them differently if you like - IPs behind corporate NAT deserve looser limits than a single account does.

Exponential backoff, not lockout. A real user who fumbles twice types their password manager's answer on attempt three - they never see the throttle. An attacker's guess rate collapses: with 8-16-32s doubling, a password list that took minutes now takes months. And because it's delay-not-lock, the DoS-by-lockout attack buys an attacker almost nothing.

429 + Retry-After, never sleep(). The tempting version - sleep($wait) - holds a PHP-FPM worker hostage for the duration; a few hundred throttled bots then constitute a successful DoS against your worker pool. Reject instantly, let the client wait. (Same worker-budget reasoning as the session-lock problem - PHP concurrency is a finite resource attackers can target.)

Round it out with: a cron pruning attempts older than a day, logging when throttles trigger (spikes = active attack = time to look), CAPTCHA as an optional gate after severe counts rather than a permanent tax, and identical response timing for "wrong password" vs "no such user". None of this needs Redis until the table gets hot - a plain indexed table survives a long way.