prefix . $suffix; } protected static function now() { return current_time( 'mysql', true ); } protected static function generate_job_key() { return strtolower( dechex( time() ) . wp_generate_password( 6, false, false ) ); } /** * Convert a category value (string, array, or CSV) to a CSV string for DB storage. * * @param string|array|null $category Category value. * @return string|null CSV string or null. */ protected static function category_to_csv( $category ) { if ( is_array( $category ) ) { $filtered = array_filter( array_map( 'sanitize_text_field', $category ) ); return ! empty( $filtered ) ? implode( ',', $filtered ) : null; } if ( is_string( $category ) && $category !== '' ) { return sanitize_text_field( $category ); } return null; } /** * Parse a stored category value (CSV string) back to string or array. * Single values remain strings; comma-separated values become arrays. * * @param string|null $category Stored category value. * @return string|array|null Parsed category. */ protected static function category_from_csv( $category ) { if ( ! is_string( $category ) || $category === '' ) { return null; } if ( strpos( $category, ',' ) !== false ) { return array_filter( array_map( 'trim', explode( ',', $category ) ) ); } return $category; } public static function maybe_upgrade() { $installed = get_option( self::DB_VERSION_OPTION ); if ( $installed === self::DB_VERSION ) { return; } self::install_tables(); self::migrate_legacy_assignments(); update_option( self::DB_VERSION_OPTION, self::DB_VERSION, false ); } public static function install_tables() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset_collate = $wpdb->get_charset_collate(); // Suppress errors during table creation to avoid warnings if tables already exist. $wpdb->suppress_errors(); $jobs_table = self::table_name( self::TABLE_JOBS ); $results_table = self::table_name( self::TABLE_RESULTS ); $services_table = self::table_name( self::TABLE_SERVICES ); $hits_table = self::table_name( self::TABLE_SERVICE_HITS ); $sql_jobs = "CREATE TABLE {$jobs_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, job_key varchar(64) NOT NULL, site_url varchar(255) NOT NULL, start_url varchar(2048) NOT NULL, status varchar(20) NOT NULL, source varchar(20) NOT NULL DEFAULT 'browser', resume_cursor longtext NULL, total_targets int NULL, processed_targets int NOT NULL DEFAULT 0, last_error_code varchar(64) NULL, last_error_message text NULL, started_at datetime NULL, finished_at datetime NULL, created_at datetime NOT NULL, updated_at datetime NOT NULL, services longtext NULL, summary longtext NULL, PRIMARY KEY (id), UNIQUE KEY job_key (job_key), KEY status (status), KEY status_updated (status, updated_at) ) {$charset_collate};"; $sql_results = "CREATE TABLE {$results_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, job_id bigint(20) unsigned NOT NULL, url varchar(2048) NOT NULL, source varchar(20) NOT NULL DEFAULT 'browser', captured_at datetime NOT NULL, http_status smallint NULL, payload_hash char(40) NOT NULL, cookies longtext NULL, storage_entries longtext NULL, network_requests longtext NULL, scripts longtext NULL, console_logs longtext NULL, metadata longtext NULL, PRIMARY KEY (id), UNIQUE KEY job_hash (job_id, payload_hash), KEY job_captured (job_id, captured_at), KEY job_source (job_id, source) ) {$charset_collate};"; $sql_services = "CREATE TABLE {$services_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, host varchar(255) NOT NULL, service_key varchar(191) NULL, first_detected_at datetime NULL, last_detected_at datetime NULL, last_job_key varchar(64) NULL, category_assigned varchar(64) NULL, category_suggested varchar(64) NULL, auto_blocking tinyint(1) NOT NULL DEFAULT 1, evidence longtext NULL, notes text NULL, metadata longtext NULL, created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY host (host), KEY assigned (category_assigned), KEY last_detected (last_detected_at) ) {$charset_collate};"; $sql_hits = "CREATE TABLE {$hits_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, service_id bigint(20) unsigned NOT NULL, job_id bigint(20) unsigned NOT NULL, result_id bigint(20) unsigned NOT NULL, type varchar(20) NOT NULL, reference varchar(191) NULL, details longtext NULL, detected_at datetime NOT NULL, PRIMARY KEY (id), KEY service_type (service_id, type), KEY job_id (job_id), KEY result_id (result_id) ) {$charset_collate};"; dbDelta( $sql_jobs ); dbDelta( $sql_results ); dbDelta( $sql_services ); dbDelta( $sql_hits ); $wpdb->suppress_errors( false ); // Verify tables were created. If not, try direct CREATE TABLE as fallback. $jobs_table = self::table_name( self::TABLE_JOBS ); $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $jobs_table ) ) === $jobs_table; if ( ! $table_exists ) { // Fallback: Try direct CREATE TABLE IF NOT EXISTS. // These are safe hardcoded schema definitions, not user input. // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared $sql_jobs_fallback = str_replace( 'CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $sql_jobs ); $sql_results_fallback = str_replace( 'CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $sql_results ); $sql_services_fallback = str_replace( 'CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $sql_services ); $sql_hits_fallback = str_replace( 'CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $sql_hits ); $wpdb->query( $sql_jobs_fallback ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $wpdb->query( $sql_results_fallback ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $wpdb->query( $sql_services_fallback ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $wpdb->query( $sql_hits_fallback ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared } } protected static function migrate_legacy_assignments() { $legacy = get_option( self::LEGACY_ASSIGNMENTS_OPTION, [] ); if ( ! is_array( $legacy ) || empty( $legacy ) ) { return; } global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $now = self::now(); foreach ( $legacy as $host => $category ) { $host_key = sanitize_text_field( $host ); if ( $host_key === '' ) { continue; } $existing_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$services_table} WHERE host = %s LIMIT 1", $host_key ) ); if ( $existing_id ) { if ( $category !== null && $category !== '' ) { $wpdb->update( $services_table, [ 'category_assigned' => sanitize_text_field( $category ), 'updated_at' => $now, ], [ 'id' => $existing_id ], [ '%s', '%s' ], [ '%d' ] ); } continue; } $wpdb->insert( $services_table, [ 'host' => $host_key, 'category_assigned' => $category !== null && $category !== '' ? sanitize_text_field( $category ) : null, 'auto_blocking' => 1, 'first_detected_at' => $now, 'last_detected_at' => $now, 'last_job_key' => null, 'created_at' => $now, 'updated_at' => $now, ], [ '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s' ] ); } delete_option( self::LEGACY_ASSIGNMENTS_OPTION ); } protected static function decode_json( $value, $default = [] ) { if ( empty( $value ) ) { return $default; } if ( is_array( $value ) ) { return $value; } $decoded = json_decode( $value, true ); return is_array( $decoded ) ? $decoded : $default; } protected static function encode_json( $value ) { return wp_json_encode( $value, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE ); } protected static function normalise_host( $value ) { if ( empty( $value ) ) { return ''; } if ( is_string( $value ) && strpos( $value, '//' ) === false && strpos( $value, 'http' ) !== 0 ) { $value = 'https://' . ltrim( $value, '/' ); } $host = wp_parse_url( $value, PHP_URL_HOST ); if ( empty( $host ) ) { return ''; } return strtolower( $host ); } protected static function ensure_tables_exist() { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $jobs_table ) ) === $jobs_table; if ( ! $table_exists ) { self::maybe_upgrade(); // Double-check after upgrade. $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $jobs_table ) ) === $jobs_table; if ( ! $table_exists ) { if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log( 'AS_CM: Critical: Scan tables still missing after upgrade attempt.' ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log } } } } public static function create_job( $start_url, $site_url ) { global $wpdb; // Ensure tables exist before attempting to insert. self::ensure_tables_exist(); $jobs_table = self::table_name( self::TABLE_JOBS ); $job_key = self::generate_job_key(); $now = self::now(); $inserted = $wpdb->insert( $jobs_table, [ 'job_key' => $job_key, 'site_url' => $site_url, 'start_url' => $start_url, 'status' => 'pending', 'source' => 'browser', 'processed_targets' => 0, 'total_targets' => null, 'created_at' => $now, 'updated_at' => $now, 'services' => self::encode_json( [] ), 'summary' => self::encode_json( [ 'hosts' => [], 'cookies' => [], ] ), ], [ '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%s', ] ); if ( ! $inserted ) { $error_message = $wpdb->last_error ? $wpdb->last_error : __( 'Unable to create scan job.', 'adsimple-cookie-manager-for-wp' ); return new WP_Error( 'scan_job_create_failed', $error_message, [ 'db_error' => $wpdb->last_error ] ); } return self::format_job_row( $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$jobs_table} WHERE job_key = %s", $job_key ), ARRAY_A ) ); } public static function list_jobs() { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $rows = $wpdb->get_results( "SELECT * FROM {$jobs_table} ORDER BY created_at DESC", ARRAY_A ); return array_map( [ __CLASS__, 'format_job_row' ], $rows ); } public static function get_job( $job_key ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$jobs_table} WHERE job_key = %s LIMIT 1", $job_key ), ARRAY_A ); return $row ? self::format_job_row( $row ) : null; } protected static function get_job_raw( $job_key ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$jobs_table} WHERE job_key = %s LIMIT 1", $job_key ), ARRAY_A ); } protected static function get_job_by_id( $id ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$jobs_table} WHERE id = %d LIMIT 1", $id ), ARRAY_A ); } protected static function format_job_row( $row ) { if ( ! is_array( $row ) ) { return null; } return [ 'id' => $row['job_key'], 'status' => $row['status'], 'start_url' => $row['start_url'], 'processed_targets' => isset( $row['processed_targets'] ) ? intval( $row['processed_targets'] ) : 0, 'total_targets' => isset( $row['total_targets'] ) ? ( $row['total_targets'] !== null ? intval( $row['total_targets'] ) : null ) : null, 'resume_cursor' => $row['resume_cursor'] ?? null, 'last_error_code' => $row['last_error_code'] ?? null, 'last_error_message'=> $row['last_error_message'] ?? null, 'created_at' => $row['created_at'] ?? null, 'updated_at' => $row['updated_at'] ?? null, ]; } public static function update_job( $job_key, array $data ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $allowed = [ 'status' => '%s', 'resume_cursor' => '%s', 'total_targets' => '%d', 'processed_targets' => '%d', 'last_error_code' => '%s', 'last_error_message'=> '%s', 'started_at' => '%s', 'finished_at' => '%s', 'services' => '%s', 'summary' => '%s', ]; $update = []; $format = []; foreach ( $data as $key => $value ) { if ( array_key_exists( $key, $allowed ) ) { $update[ $key ] = $value; $format[] = $allowed[ $key ]; } } if ( empty( $update ) ) { return self::get_job( $job_key ); } $update['updated_at'] = self::now(); $format[] = '%s'; $wpdb->update( $jobs_table, $update, [ 'job_key' => $job_key ], $format, [ '%s' ] ); return self::get_job( $job_key ); } public static function delete_job( $job_key ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $job = self::get_job_raw( $job_key ); if ( ! $job ) { return false; } $wpdb->delete( $jobs_table, [ 'job_key' => $job_key ], [ '%s' ] ); return true; } public static function record_payload( $job_key, array $payload ) { global $wpdb; $job = self::get_job_raw( $job_key ); if ( ! $job ) { return new WP_Error( 'scan_job_not_found', __( 'Scan job not found.', 'adsimple-cookie-manager-for-wp' ) ); } $results_table = self::table_name( self::TABLE_RESULTS ); $url = isset( $payload['url'] ) ? esc_url_raw( $payload['url'] ) : ''; $source = isset( $payload['source'] ) ? sanitize_text_field( $payload['source'] ) : 'browser'; $http_status = isset( $payload['http_status'] ) ? intval( $payload['http_status'] ) : null; $metadata = isset( $payload['metadata'] ) && is_array( $payload['metadata'] ) ? $payload['metadata'] : []; // New simplified structure: only cookies and hosts $cookies = isset( $payload['cookies'] ) && is_array( $payload['cookies'] ) ? $payload['cookies'] : []; $hosts = isset( $payload['hosts'] ) && is_array( $payload['hosts'] ) ? $payload['hosts'] : []; // Legacy fields - keep for backwards compatibility but default to empty $storage_entries = isset( $payload['storage_entries'] ) && is_array( $payload['storage_entries'] ) ? $payload['storage_entries'] : []; $network_entries = isset( $payload['network_requests'] ) && is_array( $payload['network_requests'] ) ? $payload['network_requests'] : []; $script_entries = isset( $payload['scripts'] ) && is_array( $payload['scripts'] ) ? $payload['scripts'] : []; $console_logs = isset( $payload['console_logs'] ) && is_array( $payload['console_logs'] ) ? $payload['console_logs'] : []; // If using new format (hosts), convert to network_requests for storage if ( ! empty( $hosts ) && empty( $network_entries ) ) { foreach ( $hosts as $host_data ) { if ( ! is_array( $host_data ) || empty( $host_data['host'] ) ) { continue; } // Store host info in network_requests format for DB compatibility $sample_urls = isset( $host_data['sample_urls'] ) && is_array( $host_data['sample_urls'] ) ? $host_data['sample_urls'] : []; $sample_url = ! empty( $sample_urls ) ? $sample_urls[0] : 'https://' . $host_data['host'] . '/'; $network_entries[] = [ 'url' => $sample_url, 'host' => $host_data['host'], 'type' => isset( $host_data['types'] ) ? implode( ',', $host_data['types'] ) : 'external', 'occurrences' => isset( $host_data['occurrences'] ) ? intval( $host_data['occurrences'] ) : 1, ]; } } $hash_seed = [ 'url' => $url, 'cookies' => $cookies, 'hosts' => $hosts, 'metadata' => $metadata, ]; $hash_provided = isset( $payload['payload_hash'] ) ? sanitize_text_field( $payload['payload_hash'] ) : ''; if ( empty( $hash_provided ) ) { $hash = sha1( wp_json_encode( $hash_seed ) ); } else { $hash = substr( sha1( $hash_provided ), 0, 40 ); } $duplicate = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$results_table} WHERE job_id = %d AND payload_hash = %s LIMIT 1", $job['id'], $hash ) ); if ( $duplicate ) { return [ 'stored' => false, 'duplicate' => true, ]; } $now = self::now(); // Build insert data - handle nullable http_status $insert_data = [ 'job_id' => $job['id'], 'url' => $url, 'source' => $source, 'captured_at' => $now, 'payload_hash' => $hash, 'cookies' => self::encode_json( $cookies ), 'storage_entries' => self::encode_json( $storage_entries ), 'network_requests' => self::encode_json( $network_entries ), 'scripts' => self::encode_json( $script_entries ), 'console_logs' => self::encode_json( $console_logs ), 'metadata' => self::encode_json( $metadata ), ]; $insert_format = [ '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ]; // Only include http_status if it's not null if ( $http_status !== null ) { $insert_data['http_status'] = $http_status; $insert_format[] = '%d'; } $inserted = $wpdb->insert( $results_table, $insert_data, $insert_format ); if ( ! $inserted ) { return new WP_Error( 'scan_payload_store_failed', __( 'Unable to persist scan payload.', 'adsimple-cookie-manager-for-wp' ) ); } $result_id = $wpdb->insert_id; self::refresh_summary_for_job( $job['id'] ); self::update_job_processed_count( $job['id'] ); self::track_services_for_payload( $job, $result_id, $payload ); return [ 'stored' => true, 'duplicate' => false, ]; } protected static function update_job_processed_count( $job_id ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $results_table = self::table_name( self::TABLE_RESULTS ); $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$results_table} WHERE job_id = %d", $job_id ) ); $wpdb->update( $jobs_table, [ 'processed_targets' => $count, 'updated_at' => self::now(), ], [ 'id' => $job_id ], [ '%d', '%s' ], [ '%d' ] ); } protected static function refresh_summary_for_job( $job_id ) { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); $results_table = self::table_name( self::TABLE_RESULTS ); $job = self::get_job_by_id( $job_id ); if ( ! $job ) { return; } // Get site host to filter it out from external hosts $site_host = ''; if ( ! empty( $job['site_url'] ) ) { $parsed = wp_parse_url( $job['site_url'] ); $site_host = isset( $parsed['host'] ) ? strtolower( $parsed['host'] ) : ''; } // Load existing summary to preserve user-assigned cookie categories $existing_summary = self::decode_json( $job['summary'], [ 'hosts' => [], 'cookies' => [] ] ); $existing_cookies = []; if ( isset( $existing_summary['cookies'] ) && is_array( $existing_summary['cookies'] ) ) { foreach ( $existing_summary['cookies'] as $existing_cookie ) { $key = strtolower( ( isset( $existing_cookie['name'] ) ? $existing_cookie['name'] : '' ) . '|' . ( isset( $existing_cookie['domain'] ) ? $existing_cookie['domain'] : '' ) ); $existing_cookies[ $key ] = $existing_cookie; } } $rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, url, cookies, network_requests FROM {$results_table} WHERE job_id = %d", $job_id ), ARRAY_A ); // Simplified summary: only hosts and cookies (no scripts) $summary = [ 'hosts' => [], 'cookies' => [], ]; $hosts_map = []; $cookies_map = []; // WordPress cookie prefixes to filter $wp_cookie_prefixes = [ 'wordpress_logged_in_', 'wordpress_sec_', 'wordpress_', 'wp-settings-', 'wp-settings-time-', 'wp-postpass_', 'comment_author_', 'woocommerce_', ]; foreach ( $rows as $row ) { $url = isset( $row['url'] ) ? esc_url_raw( $row['url'] ) : ''; $cookies = self::decode_json( $row['cookies'], [] ); $network_items = self::decode_json( $row['network_requests'], [] ); // Process hosts from network_requests (new format stores hosts here) foreach ( $network_items as $entry ) { if ( ! is_array( $entry ) ) { continue; } // Support both old format (url) and new format (host) $host = ''; if ( isset( $entry['host'] ) && ! empty( $entry['host'] ) ) { $host = strtolower( sanitize_text_field( $entry['host'] ) ); } elseif ( isset( $entry['url'] ) ) { $host = self::normalise_host( $entry['url'] ); } if ( $host === '' ) { continue; } // Filter out site host (e.g., localhost) if ( $host === $site_host ) { continue; } $occurrences = isset( $entry['occurrences'] ) ? intval( $entry['occurrences'] ) : 1; if ( ! isset( $hosts_map[ $host ] ) ) { $hosts_map[ $host ] = [ 'host' => $host, 'occurrences' => 0, 'last_seen_url'=> $url, ]; } $hosts_map[ $host ]['occurrences'] += $occurrences; $hosts_map[ $host ]['last_seen_url'] = $url; } // Process cookies with WordPress filter foreach ( $cookies as $cookie ) { if ( ! is_array( $cookie ) ) { continue; } $name = isset( $cookie['name'] ) ? sanitize_text_field( $cookie['name'] ) : ''; $domain = isset( $cookie['domain'] ) ? sanitize_text_field( $cookie['domain'] ) : ''; if ( $name === '' ) { continue; } // Filter out WordPress cookies $is_wp_cookie = false; foreach ( $wp_cookie_prefixes as $prefix ) { if ( strpos( $name, $prefix ) === 0 ) { $is_wp_cookie = true; break; } } if ( $is_wp_cookie ) { continue; } $key = strtolower( $name . '|' . $domain ); if ( ! isset( $cookies_map[ $key ] ) ) { // Check known cookies for auto-categorization. $known_cookie = self::lookup_known_cookie( $name ); // Priority: 1) existing user-set category, 2) known-cookies auto-category, 3) null $existing = isset( $existing_cookies[ $key ] ) ? $existing_cookies[ $key ] : null; $existing_category = $existing && isset( $existing['category'] ) && $existing['category'] !== null ? $existing['category'] : null; $auto_category = $known_cookie ? $known_cookie['category'] : null; $cookies_map[ $key ] = [ 'name' => $name, 'domain' => $domain !== '' ? $domain : null, 'provider' => $existing && isset( $existing['provider'] ) ? $existing['provider'] : ( $known_cookie ? $known_cookie['provider'] : ( isset( $cookie['provider'] ) ? sanitize_text_field( $cookie['provider'] ) : $domain ) ), 'type' => isset( $cookie['type'] ) ? sanitize_text_field( $cookie['type'] ) : 'HTTP', 'expires' => $known_cookie && isset( $known_cookie['expires'] ) ? $known_cookie['expires'] : ( isset( $cookie['expires'] ) ? sanitize_text_field( $cookie['expires'] ) : 'Session' ), 'description' => isset( $cookie['description'] ) ? sanitize_text_field( $cookie['description'] ) : null, 'purpose' => $existing && isset( $existing['purpose'] ) ? $existing['purpose'] : ( $known_cookie && isset( $known_cookie['purpose'] ) ? $known_cookie['purpose'] : null ), 'count' => 0, 'last_seen_url' => $url, 'category' => $existing_category !== null ? $existing_category : $auto_category, ]; } $cookies_map[ $key ]['count'] += 1; $cookies_map[ $key ]['last_seen_url'] = $url; } } $hosts_list = array_values( $hosts_map ); usort( $hosts_list, static function ( $a, $b ) { return ( $b['occurrences'] ?? 0 ) <=> ( $a['occurrences'] ?? 0 ); } ); $cookies_list = array_values( $cookies_map ); usort( $cookies_list, static function ( $a, $b ) { return ( $b['count'] ?? 0 ) <=> ( $a['count'] ?? 0 ); } ); $summary['hosts'] = $hosts_list; $summary['cookies'] = $cookies_list; $services_map = self::decode_json( $job['services'], [] ); foreach ( $hosts_list as $host ) { $host_key = $host['host']; if ( ! array_key_exists( $host_key, $services_map ) ) { $services_map[ $host_key ] = null; } } foreach ( $summary['hosts'] as $index => $host ) { $host_key = isset( $host['host'] ) ? $host['host'] : ''; if ( $host_key === '' ) { continue; } $summary['hosts'][ $index ]['category'] = isset( $services_map[ $host_key ] ) ? $services_map[ $host_key ] : null; } $wpdb->update( $jobs_table, [ 'services' => self::encode_json( $services_map ), 'summary' => self::encode_json( $summary ), 'updated_at' => self::now(), ], [ 'id' => $job_id ], [ '%s', '%s', '%s' ], [ '%d' ] ); self::sync_services_catalogue( $job['job_key'], $summary, $services_map ); } protected static function track_services_for_payload( array $job, $result_id, array $payload ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $hits_table = self::table_name( self::TABLE_SERVICE_HITS ); $now = self::now(); // Get site host to filter $site_host = ''; if ( ! empty( $job['site_url'] ) ) { $parsed = wp_parse_url( $job['site_url'] ); $site_host = isset( $parsed['host'] ) ? strtolower( $parsed['host'] ) : ''; } $hosts = []; // Support both old format (network_requests) and new format (hosts) $network_items = isset( $payload['network_requests'] ) && is_array( $payload['network_requests'] ) ? $payload['network_requests'] : []; $host_items = isset( $payload['hosts'] ) && is_array( $payload['hosts'] ) ? $payload['hosts'] : []; $cookie_items = isset( $payload['cookies'] ) && is_array( $payload['cookies'] ) ? $payload['cookies'] : []; // Process hosts from new format foreach ( $host_items as $entry ) { if ( ! is_array( $entry ) ) { continue; } $host = isset( $entry['host'] ) ? strtolower( sanitize_text_field( $entry['host'] ) ) : ''; if ( $host === '' || $host === $site_host ) { continue; } if ( ! isset( $hosts[ $host ] ) ) { $hosts[ $host ] = [ 'network' => [], 'scripts' => [], 'cookies' => [], ]; } // Store sample URLs if available $sample_urls = isset( $entry['sample_urls'] ) && is_array( $entry['sample_urls'] ) ? $entry['sample_urls'] : []; foreach ( $sample_urls as $sample_url ) { if ( count( $hosts[ $host ]['network'] ) < 5 ) { $hosts[ $host ]['network'][] = [ 'url' => esc_url_raw( $sample_url ), 'type' => isset( $entry['types'] ) ? implode( ',', $entry['types'] ) : 'external', 'status' => null, ]; } } } // Process hosts from old format (network_requests) foreach ( $network_items as $entry ) { if ( ! is_array( $entry ) ) { continue; } // Support both url-based and host-based entries $host = ''; if ( isset( $entry['host'] ) && ! empty( $entry['host'] ) ) { $host = strtolower( sanitize_text_field( $entry['host'] ) ); } elseif ( isset( $entry['url'] ) ) { $host = self::normalise_host( $entry['url'] ); } if ( $host === '' || $host === $site_host ) { continue; } if ( ! isset( $hosts[ $host ] ) ) { $hosts[ $host ] = [ 'network' => [], 'scripts' => [], 'cookies' => [], ]; } if ( count( $hosts[ $host ]['network'] ) < 5 ) { $hosts[ $host ]['network'][] = [ 'url' => isset( $entry['url'] ) ? esc_url_raw( $entry['url'] ) : '', 'type' => isset( $entry['type'] ) ? sanitize_text_field( $entry['type'] ) : null, 'status' => isset( $entry['status'] ) ? intval( $entry['status'] ) : null, ]; } } // Note: scripts are no longer collected separately in the simplified scan // Cookies are processed but not linked to hosts in the simplified format foreach ( $cookie_items as $entry ) { if ( ! is_array( $entry ) ) { continue; } $setter = isset( $entry['setter'] ) && is_array( $entry['setter'] ) ? $entry['setter'] : []; $host = self::normalise_host( isset( $setter['url'] ) ? $setter['url'] : '' ); if ( $host === '' || $host === $site_host ) { continue; } if ( ! isset( $hosts[ $host ] ) ) { $hosts[ $host ] = [ 'network' => [], 'scripts' => [], 'cookies' => [], ]; } if ( count( $hosts[ $host ]['cookies'] ) < 5 ) { $hosts[ $host ]['cookies'][] = [ 'name' => isset( $entry['name'] ) ? sanitize_text_field( $entry['name'] ) : '', 'domain' => isset( $entry['domain'] ) ? sanitize_text_field( $entry['domain'] ) : '', ]; } } foreach ( $hosts as $host => $evidence ) { $service_row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$services_table} WHERE host = %s LIMIT 1", $host ), ARRAY_A ); if ( $service_row ) { $existing_evidence = self::decode_json( $service_row['evidence'], [] ); $merged_evidence = self::merge_evidence( $existing_evidence, $evidence ); $wpdb->update( $services_table, [ 'last_detected_at' => $now, 'last_job_key' => $job['job_key'], 'evidence' => self::encode_json( $merged_evidence ), 'updated_at' => $now, ], [ 'id' => $service_row['id'] ], [ '%s', '%s', '%s', '%s' ], [ '%d' ] ); $service_id = $service_row['id']; } else { // Check known services for suggested category. $known_service = self::lookup_known_service( $host ); $category_suggested = $known_service ? self::category_to_csv( $known_service['category'] ) : null; $wpdb->insert( $services_table, [ 'host' => $host, 'first_detected_at' => $now, 'last_detected_at' => $now, 'last_job_key' => $job['job_key'], 'evidence' => self::encode_json( $evidence ), 'category_suggested' => $category_suggested, 'created_at' => $now, 'updated_at' => $now, ], [ '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ] ); $service_id = $wpdb->insert_id; } self::insert_hits_for_service( $service_id, $job['id'], $result_id, $evidence ); } } protected static function merge_evidence( $existing, $incoming ) { $merged = [ 'network' => [], 'scripts' => [], 'cookies' => [], ]; foreach ( [ 'network', 'scripts', 'cookies' ] as $key ) { $current = isset( $existing[ $key ] ) && is_array( $existing[ $key ] ) ? $existing[ $key ] : []; $new = isset( $incoming[ $key ] ) && is_array( $incoming[ $key ] ) ? $incoming[ $key ] : []; $merged[ $key ] = array_slice( array_merge( $new, $current ), 0, 10 ); } return $merged; } protected static function insert_hits_for_service( $service_id, $job_id, $result_id, array $evidence ) { global $wpdb; $hits_table = self::table_name( self::TABLE_SERVICE_HITS ); $now = self::now(); foreach ( $evidence as $type => $items ) { if ( ! is_array( $items ) ) { continue; } foreach ( $items as $item ) { $wpdb->insert( $hits_table, [ 'service_id' => $service_id, 'job_id' => $job_id, 'result_id' => $result_id, 'type' => $type, 'reference' => isset( $item['url'] ) ? sanitize_text_field( $item['url'] ) : ( isset( $item['src'] ) ? sanitize_text_field( $item['src'] ) : ( isset( $item['name'] ) ? sanitize_text_field( $item['name'] ) : '' ) ), 'details' => self::encode_json( $item ), 'detected_at' => $now, ], [ '%d', '%d', '%d', '%s', '%s', '%s' ] ); } } } protected static function sync_services_catalogue( $job_key, array $summary, array $services_map ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $now = self::now(); foreach ( $summary['hosts'] as $host ) { $host_key = isset( $host['host'] ) ? $host['host'] : ''; if ( $host_key === '' ) { continue; } $service_row = $wpdb->get_row( $wpdb->prepare( "SELECT id, category_assigned, category_suggested FROM {$services_table} WHERE host = %s LIMIT 1", $host_key ), ARRAY_A ); $current_category = isset( $services_map[ $host_key ] ) ? $services_map[ $host_key ] : null; if ( $service_row ) { $update = [ 'last_detected_at' => $now, 'last_job_key' => $job_key, 'updated_at' => $now, ]; if ( $current_category !== null && $current_category !== $service_row['category_assigned'] ) { $update['category_assigned'] = $current_category; } $wpdb->update( $services_table, $update, [ 'id' => $service_row['id'] ], array_fill( 0, count( $update ), '%s' ), [ '%d' ] ); } else { // Check known services for suggested category. $known_service = self::lookup_known_service( $host_key ); $category_suggested = $known_service ? self::category_to_csv( $known_service['category'] ) : null; $wpdb->insert( $services_table, [ 'host' => $host_key, 'first_detected_at' => $now, 'last_detected_at' => $now, 'last_job_key' => $job_key, 'category_assigned' => $current_category, 'category_suggested' => $category_suggested, 'created_at' => $now, 'updated_at' => $now, ], [ '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ] ); } } } public static function get_results( $job_key, $limit = 10 ) { global $wpdb; $job = self::get_job_raw( $job_key ); if ( ! $job ) { return null; } $results_table = self::table_name( self::TABLE_RESULTS ); $payloads = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$results_table} WHERE job_id = %d ORDER BY captured_at DESC LIMIT %d", $job['id'], absint( $limit ) ), ARRAY_A ); $payloads = array_map( static function ( $row ) { return [ 'id' => $row['id'], 'url' => $row['url'], 'source' => $row['source'], 'captured_at' => $row['captured_at'], 'http_status' => $row['http_status'], 'payload_hash' => $row['payload_hash'], 'cookies' => self::decode_json( $row['cookies'], [] ), 'storage_entries' => self::decode_json( $row['storage_entries'], [] ), 'network_requests' => self::decode_json( $row['network_requests'], [] ), 'scripts' => self::decode_json( $row['scripts'], [] ), 'console_logs' => self::decode_json( $row['console_logs'], [] ), 'metadata' => self::decode_json( $row['metadata'], [] ), ]; }, $payloads ); // Use global assignments which includes category_suggested fallback from known-services.php $services = self::get_global_assignments(); return [ 'summary' => self::decode_json( $job['summary'], [ 'hosts' => [], 'cookies' => [], 'scripts' => [] ] ), 'services' => $services, 'payloads' => $payloads, ]; } public static function update_services_for_job( $job_key, array $services ) { global $wpdb; $job = self::get_job_raw( $job_key ); if ( ! $job ) { return new WP_Error( 'scan_job_not_found', __( 'Scan job not found.', 'adsimple-cookie-manager-for-wp' ) ); } $sanitised = []; foreach ( $services as $host => $category ) { $host_key = sanitize_text_field( $host ); if ( $host_key === '' ) { continue; } $sanitised[ $host_key ] = self::category_to_csv( $category ); } $summary = self::decode_json( $job['summary'], [ 'hosts' => [], 'cookies' => [], 'scripts' => [] ] ); foreach ( $summary['hosts'] as $host ) { $host_key = isset( $host['host'] ) ? $host['host'] : ''; if ( $host_key === '' ) { continue; } if ( ! array_key_exists( $host_key, $sanitised ) ) { $sanitised[ $host_key ] = null; } } $jobs_table = self::table_name( self::TABLE_JOBS ); $services_table = self::table_name( self::TABLE_SERVICES ); $now = self::now(); $wpdb->update( $jobs_table, [ 'services' => self::encode_json( $sanitised ), 'updated_at' => $now, ], [ 'id' => $job['id'] ], [ '%s', '%s' ], [ '%d' ] ); foreach ( $sanitised as $host => $category ) { $service_row = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$services_table} WHERE host = %s LIMIT 1", $host ), ARRAY_A ); if ( $service_row ) { $wpdb->update( $services_table, [ 'category_assigned' => $category, 'updated_at' => $now, ], [ 'id' => $service_row['id'] ], [ '%s', '%s' ], [ '%d' ] ); } else { // Check known services for suggested category. $known_service = self::lookup_known_service( $host ); $category_suggested = $known_service ? self::category_to_csv( $known_service['category'] ) : null; $wpdb->insert( $services_table, [ 'host' => $host, 'category_assigned' => $category, 'category_suggested' => $category_suggested, 'first_detected_at' => $now, 'last_detected_at' => $now, 'last_job_key' => $job['job_key'], 'created_at' => $now, 'updated_at' => $now, ], [ '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ] ); } } return $sanitised; } /** * Save scan data including services and cookie metadata. * * @param string $job_key The job key. * @param array $services Array of host => category assignments. * @param array $cookies Array of cookie metadata objects. * @return array|WP_Error The saved data or error. */ public static function save_scan_data( $job_key, array $services, array $cookies ) { global $wpdb; $job = self::get_job_raw( $job_key ); if ( ! $job ) { return new WP_Error( 'scan_job_not_found', __( 'Scan job not found.', 'adsimple-cookie-manager-for-wp' ) ); } // Update services first (supports CSV for multi-category) $sanitised_services = []; foreach ( $services as $host => $category ) { $host_key = sanitize_text_field( $host ); if ( $host_key === '' ) { continue; } $sanitised_services[ $host_key ] = self::category_to_csv( $category ); } // Process cookies with metadata $summary = self::decode_json( $job['summary'], [ 'hosts' => [], 'cookies' => [] ] ); $cookies_map = []; // Index existing cookies by key foreach ( $summary['cookies'] as $existing_cookie ) { $key = strtolower( ( isset( $existing_cookie['name'] ) ? $existing_cookie['name'] : '' ) . '|' . ( isset( $existing_cookie['domain'] ) ? $existing_cookie['domain'] : '' ) ); $cookies_map[ $key ] = $existing_cookie; } // Update cookies with new metadata foreach ( $cookies as $cookie_data ) { if ( ! is_array( $cookie_data ) || empty( $cookie_data['name'] ) ) { continue; } $name = sanitize_text_field( $cookie_data['name'] ); $domain = isset( $cookie_data['domain'] ) ? sanitize_text_field( $cookie_data['domain'] ) : ''; $key = strtolower( $name . '|' . $domain ); if ( isset( $cookies_map[ $key ] ) ) { // Update existing cookie with new metadata if ( isset( $cookie_data['provider'] ) ) { $cookies_map[ $key ]['provider'] = sanitize_text_field( $cookie_data['provider'] ); } if ( isset( $cookie_data['expires'] ) ) { $cookies_map[ $key ]['expires'] = sanitize_text_field( $cookie_data['expires'] ); } if ( isset( $cookie_data['type'] ) ) { $cookies_map[ $key ]['type'] = sanitize_text_field( $cookie_data['type'] ); } if ( isset( $cookie_data['purpose'] ) ) { $cookies_map[ $key ]['purpose'] = sanitize_textarea_field( $cookie_data['purpose'] ); } if ( array_key_exists( 'category', $cookie_data ) ) { $cookies_map[ $key ]['category'] = $cookie_data['category'] !== null && $cookie_data['category'] !== '' ? sanitize_text_field( $cookie_data['category'] ) : null; } } } // Update summary with updated cookies $summary['cookies'] = array_values( $cookies_map ); // Update hosts with assigned categories foreach ( $summary['hosts'] as $index => $host ) { $host_key = isset( $host['host'] ) ? $host['host'] : ''; if ( $host_key === '' ) { continue; } if ( ! array_key_exists( $host_key, $sanitised_services ) ) { $sanitised_services[ $host_key ] = null; } $summary['hosts'][ $index ]['category'] = isset( $sanitised_services[ $host_key ] ) ? $sanitised_services[ $host_key ] : null; } $jobs_table = self::table_name( self::TABLE_JOBS ); $now = self::now(); $wpdb->update( $jobs_table, [ 'services' => self::encode_json( $sanitised_services ), 'summary' => self::encode_json( $summary ), 'updated_at' => $now, ], [ 'id' => $job['id'] ], [ '%s', '%s', '%s' ], [ '%d' ] ); // Update services catalogue $services_table = self::table_name( self::TABLE_SERVICES ); foreach ( $sanitised_services as $host => $category ) { $service_row = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$services_table} WHERE host = %s LIMIT 1", $host ), ARRAY_A ); if ( $service_row ) { $wpdb->update( $services_table, [ 'category_assigned' => $category, 'updated_at' => $now, ], [ 'id' => $service_row['id'] ], [ '%s', '%s' ], [ '%d' ] ); } } return [ 'services' => $sanitised_services, 'cookies' => $summary['cookies'], ]; } public static function mark_job_completed( $job_key, $resume_cursor = null ) { $update = [ 'status' => 'completed', 'resume_cursor' => $resume_cursor !== null ? sanitize_text_field( $resume_cursor ) : null, 'finished_at' => self::now(), ]; self::update_job( $job_key, $update ); return self::get_job( $job_key ); } public static function mark_job_failed( $job_key, $error_code, $error_message ) { $update = [ 'status' => 'failed', 'last_error_code' => sanitize_key( $error_code ), 'last_error_message'=> sanitize_text_field( $error_message ), 'finished_at' => self::now(), ]; self::update_job( $job_key, $update ); return self::get_job( $job_key ); } public static function claim_job( $job_key, $resume_cursor = null ) { $update = [ 'status' => 'running', 'resume_cursor' => $resume_cursor !== null ? sanitize_text_field( $resume_cursor ) : null, 'started_at' => self::now(), ]; self::update_job( $job_key, $update ); return self::get_job( $job_key ); } public static function heartbeat_job( $job_key, $processed_targets = null, $resume_cursor = null ) { $update = []; if ( $processed_targets !== null ) { $update['processed_targets'] = max( 0, intval( $processed_targets ) ); } if ( $resume_cursor !== null ) { $update['resume_cursor'] = sanitize_text_field( $resume_cursor ); } self::update_job( $job_key, $update ); return self::get_job( $job_key ); } public static function get_global_assignments() { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $rows = $wpdb->get_results( "SELECT host, category_assigned, category_suggested, auto_blocking FROM {$services_table}", ARRAY_A ); $map = []; foreach ( $rows as $row ) { $host = $row['host']; if ( $host === '' ) { continue; } // Use assigned category if set (supports CSV for multi-category). if ( isset( $row['category_assigned'] ) && $row['category_assigned'] !== '' ) { $map[ $host ] = self::category_from_csv( $row['category_assigned'] ); continue; } // Fall back to suggested category (from known-services, may be CSV). if ( isset( $row['category_suggested'] ) && $row['category_suggested'] !== '' ) { $map[ $host ] = self::category_from_csv( $row['category_suggested'] ); continue; } // If auto-blocking is enabled but no category, use default (blocked). if ( ! empty( $row['auto_blocking'] ) ) { $map[ $host ] = ''; } } return $map; } /** * Get blocking data with cookies and hosts grouped by category. * Used to display cookie details in the frontend banner. * * @return array Array with 'services' (host => category), 'cookies' (list), 'hosts' (list) */ public static function get_blocking_data() { global $wpdb; $jobs_table = self::table_name( self::TABLE_JOBS ); // Get the most recent completed job's summary $job = $wpdb->get_row( "SELECT summary FROM {$jobs_table} WHERE status = 'completed' ORDER BY finished_at DESC LIMIT 1", ARRAY_A ); $services = self::get_global_assignments(); $cookies = []; $hosts = []; if ( $job && ! empty( $job['summary'] ) ) { $summary = self::decode_json( $job['summary'], [] ); // Process cookies with metadata if ( isset( $summary['cookies'] ) && is_array( $summary['cookies'] ) ) { foreach ( $summary['cookies'] as $cookie ) { if ( ! is_array( $cookie ) || empty( $cookie['name'] ) ) { continue; } $cookies[] = [ 'name' => isset( $cookie['name'] ) ? $cookie['name'] : '', 'provider' => isset( $cookie['provider'] ) ? $cookie['provider'] : ( isset( $cookie['domain'] ) ? $cookie['domain'] : '' ), 'purpose' => isset( $cookie['purpose'] ) ? $cookie['purpose'] : '', 'expires' => isset( $cookie['expires'] ) ? $cookie['expires'] : 'Session', 'type' => isset( $cookie['type'] ) ? $cookie['type'] : 'HTTP', 'category' => isset( $cookie['category'] ) ? $cookie['category'] : 'unclassified', ]; } } // Process hosts if ( isset( $summary['hosts'] ) && is_array( $summary['hosts'] ) ) { foreach ( $summary['hosts'] as $host ) { if ( ! is_array( $host ) || empty( $host['host'] ) ) { continue; } $host_key = $host['host']; if ( isset( $services[ $host_key ] ) ) { $category = $services[ $host_key ]; } elseif ( isset( $host['category'] ) ) { $category = self::category_from_csv( $host['category'] ); } else { $category = 'unclassified'; } $hosts[] = [ 'host' => $host_key, 'category' => $category, ]; } } } return [ 'services' => $services, 'cookies' => $cookies, 'hosts' => $hosts, ]; } /** * List all services with optional filtering. * * @param string|null $category Filter by category. * @param string|null $search Search term for host. * @return array */ public static function list_all_services( $category = null, $search = null ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $where = []; $values = []; if ( $category !== null && $category !== '' ) { if ( $category === 'unassigned' ) { $where[] = '(category_assigned IS NULL OR category_assigned = "")'; } else { $where[] = 'category_assigned = %s'; $values[] = $category; } } if ( $search !== null && $search !== '' ) { $where[] = 'host LIKE %s'; $values[] = '%' . $wpdb->esc_like( $search ) . '%'; } $where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : ''; if ( ! empty( $values ) ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$services_table} {$where_sql} ORDER BY last_detected_at DESC", $values ), ARRAY_A ); } else { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $rows = $wpdb->get_results( "SELECT * FROM {$services_table} ORDER BY last_detected_at DESC", ARRAY_A ); } return array_map( [ __CLASS__, 'format_service_row' ], $rows ); } /** * Get a single service by ID. * * @param int $id Service ID. * @return array|null */ public static function get_service_by_id( $id ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$services_table} WHERE id = %d LIMIT 1", $id ), ARRAY_A ); return $row ? self::format_service_row( $row ) : null; } /** * Update a service. * * @param int $id Service ID. * @param array $data Fields to update. * @return array|null */ public static function update_service( $id, array $data ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $allowed = [ 'category_assigned' => '%s', 'category_suggested' => '%s', 'auto_blocking' => '%d', 'notes' => '%s', ]; // Convert array categories to CSV for storage. if ( isset( $data['category_assigned'] ) ) { $data['category_assigned'] = self::category_to_csv( $data['category_assigned'] ); } if ( isset( $data['category_suggested'] ) ) { $data['category_suggested'] = self::category_to_csv( $data['category_suggested'] ); } $update = []; $format = []; foreach ( $data as $key => $value ) { if ( array_key_exists( $key, $allowed ) ) { $update[ $key ] = $value; $format[] = $allowed[ $key ]; } } if ( empty( $update ) ) { return self::get_service_by_id( $id ); } $update['updated_at'] = self::now(); $format[] = '%s'; $wpdb->update( $services_table, $update, [ 'id' => $id ], $format, [ '%d' ] ); return self::get_service_by_id( $id ); } /** * Delete a service. * * @param int $id Service ID. * @return bool */ public static function delete_service( $id ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $hits_table = self::table_name( self::TABLE_SERVICE_HITS ); // Delete related hits first. $wpdb->delete( $hits_table, [ 'service_id' => $id ], [ '%d' ] ); $deleted = $wpdb->delete( $services_table, [ 'id' => $id ], [ '%d' ] ); return $deleted > 0; } /** * Create a new service entry manually (not from a scan). * * @param string $host The hostname. * @param string|array|null $category Category or array of categories. * @return array|WP_Error The created service or error. */ public static function create_service( $host, $category = null ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); $host = strtolower( sanitize_text_field( $host ) ); if ( $host === '' ) { return new WP_Error( 'invalid_host', __( 'A valid hostname is required.', 'adsimple-cookie-manager-for-wp' ) ); } $existing = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$services_table} WHERE host = %s LIMIT 1", $host ), ARRAY_A ); if ( $existing ) { return new WP_Error( 'host_exists', __( 'This host already exists.', 'adsimple-cookie-manager-for-wp' ) ); } $known_service = self::lookup_known_service( $host ); $category_csv = self::category_to_csv( $category ); $category_suggested = $known_service ? self::category_to_csv( $known_service['category'] ) : null; $now = self::now(); $wpdb->insert( $services_table, [ 'host' => $host, 'category_assigned' => $category_csv, 'category_suggested' => $category_suggested, 'auto_blocking' => 1, 'first_detected_at' => $now, 'last_detected_at' => $now, 'created_at' => $now, 'updated_at' => $now, ], [ '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s' ] ); return self::get_service_by_id( $wpdb->insert_id ); } /** * Bulk update services. * * @param array $ids Service IDs. * @param string|null $category Category to assign. * @param int|null $auto_blocking Auto-blocking flag. * @return int Number of updated services. */ public static function bulk_update_services( array $ids, $category = null, $auto_blocking = null ) { global $wpdb; $services_table = self::table_name( self::TABLE_SERVICES ); if ( empty( $ids ) ) { return 0; } if ( $category === null && $auto_blocking === null ) { return 0; } // Convert array categories to CSV for storage. $category_csv = $category !== null ? self::category_to_csv( $category ) : null; $now = self::now(); $placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) ); // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber if ( $category !== null && $auto_blocking !== null ) { $wpdb->query( $wpdb->prepare( "UPDATE {$services_table} SET category_assigned = %s, auto_blocking = %d, updated_at = %s WHERE id IN ({$placeholders})", array_merge( [ $category_csv, $auto_blocking, $now ], $ids ) ) ); } elseif ( $category !== null ) { $wpdb->query( $wpdb->prepare( "UPDATE {$services_table} SET category_assigned = %s, updated_at = %s WHERE id IN ({$placeholders})", array_merge( [ $category_csv, $now ], $ids ) ) ); } else { $wpdb->query( $wpdb->prepare( "UPDATE {$services_table} SET auto_blocking = %d, updated_at = %s WHERE id IN ({$placeholders})", array_merge( [ $auto_blocking, $now ], $ids ) ) ); } // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber return count( $ids ); } /** * Format a service row for API response. * * @param array $row Database row. * @return array */ protected static function format_service_row( $row ) { if ( ! is_array( $row ) ) { return []; } return [ 'id' => isset( $row['id'] ) ? intval( $row['id'] ) : 0, 'host' => $row['host'] ?? '', 'service_key' => $row['service_key'] ?? null, 'first_detected_at' => $row['first_detected_at'] ?? null, 'last_detected_at' => $row['last_detected_at'] ?? null, 'last_job_key' => $row['last_job_key'] ?? null, 'category_assigned' => self::category_from_csv( $row['category_assigned'] ?? null ), 'category_suggested' => self::category_from_csv( $row['category_suggested'] ?? null ), 'auto_blocking' => ! empty( $row['auto_blocking'] ), 'evidence' => self::decode_json( $row['evidence'] ?? '', [] ), 'notes' => $row['notes'] ?? null, 'created_at' => $row['created_at'] ?? null, 'updated_at' => $row['updated_at'] ?? null, ]; } /** * Lookup a host in the known services database. * * @param string $host The host to look up. * @return array|null Known service data or null if not found. */ public static function lookup_known_service( $host ) { static $known_services = null; if ( $known_services === null ) { $known_services = self::load_known_services(); } $host = strtolower( trim( $host ) ); // Direct match. if ( isset( $known_services[ $host ] ) ) { return $known_services[ $host ]; } // Try without www prefix. if ( strpos( $host, 'www.' ) === 0 ) { $without_www = substr( $host, 4 ); if ( isset( $known_services[ $without_www ] ) ) { return $known_services[ $without_www ]; } } // Try with www prefix. $with_www = 'www.' . $host; if ( isset( $known_services[ $with_www ] ) ) { return $known_services[ $with_www ]; } // Check for wildcard matches (e.g., *.example.com). foreach ( $known_services as $pattern => $data ) { if ( strpos( $pattern, '*.' ) === 0 ) { $suffix = substr( $pattern, 1 ); // .example.com if ( substr( $host, - strlen( $suffix ) ) === $suffix ) { return $data; } } } return null; } /** * Load the known services database. * * @return array */ protected static function load_known_services() { // Use AS_CM_DIR if defined, otherwise calculate from this file's location. if ( defined( 'AS_CM_DIR' ) ) { $file = AS_CM_DIR . 'includes/data/known-services.php'; } else { // Fallback: Calculate plugin root from this file's path // This file is in: plugin-root/includes/services/scan/storage.php $file = dirname( __DIR__, 3 ) . '/includes/data/known-services.php'; } if ( ! file_exists( $file ) ) { return []; } $services = include $file; if ( ! is_array( $services ) ) { return []; } // Normalize keys to lowercase. $normalized = []; foreach ( $services as $host => $data ) { $normalized[ strtolower( $host ) ] = $data; } return $normalized; } /** * Get all known services (for admin display). * * @return array */ public static function get_known_services() { return self::load_known_services(); } /** * Lookup a cookie name in the known cookies database. * * @param string $cookie_name The cookie name to look up. * @return array|null Known cookie data or null if not found. */ public static function lookup_known_cookie( $cookie_name ) { static $known_cookies = null; if ( $known_cookies === null ) { $known_cookies = self::load_known_cookies(); } $cookie_name = strtolower( trim( $cookie_name ) ); // Direct match. if ( isset( $known_cookies[ $cookie_name ] ) ) { return $known_cookies[ $cookie_name ]; } // Check for pattern matches (e.g., _ga_*, *_cookie, etc.). foreach ( $known_cookies as $pattern => $data ) { // Prefix match: pattern_* if ( substr( $pattern, -1 ) === '*' ) { $prefix = substr( $pattern, 0, -1 ); if ( strpos( $cookie_name, $prefix ) === 0 ) { return $data; } } // Suffix match: *_pattern if ( strpos( $pattern, '*' ) === 0 && substr( $pattern, -1 ) !== '*' ) { $suffix = substr( $pattern, 1 ); if ( substr( $cookie_name, - strlen( $suffix ) ) === $suffix ) { return $data; } } // Contains match: pattern.* (for patterns like _pk_id.*) if ( strpos( $pattern, '.*' ) !== false ) { $prefix = str_replace( '.*', '.', $pattern ); if ( strpos( $cookie_name, $prefix ) === 0 ) { return $data; } } } return null; } /** * Load the known cookies database. * * @return array */ protected static function load_known_cookies() { // Use AS_CM_DIR if defined, otherwise calculate from this file's location. if ( defined( 'AS_CM_DIR' ) ) { $file = AS_CM_DIR . 'includes/data/known-cookies.php'; } else { // Fallback: Calculate plugin root from this file's path // This file is in: plugin-root/includes/services/scan/storage.php $file = dirname( __DIR__, 3 ) . '/includes/data/known-cookies.php'; } if ( ! file_exists( $file ) ) { return []; } $cookies = include $file; if ( ! is_array( $cookies ) ) { return []; } // Normalize keys to lowercase. $normalized = []; foreach ( $cookies as $name => $data ) { $normalized[ strtolower( $name ) ] = $data; } return $normalized; } /** * Get all known cookies (for admin display). * * @return array */ public static function get_known_cookies() { return self::load_known_cookies(); } }