'int', 'event_type' => 'string', 'severity' => 'string', 'ip_hash' => 'string', 'description' => 'string', 'metadata' => 'string', 'created_at' => 'string', 'attempt_count' => 'int', 'last_attempt_at' => 'string', ); /** * Holds all the default values for the columns. * * @var array * * @since 5.2.0 */ protected static $fields_values = array( 'id' => 0, 'event_type' => '', 'severity' => 'medium', 'ip_hash' => '', 'description' => '', 'metadata' => '', 'created_at' => '', 'attempt_count' => 1, 'last_attempt_at' => '', ); /** * Event type constants. */ public const EVENT_DDOS = 'ddos'; public const EVENT_DOS = 'dos'; public const EVENT_BRUTE_FORCE = 'brute_force'; public const EVENT_PRIV_ESCALATION = 'privilege_escalation'; public const EVENT_ROLE_CHANGE = 'role_change'; public const EVENT_OPTION_CHANGE = 'option_change'; public const EVENT_FILE_EDIT = 'file_edit'; public const EVENT_USER_ENUM = 'user_enumeration'; public const EVENT_BLOCKED = 'blocked'; public const EVENT_INCIDENT = 'incident'; public const EVENT_PLUGIN_UPDATE = 'plugin_update'; public const EVENT_PLUGIN_DELETE = 'plugin_delete'; public const EVENT_THEME_SWITCH = 'theme_switch'; public const EVENT_THEME_UPDATE = 'theme_update'; public const EVENT_CORE_UPDATE = 'core_update'; public const EVENT_USER_LOGIN = 'user_login'; public const EVENT_USER_LOGOUT = 'user_logout'; public const EVENT_LOGIN_FAILED = 'user_login_failed'; public const EVENT_USER_DELETED = 'user_deleted'; public const EVENT_PASSWORD_RESET = 'user_password_reset'; public const EVENT_POST_STATUS = 'post_status_change'; /** * 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 (1 hour). * * Events of the same type from the same IP within this window * are merged into a single row with an incremented counter. * * @var int * * @since 5.3.0 */ public const AGGREGATION_WINDOW = 3600; /** * Creates table functionality. * * @param \wpdb $connection - \wpdb connection to be used for name extraction. * * @return bool * * @since 5.2.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 \'medium\', ip_hash VARCHAR(64) NOT NULL DEFAULT \'\', description TEXT NOT NULL, metadata LONGTEXT NOT NULL DEFAULT \'\', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, attempt_count INT UNSIGNED NOT NULL DEFAULT 1, last_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_event_type (event_type), KEY idx_severity (severity), KEY idx_ip_hash (ip_hash), KEY idx_created_at (created_at), KEY idx_type_created (event_type, created_at), KEY idx_type_ip_created (event_type, ip_hash, created_at) ) ENGINE=InnoDB ' . $collate . ';'; return self::maybe_create_table( $table_name, $wp_entity_sql, $connection ); } /** * Log a security event. * * @param string $event_type One of the EVENT_* constants. * @param string $severity One of the SEVERITY_* constants. * @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.2.0 */ public static function log_event( string $event_type, string $severity, string $ip_hash, string $description, array $metadata = array() ): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $allowed_types = array( self::EVENT_DDOS, self::EVENT_DOS, self::EVENT_BRUTE_FORCE, self::EVENT_PRIV_ESCALATION, self::EVENT_ROLE_CHANGE, self::EVENT_OPTION_CHANGE, self::EVENT_FILE_EDIT, self::EVENT_USER_ENUM, self::EVENT_BLOCKED, self::EVENT_INCIDENT, self::EVENT_PLUGIN_UPDATE, self::EVENT_PLUGIN_DELETE, self::EVENT_THEME_SWITCH, self::EVENT_THEME_UPDATE, self::EVENT_CORE_UPDATE, self::EVENT_USER_LOGIN, self::EVENT_USER_LOGOUT, self::EVENT_LOGIN_FAILED, self::EVENT_USER_DELETED, self::EVENT_PASSWORD_RESET, self::EVENT_POST_STATUS, ); 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_MEDIUM; } $now = current_time( 'mysql', true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "INSERT INTO `{$table}` (event_type, severity, ip_hash, description, metadata, created_at, attempt_count, last_attempt_at) VALUES (%s, %s, %s, %s, %s, %s, %d, %s)", $event_type, $severity, $ip_hash, $description, wp_json_encode( $metadata, JSON_UNESCAPED_UNICODE ), $now, 1, $now ) ); } /** * Log a security event with aggregation support. * * For high-frequency event types (blocked, dos, brute_force, etc.), * instead of inserting a new row for every occurrence, this method * looks for an existing row with the same (event_type, ip_hash) within * the aggregation window and increments its counter. * * This dramatically reduces DB row count during sustained attacks * while preserving all essential information. * * @param string $event_type One of the EVENT_* constants. * @param string $severity One of the SEVERITY_* constants. * @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_or_aggregate_event( string $event_type, string $severity, string $ip_hash, string $description, array $metadata = array() ): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $now = current_time( 'mysql', true ); $cutoff = gmdate( 'Y-m-d H:i:s', time() - self::AGGREGATION_WINDOW ); // Try to find an existing recent row with the same event_type + ip_hash. $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $existing_id = $_wpdb->get_var( $_wpdb->prepare( "SELECT id FROM `{$table}` WHERE event_type = %s AND ip_hash = %s AND created_at >= %s ORDER BY created_at DESC LIMIT 1", $event_type, $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 ) ); } $existing_id = null; } } $_wpdb->suppress_errors( false ); if ( $existing_id ) { // Aggregate into the existing row: increment counter, update timestamp. // Also escalate severity if the new event is more severe. $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "UPDATE `{$table}` SET attempt_count = attempt_count + 1, last_attempt_at = %s, severity = CASE WHEN FIELD(%s, 'low', 'medium', 'high', 'critical') > FIELD(severity, 'low', 'medium', 'high', 'critical') THEN %s ELSE severity END WHERE id = %d", $now, $severity, $severity, (int) $existing_id ) ); // If attempt_count column doesn't exist yet (pre-upgrade), add it. if ( '' !== $_wpdb->last_error && 1054 === self::get_last_sql_error( $_wpdb ) ) { self::maybe_upgrade_table(); // Retry the update after adding columns. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared self::query_run( $_wpdb->prepare( "UPDATE `{$table}` SET attempt_count = attempt_count + 1, last_attempt_at = %s, severity = CASE WHEN FIELD(%s, 'low', 'medium', 'high', 'critical') > FIELD(severity, 'low', 'medium', 'high', 'critical') THEN %s ELSE severity END WHERE id = %d", $now, $severity, $severity, (int) $existing_id ) ); } $_wpdb->suppress_errors( false ); } else { // No recent matching row — insert a new one. self::log_event( $event_type, $severity, $ip_hash, $description, $metadata ); } } /** * Upgrade the table schema for existing installations. * * Adds the attempt_count and last_attempt_at columns if they * don't already exist. Safe to call multiple times. * * @return void * * @since 5.3.0 */ public static function maybe_upgrade_table(): void { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $_wpdb->suppress_errors( true ); // Check if attempt_count column exists. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $column = $_wpdb->get_var( $_wpdb->prepare( 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s', DB_NAME, $table, 'attempt_count' ) ); if ( null === $column ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $_wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN attempt_count INT UNSIGNED NOT NULL DEFAULT 1 AFTER created_at" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $_wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN last_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER attempt_count" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $_wpdb->query( "ALTER TABLE `{$table}` ADD KEY idx_type_ip_created (event_type, ip_hash, created_at)" ); } $_wpdb->suppress_errors( false ); } /** * Get events for a date range with optional filtering. * * @param string $start_date Start date (Y-m-d). * @param string $end_date End date (Y-m-d). * @param string|null $event_type Optional event type filter. * @param string|null $severity Optional severity filter. * @param int $limit Max results. * @param int $offset Offset for pagination. * * @return array * * @since 5.2.0 */ public static function get_events( string $start_date, string $end_date, ?string $event_type = null, ?string $severity = null, int $limit = 100, int $offset = 0 ): array { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $where = 'WHERE created_at BETWEEN %s AND %s'; $params = array( $start_date . ' 00:00:00', $end_date . ' 23:59:59' ); if ( null !== $event_type ) { $where .= ' AND event_type = %s'; $params[] = $event_type; } if ( null !== $severity ) { $where .= ' AND severity = %s'; $params[] = $severity; } $params[] = $limit; $params[] = $offset; $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $results = $_wpdb->get_results( $_wpdb->prepare( "SELECT * FROM `{$table}` {$where} ORDER BY created_at DESC LIMIT %d OFFSET %d", ...$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(); } } } $_wpdb->suppress_errors( false ); return is_array( $results ) ? $results : array(); } /** * Get paginated events for the list table with search, filter, sort and count. * * @param array $args { * @type string $search_string Search text. * @type string|null $event_type Event type filter. * @type string|null $severity Severity filter. * @type string $orderby Column to sort by. * @type string $order ASC or DESC. * @type int $per_page Results per page. * @type int $offset Result offset. * } * @param int &$total_count Output: total matching rows (for pagination). * * @return array * * @since 5.2.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, '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 event_type LIKE %s OR severity LIKE %s OR ip_hash LIKE %s OR metadata 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']; } // Validate orderby. $allowed_orderby = array( 'id', 'event_type', 'severity', 'ip_hash', 'description', 'created_at', 'attempt_count', 'last_attempt_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. * * @return bool * * @since 5.2.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.2.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 all distinct event types currently stored. * * @return array * * @since 5.2.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(); } /** * Count events matching criteria. * * @param string|null $event_type Optional event type. * @param string|null $severity Optional severity. * @param int $minutes Time window in minutes (0 = all time). * * @return int * * @since 5.2.0 */ public static function count_events( ?string $event_type = null, ?string $severity = null, int $minutes = 0 ): int { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $where = 'WHERE 1=1'; $params = array(); if ( $minutes > 0 ) { $where .= ' AND created_at >= %s'; $params[] = gmdate( 'Y-m-d H:i:s', time() - ( $minutes * 60 ) ); } if ( null !== $event_type ) { $where .= ' AND event_type = %s'; $params[] = $event_type; } if ( null !== $severity ) { $where .= ' AND severity = %s'; $params[] = $severity; } $_wpdb->suppress_errors( true ); if ( ! empty( $params ) ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $count = (int) $_wpdb->get_var( $_wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` {$where}", ...$params ) ); } else { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $count = (int) $_wpdb->get_var( "SELECT COUNT(*) FROM `{$table}` {$where}" ); } 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 unique IPs that triggered events in a time window. * * @param string $event_type Event type. * @param int $minutes Time window in minutes. * * @return int * * @since 5.2.0 */ public static function count_unique_ips( string $event_type, int $minutes = 60 ): int { $_wpdb = static::get_connection(); $table = self::get_table_name( $_wpdb ); $_wpdb->suppress_errors( true ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $count = (int) $_wpdb->get_var( $_wpdb->prepare( "SELECT COUNT(DISTINCT ip_hash) FROM `{$table}` WHERE event_type = %s AND created_at >= %s", $event_type, gmdate( 'Y-m-d H:i:s', time() - ( $minutes * 60 ) ) ) ); 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 event counts grouped by 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.2.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, severity, COUNT(*) as total FROM `{$table}` WHERE created_at BETWEEN %s AND %s GROUP BY event_type, severity 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(); } /** * Prune old events beyond retention period. * * @param int $retention_days Number of days to retain. * * @return int Number of rows deleted. * * @since 5.2.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 fields. * * @return array * * @since 5.2.0 */ public static function get_column_names_admin(): array { return array( 'created_at' => \__( 'Date', '0-day-analytics' ), 'event_type' => \__( 'Event Type', '0-day-analytics' ), 'severity' => \__( 'Severity', '0-day-analytics' ), 'description' => \__( 'Description', '0-day-analytics' ), 'ip_hash' => \__( 'IP Hash', '0-day-analytics' ), 'attempt_count' => \__( 'Attempts', '0-day-analytics' ), 'metadata' => \__( 'Details', '0-day-analytics' ), ); } /** * Get the top blocked IPs. * * @param int $hours Time window in hours. * @param int $limit Max results. * * @return array * * @since 5.2.0 */ public static function get_top_blocked_ips( int $hours = 24, int $limit = 20 ): 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 ip_hash, COUNT(*) as hit_count, MAX(severity) as max_severity, MAX(created_at) as last_seen FROM `{$table}` WHERE created_at >= %s AND event_type IN (%s, %s, %s, %s) GROUP BY ip_hash ORDER BY hit_count DESC LIMIT %d", gmdate( 'Y-m-d H:i:s', time() - ( $hours * 3600 ) ), self::EVENT_DDOS, self::EVENT_DOS, self::EVENT_BRUTE_FORCE, self::EVENT_BLOCKED, $limit ), 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(); } } }