Log Failed Login Attempts to the Database

WordPress doesn’t keep any record of failed login attempts by default. If your site is being targeted by a brute-force or credential stuffing attack, you have no native way to see how many attempts have been made, which usernames are being tried, or which IP addresses are responsible. This snippet creates a lightweight custom database table and records every failed authentication event, giving you an audit trail you can query, export, or act on.

The Code

This snippet has two parts: a function that creates the database table, and a hook that records failures as they happen. Add it to your functions.php or a site-specific plugin. The table is created using dbDelta(), WordPress’s idempotent schema management function, it only creates the table if it doesn’t exist and won’t throw errors or cause data loss if run multiple times.

The Database Table

The wp_login_failures table stores four pieces of information per failed attempt: the username that was tried, the IP address of the request, the user agent string, and the timestamp. The table includes an index on ip_address and attempted to make queries filtering by IP or date range efficient even as the table grows.

The table is created on after_setup_theme, which fires on every page load. Because dbDelta() uses CREATE TABLE IF NOT EXISTS, this is inexpensive, it only runs a schema check, not an actual table creation, after the first time.

IP Address Detection

The IP detection chain checks several headers in priority order. HTTP_CF_CONNECTING_IP is the real visitor IP forwarded by Cloudflare, which is the correct value to use on sites proxied through Cloudflare. HTTP_X_FORWARDED_FOR covers other reverse proxy setups. REMOTE_ADDR is the fallback for direct connections. When HTTP_X_FORWARDED_FOR contains a comma-separated list (which happens when multiple proxies are in the chain), the snippet takes only the first value, the original client IP.

Querying the Log

You can query the log directly from phpMyAdmin or with WP-CLI. To see the most active attacker IPs:

SELECT ip_address, COUNT(*) as attempts FROM wp_login_failures GROUP BY ip_address ORDER BY attempts DESC LIMIT 20;

To see recent attempts in the last 24 hours:

SELECT * FROM wp_login_failures WHERE attempted > NOW() - INTERVAL 1 DAY ORDER BY attempted DESC;

Taking Action on the Data

The log itself doesn’t block attackers, it observes them. To act on the data, you can extend this snippet with a function that checks the log on each login attempt and blocks IPs that exceed a threshold, or export the IP list periodically and add them to your server’s firewall rules or Cloudflare’s IP Block list. Combined with a WAF rule that rate-limits requests to wp-login.php, this gives you both visibility and enforcement.

Table Maintenance

On an actively targeted site, this table can grow quickly. Consider adding a cleanup routine using wp_schedule_event() to delete records older than 30 or 90 days on a weekly cron job, keeping the table size manageable without losing recent forensic data.

functions.php

// Create the log table on plugin/theme activation
function nsl_create_login_log_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'login_failures';
    $charset = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        id         BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        username   VARCHAR(200)        NOT NULL DEFAULT '',
        ip_address VARCHAR(100)        NOT NULL DEFAULT '',
        user_agent VARCHAR(500)        NOT NULL DEFAULT '',
        attempted  DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY ip_address (ip_address),
        KEY attempted  (attempted)
    ) {$charset};";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql );
}
add_action( 'after_setup_theme', 'nsl_create_login_log_table' );

// Log the failure on each failed authentication
add_action( 'wp_login_failed', function( $username ) {
    global $wpdb;

    $ip = $_SERVER['HTTP_CF_CONNECTING_IP']
        ?? $_SERVER['HTTP_X_FORWARDED_FOR']
        ?? $_SERVER['REMOTE_ADDR']
        ?? 'unknown';

    // Take only the first IP if a comma-separated list is present
    $ip = trim( explode( ',', $ip )[0] );

    $wpdb->insert(
        $wpdb->prefix . 'login_failures',
        [
            'username'   => sanitize_text_field( $username ),
            'ip_address' => sanitize_text_field( $ip ),
            'user_agent' => sanitize_text_field( substr( $_SERVER['HTTP_USER_AGENT'] ?? '', 0, 500 ) ),
            'attempted'  => current_time( 'mysql' ),
        ],
        [ '%s', '%s', '%s', '%s' ]
    );
} );