← All changes | inc/database/tables/entries.php +40 -5 2.8.0 → 2.12.8 View file →

		@@ -36,9 +36,9 @@
36	36	* {@inheritDoc}
37	37	*
38	38	* @var int
39	39	*/
40		- protected $table_version = 1;
	40	+ protected $table_version = 2;
41	41
42	42	/**
43	43	* Current logs.
44	44	*
		@@ -119,8 +119,13 @@
119	119	public function get_columns_definition() {
120	120	return [
121	121	'ID BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
122	122	'form_id BIGINT(20) UNSIGNED',
	123	+ // NOT NULL DEFAULT 0 is load-bearing, not tidiness. An anonymous
	124	+ // submission stores 0, and Forms_Data::calculate_form_metrics() excludes
	125	+ // site editors with `user_id NOT IN (…)`. NOT IN never matches NULL, so
	126	+ // making this column nullable would drop every anonymous entry from the
	127	+ // numerator and report a conversion rate near 0% on every form.
123	128	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
124	129	'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
125	130	'logs LONGTEXT',
126	131	'notes LONGTEXT',
		@@ -132,8 +137,14 @@
132	137	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
133	138	'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
134	139	'INDEX idx_user_id (user_id)',
135	140	'INDEX idx_form_id_created_at_status (form_id, created_at, status)', // Composite index for performance improvements.
	141	+ // Leads on the two columns Forms_Data::get_editing_submitter_ids()
	142	+ // filters. idx_user_id alone cannot serve it -- created_at is not in it,
	143	+ // and no other index leads on created_at -- so that lookup was a range
	144	+ // scan with a row read per row plus a temp table for DISTINCT, on every
	145	+ // Forms-list render.
	146	+ 'INDEX idx_user_id_created_at (user_id, created_at)',
136	147	];
137	148	}
138	149
139	150	/**
		@@ -145,8 +156,10 @@
145	156	'type VARCHAR(20) AFTER status',
146	157	'extras LONGTEXT AFTER status',
147	158	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
148	159	'INDEX idx_user_id (user_id)',
	160	+ // Note: @since 2.12.7 -- Covers the conversion rate's submitter lookup.
	161	+ 'INDEX idx_user_id_created_at (user_id, created_at)',
149	162	];
150	163	}
151	164
152	165	/**
		@@ -260,9 +273,18 @@
260	273	// Add default logs if no logs provided.
261	274	$data['logs'] = $instance->get_logs();
262	275	}
263	276
264		- return $instance->use_insert( $data );
	277	+ $result = $instance->use_insert( $data );
	278	+
	279	+ if ( ! $result ) {
	280	+ // A failed entries write is the live-drop signal issue #3084 describes: the
	281	+ // table can have been dropped after being cached as present. Drop that cache
	282	+ // so the next admin load re-checks instead of trusting a stale answer.
	283	+ \SRFM\Inc\Database\Register::flush_entries_table_cache();
	284	+ }
	285	+
	286	+ return $result;
265	287	}
266	288
267	289	/**
268	290	* Update an entry by entry id.
		@@ -483,8 +505,16 @@
483	505	*
484	506	* Uses a single SQL query with JSON_EXTRACT on the form_data column
485	507	* instead of loading all entries into PHP. Stops at the first match.
486	508	*
	509	+ * SECURITY INVARIANT — do not relax the key matching. The only caller is the
	510	+ * unauthenticated uniqueness check (Form_Submit::field_unique_validation()), which
	511	+ * allows a probe only for fields the form marks unique. That restriction holds
	512	+ * because the lookup is anchored to the EXACT submitted key: a stored form_data key
	513	+ * always embeds its own block ID, so a key that resolves to field X can only carry
	514	+ * X's block ID. Matching on block ID instead, or switching to LIKE / JSON_SEARCH,
	515	+ * would break that anchoring and widen the probe beyond the allowlisted field.
	516	+ *
487	517	* @param int $form_id The form ID to search within.
488	518	* @param string $field_key The form_data JSON key to match against.
489	519	* @param string $field_value The value to check for uniqueness.
490	520	* @since 2.7.0
		@@ -496,11 +526,16 @@
496	526	}
497	527
498	528	global $wpdb;
499	529	$table_name = self::get_instance()->get_tablename();
500		- $json_path = '$."' . $field_key . '"';
501	530
502		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-off existence check; caching not beneficial for uniqueness validation.
	531	+ // $wpdb->prepare() escapes this for SQL, but the key is interpolated into a JSON
	532	+ // path string, where a quote or backslash would change the path's meaning rather
	533	+ // than break the query. Neutralise both so the path can only ever be a single
	534	+ // quoted member access.
	535	+ $json_path = '$."' . str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $field_key ) . '"';
	536	+
	537	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- One-off existence check; table name from get_tablename() (not user input); caching not beneficial for uniqueness validation.
503	538	$exists = $wpdb->get_var(
504	539	$wpdb->prepare(
505	540	"SELECT 1 FROM {$table_name} WHERE form_id = %d AND status != 'trash' AND JSON_UNQUOTE(JSON_EXTRACT(form_data, %s)) = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally generated.
506	541	$form_id,
		@@ -520,9 +555,9 @@
520	555	* @since 1.1.1
521	556	* @return array<int> An array of form IDs.
522	557	*/
523	558	public static function get_form_ids_by_entries( $entry_ids ) {
524		- if ( empty( $entry_ids ) && ! is_array( $entry_ids ) ) {
	559	+ if ( empty( $entry_ids ) || ! is_array( $entry_ids ) ) {
525	560	return [];
526	561	}
527	562
528	563	$results = self::get_instance()->get_results(