'int', 'event_type' => 'string', 'severity' => 'string', 'user_id' => 'int', 'username' => 'string', 'ip_hash' => 'string', 'user_agent' => 'string', 'description' => 'string', 'metadata' => 'string', 'created_at' => 'string', ); /** * Holds all the default values for the columns. * * @var array * * @since 5.3.0 */ protected static $fields_values = array( 'id' => 0, 'event_type' => '', 'severity' => 'low', 'user_id' => 0, 'username' => '', 'ip_hash' => '', 'user_agent' => '', 'description' => '', 'metadata' => '', 'created_at' => '', ); /** * Event type constants. */ public const EVENT_LOGIN_SUCCESS = 'login_success'; public const EVENT_LOGIN_FAILED = 'login_failed'; public const EVENT_LOGOUT = 'logout'; public const EVENT_PASSWORD_RESET = 'password_reset'; public const EVENT_PASSWORD_REQUEST = 'password_request'; public const EVENT_COOKIE_VALID = 'cookie_valid'; public const EVENT_COOKIE_INVALID = 'cookie_invalid'; public const EVENT_APP_PASSWORD = 'app_password_auth'; public const EVENT_SESSION_DESTROY = 'session_destroy'; public const EVENT_SESSION_EXPIRED = 'session_expired'; public const EVENT_USER_LOCKED = 'user_locked'; public const EVENT_LOGIN_COOKIE = 'login_cookie'; /** * Severity levels. */ public const SEVERITY_LOW = 'low'; public const SEVERITY_MEDIUM = 'medium'; public const SEVERITY_HIGH = 'high'; public const SEVERITY_CRITICAL = 'critical'; /** * Default aggregation window in seconds (5 minutes) for failed logins. * * @var int * * @since 5.3.0 */ public const AGGREGATION_WINDOW = 300; /** * Creates table functionality. * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return bool * * @since 5.3.0 */ public static function create_table( $connection = null ): bool { if ( null !== $connection ) { if ( $connection instanceof \wpdb ) { $collate = $connection->get_charset_collate(); } } else { $collate = self::get_connection()->get_charset_collate(); } $table_name = self::get_table_name( $connection ); if ( ! is_string( $table_name ) || ! preg_match( '/^[a-zA-Z0-9_]+$/', $table_name ) ) { return false; } $wp_entity_sql = ' CREATE TABLE `' . $table_name . '` ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, event_type VARCHAR(50) NOT NULL, severity VARCHAR(20) NOT NULL DEFAULT \'low\', user_id BIGINT UNSIGNED NOT NULL DEFAULT 0, username VARCHAR(60) NOT NULL DEFAULT \'\', ip_hash VARCHAR(64) NOT NULL DEFAULT \'\', user_agent VARCHAR(512) NOT NULL DEFAULT \'\', description TEXT NOT NULL, metadata LONGTEXT NOT NULL DEFAULT \'\', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_event_type (event_type), KEY idx_severity (severity), KEY idx_user_id (user_id), KEY idx_ip_hash (ip_hash), KEY idx_created_at (created_at), KEY idx_type_created (event_type, created_at), KEY idx_user_created (user_id, created_at), KEY idx_type_ip (event_type, ip_hash) ) ENGINE=InnoDB ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Log an auth event. * * @param string $event_type One of the EVENT_* constants. * @param string $severity One of the SEVERITY_* constants. * @param int $user_id WordPress user ID (0 for unknown). * @param string $username Username attempted or used. * @param string $ip_hash Hashed IP address. * @param string $description Human-readable description. * @param array $metadata Additional data to store as JSON. * * @return void * * @since 5.3.0 */ public static function log_event( string $event_type, string $severity, int $user_id, string $username, string $ip_hash, string $description, array $metadata = array() ): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $allowed_types = array( self::EVENT_LOGIN_SUCCESS, self::EVENT_LOGIN_FAILED, self::EVENT_LOGOUT, self::EVENT_PASSWORD_RESET, self::EVENT_PASSWORD_REQUEST, self::EVENT_COOKIE_VALID, self::EVENT_COOKIE_INVALID, self::EVENT_APP_PASSWORD, self::EVENT_SESSION_DESTROY, self::EVENT_SESSION_EXPIRED, self::EVENT_USER_LOCKED, self::EVENT_LOGIN_COOKIE, ); if ( ! in_array( $event_type, $allowed_types, true ) ) { return; } $allowed_severities = array( self::SEVERITY_LOW, self::SEVERITY_MEDIUM, self::SEVERITY_HIGH, self::SEVERITY_CRITICAL, ); if ( ! in_array( $severity, $allowed_severities, true ) ) { $severity = self::SEVERITY_LOW; } $now = current_time( 'mysql', true ); $user_agent = self::get_sanitized_user_agent(); $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "INSERT INTO `{$table}` (event_type, severity, user_id, username, ip_hash, user_agent, description, metadata, created_at) VALUES (%s, %s, %d, %s, %s, %s, %s, %s, %s)", $event_type, $severity, $user_id, $username, $ip_hash, $user_agent, $description, wp_json_encode( $metadata, JSON_UNESCAPED_UNICODE ), $now ) ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "INSERT INTO `{$table}` (event_type, severity, user_id, username, ip_hash, user_agent, description, metadata, created_at) VALUES (%s, %s, %d, %s, %s, %s, %s, %s, %s)", $event_type, $severity, $user_id, $username, $ip_hash, $user_agent, $description, wp_json_encode( $metadata, JSON_UNESCAPED_UNICODE ), $now ) ); } } } $_wpdb->suppress_errors( false ); } /** * Get paginated events for the list table with search, filter, sort and count. * * @param array $args Query arguments. * @param int &$total_count Output: total matching rows (for pagination). * * @return array * * @since 5.3.0 */ public static function get_list_data( array $args, int &$total_count ): array { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $defaults = array( 'search_string' => '', 'event_type' => null, 'severity' => null, 'user_id' => null, 'orderby' => 'created_at', 'order' => 'DESC', 'per_page' => 20, 'offset' => 0, ); $args = \wp_parse_args( $args, $defaults ); $where = 'WHERE 1=1'; $params = array(); if ( '' !== $args['search_string'] ) { $like = '%' . $_wpdb->esc_like( $args['search_string'] ) . '%'; $where .= ' AND (description LIKE %s OR username LIKE %s OR event_type LIKE %s OR ip_hash LIKE %s OR user_agent LIKE %s)'; $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like; $params[] = $like; } if ( null !== $args['event_type'] && '' !== $args['event_type'] ) { $where .= ' AND event_type = %s'; $params[] = $args['event_type']; } if ( null !== $args['severity'] && '' !== $args['severity'] ) { $where .= ' AND severity = %s'; $params[] = $args['severity']; } if ( null !== $args['user_id'] && $args['user_id'] > 0 ) { $where .= ' AND user_id = %d'; $params[] = (int) $args['user_id']; } // Validate orderby. $allowed_orderby = array( 'id', 'event_type', 'severity', 'user_id', 'username', 'ip_hash', 'description', 'created_at' ); $orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at'; $order = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC'; $_wpdb->suppress_errors( true ); // Count. if ( ! empty( $params ) ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $total_count = (int) $_wpdb->get_var( $_wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` {$where}", ...$params ) ); } else { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $total_count = (int) $_wpdb->get_var( "SELECT COUNT(*) FROM `{$table}` {$where}" ); } // Query rows. $query_params = $params; $query_params[] = (int) $args['per_page']; $query_params[] = (int) $args['offset']; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $results = $_wpdb->get_results( $_wpdb->prepare( "SELECT * FROM `{$table}` {$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d", ...$query_params ), ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $results = array(); $total_count = 0; } } } $_wpdb->suppress_errors( false ); return is_array( $results ) ? $results : array(); } /** * Delete a single event by ID. * * @param int $id Event ID. * @param \wpdb|null $connection Optional connection. * * @return bool * * @since 5.3.0 */ public static function delete_by_id( int $id, $connection = null ): bool { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $deleted = (int) self::query_run( $_wpdb->prepare( "DELETE FROM `{$table}` WHERE id = %d", $id ) ); return $deleted > 0; } /** * Delete multiple events by IDs. * * @param array $ids Array of event IDs. * * @return int Number deleted. * * @since 5.3.0 */ public static function delete_by_ids( array $ids ): int { if ( empty( $ids ) ) { return 0; } $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $deleted = (int) self::query_run( $_wpdb->prepare( "DELETE FROM `{$table}` WHERE id IN ({$placeholders})", ...\array_map( 'intval', $ids ) ) ); return $deleted; } /** * Get summary counts grouped by event type for a date range. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * * @return array * * @since 5.3.0 */ public static function get_event_summary( string $start_date, string $end_date ): array { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $results = $_wpdb->get_results( $_wpdb->prepare( "SELECT event_type, COUNT(*) as total FROM `{$table}` WHERE created_at BETWEEN %s AND %s GROUP BY event_type ORDER BY total DESC", $start_date . ' 00:00:00', $end_date . ' 23:59:59' ), ARRAY_A ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $results = array(); } } } $_wpdb->suppress_errors( false ); return is_array( $results ) ? $results : array(); } /** * Count failed logins for a given IP hash in the last N minutes. * * @param string $ip_hash IP hash. * @param int $minutes Time window in minutes. * * @return int * * @since 5.3.0 */ public static function count_failed_logins_by_ip( string $ip_hash, int $minutes = 5 ): int { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $minutes * 60 ) ); $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $count = (int) $_wpdb->get_var( $_wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE event_type = %s AND ip_hash = %s AND created_at >= %s", self::EVENT_LOGIN_FAILED, $ip_hash, $cutoff ) ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $count = 0; } } } $_wpdb->suppress_errors( false ); return $count; } /** * Count failed logins for a given username in the last N minutes. * * @param string $username Username. * @param int $minutes Time window in minutes. * * @return int * * @since 5.3.0 */ public static function count_failed_logins_by_username( string $username, int $minutes = 30 ): int { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $minutes * 60 ) ); $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $count = (int) $_wpdb->get_var( $_wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE event_type = %s AND username = %s AND created_at >= %s", self::EVENT_LOGIN_FAILED, $username, $cutoff ) ); if ( '' !== $_wpdb->last_error ) { if ( 1146 === self::get_last_sql_error( $_wpdb ) ) { if ( ( static::class )::create_table( $_wpdb ) ) { self::log_table_recreated( self::get_table_name( $_wpdb ) ); $count = 0; } } } $_wpdb->suppress_errors( false ); return $count; } /** * Get distinct event types currently stored. * * @return array * * @since 5.3.0 */ public static function get_distinct_event_types(): array { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $results = $_wpdb->get_col( "SELECT DISTINCT event_type FROM `{$table}` ORDER BY event_type ASC" ); $_wpdb->suppress_errors( false ); return is_array( $results ) ? $results : array(); } /** * Prune old events beyond retention period. * * @param int $retention_days Number of days to retain. * * @return int Number of rows deleted. * * @since 5.3.0 */ public static function prune( int $retention_days ): int { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $retention_days * DAY_IN_SECONDS ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $deleted = (int) self::query_run( $_wpdb->prepare( "DELETE FROM `{$table}` WHERE created_at < %s", $cutoff ) ); return $deleted; } /** * Returns the table CMS admin columns. * * @return array * * @since 5.3.0 */ public static function get_column_names_admin(): array { return array( 'created_at' => \__( 'Date', '0-day-analytics' ), 'event_type' => \__( 'Event', '0-day-analytics' ), 'severity' => \__( 'Severity', '0-day-analytics' ), 'username' => \__( 'User', '0-day-analytics' ), 'ip_hash' => \__( 'IP Hash', '0-day-analytics' ), 'user_agent' => \__( 'User Agent', '0-day-analytics' ), 'description' => \__( 'Description', '0-day-analytics' ), ); } /** * Get sanitized user agent string, truncated to 512 chars. * * @return string * * @since 5.3.0 */ private static function get_sanitized_user_agent(): string { $ua = isset( $_SERVER['HTTP_USER_AGENT'] ) ? \sanitize_text_field( \wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : ''; return substr( $ua, 0, 512 ); } /** * Human-readable event type labels. * * @return array * * @since 5.3.0 */ public static function get_event_type_labels(): array { return array( self::EVENT_LOGIN_SUCCESS => \__( 'Login Success', '0-day-analytics' ), self::EVENT_LOGIN_FAILED => \__( 'Login Failed', '0-day-analytics' ), self::EVENT_LOGOUT => \__( 'Logout', '0-day-analytics' ), self::EVENT_PASSWORD_RESET => \__( 'Password Reset', '0-day-analytics' ), self::EVENT_PASSWORD_REQUEST => \__( 'Password Request', '0-day-analytics' ), self::EVENT_COOKIE_VALID => \__( 'Cookie Validated', '0-day-analytics' ), self::EVENT_COOKIE_INVALID => \__( 'Invalid Cookie', '0-day-analytics' ), self::EVENT_APP_PASSWORD => \__( 'App Password', '0-day-analytics' ), self::EVENT_SESSION_DESTROY => \__( 'Session Destroyed', '0-day-analytics' ), self::EVENT_SESSION_EXPIRED => \__( 'Session Expired', '0-day-analytics' ), self::EVENT_USER_LOCKED => \__( 'User Locked', '0-day-analytics' ), self::EVENT_LOGIN_COOKIE => \__( 'Login Cookie', '0-day-analytics' ), ); } } }