pushly / src / php / Admin / LogStore.php

LogStore.php in Pushly 2.4.0, at src/php/Admin/LogStore.php

295 lines 11.2 KB

Raw Download Zip

1	<?php
2
3	namespace Pushly\Admin;
4
5	if ( ! defined( 'ABSPATH' ) ) {
6	exit;
7	}
8
9	/**
10	* Activity log persistence layer for the Pushly debug log.
11	*
12	* Every $wpdb call below uses $wpdb->prepare() with %i for the table identifier
13	* and explicit %s / %d placeholders for values. The WordPress.DB.DirectDatabaseQuery
14	* warnings are inherent to maintaining a custom plugin table (there is no
15	* core-function equivalent for our schema). Caching is intentionally omitted —
16	* the log is write-heavy and admin reads are paginated and infrequent.
17	*
18	* Where phpcs:ignore comments appear below they suppress false positives from
19	* static analysis that cannot trace through dynamic placeholder construction.
20	*/
21	class LogStore {
22
23	public const ROW_CAP = 5000;
24
25	private string $table_name;
26
27	public function __construct() {
28	global $wpdb;
29	$this->table_name = $wpdb->prefix . 'pushly_activity_log';
30	}
31
32	/**
33	* Creates the activity log table using dbDelta.
34	* Called on plugin activation.
35	*/
36	public function create_table(): void {
37	global $wpdb;
38	$charset_collate = $wpdb->get_charset_collate();
39
40	$sql = "CREATE TABLE {$this->table_name} (
41	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
42	timestamp DATETIME NOT NULL,
43	severity VARCHAR(10) NOT NULL,
44	event_type VARCHAR(100) NOT NULL,
45	message TEXT NOT NULL,
46	post_id BIGINT UNSIGNED NULL DEFAULT NULL,
47	context TEXT NULL DEFAULT NULL,
48	PRIMARY KEY (id),
49	KEY idx_timestamp (timestamp),
50	KEY idx_severity (severity)
51	) {$charset_collate};";
52
53	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
54	dbDelta( $sql );
55	}
56
57	/**
58	* Drops the activity log table entirely.
59	* Called on plugin uninstall.
60	*/
61	public function drop_table(): void {
62	global $wpdb;
63	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- Plugin-owned table; DDL on uninstall has no cache to invalidate.
64	$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $this->table_name ) );
65	}
66
67	/**
68	* Inserts multiple log entries in a single multi-row INSERT.
69	* Enforces the row cap before inserting.
70	*
71	* @param array<int, array{severity: string, event_type: string, message: string, post_id: ?int, context: ?string}> $entries
72	*/
73	public function insert_batch( array $entries ): void {
74	if ( empty( $entries ) ) {
75	return;
76	}
77
78	$this->enforce_row_cap( count( $entries ) );
79
80	global $wpdb;
81
82	$placeholders = [];
83	$values = [];
84
85	foreach ( $entries as $entry ) {
86	$placeholders[] = '(%s, %s, %s, %s, %s, %s)';
87
88	$context = null;
89	if ( isset( $entry['context'] ) ) {
90	$context = is_string( $entry['context'] )
91	? $entry['context']
92	: wp_json_encode( $entry['context'] );
93	if ( $context === false ) {
94	$context = null;
95	}
96	}
97
98	$values[] = $entry['timestamp'];
99	$values[] = $entry['severity'];
100	$values[] = $entry['event_type'];
101	$values[] = $entry['message'];
102	$values[] = $entry['post_id'] ?? null;
103	$values[] = $context;
104	}
105
106	$sql = 'INSERT INTO %i (timestamp, severity, event_type, message, post_id, context) VALUES ' . implode( ', ', $placeholders );
107
108	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is built from literal SQL plus a generated list of '(%s, %s, %s, %s, %s, %s)' tuples; all dynamic values flow through %i / %s placeholders.
109	$wpdb->query( $wpdb->prepare( $sql, array_merge( [ $this->table_name ], $values ) ) );
110	}
111
112	/**
113	* Enforces the row cap by deleting oldest entries when the current count
114	* plus the incoming batch would exceed the cap.
115	*
116	* @param int $incoming_count Number of new entries about to be inserted.
117	*/
118	private function enforce_row_cap( int $incoming_count ): void {
119	$current_count = $this->count();
120	$overflow = ( $current_count + $incoming_count ) - self::ROW_CAP;
121
122	if ( $overflow <= 0 ) {
123	return;
124	}
125
126	global $wpdb;
127
128	// Fetch the oldest IDs into PHP first, then delete.
129	// This avoids MySQL's restriction on referencing the target table
130	// in a subquery of a DELETE statement (which also fails on temporary tables).
131	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table; bounded read used to compute deletions, not for display.
132	$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i ORDER BY timestamp ASC, id ASC LIMIT %d', $this->table_name, $overflow ) );
133
134	if ( empty( $ids ) ) {
135	return;
136	}
137
138	$id_placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
139	$sql = "DELETE FROM %i WHERE id IN ({$id_placeholders})";
140
141	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $id_placeholders contains only '%d' tokens; every dynamic value flows through prepare().
142	$wpdb->query( $wpdb->prepare( $sql, array_merge( [ $this->table_name ], $ids ) ) );
143	}
144
145	/**
146	* Queries log entries with pagination, severity filter, and text search.
147	*
148	* @param int $page Page number (1-based).
149	* @param int $per_page Entries per page.
150	* @param string[]\|null $severities Filter by severity levels.
151	* @param string\|null $search Text search against message and event_type.
152	*
153	* @return array{entries: array, total: int, page: int, per_page: int, total_pages: int}
154	*/
155	public function query( int $page = 1, int $per_page = 50, ?array $severities = null, ?string $search = null ): array {
156	global $wpdb;
157
158	$where = $this->build_where_clause( $severities, $search );
159	$values = $this->build_where_values( $severities, $search );
160
161	$count_sql = 'SELECT COUNT(*) FROM %i' . $where;
162	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where is built from literal SQL with hard-coded %s placeholders generated in build_where_clause(); all values flow through prepare().
163	$total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, array_merge( [ $this->table_name ], $values ) ) );
164
165	$total_pages = $total > 0 ? (int) ceil( $total / $per_page ) : 0;
166	$offset = ( $page - 1 ) * $per_page;
167
168	$query_sql = 'SELECT * FROM %i' . $where . ' ORDER BY timestamp DESC, id DESC LIMIT %d OFFSET %d';
169	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where placeholders are literal; replacement count is correct at runtime (table + where-values + limit + offset).
170	$entries = $wpdb->get_results( $wpdb->prepare( $query_sql, array_merge( [ $this->table_name ], $values, [ $per_page, $offset ] ) ) );
171
172	return [
173	'entries' => $entries ?: [],
174	'total' => $total,
175	'page' => $page,
176	'per_page' => $per_page,
177	'total_pages' => $total_pages,
178	];
179	}
180
181	/**
182	* Returns all entries matching filters with a defensive LIMIT.
183	* Used for export.
184	*
185	* @param string[]\|null $severities Filter by severity levels.
186	* @param string\|null $search Text search against message and event_type.
187	*
188	* @return array<int, object>
189	*/
190	public function query_all( ?array $severities = null, ?string $search = null ): array {
191	global $wpdb;
192
193	$where = $this->build_where_clause( $severities, $search );
194	$values = $this->build_where_values( $severities, $search );
195
196	$sql = 'SELECT * FROM %i' . $where . ' ORDER BY timestamp DESC, id DESC LIMIT %d';
197	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where placeholders are literal; replacement count is correct at runtime (table + where-values + limit).
198	$entries = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( [ $this->table_name ], $values, [ self::ROW_CAP ] ) ) );
199
200	return $entries ?: [];
201	}
202
203	/**
204	* Returns the total number of log entries.
205	*/
206	public function count(): int {
207	global $wpdb;
208	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table count; result is paired with row-cap enforcement on the next write.
209	return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->table_name ) );
210	}
211
212	/**
213	* Deletes all log entries.
214	*/
215	public function truncate(): void {
216	global $wpdb;
217	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table; admin-triggered "clear log" action.
218	$wpdb->query( $wpdb->prepare( 'DELETE FROM %i', $this->table_name ) );
219	}
220
221	/**
222	* Deletes entries older than 14 days. Called by WP-Cron.
223	*/
224	public function prune_old_entries(): void {
225	global $wpdb;
226	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table; scheduled WP-Cron prune.
227	$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE timestamp < %s', $this->table_name, gmdate( 'Y-m-d H:i:s', time() - ( 14 * DAY_IN_SECONDS ) ) ) );
228	}
229
230	/**
231	* Returns the full table name.
232	*/
233	public function get_table_name(): string {
234	return $this->table_name;
235	}
236
237	/**
238	* Builds the WHERE clause for query and query_all.
239	*
240	* Returns a string containing only literal SQL and hard-coded %s placeholders —
241	* never user input. Safe to concatenate into a prepared SQL string.
242	*
243	* @param string[]\|null $severities Severity filter.
244	* @param string\|null $search Text search string.
245	*
246	* @return string SQL WHERE clause (including leading " WHERE") or empty string.
247	*/
248	private function build_where_clause( ?array $severities, ?string $search ): string {
249	$conditions = [];
250
251	if ( ! empty( $severities ) ) {
252	$placeholders = implode( ', ', array_fill( 0, count( $severities ), '%s' ) );
253	$conditions[] = "severity IN ({$placeholders})";
254	}
255
256	if ( ! empty( $search ) ) {
257	$conditions[] = '(message LIKE %s OR event_type LIKE %s)';
258	}
259
260	if ( empty( $conditions ) ) {
261	return '';
262	}
263
264	return ' WHERE ' . implode( ' AND ', $conditions );
265	}
266
267	/**
268	* Builds the values array for the WHERE clause placeholders.
269	*
270	* @param string[]\|null $severities Severity filter.
271	* @param string\|null $search Text search string.
272	*
273	* @return array<int, string> Values for wpdb::prepare().
274	*/
275	private function build_where_values( ?array $severities, ?string $search ): array {
276	global $wpdb;
277
278	$values = [];
279
280	if ( ! empty( $severities ) ) {
281	foreach ( $severities as $severity ) {
282	$values[] = $severity;
283	}
284	}
285
286	if ( ! empty( $search ) ) {
287	$like = '%' . $wpdb->esc_like( $search ) . '%';
288	$values[] = $like;
289	$values[] = $like;
290	}
291
292	return $values;
293	}
294	}
295