← All changes | database/Migrations/AttachmentsMigrator.php +22 -20 1.10.2 → 2.4.5 View file →

		@@ -13,9 +13,9 @@
13	13	$charsetCollate = $wpdb->get_charset_collate();
14	14
15	15	$table = $wpdb->prefix . static::$tableName;
16	16
17		- if ($wpdb->get_var("SHOW TABLES LIKE '$table'") != $table) {
	17	+ if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
18	18	$sql = "CREATE TABLE $table (
19	19	`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
20	20	`ticket_id` BIGINT(20) UNSIGNED NULL,
21	21	`person_id` BIGINT(20) UNSIGNED NULL,
		@@ -30,8 +30,9 @@
30	30	`status` VARCHAR(100) NULL DEFAULT 'active',
31	31	`file_size` VARCHAR(100) NULL,
32	32	`created_at` TIMESTAMP NULL,
33	33	`updated_at` TIMESTAMP NULL,
	34	+ INDEX `idx_file_hash` (`file_hash`),
34	35	INDEX `idx_ticket_id` (`ticket_id`),
35	36	INDEX `idx_person_id` (`person_id`),
36	37	INDEX `idx_conversation_id` (`conversation_id`),
37	38	INDEX `idx_status` (`status`),
		@@ -45,19 +46,22 @@
45	46
46	47	return false;
47	48	}
48	49
49		- public static function alterTable($table)
	50	+ public static function alterTable($table)
50	51	{
51	52	global $wpdb;
52		-
53		- // @todo: We will remove this on final release
54		- // This is only for beta users
55		- $existing_columns = $wpdb->get_col("DESC {$table}", 0);
56		- if(!in_array('status', $existing_columns)) {
57		- $query = "ALTER TABLE {$table} ADD `status` VARCHAR(100) NULL DEFAULT 'active' AFTER `driver`";
58		- $wpdb->query($query);
	53	+
	54	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
	55	+ $table = esc_sql($table);
	56	+
	57	+ // @todo: Beta-only migration, remove on final release.
	58	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	59	+ if (!in_array('status', $existing_columns)) {
	60	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	61	+ $wpdb->query("ALTER TABLE `{$table}` ADD `status` VARCHAR(100) NULL DEFAULT 'active' AFTER `driver`");
59	62	}
	63	+
60	64	static::addMissingIndexes($table);
61	65	}
62	66
63	67	public static function addMissingIndexes($table)
		@@ -63,13 +67,12 @@
63	67	public static function addMissingIndexes($table)
64	68	{
65	69	global $wpdb;
66	70
67		- // Escape table name
	71	+ // Escaped again in case this is called directly.
68	72	$table = esc_sql($table);
69	73
70		- // Get existing indexes
71		- $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
	74	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
72	75	$existing_index_names = [];
73	76
74	77	foreach ($existing_indexes as $index) {
75	78	$existing_index_names[] = $index->Key_name;
		@@ -74,22 +77,21 @@
74	77	foreach ($existing_indexes as $index) {
75	78	$existing_index_names[] = $index->Key_name;
76	79	}
77	80
78		- // Desired indexes
79	81	$indexes = [
80		- 'idx_ticket_id' => 'ticket_id',
81		- 'idx_person_id' => 'person_id',
	82	+ 'idx_ticket_id' => 'ticket_id',
	83	+ 'idx_person_id' => 'person_id',
82	84	'idx_conversation_id' => 'conversation_id',
83		- 'idx_status' => 'status',
84		- 'idx_created_at' => 'created_at',
	85	+ 'idx_status' => 'status',
	86	+ 'idx_created_at' => 'created_at',
	87	+ 'idx_file_hash' => 'file_hash',
85	88	];
86	89
87		- // Add missing indexes
88	90	foreach ($indexes as $index_name => $column_name) {
89	91	if (!in_array($index_name, $existing_index_names)) {
90		- $sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
91		- $wpdb->query($sql);
	92	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	93	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
92	94	}
93	95	}
94	96	}
95	97	}