" would become "SC", Seychelles). $country = strtoupper( sanitize_text_field( (string) ( $data['country_code'] ?? '' ) ) ); if ( ! preg_match( '/^[A-Z]{2}$/', $country ) ) { $country = ''; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom audit-log table; write-only insert. $wpdb->insert( $wpdb->prefix . COOKROCO_LOG_TABLE, [ 'session_id' => $this->get_session_id(), 'ip_hash' => hash_hmac('sha256',$ip,wp_salt('auth')), 'country_code' => $country, 'law_applied' => sanitize_key($data['law_applied']??'generic'), 'action' => sanitize_key($data['action']??'accept_all'), 'categories_accepted' => wp_json_encode($data['categories_accepted']??[]), // The version the visitor was shown (validated by the caller), else the site's current one. 'policy_version' => mb_substr( sanitize_text_field( (string) ( $data['policy_version'] ?? ( get_option('cookroco_settings',[])['policy_version'] ?? '1.0' ) ) ), 0, 20 ), 'created_at' => current_time('mysql'), ], ['%s','%s','%s','%s','%s','%s','%s','%s'] ); /** Fires after a consent record is written. Cookie Rocket Pro hooks this to seal the row into the tamper-evident hash-chained ledger. Inert in the free plugin. */ do_action( 'cookroco_consent_recorded', (int) $wpdb->insert_id, $data ); } /** * Visitor breakdown by legal framework over the last N days. * * Built on country_code, which the banner resolves from the time zone. No IP, no external * service and no new personal data: it tells the owner where their visitors come from, * which is the only thing that decides which law applies. * * @param int $days Window in days. * @return array{total:int, gdpr:int, ccpa:int, lgpd:int, lfpdppp:int, other:int} */ public function region_breakdown( int $days = 30 ): array { global $wpdb; $t = $wpdb->prefix . COOKROCO_LOG_TABLE; $since = gmdate( 'Y-m-d H:i:s', time() + ( (int) get_option( 'gmt_offset', 0 ) * HOUR_IN_SECONDS ) - ( max( 1, $days ) * DAY_IN_SECONDS ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- The table name cannot be a placeholder; the date does go through prepare(). $rows = $wpdb->get_results( $wpdb->prepare( "SELECT country_code AS c, COUNT(*) AS n FROM `{$t}` WHERE created_at >= %s AND country_code <> '' GROUP BY country_code", $since ), ARRAY_A ); $ue = [ 'AT','BE','BG','HR','CY','CZ','DK','EE','FI','FR','DE','GR','HU','IE','IT','LV','LT','LU','MT','NL','PL','PT','RO','SK','SI','ES','SE','IS','LI','NO','GB' ]; $out = [ 'total' => 0, 'gdpr' => 0, 'ccpa' => 0, 'lgpd' => 0, 'lfpdppp' => 0, 'other' => 0 ]; foreach ( (array) $rows as $f ) { $c = strtoupper( (string) $f['c'] ); $n = (int) $f['n']; $out['total'] += $n; if ( in_array( $c, $ue, true ) ) { $out['gdpr'] += $n; } elseif ( 'US' === $c ) { $out['ccpa'] += $n; } elseif ( 'BR' === $c ) { $out['lgpd'] += $n; } elseif ( 'MX' === $c ) { $out['lfpdppp'] += $n; } else { $out['other'] += $n; } } return $out; } public function get_stats(): array { global $wpdb; $t = esc_sql( $wpdb->prefix . COOKROCO_LOG_TABLE ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table; admin stats are not cache-critical. $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$t}`" ); // Boundary in the SAME basis as created_at (written with current_time('mysql') = site-local, // i.e. UTC + the fixed gmt_offset). Comparing against a UTC gmdate() here would be off by the // site's offset and mis-count rows near the 7-day edge. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table; date filter prepared. $last7 = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$t}` WHERE created_at >= %s", gmdate( 'Y-m-d H:i:s', time() + (int) ( get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ) - 7 * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table; static SQL. $by_action = $wpdb->get_results( "SELECT action, COUNT(*) as count FROM `{$t}` GROUP BY action", ARRAY_A ); return [ 'total' => $total, 'last7days' => $last7, 'by_action' => $by_action ?: [], 'isPro' => false ]; } public function get_records( int $limit = 20, int $offset = 0 ): array { global $wpdb; $t = esc_sql( $wpdb->prefix . COOKROCO_LOG_TABLE ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table; paginated admin read. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT created_at, action, categories_accepted, policy_version, ip_hash, session_id FROM `{$t}` ORDER BY id DESC LIMIT %d OFFSET %d", $limit, $offset ), ARRAY_A ); return $rows ?: []; } /** * A person's records: by the consent ID their browser carries (or its first characters), or by * the IP address they used. The address is turned into the same keyed hash the log stores and * is used for this search only, never kept. * * @param string $query Consent ID (6+ characters) or IP address. * @param int $limit Most rows returned. * @return array|null The matching rows, newest first, or null when the query is neither. */ public function find_records( string $query, int $limit = 100 ): ?array { global $wpdb; $t = esc_sql( $wpdb->prefix . COOKROCO_LOG_TABLE ); $query = trim( $query ); $packed = filter_var( $query, FILTER_VALIDATE_IP ) ? inet_pton( $query ) : false; if ( false !== $packed ) { // The same text form the server saw: an IPv6 address written another way still matches. $hash = hash_hmac( 'sha256', (string) inet_ntop( $packed ), wp_salt( 'auth' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table; admin lookup. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT created_at, action, categories_accepted, policy_version, ip_hash, session_id FROM `{$t}` WHERE ip_hash = %s ORDER BY id DESC LIMIT %d", $hash, $limit ), ARRAY_A ); return $rows ?: []; } if ( preg_match( '/^[0-9a-f-]{6,64}$/i', $query ) ) { $like = $wpdb->esc_like( strtolower( $query ) ) . '%'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table; admin lookup. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT created_at, action, categories_accepted, policy_version, ip_hash, session_id FROM `{$t}` WHERE session_id LIKE %s OR ip_hash LIKE %s ORDER BY id DESC LIMIT %d", $like, $like, $limit ), ARRAY_A ); return $rows ?: []; } return null; } public function cleanup_old_records(): void { global $wpdb; /** Fires right before the weekly purge (Cookie Rocket Pro counts the unsealed rows it will touch). */ do_action( 'cookroco_before_log_cleanup' ); $days = self::retention_days(); $table = esc_sql( $wpdb->prefix . COOKROCO_LOG_TABLE ); // Pro narrows this to UNSEALED rows via the filter (its tamper-evident sealed consent records // are the proof of consent and are never auto-pruned). $extra = (string) apply_filters( 'cookroco_retention_where', '' ); // A site that ran Cookie Rocket Pro has sealed rows (record_hash) chained to each other. // Pruning one breaks the chain for good, so they are spared here too — after a move back // to the free edition, Pro's filter is gone but the chain must survive a return to Pro. if ( false === strpos( $extra, 'record_hash' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom audit-log table, weekly cron. if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'record_hash' ) ) ) { $extra .= ' AND record_hash IS NULL'; } } // Same local basis as created_at (current_time('mysql')): UTC + fixed gmt_offset, minus the // retention window. A UTC gmdate() boundary would purge rows up to the site's offset early/late. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom audit-log table; $extra is a fixed AND-clause from this plugin or its own add-on, never user input; date filter prepared. $wpdb->query( $wpdb->prepare( "DELETE FROM `{$table}` WHERE created_at < %s{$extra}", gmdate( 'Y-m-d H:i:s', time() + (int) ( get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ) - $days * DAY_IN_SECONDS ) ) ); /** Fires right after the weekly purge (Cookie Rocket Pro accounts for what it removed). */ do_action( 'cookroco_after_log_cleanup' ); } /** * How long records are kept, in days: what the cleanup applies and every screen shows. * * A record is never deleted while the consent it proves is still valid: the retention * setting can shorten how long records outlive consent, never the consent itself. */ public static function retention_days(): int { $s = (array) get_option( 'cookroco_settings', [] ); /** * The longest a consent can last on this site, in days. Cookie Rocket Pro raises it when a * region keeps consent for longer than the general setting. Inert in the free plugin. */ $longest = absint( apply_filters( 'cookroco_longest_consent_days', absint( $s['consent_duration_days'] ?? 365 ) ) ); return max( absint( $s['log_retention_days'] ?? 365 ), $longest ); } private function get_session_id(): string { $name = 'cookroco_session'; if ( isset( $_COOKIE[ $name ] ) ) return sanitize_text_field( wp_unslash( $_COOKIE[ $name ] ) ); $id = wp_generate_uuid4(); // Persist a first-party session id so a visitor's LATER consent events (re-consent, withdraw) // correlate to the same session, instead of each record getting a fresh UUID and idx_session // being useless. No Secure attr (survives http://); set before any output. if ( ! headers_sent() ) { setcookie( $name, $id, [ 'expires' => time() + YEAR_IN_SECONDS, 'path' => '/', 'samesite' => 'Lax' ] ); $_COOKIE[ $name ] = $id; } return $id; } }