*/ public static function events() { return array( 'failed' => __( 'Failed login', 'codingcush-login-guard' ), 'lockout' => __( 'Locked out', 'codingcush-login-guard' ), 'blocked' => __( 'Blocked IP', 'codingcush-login-guard' ), 'unlock' => __( 'Unlocked', 'codingcush-login-guard' ), 'recovery' => __( 'Unlock email sent', 'codingcush-login-guard' ), 'turnstile' => __( 'Turnstile failed', 'codingcush-login-guard' ), ); } /** * Full table name. * * @return string */ public static function table() { global $wpdb; return $wpdb->prefix . 'cclg_log'; } /** * Creates or upgrades the table. * * @return void */ public static function install() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $table = self::table(); $charset = $wpdb->get_charset_collate(); dbDelta( "CREATE TABLE {$table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, created_at datetime NOT NULL, event varchar(20) NOT NULL DEFAULT '', ip varchar(45) NOT NULL DEFAULT '', username varchar(60) NOT NULL DEFAULT '', expires_at datetime NULL DEFAULT NULL, released tinyint(1) NOT NULL DEFAULT 0, note varchar(190) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY created_at (created_at), KEY event_ip (event, ip), KEY active (event, released, expires_at) ) {$charset};" ); update_option( 'cclg_db_version', CCLG_DB_VERSION, false ); } /** * Adds a row. * * @param string $event Event type. * @param string $ip IP address. * @param string $username Username tried (may be empty). * @param int|null $expires Unix time a lockout ends, or null. * @param string $note Short note. * @return int Row id. */ public static function add( $event, $ip, $username = '', $expires = null, $note = '' ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Writes to the plugin's own table. $wpdb->insert( self::table(), array( 'created_at' => current_time( 'mysql', true ), 'event' => substr( sanitize_key( $event ), 0, 20 ), 'ip' => substr( (string) $ip, 0, 45 ), 'username' => substr( sanitize_user( (string) $username ), 0, 60 ), 'expires_at' => $expires ? gmdate( 'Y-m-d H:i:s', (int) $expires ) : null, 'note' => substr( sanitize_text_field( (string) $note ), 0, 190 ), ), array( '%s', '%s', '%s', '%s', '%s', '%s' ) ); return (int) $wpdb->insert_id; } /** * Marks every active lockout row for an IP as released. * * @param string $ip IP address. * @return void */ public static function release( $ip ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table; always fresh. $wpdb->query( $wpdb->prepare( 'UPDATE %i SET released = 1 WHERE event = %s AND ip = %s AND released = 0', self::table(), 'lockout', $ip ) ); } /** * Lockouts that have not expired or been released. * * @return array> */ public static function active_lockouts() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table; must reflect the current state. $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT ip, MAX(expires_at) AS expires_at, MAX(username) AS username, COUNT(*) AS times FROM %i WHERE event = %s AND released = 0 AND expires_at > %s GROUP BY ip ORDER BY expires_at DESC LIMIT 200', self::table(), 'lockout', current_time( 'mysql', true ) ), ARRAY_A ); return is_array( $rows ) ? $rows : array(); } /** * Most recent rows, optionally filtered. * * @param string $event Event type or '' for all. * @param string $search IP or username fragment. * @param int $limit Max rows. * @param int $offset Offset. * @return array> */ public static function recent( $event = '', $search = '', $limit = 50, $offset = 0 ) { global $wpdb; $like = '%' . $wpdb->esc_like( (string) $search ) . '%'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table; the log must be current. $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE ( %s = \'\' OR event = %s ) AND ( %s = \'\' OR ip LIKE %s OR username LIKE %s ) ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d', self::table(), (string) $event, (string) $event, (string) $search, $like, $like, max( 1, (int) $limit ), max( 0, (int) $offset ) ), ARRAY_A ); return is_array( $rows ) ? $rows : array(); } /** * Number of rows matching the same filters as recent(). * * @param string $event Event type or ''. * @param string $search IP or username fragment. * @return int */ public static function count( $event = '', $search = '' ) { global $wpdb; $like = '%' . $wpdb->esc_like( (string) $search ) . '%'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table; the log must be current. return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE ( %s = \'\' OR event = %s ) AND ( %s = \'\' OR ip LIKE %s OR username LIKE %s )', self::table(), (string) $event, (string) $event, (string) $search, $like, $like ) ); } /** * Counts per event type since a given number of hours ago. * * @param int $hours Hours. * @return array */ public static function counts_since( $hours ) { global $wpdb; $since = gmdate( 'Y-m-d H:i:s', time() - (int) $hours * HOUR_IN_SECONDS ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table; dashboard numbers must be current. $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT event, COUNT(*) AS n FROM %i WHERE created_at >= %s GROUP BY event', self::table(), $since ), ARRAY_A ); $out = array_fill_keys( array_keys( self::events() ), 0 ); foreach ( (array) $rows as $row ) { $out[ $row['event'] ] = (int) $row['n']; } return $out; } /** * Usernames tried most often in failed logins over the last N days. * * @param int $days Days. * @param int $limit Max rows. * @return array> */ public static function top_usernames( $days = 7, $limit = 5 ) { global $wpdb; $since = gmdate( 'Y-m-d H:i:s', time() - (int) $days * DAY_IN_SECONDS ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT username, COUNT(*) AS n FROM %i WHERE event = 'failed' AND username <> '' AND created_at >= %s GROUP BY username ORDER BY n DESC LIMIT %d", self::table(), $since, (int) $limit ), ARRAY_A ); return is_array( $rows ) ? $rows : array(); } /** * Deletes rows older than N days. * * @param int $days Retention in days. * @return void */ public static function prune( $days ) { global $wpdb; $before = gmdate( 'Y-m-d H:i:s', time() - max( 1, (int) $days ) * DAY_IN_SECONDS ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table housekeeping. $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', self::table(), $before ) ); } /** * Empties the log, keeping the rows of lockouts that are still running so they * stay visible (and can be unlocked) on the overview. * * @return void */ public static function clear() { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table. $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE NOT (event = %s AND released = 0 AND expires_at > %s)', self::table(), 'lockout', current_time( 'mysql', true ) ) ); } }