← All changes | database/Migrations/TicketsMigrator.php +69 -25 1.10.2 → 2.4.0 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	`customer_id` BIGINT(20) UNSIGNED NULL,
21	21	`agent_id` BIGINT(20) UNSIGNED NULL,
		@@ -41,8 +41,11 @@
41	41	`first_response_time` INT(11) NULL, /* Seconds took for first contact */
42	42	`total_close_time` INT(11) NULL, /* Seconds took for closing this ticket */
43	43	`resolved_at` TIMESTAMP NULL,
44	44	`closed_by` BIGINT(20) UNSIGNED NULL,
	45	+ `created_by` BIGINT(20) UNSIGNED NULL,
	46	+ `serial_number` BIGINT UNSIGNED NULL,
	47	+ `ticket_number` VARCHAR(192) NULL DEFAULT NULL,
45	48	`created_at` TIMESTAMP NULL,
46	49	`updated_at` TIMESTAMP NULL,
47	50	INDEX `idx_customer_id` (`customer_id`),
48	51	INDEX `idx_agent_id` (`agent_id`),
		@@ -48,21 +51,28 @@
48	51	INDEX `idx_agent_id` (`agent_id`),
49	52	INDEX `idx_mailbox_id` (`mailbox_id`),
50	53	INDEX `idx_product_id` (`product_id`),
51	54	INDEX `idx_priority` (`priority`),
	55	+ INDEX `idx_client_priority` (`client_priority`),
52	56	INDEX `idx_status` (`status`),
53		- INDEX `idx_created_at` (`created_at`)
	57	+ INDEX `idx_created_at` (`created_at`),
	58	+ INDEX `idx_resolved_at` (`resolved_at`),
	59	+ INDEX `idx_status_resolved_at` (`status`, `resolved_at`),
	60	+ INDEX `idx_ticket_number` (`ticket_number`(191)),
	61	+ INDEX `idx_waiting_since_id` (`waiting_since`, `id`),
	62	+ INDEX `idx_updated_at_id` (`updated_at`, `id`),
	63	+ INDEX `idx_response_count_id` (`response_count`, `id`),
	64	+ UNIQUE KEY `uniq_serial_number` (`serial_number`)
54	65	) $charsetCollate;";
55	66	$created = dbDelta($sql);
56	67	return $created;
57	68	} else {
58		- static::alterTable($table);
59		- }
	69	+ static::alterTable($table); }
60	70
61	71	return false;
62	72	}
63	73
64		- public static function alterTable($table)
	74	+ public static function alterTable($table)
65	75	{
66	76	static::addMissingColumns($table);
67	77	static::addMissingIndexes($table);
68	78	}
		@@ -70,19 +80,41 @@
70	80	public static function addMissingColumns($table)
71	81	{
72	82	global $wpdb;
73	83
74		- // Escape table name
	84	+ // $table is always $wpdb->prefix . 'fs_tickets' — not user input.
	85	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	86	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
75	87	$table = esc_sql($table);
76	88
77	89	// Get existing columns
78		- $existing_columns = $wpdb->get_col("DESC `$table`", 0);
79		-
	90	+ $existing_columns = $wpdb->get_col("DESC `{$table}`", 0); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	91	+
80	92	// Add waiting_since column if missing (beta user migration)
81	93	if (!in_array('waiting_since', $existing_columns)) {
82		- $query = 'ALTER TABLE `' . $table . '` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`';
83		- $wpdb->query($query);
	94	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	95	+ $wpdb->query("ALTER TABLE `{$table}` ADD `waiting_since` TIMESTAMP NULL AFTER `last_customer_response`");
84	96	}
	97	+
	98	+ // Add created_by column to track agent who created ticket on behalf of customer
	99	+ if (!in_array('created_by', $existing_columns)) {
	100	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	101	+ $wpdb->query("ALTER TABLE `{$table}` ADD `created_by` BIGINT(20) UNSIGNED NULL AFTER `closed_by`");
	102	+ }
	103	+
	104	+ if (!in_array('serial_number', $existing_columns)) {
	105	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	106	+ $wpdb->query("ALTER TABLE `{$table}` ADD `serial_number` BIGINT UNSIGNED NULL AFTER `created_by`");
	107	+
	108	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is sanitized via esc_sql().
	109	+ $wpdb->query("UPDATE `{$table}` SET `serial_number` = `id` WHERE `serial_number` IS NULL");
	110	+ }
	111	+
	112	+ if (!in_array('ticket_number', $existing_columns)) {
	113	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- $table is sanitized via esc_sql(); column name is a hardcoded literal.
	114	+ $wpdb->query("ALTER TABLE `{$table}` ADD `ticket_number` VARCHAR(192) NULL DEFAULT NULL AFTER `serial_number`");
	115	+ }
	116	+
85	117	}
86	118
87	119	public static function addMissingIndexes($table)
88	120	{
		@@ -87,13 +119,15 @@
87	119	public static function addMissingIndexes($table)
88	120	{
89	121	global $wpdb;
90	122
91		- // Escape table name
	123	+ // $table is always $wpdb->prefix . 'fs_tickets' — not user input.
	124	+ // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
	125	+ // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
92	126	$table = esc_sql($table);
93	127
94	128	// Get existing indexes
95		- $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `$table`");
	129	+ $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
96	130	$existing_index_names = [];
97	131
98	132	foreach ($existing_indexes as $index) {
99	133	$existing_index_names[] = $index->Key_name;
		@@ -98,24 +132,34 @@
98	132	foreach ($existing_indexes as $index) {
99	133	$existing_index_names[] = $index->Key_name;
100	134	}
101	135
102		- // Desired indexes
	136	+ // Desired indexes — keys and values (including composite column lists) are
	137	+ // all hardcoded string literals; no user input reaches these queries.
103	138	$indexes = [
104		- 'idx_customer_id' => 'customer_id',
105		- 'idx_agent_id' => 'agent_id',
106		- 'idx_mailbox_id' => 'mailbox_id',
107		- 'idx_product_id' => 'product_id',
108		- 'idx_priority' => 'priority',
109		- 'idx_status' => 'status',
110		- 'idx_created_at' => 'created_at',
	139	+ 'idx_customer_id' => '`customer_id`',
	140	+ 'idx_agent_id' => '`agent_id`',
	141	+ 'idx_mailbox_id' => '`mailbox_id`',
	142	+ 'idx_product_id' => '`product_id`',
	143	+ 'idx_priority' => '`priority`',
	144	+ 'idx_client_priority' => '`client_priority`',
	145	+ 'idx_status' => '`status`',
	146	+ 'idx_created_at' => '`created_at`',
	147	+ 'idx_resolved_at' => '`resolved_at`',
	148	+ 'idx_status_resolved_at' => '`status`, `resolved_at`',
	149	+ 'idx_ticket_number' => '`ticket_number`(191)',
	150	+ 'uniq_serial_number' => '`serial_number`',
	151	+ 'idx_waiting_since_id' => '`waiting_since`, `id`',
	152	+ 'idx_updated_at_id' => '`updated_at`, `id`',
	153	+ 'idx_response_count_id' => '`response_count`, `id`',
111	154	];
112		-
113		- // Add missing indexes
114		- foreach ($indexes as $index_name => $column_name) {
	155	+ // Add missing indexes. $table is esc_sql()'d above; $index_name and
	156	+ // $columns are hardcoded array literals — no user input reaches this query.
	157	+ foreach ($indexes as $index_name => $columns) {
115	158	if (!in_array($index_name, $existing_index_names)) {
116		- $sql = "ALTER TABLE `$table` ADD INDEX `$index_name` (`$column_name`)";
117		- $wpdb->query($sql);
	159	+ // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
	160	+ $indexType = $index_name === 'uniq_serial_number' ? 'UNIQUE KEY' : 'INDEX';
	161	+ $wpdb->query("ALTER TABLE `{$table}` ADD {$indexType} `{$index_name}` ({$columns})");
118	162	}
119	163	}
120	164	}
121	165	}