← All changes | database/Migrations/ConversationsMigrator.php +23 -13 1.10.2 → 2.4.0 View file →

		@@ -12,9 +12,9 @@
12	12
13	13	$charsetCollate = $wpdb->get_charset_collate();
14	14	$table = $wpdb->prefix . static::$tableName;
15	15
16		- if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
	16	+ if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
17	17	$sql = "CREATE TABLE $table (
18	18	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
19	19	`serial` INT(11) UNSIGNED DEFAULT 1,
20	20	`ticket_id` BIGINT(20) UNSIGNED NOT NULL,
		@@ -28,9 +28,10 @@
28	28	`created_at` TIMESTAMP NULL,
29	29	`updated_at` TIMESTAMP NULL,
30	30	INDEX `idx_ticket_id` (`ticket_id`),
31	31	INDEX `idx_person_id` (`person_id`),
32		- INDEX `idx_created_at` (`created_at`)
	32	+ INDEX `idx_created_at` (`created_at`),
	33	+ INDEX `idx_person_id_created_at` (`person_id`, `created_at`)
33	34	) $charsetCollate;";
34	35	$created = dbDelta($sql);
35	36	return $created;
36	37	} else {
		@@ -39,9 +40,9 @@
39	40
40	41	return false;
41	42	}
42	43
43		- public static function alterTable($table)
	44	+ public static function alterTable($table)
44	45	{
45	46	static::addMissingIndexes($table);
46	47	}
47	48
		@@ -48,13 +49,15 @@
48	49	public static function addMissingIndexes($table)
49	50	{
50	51	global $wpdb;
51	52
52		- // Escape table name
	53	+ // $table is always $wpdb->prefix . 'fs_conversations' — not user input.
	54	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	55	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
53	56	$table = esc_sql($table);
54	57
55	58	// Get existing indexes
56		- $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
	59	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
57	60	$existing_index_names = [];
58	61
59	62	foreach ($existing_indexes as $index) {
60	63	$existing_index_names[] = $index->Key_name;
		@@ -59,20 +62,27 @@
59	62	foreach ($existing_indexes as $index) {
60	63	$existing_index_names[] = $index->Key_name;
61	64	}
62	65
63		- // Desired indexes
	66	+ // Desired indexes — keys and values are all hardcoded string literals.
	67	+ // idx_person_id_created_at speeds up Advanced Reports queries that filter
	68	+ // fs_conversations by an agent's person_id AND a date range together —
	69	+ // without it MySQL falls back to idx_person_id alone and scans an agent's
	70	+ // entire conversation history before filtering by date, which gets very
	71	+ // slow on large installs (proven ~100x slower on a 300k-ticket dataset).
64	72	$indexes = [
65		- 'idx_ticket_id' => 'ticket_id',
66		- 'idx_person_id' => 'person_id',
67		- 'idx_created_at' => 'created_at',
	73	+ 'idx_ticket_id' => '`ticket_id`',
	74	+ 'idx_person_id' => '`person_id`',
	75	+ 'idx_created_at' => '`created_at`',
	76	+ 'idx_person_id_created_at' => '`person_id`, `created_at`',
68	77	];
69	78
70		- // Add missing indexes
71		- foreach ($indexes as $index_name => $column_name) {
	79	+ // Add missing indexes. $table is esc_sql()'d above; $index_name and
	80	+ // $columns are hardcoded array literals — no user input reaches this query.
	81	+ foreach ($indexes as $index_name => $columns) {
72	82	if (!in_array($index_name, $existing_index_names)) {
73		- $sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
74		- $wpdb->query($sql);
	83	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	84	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})");
75	85	}
76	86	}
77	87	}
78	88	}