base_prefix . self::TABLE_NAME (a hardcoded constant). No user input reaches the table name. /** * Class Statistics * * Handles email statistics tracking and retrieval. * * @since 1.3.0 */ class Statistics { /** * Database table name (without prefix). * * @var string */ private const TABLE_NAME = 'robotstxt_smtp_stats'; /** * Database version for migration tracking. * * @var string */ private const DB_VERSION = '1.0'; /** * Option name for storing database version. * * @var string */ private const DB_VERSION_OPTION = 'robotstxt_smtp_stats_db_version'; /** * Singleton instance. * * @var Statistics|null */ private static ?Statistics $instance = null; /** * Gets the singleton instance. * * @return Statistics */ public static function get_instance(): Statistics { if ( null === self::$instance ) { self::$instance = new self(); } return self::$instance; } /** * Private constructor to enforce singleton. */ private function __construct() { $this->maybe_create_table(); } /** * Gets the full table name with WordPress prefix. * * @return string */ public static function get_table_name(): string { global $wpdb; return $wpdb->base_prefix . self::TABLE_NAME; } /** * Creates the statistics table if it doesn't exist. * * @return void */ private function maybe_create_table(): void { $raw_version = get_site_option( self::DB_VERSION_OPTION, '0' ); $installed_version = is_string( $raw_version ) ? $raw_version : '0'; if ( version_compare( $installed_version, self::DB_VERSION, '>=' ) ) { return; } global $wpdb; $table_name = self::get_table_name(); $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$table_name} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, blog_id bigint(20) unsigned NOT NULL DEFAULT 1, date date NOT NULL, emails_sent bigint(20) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY blog_date (blog_id, date), KEY date_idx (date), KEY blog_id_idx (blog_id) ) {$charset_collate};"; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; dbDelta( $sql ); update_site_option( self::DB_VERSION_OPTION, self::DB_VERSION ); } /** * Increments the email counter for today. * * @param int|null $blog_id Blog ID (defaults to current blog). * * @return bool True on success, false on failure. */ public function increment_today( ?int $blog_id = null ): bool { global $wpdb; if ( null === $blog_id ) { $blog_id = get_current_blog_id(); } $table_name = self::get_table_name(); $today = current_time( 'Y-m-d' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $result = $wpdb->query( $wpdb->prepare( "INSERT INTO {$table_name} (blog_id, date, emails_sent) VALUES (%d, %s, 1) ON DUPLICATE KEY UPDATE emails_sent = emails_sent + 1", $blog_id, $today ) ); return false !== $result; } /** * Gets statistics for a specific date range. * * @param string $start_date Start date (Y-m-d format). * @param string $end_date End date (Y-m-d format). * @param int|null $blog_id Blog ID (null for all sites). * * @return list Array of statistics objects. */ public function get_stats( string $start_date, string $end_date, ?int $blog_id = null ): array { global $wpdb; $table_name = self::get_table_name(); if ( null !== $blog_id ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $results = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id, date, emails_sent FROM {$table_name} WHERE blog_id = %d AND date BETWEEN %s AND %s ORDER BY date ASC", $blog_id, $start_date, $end_date ) ); } else { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $results = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id, date, emails_sent FROM {$table_name} WHERE date BETWEEN %s AND %s ORDER BY date ASC, blog_id ASC", $start_date, $end_date ) ); } return array_values( array_filter( is_array( $results ) ? $results : array(), 'is_object' ) ); } /** * Gets total emails sent for a date range. * * @param string $start_date Start date (Y-m-d format). * @param string $end_date End date (Y-m-d format). * @param int|null $blog_id Blog ID (null for all sites). * * @return int Total emails sent. */ public function get_total( string $start_date, string $end_date, ?int $blog_id = null ): int { global $wpdb; $table_name = self::get_table_name(); if ( null !== $blog_id ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $total = $wpdb->get_var( $wpdb->prepare( "SELECT SUM(emails_sent) FROM {$table_name} WHERE blog_id = %d AND date BETWEEN %s AND %s", $blog_id, $start_date, $end_date ) ); } else { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $total = $wpdb->get_var( $wpdb->prepare( "SELECT SUM(emails_sent) FROM {$table_name} WHERE date BETWEEN %s AND %s", $start_date, $end_date ) ); } return absint( $total ); } /** * Gets statistics aggregated by blog for multisite. * * @param string $start_date Start date (Y-m-d format). * @param string $end_date End date (Y-m-d format). * * @return list Array with blog_id and total_emails. */ public function get_stats_by_blog( string $start_date, string $end_date ): array { global $wpdb; $table_name = self::get_table_name(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $results = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id, SUM(emails_sent) as total_emails FROM {$table_name} WHERE date BETWEEN %s AND %s GROUP BY blog_id ORDER BY total_emails DESC", $start_date, $end_date ) ); return array_values( array_filter( is_array( $results ) ? $results : array(), 'is_object' ) ); } /** * Deletes statistics older than specified days. * * @param int $days Number of days to retain. * * @return int Number of rows deleted. */ public function cleanup_old_stats( int $days ): int { global $wpdb; $table_name = self::get_table_name(); $cutoff_timestamp = strtotime( "-{$days} days" ); $cutoff_date = false !== $cutoff_timestamp ? gmdate( 'Y-m-d', $cutoff_timestamp ) : gmdate( 'Y-m-d' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; no WP API equivalent for these queries. $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table_name} WHERE date < %s", $cutoff_date ) ); return absint( $deleted ); } }