← All changes | inc/database/tables/entries.php +39 -18 2.12.2 → 2.12.8 View file →

		@@ -109,13 +109,8 @@
109	109	'extras' => [
110	110	'type' => 'array',
111	111	'default' => [],
112	112	],
113		- // Submission language code (e.g. 'en', 'de'). Empty when no multilingual provider is active.
114		- 'language' => [
115		- 'type' => 'string',
116		- 'default' => '',
117		- ],
118	113	];
119	114	}
120	115
121	116	/**
		@@ -124,8 +119,13 @@
124	119	public function get_columns_definition() {
125	120	return [
126	121	'ID BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
127	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.
128	128	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
129	129	'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
130	130	'logs LONGTEXT',
131	131	'notes LONGTEXT',
		@@ -132,15 +132,19 @@
132	132	'submission_info LONGTEXT',
133	133	'status VARCHAR(10)',
134	134	'type VARCHAR(20)', // Note: @since 0.0.13 -- We have added type column, it will have entry's form type eg quiz, standard etc.
135	135	'extras LONGTEXT',
136		- 'language VARCHAR(20)', // Note: @since 2.11.0 -- Submission language code, captured from the active multilingual provider. Nullable to match status/type column convention; INSERT path always supplies a value or empty string. Width matches the `type` column precedent and covers extended BCP-47 codes (e.g. `ca-valencia`).
137	136	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
138	137	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
139	138	'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
140	139	'INDEX idx_user_id (user_id)',
141	140	'INDEX idx_form_id_created_at_status (form_id, created_at, status)', // Composite index for performance improvements.
142		- 'INDEX idx_form_id_language (form_id, language)', // Composite index for per-language entry filtering.
	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)',
143	147	];
144	148	}
145	149
146	150	/**
		@@ -152,15 +156,10 @@
152	156	'type VARCHAR(20) AFTER status',
153	157	'extras LONGTEXT AFTER status',
154	158	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
155	159	'INDEX idx_user_id (user_id)',
156		- // Note: @since 2.11.0 -- Added language column for multilingual submission tracking.
157		- // No `AFTER` clause: on a pre-0.0.13 install upgrading straight to this version,
158		- // `extras` is added in the SAME combined ALTER, and MySQL resolves `AFTER extras`
159		- // against the pre-ALTER schema, throwing "Unknown column 'extras'" and failing the
160		- // whole atomic ALTER. Column ordinal position is cosmetic and addressed by name everywhere.
161		- 'language VARCHAR(20)',
162		- 'INDEX idx_form_id_language (form_id, language)',
	160	+ // Note: @since 2.12.7 -- Covers the conversion rate's submitter lookup.
	161	+ 'INDEX idx_user_id_created_at (user_id, created_at)',
163	162	];
164	163	}
165	164
166	165	/**
		@@ -274,9 +273,18 @@
274	273	// Add default logs if no logs provided.
275	274	$data['logs'] = $instance->get_logs();
276	275	}
277	276
278		- 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;
279	287	}
280	288
281	289	/**
282	290	* Update an entry by entry id.
		@@ -497,8 +505,16 @@
497	505	*
498	506	* Uses a single SQL query with JSON_EXTRACT on the form_data column
499	507	* instead of loading all entries into PHP. Stops at the first match.
500	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	+ *
501	517	* @param int $form_id The form ID to search within.
502	518	* @param string $field_key The form_data JSON key to match against.
503	519	* @param string $field_value The value to check for uniqueness.
504	520	* @since 2.7.0
		@@ -510,10 +526,15 @@
510	526	}
511	527
512	528	global $wpdb;
513	529	$table_name = self::get_instance()->get_tablename();
514		- $json_path = '$."' . $field_key . '"';
515	530
	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	+
516	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.
517	538	$exists = $wpdb->get_var(
518	539	$wpdb->prepare(
519	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.
		@@ -534,9 +555,9 @@
534	555	* @since 1.1.1
535	556	* @return array<int> An array of form IDs.
536	557	*/
537	558	public static function get_form_ids_by_entries( $entry_ids ) {
538		- if ( empty( $entry_ids ) && ! is_array( $entry_ids ) ) {
	559	+ if ( empty( $entry_ids ) || ! is_array( $entry_ids ) ) {
539	560	return [];
540	561	}
541	562
542	563	$results = self::get_instance()->get_results(
		@@ -595,7 +616,7 @@
595	616	* @since 2.6.0
596	617	* @return array<string>
597	618	*/
598	619	protected function get_allowed_orderby_columns() {
599		- return [ 'ID', 'id', 'form_id', 'user_id', 'status', 'type', 'language', 'created_at', 'updated_at' ];
	620	+ return [ 'ID', 'id', 'form_id', 'user_id', 'status', 'type', 'created_at', 'updated_at' ];
600	621	}
601	622	}