← All changes | class/class-mainwp-db.php +261 -52 6.1.3 → 6.2.1 View file →

		@@ -83,8 +83,37 @@
83	83	return static::$instance;
84	84	}
85	85
86	86	/**
	87	+ * Keep only option names that are safe to splice into SQL as an identifier.
	88	+ *
	89	+ * Every wp_options view builder below puts each name in identifier position
	90	+ * (column and table aliases), where escape()/esc_sql() is not a defense: it
	91	+ * escapes quotes but leaves backticks alone, so a name like "a`, (SELECT ...)
	92	+ * AS `b" breaks out of the alias. Callers reach these through ?custom_fields on
	93	+ * the REST sites endpoints and through the view_fields param of
	94	+ * get_sql_website_by_params(). Anything outside [A-Za-z0-9_] is dropped rather
	95	+ * than escaped, which also makes the surviving escape() calls no-ops.
	96	+ *
	97	+ * @param array $fields Option names.
	98	+ *
	99	+ * @return array Option names safe for identifier position.
	100	+ */
	101	+ protected function filter_safe_option_names( $fields ) {
	102	+ if ( ! is_array( $fields ) ) {
	103	+ return array();
	104	+ }
	105	+ return array_values(
	106	+ array_filter(
	107	+ $fields,
	108	+ function ( $name ) {
	109	+ return is_string( $name ) && preg_match( '/^[A-Za-z0-9_]+$/', $name );
	110	+ }
	111	+ )
	112	+ );
	113	+ }
	114	+
	115	+ /**
87	116	* Get wp_options database table view.
88	117	*
89	118	* @compatible function.
90	119	*
		@@ -124,8 +153,10 @@
124	153	if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
125	154	$fields[] = 'cust_site_icon_info';
126	155	}
127	156
	157	+ $fields = $this->filter_safe_option_names( $fields );
	158	+
128	159	if ( is_array( $fields ) ) {
129	160	foreach ( $fields as $field ) {
130	161	if ( empty( $field ) ) {
131	162	continue;
		@@ -133,9 +164,9 @@
133	164	if ( in_array( $field, $included_opts ) ) {
134	165	continue;
135	166	}
136	167	$view .= ', ';
137		- $view .= '(SELECT ' . $this->escape( $field ) . '.value FROM ' . $this->table_name( 'wp_options' ) . ' ' . $this->escape( $field ) . ' WHERE ' . $this->escape( $field ) . '.wpid = intwp.id AND ' . $this->escape( $field ) . '.name = "' . $this->escape( $field ) . '" LIMIT 1) AS ' . $this->escape( $field );
	168	+ $view .= '(SELECT `' . $this->escape( $field ) . '`.value FROM ' . $this->table_name( 'wp_options' ) . ' `' . $this->escape( $field ) . '` WHERE `' . $this->escape( $field ) . '`.wpid = intwp.id AND `' . $this->escape( $field ) . '`.name = "' . $this->escape( $field ) . '" LIMIT 1) AS `' . $this->escape( $field ) . '`';
138	169	}
139	170	}
140	171
141	172	$view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
		@@ -145,11 +176,11 @@
145	176
146	177	/**
147	178	* Method get_wp_options_join().
148	179	*
149		- * @param array $fields Extra option fields.
150		- * @param string $view_query view query.
151		- * @param array $params Additional parameters.
	180	+ * @param array $fields Extra option fields.
	181	+ * @param string $view_query view query.
	182	+ * @param array<string, mixed> $params Additional parameters.
152	183
153	184	*
154	185	* NOTE: This method is used to improve the performance of wp_options view, as the old view with subquery for each field will cause performance issue when there are many sites, and this method will generate the SQL with LEFT JOIN which will be much faster than subquery.
155	186	* The alias of wp table must be 'wp' to make sure the LEFT JOIN works, and the alias of wp_options table will be 'owp' + key of field in fields array, and the join condition is owp.wpid = wp.id AND owp.name = field name, and the select field is owp.value AS field name.
		@@ -184,9 +215,9 @@
184	215	if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
185	216	$fields[] = 'cust_site_icon_info';
186	217	}
187	218
188		- $fields = array_values( array_unique( array_filter( $fields ) ) );
	219	+ $fields = array_values( array_unique( array_filter( $this->filter_safe_option_names( $fields ) ) ) );
189	220
190	221	$tbl_wp_options = $this->table_name( 'wp_options' );
191	222
192	223	$selects = array();
		@@ -257,8 +288,10 @@
257	288	if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
258	289	$fields[] = 'cust_site_icon_info';
259	290	}
260	291
	292	+ $fields = $this->filter_safe_option_names( $fields );
	293	+
261	294	if ( is_array( $fields ) ) {
262	295	foreach ( $fields as $field ) {
263	296	if ( empty( $field ) ) {
264	297	continue;
		@@ -266,9 +299,9 @@
266	299	if ( in_array( $field, $included_opts ) ) {
267	300	continue;
268	301	}
269	302	$view .= ', ';
270		- $view .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS ' . $this->escape( $field );
	303	+ $view .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
271	304
272	305	$included_opts[] = $this->escape( $field );
273	306	}
274	307	}
		@@ -309,9 +342,10 @@
309	342	// legacy support.
310	343	$selectgroups = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
311	344	$orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
312	345	$offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
313		- $rowcount = isset( $params['rowcount'] ) && $params['rowcount'] ? true : false;
	346	+ $rowcount = isset( $params['rowcount'] ) ? (int) $params['rowcount'] : false;
	347	+ $count_sql = isset( $params['count_sql'] ) && $params['count_sql'] ? true : false;
314	348	$extraWhere = isset( $params['where'] ) ? $params['where'] : null; // NOTE: without 'AND' at begining and ending of 'where'.
315	349	$for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
316	350	$others_fields = isset( $params['others_fields'] ) && is_array( $params['others_fields'] ) ? $params['others_fields'] : array( 'favi_icon' );
317	351	$is_staging = isset( $params['is_staging'] ) && in_array( $params['is_staging'], array( 'yes', 'no' ) ) ? $params['is_staging'] : 'no';
		@@ -395,9 +429,9 @@
395	429	$status_conds = array();
396	430	if ( in_array( 'available_update', $status ) ) {
397	431	$available_sql = " ( wp.plugin_upgrades <> '' && wp.plugin_upgrades <> '[]' ) OR ( wp.theme_upgrades <> '' && wp.theme_upgrades <> '[]' ) OR ( wp.translation_upgrades <> '' && wp.translation_upgrades <> '[]' ) OR ( wp.premium_upgrades <> '' && wp.premium_upgrades <> '[]' ) ";
398	432	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
399		- $results = $this->wpdb->get_results( "SELECT wpid FROM {$table_name} WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]'" );
	433	+ $results = $this->wpdb->get_results( "SELECT wpid FROM {$table_name} WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the rest of the query is a literal.
400	434	if ( $results ) {
401	435	$wp_ids = array();
402	436	foreach ( $results as $item ) {
403	437	if ( ! empty( $item->wpid ) ) {
		@@ -508,18 +542,19 @@
508	542	}
509	543
510	544	// wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
511	545	if ( $selectgroups ) {
512		- $qry = 'SELECT ' . $select . $view_selects . ', GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids, GROUP_CONCAT(gr.color ORDER BY gr.name SEPARATOR ",") as wpgroups_colors ' .
513		- $select_clients . '
	546	+ $select_qry = 'SELECT ' . $select . $view_selects . ', GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids, GROUP_CONCAT(gr.color ORDER BY gr.name SEPARATOR ",") as wpgroups_colors ' .
	547	+ $select_clients;
514	548
515		- FROM ' . $this->table_name( 'wp' ) . ' wp
	549	+ $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
516	550	LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
517	551	LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
518	552	' . $join_clients . '
519	553	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
520	554	' . $view_joins . '
521		- WHERE 1 ' . $where . $connected_sql . '
	555	+ WHERE 1 ' . $where . $connected_sql;
	556	+ $group_qry = '
522	557	GROUP BY wp.id, wp_sync.sync_id
523	558	ORDER BY ' . $orderBy;
524	559	} elseif ( ! empty( $specific_wp_fields ) ) { // Optimize select sites data.
525	560	$select = $specific_wp_fields;
		@@ -530,25 +565,33 @@
530	565	$select .= ',' . $specific_sync_fields;
531	566	$join_sync = ' JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid';
532	567	$group_by .= ', wp_sync.sync_id';
533	568	}
534		- $qry = 'SELECT ' . $select . $view_selects . '
535		- FROM ' . $this->table_name( 'wp' ) . ' wp
	569	+ $select_qry = 'SELECT ' . $select . $view_selects;
	570	+ $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
536	571	' . $join_sync . ' ' . $view_joins . '
537		- WHERE 1 ' . $where . $connected_sql . '
	572	+ WHERE 1 ' . $where . $connected_sql;
	573	+ $group_qry = '
538	574	GROUP BY ' . $group_by . '
539	575	ORDER BY ' . $orderBy;
540	576	} else {
541		- $qry = 'SELECT ' . $select . $view_selects . $select_clients . '
542		- FROM ' . $this->table_name( 'wp' ) . ' wp
	577	+ $select_qry = 'SELECT ' . $select . $view_selects . $select_clients;
	578	+ $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
543	579	' . $join_clients . '
544	580	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
545	581	' . $view_joins . '
546		- WHERE 1 ' . $where . $connected_sql . '
	582	+ WHERE 1 ' . $where . $connected_sql;
	583	+ $group_qry = '
547	584	GROUP BY wp.id, wp_sync.sync_id
548	585	ORDER BY ' . $orderBy;
549	586	}
550	587
	588	+ if ( $count_sql ) {
	589	+ return 'SELECT COUNT(DISTINCT wp.id) ' . $qry;
	590	+ }
	591	+
	592	+ $qry = $select_qry . $qry . $group_qry;
	593	+
551	594	if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
552	595	$qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
553	596	} elseif ( false !== $rowcount ) {
554	597	$qry .= ' LIMIT ' . $rowcount;
		@@ -600,10 +643,11 @@
600	643	'ignored_trans_updates',
601	644	);
602	645	} elseif ( in_array( $view, array( 'simple_view', 'base_view', 'monitor_view', 'ping_view', 'uptime_notification' ) ) ) {
603	646	$fields = array();
604		- if ( 'monitor_view' === $view ) {
	647	+ if ( 'monitor_view' === $view || 'ping_view' === $view ) {
605	648	$fields[] = 'health_site_status';
	649	+ $fields[] = 'bypass_cache';
606	650	}
607	651	} elseif ( 'custom_view' !== $view ) {
608	652	$fields = $default;
609	653	}
		@@ -621,9 +665,9 @@
621	665	$fields[] = 'verify_method';
622	666	}
623	667	}
624	668
625		- $fields = array_values( array_filter( $fields ) );
	669	+ $fields = array_values( array_filter( $this->filter_safe_option_names( $fields ) ) );
626	670
627	671	$tbl_wp_options = $this->table_name( 'wp_options' );
628	672
629	673	$selects = array();
		@@ -677,10 +721,11 @@
677	721	'ignored_trans_updates',
678	722	);
679	723	} elseif ( in_array( $view, array( 'simple_view', 'base_view', 'monitor_view', 'ping_view', 'uptime_notification' ) ) ) {
680	724	$fields = array();
681		- if ( 'monitor_view' === $view ) {
	725	+ if ( 'monitor_view' === $view || 'ping_view' === $view ) {
682	726	$fields[] = 'health_site_status';
	727	+ $fields[] = 'bypass_cache';
683	728	}
684	729	} elseif ( 'custom_view' !== $view ) {
685	730	$fields = $default;
686	731	}
		@@ -700,8 +745,10 @@
700	745	$fields[] = 'verify_method';
701	746	}
702	747	}
703	748
	749	+ $fields = $this->filter_safe_option_names( $fields );
	750	+
704	751	foreach ( $fields as $field ) {
705	752
706	753	if ( empty( $field ) ) {
707	754	continue;
		@@ -707,9 +754,9 @@
707	754	continue;
708	755	}
709	756
710	757	$view_query .= ', ';
711		- $view_query .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS ' . $this->escape( $field );
	758	+ $view_query .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
712	759	}
713	760
714	761	$view_query .= ' FROM ' . $this->table_name( 'wp_options' ) .
715	762	" WHERE name IN ('" . implode( "','", $fields ) . "')
		@@ -927,9 +974,9 @@
927	974	return false;
928	975	}
929	976
930	977	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
931		- $value = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT value FROM {$table_name} WHERE wpid = %d AND name = %s", $site_id, $option ) );
	978	+ $value = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT value FROM {$table_name} WHERE wpid = %d AND name = %s", $site_id, $option ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
932	979
933	980	if ( null === $value && null !== $default_value ) {
934	981	return $default_value;
935	982	}
		@@ -1007,9 +1054,9 @@
1007	1054	}
1008	1055
1009	1056	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
1010	1057	$placeholders = implode( ',', array_fill( 0, count( $get_options ), '%s' ) );
1011		- $options_db = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name, value FROM {$table_name} WHERE wpid = %d AND name IN ({$placeholders})", array_merge( array( $site_id ), $get_options ) ) );
	1058	+ $options_db = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name, value FROM {$table_name} WHERE wpid = %d AND name IN ({$placeholders})", array_merge( array( $site_id ), $get_options ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; $placeholders is a generated list of %s.
1012	1059
1013	1060	$fill_options = array(
1014	1061	'primary_lasttime_backup',
1015	1062	);
		@@ -1046,9 +1093,9 @@
1046	1093	$site_id = $website->id;
1047	1094	}
1048	1095
1049	1096	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
1050		- $rslt = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name FROM {$table_name} WHERE wpid = %d AND name = %s", $site_id, $option ) );
	1097	+ $rslt = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name FROM {$table_name} WHERE wpid = %d AND name = %s", $site_id, $option ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
1051	1098	if ( empty( $rslt ) ) {
1052	1099	$this->wpdb->insert(
1053	1100	$this->table_name( 'wp_options' ),
1054	1101	array(
		@@ -1093,9 +1140,9 @@
1093	1140	}
1094	1141
1095	1142	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
1096	1143	foreach ( $options as $opt ) {
1097		- $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$table_name} WHERE wpid=%d AND name=%s", $site_id, $opt ) );
	1144	+ $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$table_name} WHERE wpid=%d AND name=%s", $site_id, $opt ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
1098	1145	}
1099	1146	}
1100	1147
1101	1148
		@@ -1116,9 +1163,9 @@
1116	1163	static::$general_options[] = array();
1117	1164	}
1118	1165
1119	1166	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
1120		- $val = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT value FROM {$table_name} WHERE wpid = %d AND name = %s", 0, $option ) );
	1167	+ $val = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT value FROM {$table_name} WHERE wpid = %d AND name = %s", 0, $option ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
1121	1168
1122	1169	static::$general_options[ $option ] = $val;
1123	1170	return $val;
1124	1171	}
		@@ -1159,9 +1206,9 @@
1159	1206	}
1160	1207
1161	1208	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
1162	1209	$placeholders = implode( ',', array_fill( 0, count( $diff_options ), '%s' ) );
1163		- $options_db = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name, value FROM {$table_name} WHERE wpid = %d AND name IN ({$placeholders})", array_merge( array( 0 ), $diff_options ) ) );
	1210	+ $options_db = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name, value FROM {$table_name} WHERE wpid = %d AND name IN ({$placeholders})", array_merge( array( 0 ), $diff_options ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; $placeholders is a generated list of %s.
1164	1211
1165	1212	foreach ( (array) $options_db as $o ) {
1166	1213	$return_options[ $o->name ] = $o->value;
1167	1214	static::$general_options[ $o->name ] = $o->value;
		@@ -1192,9 +1239,9 @@
1192	1239	}
1193	1240	static::$general_options[ $option ] = $value;
1194	1241
1195	1242	$table_name = esc_sql( $this->table_name( 'wp_options' ) );
1196		- $rslt = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name FROM {$table_name} WHERE wpid = %d AND name = %s", 0, $option ) );
	1243	+ $rslt = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name FROM {$table_name} WHERE wpid = %d AND name = %s", 0, $option ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
1197	1244
1198	1245	if ( empty( $rslt ) ) {
1199	1246	$this->wpdb->insert(
1200	1247	$this->table_name( 'wp_options' ),
		@@ -1463,9 +1510,9 @@
1463	1510	$status_conds = array();
1464	1511	if ( in_array( 'available_update', $status ) ) {
1465	1512	$available_sql = " ( wp.plugin_upgrades <> '' && wp.plugin_upgrades <> '[]' ) OR ( wp.theme_upgrades <> '' && wp.theme_upgrades <> '[]' ) OR ( wp.translation_upgrades <> '' && wp.translation_upgrades <> '[]' ) OR ( wp.premium_upgrades <> '' && wp.premium_upgrades <> '[]' ) ";
1466	1513	$options_table = esc_sql( $this->table_name( 'wp_options' ) );
1467		- $results = $this->wpdb->get_results( "SELECT wpid FROM {$options_table} WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]'" );
	1514	+ $results = $this->wpdb->get_results( "SELECT wpid FROM {$options_table} WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the rest of the query is a literal.
1468	1515	if ( $results ) {
1469	1516	$wp_ids = array();
1470	1517	foreach ( $results as $item ) {
1471	1518	if ( ! empty( $item->wpid ) ) {
		@@ -1925,9 +1972,12 @@
1925	1972	$obj_data = MainWP_Utility::map_site( $website, $data );
1926	1973
1927	1974	if ( $full_data ) {
1928	1975	$sum_upgrades = 0;
1929		- if ( '' !== $obj_data->plugin_upgrades ) {
	1976	+ // The option-backed columns are SQL NULL until a first sync writes them, and
	1977	+ // map_site() copies a null property through, so an '' check alone leaves
	1978	+ // json_decode() a null on PHP 8.1+.
	1979	+ if ( ! empty( $obj_data->plugin_upgrades ) ) {
1930	1980	$plugin_upgrades = json_decode( $obj_data->plugin_upgrades, true );
1931	1981	if ( is_array( $plugin_upgrades ) ) {
1932	1982	$sum_upgrades += count( $plugin_upgrades );
1933	1983	}
		@@ -1932,9 +1982,9 @@
1932	1982	$sum_upgrades += count( $plugin_upgrades );
1933	1983	}
1934	1984	}
1935	1985
1936		- if ( '' !== $obj_data->theme_upgrades ) {
	1986	+ if ( ! empty( $obj_data->theme_upgrades ) ) {
1937	1987	$theme_upgrades = json_decode( $obj_data->theme_upgrades, true );
1938	1988	if ( is_array( $theme_upgrades ) ) {
1939	1989	$sum_upgrades += count( $theme_upgrades );
1940	1990	}
		@@ -1939,9 +1989,9 @@
1939	1989	$sum_upgrades += count( $theme_upgrades );
1940	1990	}
1941	1991	}
1942	1992
1943		- if ( '' !== $obj_data->wp_upgrades ) {
	1993	+ if ( ! empty( $obj_data->wp_upgrades ) ) {
1944	1994	$wp_upgrades = json_decode( $obj_data->wp_upgrades, true );
1945	1995	if ( is_array( $wp_upgrades ) ) {
1946	1996	$sum_upgrades += count( $wp_upgrades );
1947	1997	}
		@@ -2915,9 +2965,36 @@
2915	2965
2916	2966	return (int) $this->wpdb->get_var( $qry ); //phpcs:ignore PluginCheck.Security.DirectDB.Unprepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where_allowed is from validated get_sql_where_access_sites() with numeric IDs.
2917	2967	}
2918	2968
	2969	+
2919	2970	/**
	2971	+ * Get child site connection status counts.
	2972	+ *
	2973	+ * @return array|null {
	2974	+ * Connection status counts.
	2975	+ *
	2976	+ * @type int $total_sites Total number of sites.
	2977	+ * @type int $connected_sites Number of connected sites.
	2978	+ * @type int $disconnected_sites Number of disconnected sites.
	2979	+ * }
	2980	+ */
	2981	+ public function get_sites_connections_status() {
	2982	+
	2983	+ $is_staging = 'no';
	2984	+
	2985	+ $sites_table = $this->table_name( 'wp' );
	2986	+ $sync_table = $this->table_name( 'wp_sync' );
	2987	+
	2988	+ return $this->wpdb->get_row(
	2989	+ "SELECT COUNT(wp.id) AS total_sites, SUM(CASE WHEN s.sync_errors = '' THEN 1 ELSE 0 END) AS connected_sites, SUM(CASE WHEN s.sync_errors <> '' THEN 1 ELSE 0 END) AS disconnected_sites FROM {$sites_table} wp JOIN {$sync_table} s ON s.wpid = wp.id " . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names come from table_name(), which is the DB prefix plus a hardcoded suffix.
	2990	+ $this->get_sql_where_allow_access_sites( 'wp', $is_staging ),
	2991	+ ARRAY_A
	2992	+ );
	2993	+ }
	2994	+
	2995	+
	2996	+ /**
2920	2997	* Get child sites by group id via SQL.
2921	2998	*
2922	2999	* @param int $id Group ID.
2923	3000	* @param bool $selectgroups Selected groups. Default: false.
		@@ -3084,9 +3161,9 @@
3084	3161	* @return string|null Child site IP address or null on failure.
3085	3162	*/
3086	3163	public function get_wp_ip( $wpid ) {
3087	3164	$table_name = esc_sql( $this->table_name( 'request_log' ) );
3088		- return $this->wpdb->get_var( $this->wpdb->prepare( "SELECT ip FROM {$table_name} WHERE wpid = %d", $wpid ) );
	3165	+ return $this->wpdb->get_var( $this->wpdb->prepare( "SELECT ip FROM {$table_name} WHERE wpid = %d", $wpid ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3089	3166	}
3090	3167
3091	3168	/**
3092	3169	* Add website to the MainWP Dashboard.
		@@ -3125,8 +3202,12 @@
3125	3202	$http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
3126	3203	$sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
3127	3204	$wpe = isset( $params['wpe'] ) ? $params['wpe'] : 0;
3128	3205	$isStaging = isset( $params['isStaging'] ) ? $params['isStaging'] : 0;
	3206	+ // 2 means "use the global mainwp_forceUseIPv4 option" and mainwp_string_to_bool()
	3207	+ // collapses it to 0, so it is kept before the boolean conversion. Null or absent
	3208	+ // stays null and leaves the NOT NULL column at its default.
	3209	+ $force_use_ipv4 = isset( $params['force_use_ipv4'] ) ? ( 2 === (int) $params['force_use_ipv4'] ? 2 : (int) mainwp_string_to_bool( $params['force_use_ipv4'] ) ) : null;
3129	3210
3130	3211	// MWP-1548: encrypt http_user / http_pass at rest. Empty / null
3131	3212	// values pass through unchanged (encrypt_credential is a no-op
3132	3213	// for those). A non-empty value that fails to encrypt produces
		@@ -3185,8 +3266,12 @@
3185	3266	'wpe' => $wpe,
3186	3267	'is_staging' => $isStaging,
3187	3268	);
3188	3269
	3270	+ if ( null !== $force_use_ipv4 ) {
	3271	+ $values['force_use_ipv4'] = $force_use_ipv4;
	3272	+ }
	3273	+
3189	3274	$syncValues = array(
3190	3275	'dtsSync' => 0,
3191	3276	'dtsSyncStart' => 0,
3192	3277	'dtsAutomaticSync' => 0,
		@@ -3231,8 +3316,26 @@
3231	3316	'groupid' => $groupid,
3232	3317	)
3233	3318	);
3234	3319	}
	3320	+
	3321	+ // Add primary monitor, use global settings.
	3322	+ $success = MainWP_DB_Uptime_Monitoring::instance()->update_wp_monitor(
	3323	+ array(
	3324	+ 'wpid' => $websiteid,
	3325	+ 'suburl' => '',
	3326	+ )
	3327	+ );
	3328	+
	3329	+ $opt_name = 'mainwp_primary_monitor_repair_attempts_for_wp_' . $websiteid;
	3330	+
	3331	+ if ( ! $success ) {
	3332	+ update_option( $opt_name, 1 );
	3333	+ MainWP_Utility::unhide_mainwp_message( 'failed-attempt-create-primary-monitor-' . $websiteid );
	3334	+ } elseif ( get_option( $opt_name ) ) {
	3335	+ delete_option( $opt_name );
	3336	+ }
	3337	+
3235	3338	MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
3236	3339	return $websiteid;
3237	3340	}
3238	3341	}
		@@ -3255,9 +3358,9 @@
3255	3358	$this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
3256	3359	$this->wpdb->delete( $this->table_name( 'wp_sync' ), array( 'wpid' => $websiteid ) );
3257	3360	$this->wpdb->delete( $this->table_name( 'wp_options' ), array( 'wpid' => $websiteid ) );
3258	3361	MainWP_Encrypt_Data_Lib::remove_key_file( $websiteid );
3259		- MainWP_DB_Uptime_Monitoring::instance()->delete_monitor( array( 'wpid' => $websiteid ) );
	3362	+ MainWP_DB_Uptime_Monitoring::instance()->delete_site_monitors( $websiteid );
3260	3363	MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
3261	3364	return $nr;
3262	3365	}
3263	3366
		@@ -3295,9 +3398,9 @@
3295	3398	}
3296	3399	// Lock the data stream to prevent other processes from updating at the same time.
3297	3400	$table_name = esc_sql( $this->table_name( 'wp' ) );
3298	3401	$sql = $this->wpdb->prepare(
3299		- "SELECT * FROM {$table_name} WHERE id = %d FOR UPDATE",
	3402	+ "SELECT * FROM {$table_name} WHERE id = %d FOR UPDATE", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3300	3403	$websiteid
3301	3404	);
3302	3405	$this->wpdb->get_row( $sql );
3303	3406
		@@ -3519,9 +3622,9 @@
3519	3622	$url .= '/';
3520	3623	}
3521	3624	$wp_table = esc_sql( $this->table_name( 'wp' ) );
3522	3625	$wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3523		- $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT );
	3626	+ $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
3524	3627	if ( $results ) {
3525	3628	return $results;
3526	3629	}
3527	3630
		@@ -3533,9 +3636,9 @@
3533	3636	$url = str_replace( 'https://', 'https://www.', $url );
3534	3637	$url = str_replace( 'http://', 'http://www.', $url );
3535	3638	}
3536	3639
3537		- $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT );
	3640	+ $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
3538	3641	if ( $results ) {
3539	3642	return $results;
3540	3643	}
3541	3644
		@@ -3540,12 +3643,68 @@
3540	3643	}
3541	3644
3542	3645	$url = str_replace( array( 'https://www.', 'http://www.', 'https://', 'http://', 'www.' ), array( '', '', '', '', '' ), $url );
3543	3646
3544		- return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s", $url ), OBJECT );
	3647	+ return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
3545	3648	}
3546	3649
3547	3650	/**
	3651	+ * Get recently-synced child sites whose stored url differs from the
	3652	+ * child-reported siteurl.
	3653	+ *
	3654	+ * The trailing slash must be normalized in SQL: stored urls always end
	3655	+ * with a slash while child-reported siteurl values never do, so a plain
	3656	+ * inequality would match nearly every site.
	3657	+ *
	3658	+ * @since 6.2
	3659	+ *
	3660	+ * @param int $days Freshness gate: only sites synced within this many days.
	3661	+ *
	3662	+ * @return array|object|null Rows with id, url, siteurl or null on failure.
	3663	+ */
	3664	+ public function get_websites_with_url_siteurl_mismatch( $days = 30 ) {
	3665	+ $wp_table = esc_sql( $this->table_name( 'wp' ) );
	3666	+ $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
	3667	+ $minimum_sync = time() - intval( $days ) * DAY_IN_SECONDS;
	3668	+
	3669	+ return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT wp.id, wp.url, wp.siteurl FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.siteurl <> '' AND TRIM(TRAILING '/' FROM wp.url) <> TRIM(TRAILING '/' FROM wp.siteurl) AND wp_sync.dtsSync >= %d", $minimum_sync ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
	3670	+ }
	3671	+
	3672	+ /**
	3673	+ * Get IDs of child sites that have a non-empty value for a website option.
	3674	+ *
	3675	+ * @since 6.2
	3676	+ *
	3677	+ * @param string $option_name Website option name.
	3678	+ * @param int $limit Maximum number of IDs to return.
	3679	+ *
	3680	+ * @return array Site IDs.
	3681	+ */
	3682	+ public function get_website_ids_with_nonempty_option( $option_name, $limit = 3 ) {
	3683	+ $options_table = esc_sql( $this->table_name( 'wp_options' ) );
	3684	+
	3685	+ return $this->wpdb->get_col( $this->wpdb->prepare( "SELECT wpid FROM {$options_table} WHERE name = %s AND value <> '' ORDER BY wpid ASC LIMIT %d", $option_name, max( 1, intval( $limit ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
	3686	+ }
	3687	+
	3688	+ /**
	3689	+ * Get one child site whose stored url exactly matches the given URL,
	3690	+ * excluding a site ID. Used as the duplicate guard before a URL correction.
	3691	+ *
	3692	+ * @since 6.2
	3693	+ *
	3694	+ * @param string $url URL to match ( trailing slash enforced ).
	3695	+ * @param int $exclude_id Site ID to exclude.
	3696	+ *
	3697	+ * @return object|null Matching row ( id, url ) or null.
	3698	+ */
	3699	+ public function get_website_by_exact_url_excluding_id( $url, $exclude_id ) {
	3700	+ $url = rtrim( (string) $url, '/' );
	3701	+ $wp_table = esc_sql( $this->table_name( 'wp' ) );
	3702	+
	3703	+ return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT id, url FROM {$wp_table} WHERE TRIM(TRAILING '/' FROM url) = %s AND id <> %d", $url, intval( $exclude_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
	3704	+ }
	3705	+
	3706	+ /**
3548	3707	* Method get_websites_to_notice_health_threshold()
3549	3708	*
3550	3709	* Get websites to notice site health.
3551	3710	*
		@@ -3579,12 +3738,9 @@
3579	3738	$view_selects = ',' . $opts_view['selects'];
3580	3739	$view_joins = $opts_view['joins'];
3581	3740	}
3582	3741	return $this->wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $option_view is a validated SQL subquery
3583		- "SELECT wp.*,wp_sync.* {$view_selects} FROM {$wp_table} wp
3584		- JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid
3585		- {$view_joins}
3586		- WHERE wp.disable_health_check <> 1 AND wp.offline_check_result = 1 AND ( {$where_global_threshold} OR{$where_site_threshold} ) AND wp_sync.health_site_noticed = 0 " .
	3742	+ "SELECT wp.*,wp_sync.* {$view_selects} FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid {$view_joins} WHERE wp.disable_health_check <> 1 AND wp.offline_check_result = 1 AND ( {$where_global_threshold} OR{$where_site_threshold} ) AND wp_sync.health_site_noticed = 0 " . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd, the view fragments come from get_wp_options_join(), which allowlists option names, and both threshold fragments are literals picked above.
3587	3743	$where . ' GROUP BY wp.id ',
3588	3744	OBJECT
3589	3745	);
3590	3746	}
		@@ -3610,10 +3766,9 @@
3610	3766	}
3611	3767
3612	3768	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- is a validated SQL subquery.
3613	3769	return $this->wpdb->get_results(
3614		- "SELECT wp.*{$view_selects} FROM {$wp_table} wp
3615		- {$view_joins}" . ' WHERE wp.suspended = 0 AND wp.http_code_noticed = 0 AND wp.offline_check_result = -1 ' .
	3770	+ "SELECT wp.*{$view_selects} FROM {$wp_table} wp {$view_joins}" . ' WHERE wp.suspended = 0 AND wp.http_code_noticed = 0 AND wp.offline_check_result = -1 ' . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the view fragments come from get_wp_options_join(), which allowlists option names.
3616	3771	$where . ' GROUP BY wp.id ',
3617	3772	OBJECT
3618	3773	);
3619	3774	}
		@@ -3862,18 +4017,28 @@
3862	4017	*
3863	4018	* @return mixed Result
3864	4019	*/
3865	4020	public function insert_lookup_item( $item_name, $item_id, $obj_name, $obj_id ) {
	4021	+ // get_lookup_items() and delete_lookup_items() match item_name as given, so a
	4022	+ // name that sanitizing would alter is refused instead of being stored under a
	4023	+ // value the caller can never read back or delete.
	4024	+ if ( ! is_string( $item_name ) || sanitize_text_field( $item_name ) !== $item_name ) {
	4025	+ return false;
	4026	+ }
3866	4027	if ( empty( $item_name ) || empty( $item_id ) || empty( $obj_name ) || empty( $obj_id ) ) {
3867	4028	return false;
3868	4029	}
3869	4030	$data = array(
3870		- 'item_name' => 'cost',
	4031	+ 'item_name' => $item_name,
3871	4032	'item_id' => $item_id,
3872	4033	'object_name' => $obj_name,
3873	4034	'object_id' => $obj_id,
3874	4035	);
3875		- $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data );
	4036	+ // wpdb refuses a value past the column length before any SQL runs, and
	4037	+ // insert_id would still hold the previous insert's id.
	4038	+ if ( false === $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data ) ) {
	4039	+ return false;
	4040	+ }
3876	4041	return $this->wpdb->insert_id; // must return lookup id.
3877	4042	}
3878	4043
3879	4044	/**
		@@ -4048,9 +4213,9 @@
4048	4213	* @return bool result.
4049	4214	*/
4050	4215	public function is_existed_enabled_rest_key() {
4051	4216	$table_name = esc_sql( $this->table_name( 'api_keys' ) );
4052		- $enabled = $this->wpdb->get_row( "SELECT * FROM {$table_name} WHERE enabled = 1 LIMIT 1" );
	4217	+ $enabled = $this->wpdb->get_row( "SELECT * FROM {$table_name} WHERE enabled = 1 LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4053	4218	return $enabled ? true : false;
4054	4219	}
4055	4220
4056	4221	/**
		@@ -4061,9 +4226,9 @@
4061	4226	* @return array
4062	4227	*/
4063	4228	public function get_rest_api_key_by( $id ) {
4064	4229	$table_name = esc_sql( $this->table_name( 'api_keys' ) );
4065		- return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$table_name} WHERE key_id = %d", $id ) );
	4230	+ return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$table_name} WHERE key_id = %d", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4066	4231	}
4067	4232
4068	4233	/**
4069	4234	* Method remove_rest_api_key().
		@@ -4073,9 +4238,9 @@
4073	4238	* @return array
4074	4239	*/
4075	4240	public function remove_rest_api_key( $id ) {
4076	4241	$table_name = esc_sql( $this->table_name( 'api_keys' ) );
4077		- return $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$table_name} WHERE key_id = %s", $id ) );
	4242	+ return $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$table_name} WHERE key_id = %s", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4078	4243	}
4079	4244
4080	4245	/**
4081	4246	* Method get_rest_api_keys().
		@@ -4083,9 +4248,9 @@
4083	4248	* @return array
4084	4249	*/
4085	4250	public function get_rest_api_keys() {
4086	4251	$table_name = esc_sql( $this->table_name( 'api_keys' ) );
4087		- return $this->wpdb->get_results( "SELECT * FROM {$table_name} ORDER BY key_id DESC" );
	4252	+ return $this->wpdb->get_results( "SELECT * FROM {$table_name} ORDER BY key_id DESC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4088	4253	}
4089	4254
4090	4255
4091	4256	/**
		@@ -4155,9 +4320,9 @@
4155	4320	* @return mixed result
4156	4321	*/
4157	4322	public function get_regular_process_by_item_id_type_slug( $item_id, $type, $process_slug ) {
4158	4323	$table_name = esc_sql( $this->table_name( 'schedule_processes' ) );
4159		- return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT pr.* FROM {$table_name} pr WHERE pr.item_id = %d AND pr.type = %s AND pr.process_slug = %s", $item_id, $type, $process_slug ) );
	4324	+ return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT pr.* FROM {$table_name} pr WHERE pr.item_id = %d AND pr.type = %s AND pr.process_slug = %s", $item_id, $type, $process_slug ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4160	4325	}
4161	4326
4162	4327	/**
4163	4328	* Log SQL queries for debugging via hook.
		@@ -4187,6 +4352,50 @@
4187	4352	$params = apply_filters( 'mainwp_log_system_query_params', $params, $sql, $caller );
4188	4353	if ( is_array( $params ) && ! empty( $params['dev_log_query'] ) && ! empty( $sql ) ) {
4189	4354	do_action( 'mainwp_log_system_query', $params, $sql, $caller );
4190	4355	}
	4356	+ }
	4357	+
	4358	+ /**
	4359	+ * Method get_core_tables().
	4360	+ *
	4361	+ * Returns the list of core tables used by MainWP for validation data integrity and other purposes.
	4362	+ *
	4363	+ * @return array Core tables info
	4364	+ */
	4365	+ public function get_core_tables() {
	4366	+
	4367	+ $core_tables = array(
	4368	+ $this->table_name( 'wp_clients' ),
	4369	+ $this->table_name( 'wp_clients_fields' ),
	4370	+ $this->table_name( 'wp_clients_field_values' ),
	4371	+ $this->table_name( 'wp_clients_contacts' ),
	4372	+ $this->table_name( 'monitors' ),
	4373	+ $this->table_name( 'monitor_heartbeat' ),
	4374	+ $this->table_name( 'monitor_stat_hourly' ),
	4375	+ $this->table_name( 'wp' ),
	4376	+ $this->table_name( 'wp_sync' ),
	4377	+ $this->table_name( 'wp_options' ),
	4378	+ $this->table_name( 'wp_settings_backup' ),
	4379	+ $this->table_name( 'users' ),
	4380	+ $this->table_name( 'wp_status' ),
	4381	+ $this->table_name( 'group' ),
	4382	+ $this->table_name( 'wp_group' ),
	4383	+ $this->table_name( 'lookup_item_objects' ),
	4384	+ $this->table_name( 'wp_backup_progress' ),
	4385	+ $this->table_name( 'wp_backup' ),
	4386	+ $this->table_name( 'api_keys' ),
	4387	+ $this->table_name( 'action_log' ),
	4388	+ $this->table_name( 'request_log' ),
	4389	+ $this->table_name( 'schedule_processes' ),
	4390	+ $this->table_name( 'cost_tracker' ),
	4391	+ );
	4392	+
	4393	+ if ( ! defined( 'MAINWP_MODULE_LOG_ENABLED' ) || MAINWP_MODULE_LOG_ENABLED ) {
	4394	+ $core_tables[] = $this->table_name( 'wp_logs' );
	4395	+ $core_tables[] = $this->table_name( 'wp_logs_meta' );
	4396	+ $core_tables[] = $this->table_name( 'wp_logs_meta_archive' );
	4397	+ }
	4398	+
	4399	+ return apply_filters( 'mainwp_database_core_tables', $core_tables );
4191	4400	}
4192	4401	}