PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / trunk
MainWP Dashboard: Self-hosted WordPress Management for Agencies vtrunk
6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 5.2 5.2.1 5.2.2 5.3 All 153 releases
← All changes | class/class-mainwp-db.php +1565 -256 5.2 → trunk View file →
@@ -8,8 +8,13 @@
8 8 */
9 9
10 10 namespace MainWP\Dashboard;
11 11
12 +// Exit if accessed directly.
13 +if ( ! defined( 'ABSPATH' ) ) {
14 + exit;
15 +}
16 +
12 17 /**
13 18 * Class MainWP_DB
14 19 *
15 20 * @package MainWP\Dashboard
@@ -78,16 +83,47 @@
78 83 return static::$instance;
79 84 }
80 85
81 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 + /**
82 116 * Get wp_options database table view.
83 117 *
84 - * @param array $fields Extra option fields.
85 - * @param bool $default_value Whether or not to get default option fields.
118 + * @compatible function.
86 119 *
120 + * @param array $fields Extra option fields.
121 + * @param string $view_query view query.
122 + *
87 123 * @return array wp_options view.
88 124 */
89 - public function get_option_view( $fields = array(), $default_value = true ) {
125 + public function get_option_view( $fields = array(), $view_query = 'default' ) {
90 126
91 127 if ( ! is_array( $fields ) ) {
92 128 $fields = array();
93 129 }
@@ -95,9 +131,9 @@
95 131 $view = '(SELECT intwp.id AS wpid ';
96 132
97 133 $included_opts = array();
98 134
99 - if ( empty( $fields ) || $default_value ) {
135 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
100 136 $view .= ',(SELECT recent_comments.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_comments WHERE recent_comments.wpid = intwp.id AND recent_comments.name = "recent_comments" LIMIT 1) AS recent_comments,
101 137 (SELECT recent_posts.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_posts WHERE recent_posts.wpid = intwp.id AND recent_posts.name = "recent_posts" LIMIT 1) AS recent_posts,
102 138 (SELECT recent_pages.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_pages WHERE recent_pages.wpid = intwp.id AND recent_pages.name = "recent_pages" LIMIT 1) AS recent_pages,
103 139 (SELECT phpversion.value FROM ' . $this->table_name( 'wp_options' ) . ' phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = "phpversion" LIMIT 1) AS phpversion,
@@ -117,8 +153,10 @@
117 153 if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
118 154 $fields[] = 'cust_site_icon_info';
119 155 }
120 156
157 + $fields = $this->filter_safe_option_names( $fields );
158 +
121 159 if ( is_array( $fields ) ) {
122 160 foreach ( $fields as $field ) {
123 161 if ( empty( $field ) ) {
124 162 continue;
@@ -126,9 +164,9 @@
126 164 if ( in_array( $field, $included_opts ) ) {
127 165 continue;
128 166 }
129 167 $view .= ', ';
130 - $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 ) . '`';
131 169 }
132 170 }
133 171
134 172 $view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
@@ -135,11 +173,150 @@
135 173
136 174 return $view;
137 175 }
138 176
177 + /**
178 + * Method get_wp_options_join().
179 + *
180 + * @param array $fields Extra option fields.
181 + * @param string $view_query view query.
182 + * @param array<string, mixed> $params Additional parameters.
139 183
184 + *
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.
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.
187 + *
188 + * @since 6.0.8
189 + *
190 + * @return array wp_options view.
191 + */
192 + public function get_wp_options_join( $fields = array(), $view_query = 'default', $params = array() ) {
140 193
194 + if ( ! is_array( $fields ) ) {
195 + $fields = array();
196 + }
197 +
198 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
199 + $fields[] = 'recent_comments';
200 + $fields[] = 'recent_posts';
201 + $fields[] = 'recent_pages';
202 + $fields[] = 'phpversion';
203 + $fields[] = 'added_timestamp';
204 + $fields[] = 'wp_upgrades';
205 + }
206 +
207 + if ( ! in_array( 'signature_algo', $fields ) ) {
208 + $fields[] = 'signature_algo';
209 + }
210 +
211 + if ( ! in_array( 'verify_method', $fields ) ) {
212 + $fields[] = 'verify_method';
213 + }
214 +
215 + if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
216 + $fields[] = 'cust_site_icon_info';
217 + }
218 +
219 + $fields = array_values( array_unique( array_filter( $this->filter_safe_option_names( $fields ) ) ) );
220 +
221 + $tbl_wp_options = $this->table_name( 'wp_options' );
222 +
223 + $selects = array();
224 + $joins = array();
225 +
226 + foreach ( $fields as $name ) {
227 + $alias = 'owp_' . preg_replace( '/[^a-z0-9_]/i', '_', $name );
228 +
229 + // SELECT.
230 + $selects[] = "{$alias}.value AS `" . $this->escape( $name ) . '`';
231 +
232 + // JOIN.
233 + $joins[] = "LEFT JOIN {$tbl_wp_options} {$alias}
234 + ON {$alias}.wpid = wp.id
235 + AND {$alias}.name = '" . $this->escape( $name ) . "'";
236 + }
237 +
238 + return array(
239 + 'selects' => implode( ', ', $selects ),
240 + 'joins' => implode( "\n", $joins ),
241 + );
242 + }
243 +
141 244 /**
245 + * Method get_wp_options_view().
246 + *
247 + * @param array $fields Extra option fields.
248 + * @param string $view_query view query.
249 + * @param int $siteid Site id.
250 + *
251 + * @return string SQL subquery for wp_options view.
252 + */
253 + public function get_wp_options_view( $fields = array(), $view_query = 'default', $siteid = 0 ) {
254 +
255 + if ( ! is_array( $fields ) ) {
256 + $fields = array();
257 + }
258 +
259 + $where_site = '';
260 +
261 + if ( ! empty( $siteid ) && is_numeric( $siteid ) ) {
262 + $where_site = ' AND wpid = ' . intval( $siteid ) . ' ';
263 + }
264 +
265 + $view = '(SELECT wpid ';
266 +
267 + $included_opts = array();
268 +
269 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
270 + $view .= ',
271 + MAX(CASE WHEN name = "recent_comments" THEN value END) AS recent_comments,
272 + MAX(CASE WHEN name = "recent_posts" THEN value END) AS recent_posts,
273 + MAX(CASE WHEN name = "recent_pages" THEN value END) AS recent_pages,
274 + MAX(CASE WHEN name = "phpversion" THEN value END) AS phpversion,
275 + MAX(CASE WHEN name = "added_timestamp" THEN value END) AS added_timestamp,
276 + MAX(CASE WHEN name = "wp_upgrades" THEN value END) AS wp_upgrades ';
277 + $included_opts = array( 'recent_comments', 'recent_posts', 'recent_pages', 'phpversion', 'added_timestamp', 'wp_upgrades' );
278 + }
279 +
280 + if ( ! in_array( 'signature_algo', $fields ) ) {
281 + $fields[] = 'signature_algo';
282 + }
283 +
284 + if ( ! in_array( 'verify_method', $fields ) ) {
285 + $fields[] = 'verify_method';
286 + }
287 +
288 + if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
289 + $fields[] = 'cust_site_icon_info';
290 + }
291 +
292 + $fields = $this->filter_safe_option_names( $fields );
293 +
294 + if ( is_array( $fields ) ) {
295 + foreach ( $fields as $field ) {
296 + if ( empty( $field ) ) {
297 + continue;
298 + }
299 + if ( in_array( $field, $included_opts ) ) {
300 + continue;
301 + }
302 + $view .= ', ';
303 + $view .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
304 +
305 + $included_opts[] = $this->escape( $field );
306 + }
307 + }
308 +
309 + $view .= ' FROM ' . $this->table_name( 'wp_options' ) .
310 + " WHERE 1 {$where_site} AND name IN ('" . implode( "','", $included_opts ) . "')
311 + GROUP BY wpid ) ";
312 +
313 + return $view;
314 + }
315 +
316 +
317 +
318 + /**
142 319 * Get SQL to get child sites for current user.
143 320 *
144 321 * @since 5.2.
145 322 * @param array $params other params.
@@ -150,8 +327,16 @@
150 327
151 328 if ( ! is_array( $params ) ) {
152 329 $params = array();
153 330 }
331 +
332 + /**
333 + * The hook mainwp_get_sql_websites_by_params
334 + *
335 + * @since 5.5
336 + */
337 + $params = apply_filters( 'mainwp_get_sql_websites_by_params', $params );
338 +
154 339 $view = isset( $params['view'] ) ? $params['view'] : 'default';
155 340 $with_clients = isset( $params['with_clients'] ) && $params['with_clients'] ? true : false;
156 341
157 342 // legacy support.
@@ -157,9 +342,10 @@
157 342 // legacy support.
158 343 $selectgroups = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
159 344 $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
160 345 $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
161 - $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;
162 348 $extraWhere = isset( $params['where'] ) ? $params['where'] : null; // NOTE: without 'AND' at begining and ending of 'where'.
163 349 $for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
164 350 $others_fields = isset( $params['others_fields'] ) && is_array( $params['others_fields'] ) ? $params['others_fields'] : array( 'favi_icon' );
165 351 $is_staging = isset( $params['is_staging'] ) && in_array( $params['is_staging'], array( 'yes', 'no' ) ) ? $params['is_staging'] : 'no';
@@ -164,8 +350,10 @@
164 350 $others_fields = isset( $params['others_fields'] ) && is_array( $params['others_fields'] ) ? $params['others_fields'] : array( 'favi_icon' );
165 351 $is_staging = isset( $params['is_staging'] ) && in_array( $params['is_staging'], array( 'yes', 'no' ) ) ? $params['is_staging'] : 'no';
166 352 $limit = isset( $params['limit'] ) ? intval( $params['limit'] ) : '';
167 353
354 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
355 +
168 356 $s = isset( $params['s'] ) ? $params['s'] : '';
169 357 $exclude = isset( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
170 358 $include = isset( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
171 359 $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
@@ -171,8 +359,14 @@
171 359 $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
172 360 $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
173 361 $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
174 362
363 + // This parameter is used to enable caching in certain cases.
364 + $_included_cache_ids = isset( $params['_included_cache_ids'] ) ? wp_parse_id_list( $params['_included_cache_ids'] ) : array();
365 +
366 + $select_wpfields = isset( $params['select_wp_fields'] ) ? wp_parse_list( $params['select_wp_fields'] ) : '';
367 + $select_syncfields = isset( $params['select_sync_fields'] ) ? wp_parse_list( $params['select_sync_fields'] ) : '';
368 +
175 369 $where = '';
176 370
177 371 if ( ! empty( $extraWhere ) ) {
178 372 $where .= ' AND ' . $extraWhere;
@@ -190,9 +384,17 @@
190 384 $connected_sql = ' AND wp_sync.sync_errors <> "" ';
191 385 }
192 386
193 387 if ( ! empty( $s ) ) {
194 - $where .= ' AND ( wp.id LIKE "%' . $this->escape( $s ) . '%" OR wp.name LIKE "%' . $this->escape( $s ) . '%" OR wp.url LIKE "%' . $this->escape( $s ) . '%" ) ';
388 + $s = trim( $s );
389 + // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
390 + $like_pattern = '%' . $this->wpdb->esc_like( $s ) . '%';
391 + $where .= $this->wpdb->prepare(
392 + ' AND ( wp.id LIKE %s OR wp.name LIKE %s OR wp.url LIKE %s ) ',
393 + $like_pattern,
394 + $like_pattern,
395 + $like_pattern
396 + );
195 397 }
196 398
197 399 if ( ! empty( $exclude ) ) {
198 400 $where .= ' AND wp.id NOT IN (' . implode( ',', $exclude ) . ') ';
@@ -201,14 +403,35 @@
201 403 if ( ! empty( $include ) ) {
202 404 $where .= ' AND wp.id IN (' . implode( ',', $include ) . ') ';
203 405 }
204 406
407 + if ( ! empty( $_included_cache_ids ) ) {
408 + $where .= ' AND wp.id IN (' . implode( ',', $_included_cache_ids ) . ') ';
409 + }
410 +
411 + $specific_wp_fields = '';
412 + if ( ! empty( $select_wpfields ) ) {
413 + foreach ( $select_wpfields as $_field ) {
414 + $specific_wp_fields .= 'wp.' . $this->escape( $_field ) . ',';
415 + }
416 + $specific_wp_fields = rtrim( $specific_wp_fields, ',' );
417 + }
418 +
419 + $specific_sync_fields = '';
420 + if ( ! empty( $select_syncfields ) ) {
421 + foreach ( $select_syncfields as $_field ) {
422 + $specific_sync_fields .= 'wp_sync.' . $this->escape( $_field ) . ',';
423 + }
424 + $specific_sync_fields = rtrim( $specific_sync_fields, ',' );
425 + }
426 +
205 427 // any, connected, disconnected, suspended, available_update.
206 428 if ( ! empty( $status ) && is_array( $status ) && ! in_array( 'any', $status ) ) {
207 429 $status_conds = array();
208 430 if ( in_array( 'available_update', $status ) ) {
209 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 <> '[]' ) ";
210 - $results = $this->wpdb->get_results( 'SELECT wpid FROM ' . $this->table_name( 'wp_options' ) . " WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]' " );
432 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
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.
211 434 if ( $results ) {
212 435 $wp_ids = array();
213 436 foreach ( $results as $item ) {
214 437 if ( ! empty( $item->wpid ) ) {
@@ -223,9 +446,9 @@
223 446 $status_conds[] = ' ( ' . $available_sql . ') ';
224 447 }
225 448
226 449 if ( in_array( 'connected', $status ) ) {
227 - $status_conds[] = ' ( wp_sync.sync_errors == "" ) ';
450 + $status_conds[] = ' ( wp_sync.sync_errors = "" ) ';
228 451 }
229 452 if ( in_array( 'disconnected', $status ) ) {
230 453 $status_conds[] = " wp_sync.sync_errors <> '' ";
231 454 }
@@ -236,8 +459,11 @@
236 459
237 460 if ( ! empty( $status_conds ) ) {
238 461 $where .= ' AND ( ' . implode( ' OR ', $status_conds ) . ' ) ';
239 462 }
463 + if ( in_array( 'unsuspended', $status ) && ! in_array( 'suspended', $status ) ) { // to sure not conflict the suspended status.
464 + $where .= ' AND wp.suspended = 0 ';
465 + }
240 466 }
241 467
242 468 if ( ! empty( $page ) && ! empty( $per_page ) ) {
243 469 $limit = ( $page - 1 ) * $per_page . ',' . $per_page;
@@ -251,9 +477,10 @@
251 477 $join_clients = '';
252 478
253 479 if ( $with_clients ) {
254 480 $select_clients = ', wpclient.name as client_name ';
255 - $join_clients = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
481 + $clients_table = esc_sql( $this->table_name( 'wp_clients' ) );
482 + $join_clients = " LEFT JOIN {$clients_table} wpclient ON wp.client_id = wpclient.client_id ";
256 483 }
257 484
258 485 $base_fields = array(
259 486 'wp.id',
@@ -268,9 +495,8 @@
268 495 'wp.privkey',
269 496 'wp.pubkey',
270 497 'wp.wpe',
271 498 'wp.is_staging',
272 - 'wp.pubkey',
273 499 'wp.force_use_ipv4',
274 500 'wp.siteurl',
275 501 'wp.suspended',
276 502 'wp.mainwpdir',
@@ -281,11 +507,13 @@
281 507 'wp.backup_before_upgrade',
282 508 'wp.userid',
283 509 'wp.plugins',
284 510 'wp.themes',
511 + 'wp.offline_check_result', // 1 - online, -1 offline.
512 + 'wp.automatic_update',
285 513 );
286 514
287 - $select = ' wp.*,wp_sync.*,wp_optionview.* ';
515 + $select = ' wp.*,wp_sync.* ';
288 516 if ( 'base_view' === $view ) {
289 517 $select = implode( ',', $base_fields );
290 518 } elseif ( 'updates_view' === $view ) {
291 519 $updates_fields = array(
@@ -298,33 +526,72 @@
298 526 );
299 527 $select = implode( ',', array_merge( $updates_fields, $base_fields ) );
300 528 }
301 529
530 + $view_selects = '';
531 + $view_joins = '';
532 +
533 + if ( $use_comp_subquery ) {
534 + $view_selects = ',wp_optionview.* ';
535 + $view_joins = ' JOIN ' . $this->get_option_view_by( $view, $others_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid ';
536 + } else {
537 + $opts_view = $this->get_option_view_by_join( $view, $others_fields );
538 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
539 + $view_selects = ',' . $opts_view['selects'];
540 + $view_joins = $opts_view['joins'];
541 + }
542 + }
543 +
302 544 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
303 545 if ( $selectgroups ) {
304 - $qry = 'SELECT ' . $select . ', 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,
305 - ' . $select_clients . '
306 - FROM ' . $this->table_name( 'wp' ) . ' wp
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;
548 +
549 + $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
307 550 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
308 551 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
309 552 ' . $join_clients . '
310 553 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
311 - JOIN ' . $this->get_option_view_by( $view, $others_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid
312 - WHERE 1 ' . $where . $connected_sql . '
554 + ' . $view_joins . '
555 + WHERE 1 ' . $where . $connected_sql;
556 + $group_qry = '
313 557 GROUP BY wp.id, wp_sync.sync_id
314 558 ORDER BY ' . $orderBy;
559 + } elseif ( ! empty( $specific_wp_fields ) ) { // Optimize select sites data.
560 + $select = $specific_wp_fields;
561 + $join_sync = '';
562 + $group_by = 'wp.id';
563 +
564 + if ( ! empty( $specific_sync_fields ) ) {
565 + $select .= ',' . $specific_sync_fields;
566 + $join_sync = ' JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid';
567 + $group_by .= ', wp_sync.sync_id';
568 + }
569 + $select_qry = 'SELECT ' . $select . $view_selects;
570 + $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
571 + ' . $join_sync . ' ' . $view_joins . '
572 + WHERE 1 ' . $where . $connected_sql;
573 + $group_qry = '
574 + GROUP BY ' . $group_by . '
575 + ORDER BY ' . $orderBy;
315 576 } else {
316 - $qry = 'SELECT ' . $select .
317 - $select_clients . '
318 - FROM ' . $this->table_name( 'wp' ) . ' wp
577 + $select_qry = 'SELECT ' . $select . $view_selects . $select_clients;
578 + $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
319 579 ' . $join_clients . '
320 580 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
321 - JOIN ' . $this->get_option_view_by( $view, $others_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid
322 - WHERE 1 ' . $where . $connected_sql . '
581 + ' . $view_joins . '
582 + WHERE 1 ' . $where . $connected_sql;
583 + $group_qry = '
323 584 GROUP BY wp.id, wp_sync.sync_id
324 585 ORDER BY ' . $orderBy;
325 586 }
326 587
588 + if ( $count_sql ) {
589 + return 'SELECT COUNT(DISTINCT wp.id) ' . $qry;
590 + }
591 +
592 + $qry = $select_qry . $qry . $group_qry;
593 +
327 594 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
328 595 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
329 596 } elseif ( false !== $rowcount ) {
330 597 $qry .= ' LIMIT ' . $rowcount;
@@ -340,14 +607,96 @@
340 607 $qry .= ' LIMIT ' . intval( $start ) . ', ' . intval( $limit_sites );
341 608 }
342 609 }
343 610
611 + if ( ! empty( $_included_cache_ids ) ) {
612 + MainWP_Logger::instance()->log_events( 'cache-metrics', sprintf( '[sql websites by params=%s]', $qry ) );
613 + }
614 + MainWP_Logger::instance()->log_events( 'db-queries', sprintf( '[sql websites by params=%s]', $qry ) );
344 615 return $qry;
345 616 }
346 617
347 618 /**
619 + * Improve get wp_options database table view.
620 + *
621 + * @param array $view Option view.
622 + * @param array $other_fields Extra option fields.
623 + *
624 + * @return array wp_options view.
625 + */
626 + public function get_option_view_by_join( $view = '', $other_fields = array() ) {
627 +
628 + $default = array(
629 + 'recent_comments',
630 + 'recent_posts',
631 + 'recent_pages',
632 + 'phpversion',
633 + 'added_timestamp',
634 + 'wp_upgrades',
635 + );
636 +
637 + $fields = array();
638 +
639 + if ( 'updates_view' === $view ) {
640 + $fields = array(
641 + 'wp_upgrades',
642 + 'ignored_wp_upgrades',
643 + 'ignored_trans_updates',
644 + );
645 + } elseif ( in_array( $view, array( 'simple_view', 'base_view', 'monitor_view', 'ping_view', 'uptime_notification' ) ) ) {
646 + $fields = array();
647 + if ( 'monitor_view' === $view || 'ping_view' === $view ) {
648 + $fields[] = 'health_site_status';
649 + $fields[] = 'bypass_cache';
650 + }
651 + } elseif ( 'custom_view' !== $view ) {
652 + $fields = $default;
653 + }
654 +
655 + if ( is_array( $other_fields ) && ! empty( $other_fields ) ) {
656 + $fields = array_unique( array_merge( $fields, $other_fields ) );
657 + }
658 +
659 + if ( 'custom_view' !== $view ) {
660 + if ( ! in_array( 'signature_algo', $fields ) ) {
661 + $fields[] = 'signature_algo';
662 + }
663 +
664 + if ( ! in_array( 'verify_method', $fields ) ) {
665 + $fields[] = 'verify_method';
666 + }
667 + }
668 +
669 + $fields = array_values( array_filter( $this->filter_safe_option_names( $fields ) ) );
670 +
671 + $tbl_wp_options = $this->table_name( 'wp_options' );
672 +
673 + $selects = array();
674 + $joins = array();
675 +
676 + foreach ( $fields as $name ) {
677 + $alias = 'owp_' . preg_replace( '/[^a-z0-9_]/i', '_', $name );
678 +
679 + // SELECT.
680 + $selects[] = "{$alias}.value AS `" . $this->escape( $name ) . '`';
681 +
682 + // JOIN.
683 + $joins[] = "LEFT JOIN {$tbl_wp_options} {$alias}
684 + ON {$alias}.wpid = wp.id
685 + AND {$alias}.name = '" . $this->escape( $name ) . "'";
686 + }
687 +
688 + return array(
689 + 'selects' => implode( ', ', $selects ),
690 + 'joins' => implode( "\n", $joins ),
691 + );
692 + }
693 +
694 + /**
348 695 * Get wp_options database table view.
349 696 *
697 + * Use new get_option_view_by_join() method to improve the performance of wp_options view, and this method is used to generate the SQL with LEFT JOIN which will be much faster than subquery, but it will return the same result as get_option_view() method, and 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.
698 + *
350 699 * @param array $view Option view.
351 700 * @param array $other_fields Extra option fields.
352 701 *
353 702 * @return array wp_options view.
@@ -362,16 +711,23 @@
362 711 'added_timestamp',
363 712 'wp_upgrades',
364 713 );
365 714
715 + $fields = array();
716 +
366 717 if ( 'updates_view' === $view ) {
367 718 $fields = array(
368 719 'wp_upgrades',
369 720 'ignored_wp_upgrades',
721 + 'ignored_trans_updates',
370 722 );
371 - } elseif ( 'base_view' === $view ) {
723 + } elseif ( in_array( $view, array( 'simple_view', 'base_view', 'monitor_view', 'ping_view', 'uptime_notification' ) ) ) {
372 724 $fields = array();
373 - } else {
725 + if ( 'monitor_view' === $view || 'ping_view' === $view ) {
726 + $fields[] = 'health_site_status';
727 + $fields[] = 'bypass_cache';
728 + }
729 + } elseif ( 'custom_view' !== $view ) {
374 730 $fields = $default;
375 731 }
376 732
377 733 if ( is_array( $other_fields ) && ! empty( $other_fields ) ) {
@@ -377,17 +733,21 @@
377 733 if ( is_array( $other_fields ) && ! empty( $other_fields ) ) {
378 734 $fields = array_unique( array_merge( $fields, $other_fields ) );
379 735 }
380 736
381 - $view = '(SELECT intwp.id AS wpid ';
737 + $view_query = '(SELECT wpid ';
382 738
383 - if ( ! in_array( 'signature_algo', $fields ) ) {
384 - $fields[] = 'signature_algo';
739 + if ( 'custom_view' !== $view ) {
740 + if ( ! in_array( 'signature_algo', $fields ) ) {
741 + $fields[] = 'signature_algo';
742 + }
743 +
744 + if ( ! in_array( 'verify_method', $fields ) ) {
745 + $fields[] = 'verify_method';
746 + }
385 747 }
386 748
387 - if ( ! in_array( 'verify_method', $fields ) ) {
388 - $fields[] = 'verify_method';
389 - }
749 + $fields = $this->filter_safe_option_names( $fields );
390 750
391 751 foreach ( $fields as $field ) {
392 752
393 753 if ( empty( $field ) ) {
@@ -393,15 +753,17 @@
393 753 if ( empty( $field ) ) {
394 754 continue;
395 755 }
396 756
397 - $view .= ', ';
398 - $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 );
757 + $view_query .= ', ';
758 + $view_query .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
399 759 }
400 760
401 - $view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
761 + $view_query .= ' FROM ' . $this->table_name( 'wp_options' ) .
762 + " WHERE name IN ('" . implode( "','", $fields ) . "')
763 + GROUP BY wpid ) ";
402 764
403 - return $view;
765 + return $view_query;
404 766 }
405 767
406 768 /**
407 769 * Method get_select_groups_belong().
@@ -425,18 +787,20 @@
425 787 *
426 788 * @return array $connected_sites Array of connected sites.
427 789 */
428 790 public function get_connected_websites( $sites_ids = false ) {
429 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
791 + $where = $this->get_sql_where_allow_access_sites( 'wp' );
792 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
793 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
430 794
431 - $sql = 'SELECT wp.*,wp_sync.*
432 - FROM ' . $this->table_name( 'wp' ) . ' wp
433 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync
795 + $sql = "SELECT wp.*,wp_sync.*
796 + FROM {$wp_table} wp
797 + JOIN {$wp_sync_table} wp_sync
434 798 ON wp.id = wp_sync.wpid
435 - WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors = "") ' .
799 + WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors = \"\") " .
436 800 $where;
437 801
438 - $websites = $this->wpdb->get_results( $sql );
802 + $websites = $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is fully escaped: table names via esc_sql(), WHERE fragment from validated get_sql_where_allow_access_sites()
439 803 $connected_sites = array();
440 804 if ( $websites ) {
441 805 foreach ( $websites as $website ) {
442 806
@@ -461,18 +825,20 @@
461 825 *
462 826 * @return array $disc_sites Array of disonnected sites.
463 827 */
464 828 public function get_disconnected_websites( $sites_ids = false ) {
465 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
829 + $where = $this->get_sql_where_allow_access_sites( 'wp' );
830 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
831 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
466 832
467 - $sql = 'SELECT wp.*,wp_sync.*
468 - FROM ' . $this->table_name( 'wp' ) . ' wp
469 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync
833 + $sql = "SELECT wp.*,wp_sync.*
834 + FROM {$wp_table} wp
835 + JOIN {$wp_sync_table} wp_sync
470 836 ON wp.id = wp_sync.wpid
471 - WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors <> "") ' .
837 + WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors <> \"\") " .
472 838 $where;
473 839
474 - $websites = $this->wpdb->get_results( $sql );
840 + $websites = $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is fully escaped: table names via esc_sql(), WHERE fragment from validated get_sql_where_allow_access_sites()
475 841 $disc_sites = array();
476 842 if ( $websites ) {
477 843 foreach ( $websites as $website ) {
478 844
@@ -498,8 +864,12 @@
498 864 *
499 865 * @return int Child site count.
500 866 *
501 867 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
868 + *
869 + * @see get_websites_count_for_current_user() For Abilities API with status/tags/client filters.
870 + * This method is intentionally simple for UI display purposes (total sites count).
871 + * The two methods serve different use cases and should not be consolidated.
502 872 */
503 873 public function get_websites_count( $userId = null, $all_access = false ) {
504 874 static $total_sites;
505 875 if ( null !== $total_sites ) { // NOSONAR -- static value.
@@ -515,15 +885,16 @@
515 885 global $current_user;
516 886
517 887 $userId = $current_user->ID;
518 888 }
519 - $where = ( null === $userId ? '' : ' wp.userid = ' . $userId );
889 + $where = ( null === $userId ? '' : ' wp.userid = ' . intval( $userId ) );
520 890 if ( ! $all_access ) {
521 891 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
522 892 }
523 - $qry = 'SELECT COUNT(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp WHERE 1 ' . $where;
893 + $table_name = esc_sql( $this->table_name( 'wp' ) );
894 + $qry = "SELECT COUNT(wp.id) FROM {$table_name} wp WHERE 1 {$where}";
524 895
525 - $total = $this->wpdb->get_var( $qry );
896 + $total = $this->wpdb->get_var( $qry ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is fully escaped: table names via esc_sql(), WHERE fragment from validated get_sql_where_allow_access_sites()
526 897 $total_sites = $total;// NOSONAR -- static value.
527 898 return $total;
528 899 }
529 900
@@ -551,9 +922,9 @@
551 922
552 923 $select_stats = ' ( SELECT COUNT(wp.id) as count_all ';
553 924 if ( ! empty( $params['count_disconnected'] ) ) {
554 925 $select_stats .= ',( SELECT COUNT(wp_disconnected.id) FROM ' . $this->table_name( 'wp' ) . ' wp_disconnected LEFT JOIN ' . $this->table_name( 'wp_sync' ) . ' as wp_sync ';
555 - $select_stats .= ' ON wp_disconnected.id = wp_sync.wpid WHERE wp_sync.sync_errors != "" ) as count_disconnected ';
926 + $select_stats .= ' ON wp_disconnected.id = wp_sync.wpid WHERE wp_sync.sync_errors <> "" ) as count_disconnected ';
556 927 }
557 928 if ( ! empty( $params['count_suspended'] ) ) {
558 929 $select_stats .= ',( SELECT COUNT(wp_suspended.id) FROM ' . $this->table_name( 'wp' ) . ' wp_suspended WHERE wp_suspended.suspended = 1 ) as count_suspended ';
559 930 }
@@ -602,9 +973,10 @@
602 973 } else {
603 974 return false;
604 975 }
605 976
606 - $value = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $site_id ) );
977 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
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.
607 979
608 980 if ( null === $value && null !== $default_value ) {
609 981 return $default_value;
610 982 }
@@ -680,13 +1052,12 @@
680 1052 if ( empty( $get_options ) ) {
681 1053 return $arr_options; // all options.
682 1054 }
683 1055
684 - $options_name = implode( "','", $get_options );
685 - $options_name = "'" . $options_name . "'";
1056 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
1057 + $placeholders = implode( ',', array_fill( 0, count( $get_options ), '%s' ) );
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.
686 1059
687 - $options_db = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name, value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name IN (' . $options_name . ')', $site_id ) );
688 -
689 1060 $fill_options = array(
690 1061 'primary_lasttime_backup',
691 1062 );
692 1063
@@ -721,9 +1092,10 @@
721 1092 } else {
722 1093 $site_id = $website->id;
723 1094 }
724 1095
725 - $rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $site_id ) );
1096 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
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.
726 1098 if ( empty( $rslt ) ) {
727 1099 $this->wpdb->insert(
728 1100 $this->table_name( 'wp_options' ),
729 1101 array(
@@ -745,8 +1117,37 @@
745 1117 }
746 1118
747 1119
748 1120 /**
1121 + * Remove child site options.
1122 + *
1123 + * @param object $website Child site object.
1124 + * @param mixed $options Option to update.
1125 + */
1126 + public function remove_website_option( $website, $options ) {
1127 +
1128 + if ( empty( $options ) ) {
1129 + return;
1130 + }
1131 +
1132 + if ( is_numeric( $website ) ) {
1133 + $site_id = intval( $website );
1134 + } else {
1135 + $site_id = $website->id;
1136 + }
1137 +
1138 + if ( ! is_array( $options ) ) {
1139 + $options = (array) $options;
1140 + }
1141 +
1142 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
1143 + foreach ( $options as $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.
1145 + }
1146 + }
1147 +
1148 +
1149 + /**
749 1150 * Get general Child site option.
750 1151 *
751 1152 * @param mixed $option Child Site option name.
752 1153 *
@@ -761,9 +1162,10 @@
761 1162 } else {
762 1163 static::$general_options[] = array();
763 1164 }
764 1165
765 - $val = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', 0 ) );
1166 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
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.
766 1168
767 1169 static::$general_options[ $option ] = $val;
768 1170 return $val;
769 1171 }
@@ -798,12 +1200,15 @@
798 1200 $diff_options[] = $opt;
799 1201 }
800 1202 }
801 1203
802 - $options_name = implode( "','", $diff_options );
803 - $options_name = "'" . $options_name . "'";
1204 + if ( empty( $diff_options ) ) {
1205 + return $return_options;
1206 + }
804 1207
805 - $options_db = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name, value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name IN (' . $options_name . ')', 0 ) );
1208 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
1209 + $placeholders = implode( ',', array_fill( 0, count( $diff_options ), '%s' ) );
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.
806 1211
807 1212 foreach ( (array) $options_db as $o ) {
808 1213 $return_options[ $o->name ] = $o->value;
809 1214 static::$general_options[ $o->name ] = $o->value;
@@ -833,9 +1238,10 @@
833 1238 static::$general_options[] = array();
834 1239 }
835 1240 static::$general_options[ $option ] = $value;
836 1241
837 - $rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', 0 ) );
1242 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
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.
838 1244
839 1245 if ( empty( $rslt ) ) {
840 1246 $this->wpdb->insert(
841 1247 $this->table_name( 'wp_options' ),
@@ -900,48 +1306,23 @@
900 1306 */
901 1307 public function get_sql_websites() {
902 1308 $where = $this->get_sql_where_allow_access_sites( 'wp' );
903 1309
904 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
905 - FROM ' . $this->table_name( 'wp' ) . ' wp
906 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
907 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
908 - WHERE 1 ' . $where;
909 - }
1310 + $view_selects = '';
1311 + $view_joins = '';
910 1312
911 - /**
912 - * Get child sites to run the status check process.
913 - *
914 - * @param int $last_check Time of the last check.
915 - * @param int $count Number of websites.
916 - *
917 - * @return string SQL string.
918 - */
919 - public function get_sql_websites_to_check_status( $last_check, $count = 20 ) {
920 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
921 - return 'SELECT wp.*
922 - FROM ' . $this->table_name( 'wp' ) . ' wp
923 - WHERE wp.disable_status_check <> 1 AND ( wp.status_check_interval = 0 AND wp.offline_checks_last < ' . intval( $last_check ) . ' )' .
924 - $where . '
925 - LIMIT ' . intval( $count );
926 - }
1313 + $opts_view = $this->get_wp_options_join();
927 1314
1315 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
1316 + $view_selects = ',' . $opts_view['selects'];
1317 + $view_joins = $opts_view['joins'];
1318 + }
928 1319
929 - /**
930 - * Get child sites to run the status individual check process.
931 - *
932 - * @param int $count Number of websites.
933 - *
934 - * @return string SQL string.
935 - */
936 - public function get_sql_websites_to_check_individual_status( $count = 20 ) {
937 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
938 - $time = time();
939 - return 'SELECT wp.*
1320 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
940 1321 FROM ' . $this->table_name( 'wp' ) . ' wp
941 - WHERE wp.disable_status_check <> 1 AND ( wp.status_check_interval <> 0 AND ( wp.offline_checks_last + wp.status_check_interval * 60 < ' . intval( $time ) . ' ) )' .
942 - $where . '
943 - LIMIT ' . intval( $count );
1322 + JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1323 + ' . $view_joins . '
1324 + WHERE 1 ' . $where . ' ORDER BY wp.id';
944 1325 }
945 1326
946 1327 /**
947 1328 * Get child sites by user id via SQL.
@@ -966,24 +1347,34 @@
966 1347 }
967 1348
968 1349 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
969 1350
1351 + $view_selects = '';
1352 + $view_joins = '';
1353 +
1354 + $opts_view = $this->get_wp_options_join();
1355 +
1356 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
1357 + $view_selects = ',' . $opts_view['selects'];
1358 + $view_joins = $opts_view['joins'];
1359 + }
1360 +
970 1361 if ( $selectgroups ) {
971 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, 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
1362 + $qry = 'SELECT wp.*,wp_sync.*' . $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
972 1363 FROM ' . $this->table_name( 'wp' ) . ' wp
973 1364 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
974 1365 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
975 1366 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
976 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1367 + ' . $view_joins . '
977 1368 WHERE wp.userid = ' . $userid . "
978 1369 $where
979 1370 GROUP BY wp.id, wp_sync.sync_id
980 1371 ORDER BY " . $orderBy;
981 1372 } else {
982 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
1373 + $qry = 'SELECT wp.*,wp_sync.*' . $view_selects . '
983 1374 FROM ' . $this->table_name( 'wp' ) . ' wp
984 1375 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
985 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1376 + ' . $view_joins . '
986 1377 WHERE wp.userid = ' . $userid . "
987 1378 $where
988 1379 ORDER BY " . $orderBy;
989 1380 }
@@ -1030,8 +1421,15 @@
1030 1421 $is_staging = 'no',
1031 1422 $params = array()
1032 1423 ) {
1033 1424
1425 + /**
1426 + * The hook mainwp_get_sql_websites
1427 + *
1428 + * @since 5.5
1429 + */
1430 + $params = apply_filters( 'mainwp_get_sql_websites', $params, $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging );
1431 +
1034 1432 $where = '';
1035 1433 if ( MainWP_System::instance()->is_multi_user() ) {
1036 1434
1037 1435 /**
@@ -1045,9 +1443,15 @@
1045 1443 }
1046 1444
1047 1445 if ( null !== $search_site ) {
1048 1446 $search_site = trim( $search_site );
1049 - $where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
1447 + // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
1448 + $like_pattern = '%' . $this->wpdb->esc_like( $search_site ) . '%';
1449 + $where .= $this->wpdb->prepare(
1450 + ' AND (wp.name LIKE %s OR wp.url LIKE %s) ',
1451 + $like_pattern,
1452 + $like_pattern
1453 + );
1050 1454 }
1051 1455
1052 1456 if ( ! empty( $extraWhere ) ) {
1053 1457 $where .= ' AND ' . $extraWhere . ' ';
@@ -1064,8 +1468,10 @@
1064 1468 } elseif ( is_array( $params ) && isset( $params['connected'] ) && 'no' === $params['connected'] ) {
1065 1469 $connected_sql = ' AND wp_sync.sync_errors <> "" ';
1066 1470 }
1067 1471
1472 + $use_comp_subquery = false;
1473 +
1068 1474 $limit = '';
1069 1475 if ( $params && is_array( $params ) ) {
1070 1476 $s = isset( $params['s'] ) ? $params['s'] : '';
1071 1477 $exclude = isset( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
@@ -1073,10 +1479,19 @@
1073 1479 $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
1074 1480 $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
1075 1481 $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
1076 1482
1483 + // This parameter is used to enable caching in certain cases.
1484 + $_included_cache_ids = isset( $params['_included_cache_ids'] ) ? wp_parse_id_list( $params['_included_cache_ids'] ) : array();
1485 +
1077 1486 if ( ! empty( $s ) ) {
1078 - $where .= ' AND ( wp.id LIKE "%' . $this->escape( $s ) . '%" OR wp.name LIKE "%' . $this->escape( $s ) . '%" OR wp.url LIKE "%' . $this->escape( $s ) . '%" ) ';
1487 + $s = trim( $s );
1488 + // Note: This SQL is executed via m_query() which bypasses wpdb, so we can't
1489 + // use wpdb->prepare() (its placeholders won't be resolved). Instead, escape
1490 + // LIKE wildcards and the value manually. First escape LIKE special chars,
1491 + // then SQL escape the result.
1492 + $like_value = '%' . $this->escape( $this->wpdb->esc_like( $s ) ) . '%';
1493 + $where .= " AND ( wp.id LIKE '{$like_value}' OR wp.name LIKE '{$like_value}' OR wp.url LIKE '{$like_value}' ) ";
1079 1494 }
1080 1495
1081 1496 if ( ! empty( $exclude ) ) {
1082 1497 $where .= ' AND wp.id NOT IN (' . implode( ',', $exclude ) . ') ';
@@ -1085,14 +1500,19 @@
1085 1500 if ( ! empty( $include ) ) {
1086 1501 $where .= ' AND wp.id IN (' . implode( ',', $include ) . ') ';
1087 1502 }
1088 1503
1504 + if ( ! empty( $_included_cache_ids ) ) {
1505 + $where .= ' AND wp.id IN (' . implode( ',', $_included_cache_ids ) . ') ';
1506 + }
1507 +
1089 1508 // any, connected, disconnected, suspended, available_update.
1090 1509 if ( ! empty( $status ) && is_array( $status ) && ! in_array( 'any', $status ) ) {
1091 1510 $status_conds = array();
1092 1511 if ( in_array( 'available_update', $status ) ) {
1093 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 <> '[]' ) ";
1094 - $results = $this->wpdb->get_results( 'SELECT wpid FROM ' . $this->table_name( 'wp_options' ) . " WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]' " );
1513 + $options_table = esc_sql( $this->table_name( 'wp_options' ) );
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.
1095 1515 if ( $results ) {
1096 1516 $wp_ids = array();
1097 1517 foreach ( $results as $item ) {
1098 1518 if ( ! empty( $item->wpid ) ) {
@@ -1107,9 +1527,9 @@
1107 1527 $status_conds[] = ' ( ' . $available_sql . ') ';
1108 1528 }
1109 1529
1110 1530 if ( in_array( 'connected', $status ) ) {
1111 - $status_conds[] = ' ( wp_sync.sync_errors == "" ) ';
1531 + $status_conds[] = ' ( wp_sync.sync_errors = "" ) ';
1112 1532 }
1113 1533 if ( in_array( 'disconnected', $status ) ) {
1114 1534 $status_conds[] = " wp_sync.sync_errors <> '' ";
1115 1535 }
@@ -1120,13 +1540,18 @@
1120 1540
1121 1541 if ( ! empty( $status_conds ) ) {
1122 1542 $where .= ' AND ( ' . implode( ' OR ', $status_conds ) . ' ) ';
1123 1543 }
1544 +
1545 + if ( in_array( 'unsuspended', $status ) && ! in_array( 'suspended', $status ) ) { // to sure not conflict the suspended status.
1546 + $where .= ' AND wp.suspended = 0 ';
1547 + }
1124 1548 }
1125 1549
1126 1550 if ( ! empty( $page ) && ! empty( $per_page ) ) {
1127 1551 $limit = ( $page - 1 ) * $per_page . ',' . $per_page;
1128 1552 }
1553 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
1129 1554 }
1130 1555
1131 1556 if ( 'wp.url' === $orderBy ) {
1132 1557 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
@@ -1131,11 +1556,26 @@
1131 1556 if ( 'wp.url' === $orderBy ) {
1132 1557 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
1133 1558 }
1134 1559
1560 + $view_selects = '';
1561 + $view_joins = '';
1562 +
1563 + if ( $use_comp_subquery ) {
1564 + $view_selects = ',wp_optionview.* ';
1565 + $view_joins = ' JOIN ' . $this->get_wp_options_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ';
1566 + } else {
1567 + $opts_view = $this->get_wp_options_join( $extra_view );
1568 +
1569 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
1570 + $view_selects = ',' . $opts_view['selects'];
1571 + $view_joins = $opts_view['joins'];
1572 + }
1573 + }
1574 +
1135 1575 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1136 1576 if ( $selectgroups ) {
1137 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, 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,
1577 + $qry = 'SELECT wp.*,wp_sync.*' . $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,
1138 1578 wpclient.name as client_name
1139 1579 FROM ' . $this->table_name( 'wp' ) . ' wp
1140 1580 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1141 1581 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
@@ -1140,18 +1580,18 @@
1140 1580 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1141 1581 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1142 1582 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1143 1583 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1144 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1584 + ' . $view_joins . '
1145 1585 WHERE 1 ' . $where . $connected_sql . '
1146 1586 GROUP BY wp.id, wp_sync.sync_id
1147 1587 ORDER BY ' . $orderBy;
1148 1588 } else {
1149 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, wpclient.name as client_name
1589 + $qry = 'SELECT wp.*,wp_sync.*' . $view_selects . ', wpclient.name as client_name
1150 1590 FROM ' . $this->table_name( 'wp' ) . ' wp
1151 1591 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1152 1592 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1153 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1593 + ' . $view_joins . '
1154 1594 WHERE 1 ' . $where . $connected_sql . '
1155 1595 GROUP BY wp.id, wp_sync.sync_id
1156 1596 ORDER BY ' . $orderBy;
1157 1597 }
@@ -1172,13 +1612,17 @@
1172 1612 $qry .= ' LIMIT ' . intval( $start ) . ', ' . intval( $limit_sites );
1173 1613 }
1174 1614 }
1175 1615
1616 + if ( ! empty( $_included_cache_ids ) ) {
1617 + MainWP_Logger::instance()->log_events( 'cache-metrics', sprintf( '[sql websites=%s]', $qry ) );
1618 + }
1619 + MainWP_Logger::instance()->log_events( 'db-queries', sprintf( '[sql websites=%s]', $qry ) );
1620 +
1176 1621 return $qry;
1177 1622 }
1178 1623
1179 1624
1180 -
1181 1625 /**
1182 1626 * Get SQL to get wp child sites for current user.
1183 1627 *
1184 1628 * @since 4.3
@@ -1191,17 +1635,18 @@
1191 1635 if ( ! is_array( $params ) ) {
1192 1636 $params = array();
1193 1637 }
1194 1638
1195 - $selectgroups = ! empty( $params['select_groups'] ) ? true : false;
1196 - $search_site = isset( $params['search_site'] ) && ! empty( $params['search_site'] ) ? $params['search_site'] : null;
1197 - $orderBy = isset( $params['order_by'] ) && ! empty( $params['order_by'] ) ? $params['order_by'] : 'wp.url';
1198 - $offset = isset( $params['offset'] ) ? $params['offset'] : false;
1199 - $rowcount = isset( $params['row_count'] ) ? $params['row_count'] : false;
1200 - $for_manager = isset( $params['for_manager'] ) ? $params['for_manager'] : false;
1201 - $extraWhere = isset( $params['extra_where'] ) && ! empty( $params['extra_where'] ) ? $params['extra_where'] : null;
1202 - $extra_view = isset( $params['extra_view'] ) && is_array( $params['extra_view'] ) && ! empty( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
1203 - $extra_join = isset( $params['extra_join'] ) ? $params['extra_join'] : '';
1639 + $selectgroups = ! empty( $params['select_groups'] ) ? true : false;
1640 + $search_site = isset( $params['search_site'] ) && ! empty( $params['search_site'] ) ? $params['search_site'] : null;
1641 + $orderBy = isset( $params['order_by'] ) && ! empty( $params['order_by'] ) ? $params['order_by'] : 'wp.url';
1642 + $offset = isset( $params['offset'] ) ? $params['offset'] : false;
1643 + $rowcount = isset( $params['row_count'] ) ? $params['row_count'] : false;
1644 + $for_manager = isset( $params['for_manager'] ) ? $params['for_manager'] : false;
1645 + $extraWhere = isset( $params['extra_where'] ) && ! empty( $params['extra_where'] ) ? $params['extra_where'] : null;
1646 + $extra_view = isset( $params['extra_view'] ) && is_array( $params['extra_view'] ) && ! empty( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
1647 + $extra_join = isset( $params['extra_join'] ) ? $params['extra_join'] : '';
1648 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
1204 1649
1205 1650 $extra_select_wp_fields = isset( $params['extra_select_wp_fields'] ) && is_array( $params['extra_select_wp_fields'] ) && ! empty( $params['extra_select_wp_fields'] ) ? $params['extra_select_wp_fields'] : array();
1206 1651 $extra_select_sql_fields = isset( $params['extra_select_sql_fields'] ) && ! empty( $params['extra_select_sql_fields'] ) ? $params['extra_select_sql_fields'] : '';
1207 1652
@@ -1211,9 +1656,15 @@
1211 1656 $where = '';
1212 1657
1213 1658 if ( null !== $search_site ) {
1214 1659 $search_site = trim( $search_site );
1215 - $where .= ' AND (wp.name LIKE "%' . $this->escape( $search_site ) . '%" OR wp.url LIKE "%' . $this->escape( $search_site ) . '%") ';
1660 + // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
1661 + $like_pattern = '%' . $this->wpdb->esc_like( $search_site ) . '%';
1662 + $where .= $this->wpdb->prepare(
1663 + ' AND (wp.name LIKE %s OR wp.url LIKE %s) ',
1664 + $like_pattern,
1665 + $like_pattern
1666 + );
1216 1667 }
1217 1668
1218 1669 if ( null !== $extraWhere ) {
1219 1670 $where .= ' AND ' . $extraWhere;
@@ -1232,8 +1683,22 @@
1232 1683 if ( ! empty( $extra_select_sql_fields ) ) {
1233 1684 $extra_select_sql_fields = ',' . $extra_select_sql_fields;
1234 1685 }
1235 1686
1687 + $view_selects = '';
1688 + $view_joins = '';
1689 +
1690 + if ( $use_comp_subquery ) {
1691 + $view_selects = ',wp_optionview.* ';
1692 + $view_joins = ' JOIN ' . $this->get_wp_options_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ';
1693 + } else {
1694 + $opts_view = $this->get_wp_options_join( $extra_view );
1695 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
1696 + $view_selects = ',' . $opts_view['selects'];
1697 + $view_joins = $opts_view['joins'];
1698 + }
1699 + }
1700 +
1236 1701 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1237 1702 if ( $selectgroups ) {
1238 1703 if ( $count_only ) {
1239 1704 $select = ' COUNT(DISTINCT(wp.id)) ';
@@ -1239,9 +1704,9 @@
1239 1704 $select = ' COUNT(DISTINCT(wp.id)) ';
1240 1705 } else {
1241 1706 $select = $select_wp_fields . '
1242 1707 ' . $extra_select_sql_fields . '
1243 - ,wp_sync.sync_errors,wp_optionview.*, 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, wpclient.name as client_name ';
1708 + ,wp_sync.sync_errors' . $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, wpclient.name as client_name ';
1244 1709 }
1245 1710 $qry = 'SELECT ' . $select . '
1246 1711 FROM ' . $this->table_name( 'wp' ) . ' wp
1247 1712 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
@@ -1247,10 +1712,10 @@
1247 1712 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1248 1713 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1249 1714 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1250 1715 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1251 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
1252 - $extra_join . '
1716 + ' . $view_joins . '
1717 + ' . $extra_join . '
1253 1718 WHERE 1 ' . $where;
1254 1719 if ( ! $count_only ) {
1255 1720 $qry .= ' GROUP BY wp.id, wp_sync.sync_id';
1256 1721 }
@@ -1260,16 +1725,16 @@
1260 1725 $select = ' COUNT(DISTINCT(wp.id)) ';
1261 1726 } else {
1262 1727 $select = $select_wp_fields . '
1263 1728 ' . $extra_select_sql_fields . '
1264 - ,wp_sync.sync_errors,wp_optionview.*, wpclient.name as client_name ';
1729 + ,wp_sync.sync_errors' . $view_selects . ', wpclient.name as client_name ';
1265 1730 }
1266 1731 $qry = 'SELECT ' . $select . '
1267 1732 FROM ' . $this->table_name( 'wp' ) . ' wp
1268 1733 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1269 1734 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1270 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
1271 - $extra_join . '
1735 + ' . $view_joins . '
1736 + ' . $extra_join . '
1272 1737 WHERE 1 ' . $where;
1273 1738 if ( ! $count_only ) {
1274 1739 $qry .= ' GROUP BY wp.id, wp_sync.sync_id';
1275 1740 }
@@ -1297,13 +1762,11 @@
1297 1762 public function get_sql_select_wp_valid_fields( $other_fields = array() ) {
1298 1763
1299 1764 $allow_other_fields = array(
1300 1765 'offline_checks_last',
1301 - 'offline_check_result',
1766 + 'offline_check_result', // 1 - online, -1 offline.
1302 1767 'http_response_code',
1303 - 'disable_status_check',
1304 1768 'disable_health_check',
1305 - 'status_check_interval',
1306 1769 'health_threshold',
1307 1770 'note',
1308 1771 'statsUpdate',
1309 1772 'directories',
@@ -1382,9 +1845,9 @@
1382 1845 $format = isset( $params['format'] ) ? $params['format'] : '';
1383 1846 $clients = isset( $params['client'] ) ? $params['client'] : '';
1384 1847 $fields = isset( $params['fields'] ) && is_array( $params['fields'] ) ? $params['fields'] : array();
1385 1848
1386 - $for_manager = false;
1849 + $for_manager = isset( $params['no_perm_check'] ) && true === $params['no_perm_check'];
1387 1850
1388 1851 $urlsWhere = '';
1389 1852
1390 1853 if ( isset( $params['urls'] ) && ! empty( $params['urls'] ) ) {
@@ -1445,13 +1908,11 @@
1445 1908 'id',
1446 1909 'url',
1447 1910 'name',
1448 1911 'offline_checks_last',
1449 - 'offline_check_result',
1912 + 'offline_check_result', // 1 - online, -1 offline.
1450 1913 'http_response_code',
1451 - 'disable_status_check',
1452 1914 'disable_health_check',
1453 - 'status_check_interval',
1454 1915 'health_threshold',
1455 1916 'note',
1456 1917 'dbsize',
1457 1918 'plugin_upgrades',
@@ -1501,9 +1962,12 @@
1501 1962 $data = array_unique( array_merge( $fields, $data ) ); // to prevent difference fields name.
1502 1963 }
1503 1964
1504 1965 $dbwebsites = array();
1505 - $websites = $this->query( $this->get_sql_websites_for_current_user( $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging, $args ) );
1966 +
1967 + $sql = $this->get_sql_websites_for_current_user( $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging, $args );
1968 + $websites = $this->query( $sql );
1969 +
1506 1970 while ( $websites && ( $website = static::fetch_object( $websites ) ) ) {
1507 1971
1508 1972 $obj_data = MainWP_Utility::map_site( $website, $data );
1509 1973
@@ -1508,9 +1972,12 @@
1508 1972 $obj_data = MainWP_Utility::map_site( $website, $data );
1509 1973
1510 1974 if ( $full_data ) {
1511 1975 $sum_upgrades = 0;
1512 - 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 ) ) {
1513 1980 $plugin_upgrades = json_decode( $obj_data->plugin_upgrades, true );
1514 1981 if ( is_array( $plugin_upgrades ) ) {
1515 1982 $sum_upgrades += count( $plugin_upgrades );
1516 1983 }
@@ -1515,9 +1982,9 @@
1515 1982 $sum_upgrades += count( $plugin_upgrades );
1516 1983 }
1517 1984 }
1518 1985
1519 - if ( '' !== $obj_data->theme_upgrades ) {
1986 + if ( ! empty( $obj_data->theme_upgrades ) ) {
1520 1987 $theme_upgrades = json_decode( $obj_data->theme_upgrades, true );
1521 1988 if ( is_array( $theme_upgrades ) ) {
1522 1989 $sum_upgrades += count( $theme_upgrades );
1523 1990 }
@@ -1522,9 +1989,9 @@
1522 1989 $sum_upgrades += count( $theme_upgrades );
1523 1990 }
1524 1991 }
1525 1992
1526 - if ( '' !== $obj_data->wp_upgrades ) {
1993 + if ( ! empty( $obj_data->wp_upgrades ) ) {
1527 1994 $wp_upgrades = json_decode( $obj_data->wp_upgrades, true );
1528 1995 if ( is_array( $wp_upgrades ) ) {
1529 1996 $sum_upgrades += count( $wp_upgrades );
1530 1997 }
@@ -1542,8 +2009,126 @@
1542 2009 return $dbwebsites;
1543 2010 }
1544 2011
1545 2012 /**
2013 + * Get count of child sites for the current user with filters.
2014 + *
2015 + * This method is optimized for the Abilities API to return site counts
2016 + * with filtering support for status, tags (groups), and client_id.
2017 + *
2018 + * IMPORTANT: This method is intended ONLY for Abilities API consumers.
2019 + * For legacy UI code paths (e.g., admin dashboard widgets, site count displays),
2020 + * use get_websites_count() instead. The two methods serve different purposes:
2021 + * - get_websites_count(): Simple total count for UI display (cached, no filters)
2022 + * - get_websites_count_for_current_user(): Filtered count for API pagination
2023 + *
2024 + * @since 5.3
2025 + *
2026 + * @param array $params Filter parameters:
2027 + * - status (string): 'connected', 'disconnected', 'suspended'
2028 + * - tags (array): Array of tag/group IDs to filter by
2029 + * - client_id (int): Client ID to filter by.
2030 + *
2031 + * @return int Count of sites matching the filters.
2032 + */
2033 + public function get_websites_count_for_current_user( $params = array() ) { //phpcs:ignore -- NOSONAR - complex.
2034 +
2035 + if ( ! is_array( $params ) ) {
2036 + $params = array();
2037 + }
2038 +
2039 + $status = isset( $params['status'] ) ? $params['status'] : '';
2040 + $tags = isset( $params['tags'] ) && is_array( $params['tags'] ) ? $params['tags'] : array();
2041 + $client_id = isset( $params['client_id'] ) ? intval( $params['client_id'] ) : 0;
2042 + $s = isset( $params['s'] ) ? $params['s'] : '';
2043 +
2044 + // Validate status value (defense-in-depth for direct callers outside Abilities API).
2045 + $valid_statuses = array( 'connected', 'disconnected', 'suspended', '' );
2046 + if ( ! in_array( $status, $valid_statuses, true ) ) {
2047 + $status = '';
2048 + }
2049 +
2050 + $where = '';
2051 + $sql_params = array();
2052 +
2053 + // Multi-user support: filter by current user.
2054 + if ( MainWP_System::instance()->is_multi_user() ) {
2055 + global $current_user;
2056 + $where .= ' AND wp.userid = %d ';
2057 + $sql_params[] = (int) $current_user->ID;
2058 + }
2059 +
2060 + // Access control for sites.
2061 + $where .= $this->get_sql_where_allow_access_sites( 'wp', 'no' );
2062 +
2063 + // Status filtering: connected, disconnected, suspended.
2064 + if ( ! empty( $status ) ) {
2065 + switch ( $status ) {
2066 + case 'connected':
2067 + $where .= ' AND wp_sync.sync_errors = "" AND wp.suspended = 0 ';
2068 + break;
2069 + case 'disconnected':
2070 + $where .= ' AND wp_sync.sync_errors <> "" ';
2071 + break;
2072 + case 'suspended':
2073 + $where .= ' AND wp.suspended = 1 ';
2074 + break;
2075 + default:
2076 + // No additional filtering.
2077 + break;
2078 + }
2079 + }
2080 +
2081 + // Client ID filtering.
2082 + if ( ! empty( $client_id ) ) {
2083 + $where .= ' AND wp.client_id = %d ';
2084 + $sql_params[] = (int) $client_id;
2085 + }
2086 +
2087 + // Search filtering.
2088 + if ( ! empty( $s ) ) {
2089 + $s = trim( $s );
2090 + $like_pattern = '%' . $this->wpdb->esc_like( $s ) . '%';
2091 + $where .= $this->wpdb->prepare(
2092 + ' AND ( wp.id LIKE %s OR wp.name LIKE %s OR wp.url LIKE %s ) ',
2093 + $like_pattern,
2094 + $like_pattern,
2095 + $like_pattern
2096 + );
2097 + }
2098 +
2099 + // Tags (groups) filtering.
2100 + $join_group = '';
2101 + if ( ! empty( $tags ) ) {
2102 + $tags = array_map( 'intval', $tags );
2103 + $tags = array_filter(
2104 + $tags,
2105 + function ( $id ) {
2106 + return $id > 0;
2107 + }
2108 + );
2109 + if ( ! empty( $tags ) ) {
2110 + $join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
2111 + $placeholders = implode( ', ', array_fill( 0, count( $tags ), '%d' ) );
2112 + $where .= " AND wpgroup.groupid IN ( $placeholders ) ";
2113 + $sql_params = array_merge( $sql_params, $tags );
2114 + }
2115 + }
2116 +
2117 + $qry = 'SELECT COUNT(DISTINCT wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp ' .
2118 + 'JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid ' .
2119 + $join_group .
2120 + 'WHERE 1 ' . $where;
2121 +
2122 + // Only call prepare() when we have placeholders.
2123 + $result = $sql_params
2124 + ? $this->wpdb->get_var( $this->wpdb->prepare( $qry, $sql_params ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- We have already prepared the query with the parameters.
2125 + : $this->wpdb->get_var( $qry ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- We have already prepared the query with the parameters.
2126 +
2127 + return (int) $result;
2128 + }
2129 +
2130 + /**
1546 2131 * Get the child sites the current user has searched for.
1547 2132 *
1548 2133 * @param array $params Query parameters.
1549 2134 *
@@ -1556,14 +2141,15 @@
1556 2141 if ( ! is_array( $params ) ) {
1557 2142 $params = array();
1558 2143 }
1559 2144
2145 + $view = isset( $params['view'] ) ? $params['view'] : 'default'; // must be default.
1560 2146 $selectgroups = isset( $params['selectgroups'] ) && $params['selectgroups'] ? true : false;
1561 - $search_site = isset( $params['search'] ) ? $this->escape( trim( $params['search'] ) ) : null;
2147 + $search_site = isset( $params['search'] ) ? trim( $params['search'] ) : null;
1562 2148 $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
1563 2149 $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
1564 2150 $rowcount = isset( $params['rowcount'] ) ? intval( $params['rowcount'] ) : false;
1565 - $extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null;
2151 + $extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null; // without AND prefix.
1566 2152 $for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
1567 2153 $extra_view = isset( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
1568 2154 $is_staging = isset( $params['is_staging'] ) && 'yes' === $params['is_staging'] ? 'yes' : 'no';
1569 2155 $is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
@@ -1568,11 +2154,16 @@
1568 2154 $is_staging = isset( $params['is_staging'] ) && 'yes' === $params['is_staging'] ? 'yes' : 'no';
1569 2155 $is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
1570 2156 $group_ids = isset( $params['group_id'] ) && ! empty( $params['group_id'] ) ? $params['group_id'] : array();
1571 2157 $client_ids = isset( $params['client_id'] ) && ! empty( $params['client_id'] ) ? $params['client_id'] : array();
2158 + $group_logic = ( isset( $params['group_logic'] ) && 'and' === $params['group_logic'] ) ? 'and' : 'or';
1572 2159 $is_not = isset( $params['isnot'] ) && ! empty( $params['isnot'] ) ? true : false;
1573 2160 $selected_sites = isset( $params['selected_sites'] ) ? $params['selected_sites'] : array();
1574 2161
2162 + // This parameter is used to enable caching in certain cases.
2163 + $_included_cache_ids = isset( $params['_included_cache_ids'] ) ? wp_parse_id_list( $params['_included_cache_ids'] ) : array();
2164 + $where_cache_ids = '';
2165 +
1575 2166 if ( ! is_array( $group_ids ) ) {
1576 2167 $group_ids = array();
1577 2168 }
1578 2169
@@ -1630,19 +2221,46 @@
1630 2221
1631 2222 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
1632 2223 }
1633 2224
1634 - if ( ! empty( $selected_sites ) ) {
1635 - $where .= ' AND wp.id IN (' . implode( ',', $selected_sites ) . ') ';
2225 + if ( ! empty( $_included_cache_ids ) ) {
2226 + $where_cache_ids .= ' AND wp.id IN (' . implode( ',', $_included_cache_ids ) . ') ';
2227 + } else {
2228 + if ( ! empty( $selected_sites ) ) {
2229 + $where .= ' AND wp.id IN (' . implode( ',', $selected_sites ) . ') ';
2230 + }
2231 +
2232 + if ( ! empty( $extraWhere ) ) {
2233 + $where .= ' AND ' . $extraWhere;
2234 + }
1636 2235 }
1637 2236
1638 - // for searching.
2237 + // Search filtering.
1639 2238 if ( null !== $search_site && '' !== $search_site ) {
1640 - $where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
2239 + // Escape LIKE wildcards first (%, _, \).
2240 + // Note: Cannot use WordPress escaping functions (esc_like, esc_sql, prepare) because
2241 + // WordPress 6.2+ creates placeholder tokens that are only substituted during query execution.
2242 + // Since this method returns a SQL string for later execution, tokens would never be substituted.
2243 + // We use direct mysqli_real_escape_string() which is the same underlying function WordPress uses.
2244 + $search_escaped = str_replace( array( '\\', '%', '_' ), array( '\\\\', '\\%', '\\_' ), $search_site );
2245 + $like_pattern = '%' . $search_escaped . '%';
2246 +
2247 + // SQL escape using direct mysqli function to bypass WordPress placeholder tokens.
2248 + if ( $this->wpdb->dbh instanceof \mysqli ) {
2249 + $like_pattern_escaped = mysqli_real_escape_string( $this->wpdb->dbh, $like_pattern );
2250 + } else {
2251 + // Fallback: reject if no mysqli connection available.
2252 + $like_pattern_escaped = '';
2253 + }
2254 +
2255 + if ( '' !== $like_pattern_escaped ) {
2256 + $where .= " AND (wp.name LIKE '" . $like_pattern_escaped . "' OR wp.url LIKE '" . $like_pattern_escaped . "') ";
2257 + }
1641 2258 }
1642 2259
1643 - if ( null !== $extraWhere ) {
1644 - $where .= ' AND ' . $extraWhere;
2260 + $staging_enabled = is_plugin_active( 'mainwp-staging-extension/mainwp-staging-extension.php' ) || is_plugin_active( 'mainwp-timecapsule-extension/mainwp-timecapsule-extension.php' );
2261 + if ( ! $staging_enabled ) {
2262 + $is_staging = 'no';
1645 2263 }
1646 2264
1647 2265 if ( ! $for_manager ) {
1648 2266 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
@@ -1657,10 +2275,11 @@
1657 2275 if ( ! empty( $orderBy ) ) {
1658 2276 $orderBy = ' ORDER BY ' . $orderBy;
1659 2277 }
1660 2278
1661 - $join_group = '';
1662 - $where_group = '';
2279 + $join_group = '';
2280 + $where_group = '';
2281 + $having_group = '';
1663 2282
1664 2283 if ( in_array( 'nogroups', $group_ids ) ) {
1665 2284 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1666 2285 $group_ids = array_filter(
@@ -1669,14 +2288,21 @@
1669 2288 return 'nogroups' !== $e;
1670 2289 }
1671 2290 );
1672 2291 if ( ! empty( $group_ids ) ) {
1673 - $groups = implode( ',', $group_ids );
2292 + $groups = implode( ',', $group_ids );
2293 + $groups_count = count( $group_ids );
1674 2294 if ( $is_not ) {
1675 - $where_group = ' AND wpgroup.groupid IS NOT NULL AND wpgroup.groupid NOT IN (' . $groups . ') ';
1676 - // to fix.
1677 - $sub_select_is_not = ' SELECT wp.id FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . $groups . ') ';
1678 - $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_is_not . ' ) ';
2295 + $where_group = ' AND wpgroup.groupid IS NOT NULL ';
2296 + if ( 'and' === $group_logic ) {
2297 + $sub_select_match_all = ' SELECT wpand.id FROM ' . $this->table_name( 'wp' ) . ' wpand JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup_and ON wpand.id = wpgroup_and.wpid WHERE wpgroup_and.groupid IN (' . $groups . ') GROUP BY wpand.id HAVING COUNT(DISTINCT wpgroup_and.groupid) = ' . $groups_count . ' ';
2298 + $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_match_all . ' ) ';
2299 + } else {
2300 + $sub_select_is_not = ' SELECT wp_or.id FROM ' . $this->table_name( 'wp' ) . ' wp_or JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup_or ON wp_or.id = wpgroup_or.wpid WHERE wpgroup_or.groupid IN (' . $groups . ') ';
2301 + $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_is_not . ' ) ';
2302 + }
2303 + } elseif ( 'and' === $group_logic ) {
2304 + $where_group = ' AND 1 = 0 ';
1679 2305 } else {
1680 2306 $where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
1681 2307 }
1682 2308 } elseif ( $is_not ) {
@@ -1684,18 +2310,26 @@
1684 2310 } else {
1685 2311 $where_group = ' AND wpgroup.groupid IS NULL ';
1686 2312 }
1687 2313 } elseif ( $group_ids ) {
1688 - $groups = implode( ',', $group_ids );
2314 + $groups = implode( ',', $group_ids );
2315 + $groups_count = count( $group_ids );
1689 2316 if ( $is_not ) {
1690 2317 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1691 - $where_group = ' AND ( wpgroup.groupid NOT IN (' . $groups . ') OR wpgroup.groupid IS NULL ) ';
1692 - // to fix.
1693 - $sub_select_is_not = ' SELECT wp.id FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . $groups . ') ';
1694 - $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_is_not . ' ) ';
2318 + $where_group = '';
2319 + if ( 'and' === $group_logic ) {
2320 + $sub_select_match_all = ' SELECT wpand.id FROM ' . $this->table_name( 'wp' ) . ' wpand JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup_and ON wpand.id = wpgroup_and.wpid WHERE wpgroup_and.groupid IN (' . $groups . ') GROUP BY wpand.id HAVING COUNT(DISTINCT wpgroup_and.groupid) = ' . $groups_count . ' ';
2321 + $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_match_all . ' ) ';
2322 + } else {
2323 + $sub_select_is_not = ' SELECT wp_or.id FROM ' . $this->table_name( 'wp' ) . ' wp_or JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup_or ON wp_or.id = wpgroup_or.wpid WHERE wpgroup_or.groupid IN (' . $groups . ') ';
2324 + $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_is_not . ' ) ';
2325 + }
1695 2326 } else {
1696 2327 $join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1697 2328 $where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
2329 + if ( 'and' === $group_logic ) {
2330 + $having_group = 'COUNT(DISTINCT wpgroup.groupid) = ' . $groups_count;
2331 + }
1698 2332 }
1699 2333 }
1700 2334
1701 2335 $select_groups_belong = '';
@@ -1705,9 +2339,16 @@
1705 2339 }
1706 2340
1707 2341 $join_client = '';
1708 2342 $where_client = '';
1709 -
2343 + $group_by = ' GROUP BY wp.id, wp_sync.sync_id';
2344 + if ( ! empty( $having_group ) ) {
2345 + $group_by .= ' HAVING ' . $having_group;
2346 + }
2347 + $group_by = ' GROUP BY wp.id, wp_sync.sync_id';
2348 + if ( ! empty( $having_group ) ) {
2349 + $group_by .= ' HAVING ' . $having_group;
2350 + }
1710 2351 if ( in_array( 'noclients', $client_ids ) ) {
1711 2352 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1712 2353 $client_ids = array_filter(
1713 2354 $client_ids,
@@ -1740,8 +2381,89 @@
1740 2381
1741 2382 if ( '' === $join_client ) {
1742 2383 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1743 2384 }
2385 +
2386 + $light_fields = array(
2387 + 'wp.id',
2388 + 'wp.url',
2389 + 'wp.name',
2390 + 'wp.client_id',
2391 + 'wp.verify_certificate',
2392 + 'wp.http_user',
2393 + 'wp.http_pass',
2394 + 'wp.ssl_version',
2395 + 'wp.adminname',
2396 + 'wp.privkey',
2397 + 'wp.pubkey',
2398 + 'wp.wpe',
2399 + 'wp.is_staging',
2400 + 'wp.force_use_ipv4',
2401 + 'wp.siteurl',
2402 + 'wp.suspended',
2403 + 'wp.mainwpdir',
2404 + 'wp.is_ignoreCoreUpdates',
2405 + 'wp.is_ignorePluginUpdates',
2406 + 'wp.is_ignoreThemeUpdates',
2407 + 'wp.backup_before_upgrade',
2408 + 'wp.userid',
2409 + 'wp_sync.sync_errors',
2410 + );
2411 +
2412 + $legacy_status_fields = array(
2413 + 'wp.offline_check_result', // 1 - online, -1 offline.
2414 + 'wp.http_response_code',
2415 + 'wp.offline_checks_last',
2416 + );
2417 +
2418 + $light_fields = array_merge( $light_fields, $legacy_status_fields );
2419 +
2420 + $join_monitors = '';
2421 +
2422 + $select_fields = array(
2423 + 'wp.*',
2424 + 'wp_sync.*',
2425 + );
2426 +
2427 + if ( 'light_view' === $view ) {
2428 + $select_fields = $light_fields;
2429 + } elseif ( 'monitor_view' === $view ) {
2430 + $select_fields = $light_fields;
2431 + $select_fields[] = 'mo.*';
2432 + $join_monitors = 'LEFT JOIN (
2433 + SELECT m1.*
2434 + FROM ' . $this->table_name( 'monitors' ) . ' m1
2435 + JOIN (
2436 + SELECT wpid, MAX(monitor_id) AS max_id
2437 + FROM ' . $this->table_name( 'monitors' ) . '
2438 + WHERE issub = 0
2439 + GROUP BY wpid
2440 + ) mm ON mm.wpid = m1.wpid AND m1.monitor_id = mm.max_id
2441 + ) mo ON mo.wpid = wp.id ';
2442 + } elseif ( 'manage_site' === $view ) {
2443 + $select_fields[] = 'mo.monitor_id';
2444 + $join_monitors = ' LEFT JOIN (
2445 + SELECT wpid, MAX(monitor_id) AS monitor_id
2446 + FROM ' . $this->table_name( 'monitors' ) . '
2447 + WHERE issub = 0
2448 + GROUP BY wpid
2449 + ) AS mo
2450 + ON mo.wpid = wp.id ';
2451 +
2452 + }
2453 +
2454 + $select = implode( ',', $select_fields );
2455 +
2456 + $view_selects = '';
2457 + $view_joins = '';
2458 +
2459 + $opts_view = $this->get_wp_options_join( $extra_view, $view );
2460 +
2461 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
2462 + $view_selects = ',' . $opts_view['selects'];
2463 + $view_joins = $opts_view['joins'];
2464 + }
2465 +
1744 2466 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1745 2467 if ( $selectgroups ) {
1746 2468
1747 2469 if ( empty( $join_group ) ) {
@@ -1747,38 +2469,45 @@
1747 2469 if ( empty( $join_group ) ) {
1748 2470 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1749 2471 }
1750 2472
1751 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, 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, wpclient.name as client_name ' .
2473 + $qry = 'SELECT ' . $select . $view_selects . ', GROUP_CONCAT(DISTINCT gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(DISTINCT gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids, GROUP_CONCAT(DISTINCT gr.color ORDER BY gr.name SEPARATOR ",") as wpgroups_colors, wpclient.name as client_name ' .
1752 2474 $select_groups_belong . ' FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1753 2475 $join_client . ' ' .
1754 - $join_group . '
2476 + $join_group .
2477 + $join_monitors . '
1755 2478 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgroup.groupid = gr.id
1756 2479
1757 2480 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1758 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1759 - WHERE 1 ' . $where . $where_group . $where_client . '
1760 - GROUP BY wp.id, wp_sync.sync_id ' .
2481 + ' . $view_joins . '
2482 + WHERE 1 ' . $where_cache_ids . $where . $where_group . $where_client . $group_by .
1761 2483 $orderBy;
1762 2484 } else {
1763 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, wpclient.name as client_name ' .
2485 + $qry = 'SELECT ' . $select . $view_selects . ', wpclient.name as client_name ' .
1764 2486 $select_groups_belong . ' FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1765 2487 $join_group . ' ' .
1766 - $join_client . '
2488 + $join_client .
2489 + $join_monitors . '
1767 2490 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1768 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1769 - WHERE 1 ' . $where . $where_group . $where_client . '
1770 - GROUP BY wp.id, wp_sync.sync_id ' .
2491 + ' . $view_joins . '
2492 + WHERE 1 ' . $where_cache_ids . $where . $where_group . $where_client . $group_by .
1771 2493 $orderBy;
1772 2494 }
1773 2495
1774 2496 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
1775 - $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
2497 + // When cache IDs are provided, they already represent the page-specific subset,
2498 + // so the effective offset within that subset is always 0.
2499 + $effective_offset = ! empty( $_included_cache_ids ) ? 0 : $offset;
2500 + $qry .= ' LIMIT ' . $effective_offset . ', ' . $rowcount;
1776 2501 } elseif ( false !== $rowcount ) {
1777 2502 $qry .= ' LIMIT ' . $rowcount;
1778 2503 }
1779 - //phpcs:ignore
1780 - // error_log( $qry ); // NOSONAR - for dev.
2504 +
2505 + if ( ! empty( $_included_cache_ids ) ) {
2506 + MainWP_Logger::instance()->log_events( 'cache-metrics', sprintf( '[sql search websites=%s]', $qry ) );
2507 + }
2508 + MainWP_Logger::instance()->log_events( 'db-queries', sprintf( '[sql search websites=%s]', $qry ) );
2509 +
1781 2510 return $qry;
1782 2511 }
1783 2512
1784 2513 /**
@@ -1921,19 +2650,98 @@
1921 2650 return ' AND 0 ';
1922 2651 }
1923 2652 }
1924 2653
2654 +
1925 2655 /**
2656 + * Get child site by id and params.
2657 + *
2658 + * @param int $id Child site ID.
2659 + * @param array $params params.
2660 + * @param string $obj OBJECT|ARRAY_A.
2661 + *
2662 + * @return object|null Database query results or null on failure.
2663 + */
2664 + public function get_website_by_id_params( $id, $params = array(), $obj = OBJECT ) {
2665 + return $this->get_row_result( $this->get_sql_website_by_params( $id, $params ), $obj );
2666 + }
2667 +
2668 + /**
2669 + * Get sql child site by id and params.
2670 + *
2671 + * @param int $id Child site ID.
2672 + * @param array $params params.
2673 + *
2674 + * @return object|null Database query results or null on failure.
2675 + */
2676 + public function get_sql_website_by_params( $id, $params = array() ) {
2677 +
2678 + if ( ! is_array( $params ) ) {
2679 + $params = array();
2680 + }
2681 +
2682 + $select_groups = ! empty( $params['select_groups'] ) ? true : false;
2683 +
2684 + $view = ! empty( $params['view'] ) ? $params['view'] : 'simple_view';
2685 + $view_fields = isset( $params['view_fields'] ) ? $params['view_fields'] : array();
2686 +
2687 + if ( is_string( $view_fields ) ) {
2688 + $view_fields = (array) $view_fields;
2689 + } elseif ( ! is_array( $view_fields ) ) {
2690 + $view_fields = array();
2691 + }
2692 +
2693 + if ( MainWP_Utility::ctype_digit( $id ) ) {
2694 +
2695 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
2696 +
2697 + $view_selects = '';
2698 + $view_joins = '';
2699 +
2700 + if ( $use_comp_subquery ) {
2701 + $view_selects = ',wp_optionview.* ';
2702 + $view_joins = ' JOIN ' . $this->get_option_view_by( $view, $view_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid ';
2703 + } else {
2704 + $opts_view = $this->get_option_view_by_join( $view, $view_fields );
2705 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
2706 + $view_selects = ',' . $opts_view['selects'];
2707 + $view_joins = $opts_view['joins'];
2708 + }
2709 + }
2710 +
2711 + $where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
2712 + if ( $select_groups ) {
2713 + return 'SELECT wp.*,wp_sync.*' . $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
2714 + FROM ' . $this->table_name( 'wp' ) . ' wp
2715 + LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
2716 + LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
2717 + JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2718 + ' . $view_joins . '
2719 + WHERE wp.id = ' . $id . $where . '
2720 + GROUP BY wp.id, wp_sync.sync_id';
2721 + }
2722 +
2723 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
2724 + FROM ' . $this->table_name( 'wp' ) . ' wp
2725 + JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2726 + ' . $view_joins . '
2727 + WHERE id = ' . $id . $where;
2728 + }
2729 + return null;
2730 + }
2731 +
2732 + /**
1926 2733 * Get child site by id.
1927 2734 *
1928 2735 * @param int $id Child site ID.
1929 2736 * @param array $selectGroups Select groups.
1930 2737 * @param array $extra_view Get extra option fields.
2738 + * @param int $obj OBJECT|ARRAY_A.
1931 2739 *
1932 2740 * @return object|null Database query results or null on failure.
1933 2741 */
1934 - public function get_website_by_id( $id, $selectGroups = false, $extra_view = array() ) {
1935 - return $this->get_row_result( $this->get_sql_website_by_id( $id, $selectGroups, $extra_view ) );
2742 + public function get_website_by_id( $id, $selectGroups = false, $extra_view = array(), $obj = OBJECT ) {
2743 + return $this->get_row_result( $this->get_sql_website_by_id( $id, $selectGroups, $extra_view ), $obj );
1936 2744 }
1937 2745
1938 2746 /**
1939 2747 * Get child site by id via SQL.
@@ -1952,24 +2760,35 @@
1952 2760 $extra_view = array( 'favi_icon', 'site_info' );
1953 2761 }
1954 2762
1955 2763 if ( MainWP_Utility::ctype_digit( $id ) ) {
2764 +
2765 + $view_selects = '';
2766 + $view_joins = '';
2767 +
2768 + $opts_view = $this->get_wp_options_join( $extra_view );
2769 +
2770 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
2771 + $view_selects = ',' . $opts_view['selects'];
2772 + $view_joins = $opts_view['joins'];
2773 + }
2774 +
1956 2775 $where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
1957 2776 if ( $selectGroups ) {
1958 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*, 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
2777 + return 'SELECT wp.*,wp_sync.*' . $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
1959 2778 FROM ' . $this->table_name( 'wp' ) . ' wp
1960 2779 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1961 2780 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1962 2781 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1963 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2782 + ' . $view_joins . '
1964 2783 WHERE wp.id = ' . $id . $where . '
1965 2784 GROUP BY wp.id, wp_sync.sync_id';
1966 2785 }
1967 2786
1968 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
2787 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
1969 2788 FROM ' . $this->table_name( 'wp' ) . ' wp
1970 2789 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1971 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2790 + ' . $view_joins . '
1972 2791 WHERE id = ' . $id . $where;
1973 2792 }
1974 2793
1975 2794 return null;
@@ -2007,11 +2826,22 @@
2007 2826 return ( is_numeric( $e ) && 0 < $e ) ? true : false;
2008 2827 }
2009 2828 );
2010 2829
2011 - $where = $this->get_sql_where_allow_access_sites();
2830 + $where = $this->get_sql_where_allow_access_sites();
2831 + $table_name = esc_sql( $this->table_name( 'wp' ) );
2832 + $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
2833 + $sql = "SELECT * FROM {$table_name} WHERE id IN ({$placeholders})";
2834 + $params = $ids;
2012 2835
2013 - return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE id IN (' . implode( ',', $ids ) . ')' . ( null !== $userId ? ' AND userid = ' . intval( $userId ) : '' ) . $where, OBJECT );
2836 + if ( null !== $userId ) {
2837 + $sql .= ' AND userid = %d';
2838 + $params[] = intval( $userId );
2839 + }
2840 +
2841 + $sql .= ' ' . $where;
2842 +
2843 + return $this->wpdb->get_results( $this->wpdb->prepare( $sql, ...$params ), OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where fragment is from validated get_sql_where_access_sites() with numeric IDs
2014 2844 }
2015 2845
2016 2846 /**
2017 2847 * Get child sites by groups IDs.
@@ -2064,17 +2894,107 @@
2064 2894
2065 2895 /**
2066 2896 * Get child sites by group ID.
2067 2897 *
2898 + * @param int $id Group ID.
2899 + * @param bool $selectgroups Selected groups. Default: false.
2900 + * @param string $orderBy Order list by. Default: URL.
2901 + * @param bool $offset Query offset. Default: false.
2902 + * @param bool $rowcount Row count. Default: falese.
2903 + * @param null $where SQL WHERE value.
2904 + * @param null $search_site Site search field value. Default: null.
2905 + * @param array $others Others params.
2906 + *
2907 + * @return object|null Database query result or null on failure.
2908 + */
2909 + public function get_websites_by_group_id( //phpcs:ignore -- NOSONAR -ok.
2910 + $id,
2911 + $selectgroups = false,
2912 + $orderBy = 'wp.url',
2913 + $offset = false,
2914 + $rowcount = false,
2915 + $where = null,
2916 + $search_site = null,
2917 + $others = array()
2918 + ) {
2919 + return $this->get_results_result(
2920 + $this->get_sql_websites_by_group_id(
2921 + $id,
2922 + $selectgroups,
2923 + $orderBy,
2924 + $offset,
2925 + $rowcount,
2926 + $where,
2927 + $search_site,
2928 + $others
2929 + )
2930 + );
2931 + }
2932 +
2933 + /**
2934 + * Get count of child sites by group ID.
2935 + *
2936 + * Uses an efficient COUNT query instead of fetching all rows.
2937 + *
2068 2938 * @param int $id Group ID.
2069 2939 *
2070 - * @return object|null Database query result or null on failure.
2940 + * @return int Number of sites in the group.
2941 + *
2942 + * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
2071 2943 */
2072 - public function get_websites_by_group_id( $id ) {
2073 - return $this->get_results_result( $this->get_sql_websites_by_group_id( $id ) );
2944 + public function get_websites_count_by_group_id( $id ) {
2945 + if ( ! MainWP_Utility::ctype_digit( $id ) ) {
2946 + return 0;
2947 + }
2948 +
2949 + // Determine if this is the staging group.
2950 + $is_staging = 'no';
2951 + $staging_group = get_option( 'mainwp_stagingsites_group_id' );
2952 + if ( $staging_group && (int) $id === (int) $staging_group ) {
2953 + $is_staging = 'yes';
2954 + }
2955 +
2956 + $where_allowed = $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
2957 +
2958 + // Use prepare() for the groupid parameter.
2959 + $qry = $this->wpdb->prepare(
2960 + 'SELECT COUNT(DISTINCT wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp
2961 + JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2962 + WHERE wpgroup.groupid = %d',
2963 + $id
2964 + ) . $where_allowed;
2965 +
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.
2074 2967 }
2075 2968
2969 +
2076 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 + /**
2077 2997 * Get child sites by group id via SQL.
2078 2998 *
2079 2999 * @param int $id Group ID.
2080 3000 * @param bool $selectgroups Selected groups. Default: false.
@@ -2109,33 +3029,58 @@
2109 3029 }
2110 3030
2111 3031 $where_search = '';
2112 3032 if ( ! empty( $search_site ) ) {
2113 - $search_site = trim( $search_site );
2114 - $where_search .= ' AND (wp.name LIKE "%' . $this->escape( $search_site ) . '%" OR wp.url LIKE "%' . $this->escape( $search_site ) . '%") ';
3033 + $search_site = trim( $search_site );
3034 + // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
3035 + $like_pattern = '%' . $this->wpdb->esc_like( $search_site ) . '%';
3036 + $where_search .= $this->wpdb->prepare(
3037 + ' AND (wp.name LIKE %s OR wp.url LIKE %s) ',
3038 + $like_pattern,
3039 + $like_pattern
3040 + );
2115 3041 }
2116 3042
2117 3043 $extra_view = is_array( $others ) && isset( $others['extra_view'] ) && is_array( $others['extra_view'] ) && ! empty( $others['extra_view'] ) ? $others['extra_view'] : array( 'site_info' );
2118 3044
3045 + $view_query = null;
3046 + if ( is_array( $others ) && ! empty( $others['view_query'] ) ) {
3047 + $view_query = $others['view_query'];
3048 + }
3049 +
3050 + if ( empty( $view_query ) ) {
3051 + $view_query = $selectgroups ? 'default' : 'group'; // To compatible.
3052 + }
3053 +
3054 + $view_selects = '';
3055 + $view_joins = '';
3056 +
3057 + $opts_view = $this->get_wp_options_join( $extra_view, $view_query );
3058 +
3059 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3060 + $view_selects = ',' . $opts_view['selects'];
3061 + $view_joins = $opts_view['joins'];
3062 + }
3063 +
2119 3064 if ( MainWP_Utility::ctype_digit( $id ) ) {
2120 3065 $where_allowed = $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
2121 3066 if ( $selectgroups ) {
2122 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, 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
3067 + $qry = 'SELECT wp.*,wp_sync.*' . $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
2123 3068 FROM ' . $this->table_name( 'wp' ) . ' wp
2124 3069 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2125 3070 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
2126 3071 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
2127 3072 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2128 - JOIN ' . $this->get_option_view( $extra_view, true ) . ' wp_optionview ON wp.id = wp_optionview.wpid
3073 + ' . $view_joins . '
2129 3074 WHERE wpgroup.groupid = ' . $id . ' ' .
2130 3075 ( empty( $where ) ? '' : ' AND ' . $where ) . $where_allowed . $where_search . '
2131 3076 GROUP BY wp.id, wp_sync.sync_id
2132 3077 ORDER BY ' . $orderBy;
2133 3078 } else {
2134 - $qry = 'SELECT wp.*,wp_optionview.*, wp_sync.* FROM ' . $this->table_name( 'wp' ) . ' wp
3079 + $qry = 'SELECT wp.*' . $view_selects . ', wp_sync.* FROM ' . $this->table_name( 'wp' ) . ' wp
2135 3080 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2136 3081 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2137 - JOIN ' . $this->get_option_view( $extra_view, false ) . ' wp_optionview ON wp.id = wp_optionview.wpid
3082 + ' . $view_joins . '
2138 3083 WHERE wpgroup.groupid = ' . $id . ' ' . $where_allowed . $where_search .
2139 3084 ( empty( $where ) ? '' : ' AND ' . $where ) . ' ORDER BY ' . $orderBy;
2140 3085 }
2141 3086 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
@@ -2184,13 +3129,23 @@
2184 3129
2185 3130 $userid = $current_user->ID;
2186 3131 }
2187 3132
2188 - $sql = 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
3133 + $view_selects = '';
3134 + $view_joins = '';
3135 +
3136 + $opts_view = $this->get_wp_options_join();
3137 +
3138 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3139 + $view_selects = ',' . $opts_view['selects'];
3140 + $view_joins = $opts_view['joins'];
3141 + }
3142 +
3143 + $sql = 'SELECT wp.*,wp_sync.*' . $view_selects . ' FROM ' . $this->table_name( 'wp' ) . ' wp
2189 3144 INNER JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2190 3145 JOIN ' . $this->table_name( 'group' ) . ' g ON wpgroup.groupid = g.id
2191 3146 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2192 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
3147 + ' . $view_joins . '
2193 3148 WHERE g.name="' . $this->escape( $groupname ) . '"';
2194 3149 if ( null !== $userid ) {
2195 3150 $sql .= ' AND g.userid = "' . intval( $userid ) . '"';
2196 3151 }
@@ -2205,9 +3160,10 @@
2205 3160 *
2206 3161 * @return string|null Child site IP address or null on failure.
2207 3162 */
2208 3163 public function get_wp_ip( $wpid ) {
2209 - return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT ip FROM ' . $this->table_name( 'request_log' ) . ' WHERE wpid = %d', $wpid ) );
3164 + $table_name = esc_sql( $this->table_name( 'request_log' ) );
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.
2210 3166 }
2211 3167
2212 3168 /**
2213 3169 * Add website to the MainWP Dashboard.
@@ -2239,9 +3195,9 @@
2239 3195 }
2240 3196
2241 3197 $groupids = isset( $params['groupids'] ) ? $params['groupids'] : array();
2242 3198 $groupnames = isset( $params['groupnames'] ) ? $params['groupnames'] : array();
2243 - $verifyCertificate = isset( $params['verifyCertificate'] ) ? (int) $params['verifyCertificate'] : 1;
3199 + $verifyCertificate = isset( $params['verifyCertificate'] ) ? (int) $params['verifyCertificate'] : 2;
2244 3200 $uniqueId = isset( $params['uniqueId'] ) ? $params['uniqueId'] : '';
2245 3201 $http_user = isset( $params['http_user'] ) ? $params['http_user'] : null;
2246 3202 $http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
2247 3203 $sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
@@ -2246,13 +3202,33 @@
2246 3202 $http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
2247 3203 $sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
2248 3204 $wpe = isset( $params['wpe'] ) ? $params['wpe'] : 0;
2249 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;
2250 3210
3211 + // MWP-1548: encrypt http_user / http_pass at rest. Empty / null
3212 + // values pass through unchanged (encrypt_credential is a no-op
3213 + // for those). A non-empty value that fails to encrypt produces
3214 + // false here, which we treat as a hard failure -- refuse to
3215 + // persist plaintext credentials when the encryption layer is
3216 + // unhealthy (missing keyfile, un-writable uploads dir).
3217 + $encrypted_http_user = MainWP_Credential_Storage::encrypt_credential( $http_user, 'http_user' );
3218 + $encrypted_http_pass = MainWP_Credential_Storage::encrypt_credential( $http_pass, 'http_pass' );
3219 + if ( false === $encrypted_http_user || false === $encrypted_http_pass ) {
3220 + return false;
3221 + }
3222 +
2251 3223 if ( MainWP_Utility::ctype_digit( $userid ) ) {
2252 3224 if ( '/' !== substr( $url, - 1 ) ) {
2253 3225 $url .= '/';
2254 3226 }
3227 +
3228 + $en_pk_data = MainWP_Encrypt_Data_Lib::instance()->encrypt_privkey( base64_decode( $privkey ) ); // phpcs:ignore -- NOSONAR - base64_encode trust.
3229 + $en_privkey = isset( $en_pk_data['en_data'] ) ? $en_pk_data['en_data'] : '';
3230 +
2255 3231 $values = array(
2256 3232 'userid' => $userid,
2257 3233 'adminname' => $this->escape( $admin ),
2258 3234 'name' => $this->escape( wp_strip_all_tags( $name ) ),
@@ -2257,9 +3233,9 @@
2257 3233 'adminname' => $this->escape( $admin ),
2258 3234 'name' => $this->escape( wp_strip_all_tags( $name ) ),
2259 3235 'url' => $this->escape( $url ),
2260 3236 'pubkey' => $this->escape( $pubkey ),
2261 - 'privkey' => $this->escape( $privkey ),
3237 + 'privkey' => $this->escape( base64_encode( $en_privkey ) ), // phpcs:ignore -- NOSONAR - trust.
2262 3238 'siteurl' => '',
2263 3239 'ga_id' => '',
2264 3240 'gas_id' => 0,
2265 3241 'offline_checks_last' => 0,
@@ -2270,8 +3246,9 @@
2270 3246 'plugin_upgrades' => '',
2271 3247 'theme_upgrades' => '',
2272 3248 'translation_upgrades' => '',
2273 3249 'securityIssues' => '',
3250 + 'premium_upgrades' => '',
2274 3251 'themes' => '',
2275 3252 'ignored_themes' => '',
2276 3253 'plugins' => '',
2277 3254 'ignored_plugins' => '',
@@ -2283,14 +3260,18 @@
2283 3260 'verify_certificate' => intval( $verifyCertificate ),
2284 3261 'ssl_version' => $sslVersion,
2285 3262 'uniqueId' => $uniqueId,
2286 3263 'mainwpdir' => 0,
2287 - 'http_user' => $http_user,
2288 - 'http_pass' => $http_pass,
3264 + 'http_user' => $encrypted_http_user,
3265 + 'http_pass' => $encrypted_http_pass,
2289 3266 'wpe' => $wpe,
2290 3267 'is_staging' => $isStaging,
2291 3268 );
2292 3269
3270 + if ( null !== $force_use_ipv4 ) {
3271 + $values['force_use_ipv4'] = $force_use_ipv4;
3272 + }
3273 +
2293 3274 $syncValues = array(
2294 3275 'dtsSync' => 0,
2295 3276 'dtsSyncStart' => 0,
2296 3277 'dtsAutomaticSync' => 0,
@@ -2299,11 +3280,14 @@
2299 3280 'extauth' => '',
2300 3281 'sync_errors' => '',
2301 3282 );
2302 3283 if ( $this->wpdb->insert( $this->table_name( 'wp' ), $values ) ) {
2303 - $websiteid = $this->wpdb->insert_id;
3284 + $websiteid = $this->wpdb->insert_id;
3285 + MainWP_Logger::instance()->log_events( 'db-queries', sprintf( '[Insert site=%s]', $this->get_last_query() ) ); // after: $this->wpdb->insert_id.
3286 + MainWP_Encrypt_Data_Lib::instance()->encrypt_save_keys( $websiteid, $en_pk_data );
2304 3287 $syncValues['wpid'] = $websiteid;
2305 3288 $this->wpdb->insert( $this->table_name( 'wp_sync' ), $syncValues );
3289 + MainWP_Logger::instance()->log_events( 'db-queries', sprintf( '[Insert sync data=%s]', $this->get_last_query() ) );
2306 3290 $this->wpdb->insert(
2307 3291 $this->table_name( 'wp_settings_backup' ),
2308 3292 array(
2309 3293 'wpid' => $websiteid,
@@ -2332,9 +3316,9 @@
2332 3316 'groupid' => $groupid,
2333 3317 )
2334 3318 );
2335 3319 }
2336 -
3320 + MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
2337 3321 return $websiteid;
2338 3322 }
2339 3323 }
2340 3324
@@ -2351,14 +3335,15 @@
2351 3335 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
2352 3336 */
2353 3337 public function remove_website( $websiteid ) {
2354 3338 if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
2355 - $nr = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp' ) . ' WHERE id=%d', $websiteid ) );
2356 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
2357 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_sync' ) . ' WHERE wpid=%d', $websiteid ) );
2358 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid=%d', $websiteid ) );
2359 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_status' ) . ' WHERE wpid=%d', $websiteid ) );
2360 -
3339 + $nr = $this->wpdb->delete( $this->table_name( 'wp' ), array( 'id' => $websiteid ) );
3340 + $this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
3341 + $this->wpdb->delete( $this->table_name( 'wp_sync' ), array( 'wpid' => $websiteid ) );
3342 + $this->wpdb->delete( $this->table_name( 'wp_options' ), array( 'wpid' => $websiteid ) );
3343 + MainWP_Encrypt_Data_Lib::remove_key_file( $websiteid );
3344 + MainWP_DB_Uptime_Monitoring::instance()->delete_monitor( array( 'wpid' => $websiteid ) );
3345 + MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
2361 3346 return $nr;
2362 3347 }
2363 3348
2364 3349 return false;
@@ -2373,8 +3358,35 @@
2373 3358 * @return int|boolean The number of rows updated, or false on error.
2374 3359 */
2375 3360 public function update_website_values( $websiteid, $fields ) {
2376 3361 if ( ! empty( $fields ) ) {
3362 + // MWP-1548: encrypt http_user / http_pass at rest if either
3363 + // is present in $fields. Generic-fields updaters (clone
3364 + // flow in extensions-handler, callers that pass arbitrary
3365 + // column subsets) must go through the same fail-closed
3366 + // contract as add_website / update_website.
3367 + if ( array_key_exists( 'http_user', $fields ) ) {
3368 + $encrypted = MainWP_Credential_Storage::encrypt_credential( $fields['http_user'], 'http_user' );
3369 + if ( false === $encrypted ) {
3370 + return false;
3371 + }
3372 + $fields['http_user'] = $encrypted;
3373 + }
3374 + if ( array_key_exists( 'http_pass', $fields ) ) {
3375 + $encrypted = MainWP_Credential_Storage::encrypt_credential( $fields['http_pass'], 'http_pass' );
3376 + if ( false === $encrypted ) {
3377 + return false;
3378 + }
3379 + $fields['http_pass'] = $encrypted;
3380 + }
3381 + // Lock the data stream to prevent other processes from updating at the same time.
3382 + $table_name = esc_sql( $this->table_name( 'wp' ) );
3383 + $sql = $this->wpdb->prepare(
3384 + "SELECT * FROM {$table_name} WHERE id = %d FOR UPDATE", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3385 + $websiteid
3386 + );
3387 + $this->wpdb->get_row( $sql );
3388 +
2377 3389 return $this->wpdb->update( $this->table_name( 'wp' ), $fields, array( 'id' => $websiteid ) );
2378 3390 }
2379 3391
2380 3392 return false;
@@ -2415,13 +3427,11 @@
2415 3427 * @param string $uniqueId Unique security ID.
2416 3428 * @param string $http_user HTTP Basic Authentication username.
2417 3429 * @param string $http_pass HTTP Basic Authentication password.
2418 3430 * @param int $sslVersion SSL Version.
2419 - * @param int $disableChecking Wether or not disable sites status checking.
2420 - * @param int $checkInterval Status checking interval.
2421 3431 * @param bool $disableHealthChecking Disable Site health threshold.
2422 3432 * @param int $healthThreshold Site health threshold.
2423 - * @param int $wpe Is it WP Engine hosted site.
3433 + * @param string $backup_method Primary backup method.
2424 3434 *
2425 3435 * @return boolean ture on success or false on failure.
2426 3436 *
2427 3437 * @uses \MainWP\Dashboard\MainWP_System_Utility::can_edit_website()
@@ -2444,28 +3454,68 @@
2444 3454 $uniqueId = '',
2445 3455 $http_user = null,
2446 3456 $http_pass = null,
2447 3457 $sslVersion = 0,
2448 - $disableChecking = 1,
2449 - $checkInterval = 1440,
2450 3458 $disableHealthChecking = 1,
2451 - $healthThreshold = 80,
2452 - $wpe = 0
3459 + $healthThreshold = 0,
3460 + $backup_method = 'global'
2453 3461 ) {
2454 3462
3463 + $wpe = 0; // going to update when sync.
3464 +
2455 3465 if ( MainWP_Utility::ctype_digit( $websiteid ) && MainWP_Utility::ctype_digit( $userid ) ) {
2456 3466 $website = $this->get_website_by_id( $websiteid );
2457 3467 if ( MainWP_System_Utility::can_edit_website( $website ) ) {
3468 + // MWP-1548: encrypt http_user / http_pass at rest. Same
3469 + // fail-closed contract as add_website -- a non-empty
3470 + // value that fails to encrypt aborts the update so we
3471 + // never overwrite an existing encrypted row with
3472 + // plaintext when the encryption layer is unhealthy.
3473 + $encrypted_http_user = MainWP_Credential_Storage::encrypt_credential( $http_user, 'http_user' );
3474 + $encrypted_http_pass = MainWP_Credential_Storage::encrypt_credential( $http_pass, 'http_pass' );
3475 + if ( false === $encrypted_http_user || false === $encrypted_http_pass ) {
3476 + return false;
3477 + }
2458 3478 // update admin.
2459 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET url="' . $this->escape( $url ) . '", name="' . $this->escape( wp_strip_all_tags( $name ) ) . '", adminname="' . $this->escape( $siteadmin ) . '",pluginDir="' . $this->escape( $pluginDir ) . '", verify_certificate="' . intval( $verifyCertificate ) . '", ssl_version="' . intval( $sslVersion ) . '", wpe="' . intval( $wpe ) . '", uniqueId="' . $this->escape( $uniqueId ) . '", http_user="' . $this->escape( $http_user ) . '", http_pass="' . $this->escape( $http_pass ) . '", disable_status_check="' . $this->escape( $disableChecking ) . '", status_check_interval="' . $this->escape( $checkInterval ) . '", disable_health_check="' . $this->escape( $disableHealthChecking ) . '", health_threshold="' . $this->escape( $healthThreshold ) . '" WHERE id=%d', $websiteid ) );
2460 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp_settings_backup' ) . ' SET archiveFormat = "' . $this->escape( $archiveFormat ) . '" WHERE wpid=%d', $websiteid ) );
3479 + $this->wpdb->update(
3480 + $this->table_name( 'wp' ),
3481 + array(
3482 + 'url' => $url,
3483 + 'name' => wp_strip_all_tags( $name ),
3484 + 'adminname' => $siteadmin,
3485 + 'pluginDir' => $pluginDir,
3486 + 'verify_certificate' => intval( $verifyCertificate ),
3487 + 'ssl_version' => intval( $sslVersion ),
3488 + 'wpe' => intval( $wpe ),
3489 + 'uniqueId' => $uniqueId,
3490 + 'http_user' => $encrypted_http_user,
3491 + 'http_pass' => $encrypted_http_pass,
3492 + 'disable_health_check' => $disableHealthChecking,
3493 + 'health_threshold' => $healthThreshold,
3494 + 'primary_backup_method' => $backup_method,
3495 + ),
3496 + array( 'id' => $websiteid )
3497 + );
3498 + $this->wpdb->update(
3499 + $this->table_name( 'wp_settings_backup' ),
3500 + array( 'archiveFormat' => $archiveFormat ),
3501 + array( 'wpid' => $websiteid )
3502 + );
2461 3503
2462 3504 if ( get_option( 'mainwp_enableLegacyBackupFeature' ) ) {
2463 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET maximumFileDescriptorsOverride = ' . ( $maximumFileDescriptorsOverride ? 1 : 0 ) . ',maximumFileDescriptorsAuto= ' . ( $maximumFileDescriptorsAuto ? 1 : 0 ) . ',maximumFileDescriptors = ' . $maximumFileDescriptors . ' WHERE id=%d', $websiteid ) );
3505 + $this->wpdb->update(
3506 + $this->table_name( 'wp' ),
3507 + array(
3508 + 'maximumFileDescriptorsOverride' => (int) $maximumFileDescriptorsOverride,
3509 + 'maximumFileDescriptorsAuto' => (int) $maximumFileDescriptorsAuto,
3510 + 'maximumFileDescriptors' => (int) $maximumFileDescriptors,
3511 + ),
3512 + array( 'id' => $websiteid )
3513 + );
2464 3514 }
2465 3515
2466 3516 // remove groups.
2467 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
3517 + $this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
2468 3518 // Remove GA stats.
2469 3519 $showErrors = $this->wpdb->hide_errors();
2470 3520
2471 3521 /**
@@ -2552,9 +3602,11 @@
2552 3602 public function get_websites_by_url( $url ) {
2553 3603 if ( '/' !== substr( $url, - 1 ) ) {
2554 3604 $url .= '/';
2555 3605 }
2556 - $results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s ', $this->escape( $url ) ), OBJECT );
3606 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3607 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3608 + $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.
2557 3609 if ( $results ) {
2558 3610 return $results;
2559 3611 }
2560 3612
@@ -2566,9 +3618,9 @@
2566 3618 $url = str_replace( 'https://', 'https://www.', $url );
2567 3619 $url = str_replace( 'http://', 'http://www.', $url );
2568 3620 }
2569 3621
2570 - $results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s ', $this->escape( $url ) ), OBJECT );
3622 + $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.
2571 3623 if ( $results ) {
2572 3624 return $results;
2573 3625 }
2574 3626
@@ -2573,31 +3625,68 @@
2573 3625 }
2574 3626
2575 3627 $url = str_replace( array( 'https://www.', 'http://www.', 'https://', 'http://', 'www.' ), array( '', '', '', '', '' ), $url );
2576 3628
2577 - return $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . " wp_sync ON wp.id = wp_sync.wpid WHERE replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s ", $this->escape( $url ) ), OBJECT );
3629 + 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.
2578 3630 }
2579 3631
2580 3632 /**
2581 - * Get websites offline status.
3633 + * Get recently-synced child sites whose stored url differs from the
3634 + * child-reported siteurl.
2582 3635 *
2583 - * @return array Child site monitoring status.
3636 + * The trailing slash must be normalized in SQL: stored urls always end
3637 + * with a slash while child-reported siteurl values never do, so a plain
3638 + * inequality would match nearly every site.
3639 + *
3640 + * @since 6.2
3641 + *
3642 + * @param int $days Freshness gate: only sites synced within this many days.
3643 + *
3644 + * @return array|object|null Rows with id, url, siteurl or null on failure.
2584 3645 */
2585 - public function get_websites_offline_status_to_send_notice() {
2586 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
2587 - $extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
3646 + public function get_websites_with_url_siteurl_mismatch( $days = 30 ) {
3647 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3648 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3649 + $minimum_sync = time() - intval( $days ) * DAY_IN_SECONDS;
2588 3650
2589 - return $this->wpdb->get_results(
2590 - 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2591 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2592 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2593 - WHERE wp.disable_status_check <> 1 AND wp.offline_check_result <> 1 AND wp.offline_check_result <> 0 AND wp.http_code_noticed = 0' . // http_code_noticed = 0: not noticed yet.
2594 - $where,
2595 - OBJECT
2596 - );
3651 + 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.
2597 3652 }
2598 3653
2599 3654 /**
3655 + * Get IDs of child sites that have a non-empty value for a website option.
3656 + *
3657 + * @since 6.2
3658 + *
3659 + * @param string $option_name Website option name.
3660 + * @param int $limit Maximum number of IDs to return.
3661 + *
3662 + * @return array Site IDs.
3663 + */
3664 + public function get_website_ids_with_nonempty_option( $option_name, $limit = 3 ) {
3665 + $options_table = esc_sql( $this->table_name( 'wp_options' ) );
3666 +
3667 + 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.
3668 + }
3669 +
3670 + /**
3671 + * Get one child site whose stored url exactly matches the given URL,
3672 + * excluding a site ID. Used as the duplicate guard before a URL correction.
3673 + *
3674 + * @since 6.2
3675 + *
3676 + * @param string $url URL to match ( trailing slash enforced ).
3677 + * @param int $exclude_id Site ID to exclude.
3678 + *
3679 + * @return object|null Matching row ( id, url ) or null.
3680 + */
3681 + public function get_website_by_exact_url_excluding_id( $url, $exclude_id ) {
3682 + $url = rtrim( (string) $url, '/' );
3683 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3684 +
3685 + 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.
3686 + }
3687 +
3688 + /**
2600 3689 * Method get_websites_to_notice_health_threshold()
2601 3690 *
2602 3691 * Get websites to notice site health.
2603 3692 *
@@ -2618,14 +3707,23 @@
2618 3707
2619 3708 $where_site_threshold = ' ( wp.health_threshold = 80 AND wp_sync.health_value < 80 ) '; // should-be-improved site health.
2620 3709 $where_site_threshold .= ' OR ( wp.health_threshold = 100 AND wp_sync.health_value >= 80 ) '; // good site health.
2621 3710
2622 - return $this->wpdb->get_results(
2623 - 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2624 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2625 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2626 - 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 ' .
2627 - $where,
3711 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3712 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3713 +
3714 + $view_selects = '';
3715 + $view_joins = '';
3716 +
3717 + $opts_view = $this->get_wp_options_join( $extra_view );
3718 +
3719 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3720 + $view_selects = ',' . $opts_view['selects'];
3721 + $view_joins = $opts_view['joins'];
3722 + }
3723 + return $this->wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $option_view is a validated SQL subquery
3724 + "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.
3725 + $where . ' GROUP BY wp.id ',
2628 3726 OBJECT
2629 3727 );
2630 3728 }
2631 3729
@@ -2633,22 +3731,46 @@
2633 3731 * Get websites offline status.
2634 3732 *
2635 3733 * @return array Sites with offline status.
2636 3734 */
2637 - public function get_websites_offline_check_status() {
3735 + public function get_websites_http_check_status() {
2638 3736 $where = $this->get_sql_where_allow_access_sites( 'wp' );
2639 3737 $extra_view = array( 'settings_notification_emails' );
3738 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
2640 3739
3740 + $view_selects = '';
3741 + $view_joins = '';
3742 +
3743 + $opts_view = $this->get_wp_options_join( $extra_view );
3744 +
3745 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3746 + $view_selects = ',' . $opts_view['selects'];
3747 + $view_joins = $opts_view['joins'];
3748 + }
3749 +
3750 + // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- is a validated SQL subquery.
2641 3751 return $this->wpdb->get_results(
2642 - 'SELECT wp.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2643 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2644 - WHERE wp.disable_status_check <> 1 AND wp.offline_check_result = -1' . // offline checked status.
2645 - $where,
3752 + "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.
3753 + $where . ' GROUP BY wp.id ',
2646 3754 OBJECT
2647 3755 );
2648 3756 }
2649 3757
2650 3758 /**
3759 + * Method set_website_noticed_http_check().
3760 + *
3761 + * @param array $site_id The site id .
3762 + *
3763 + * @return void
3764 + */
3765 + public function set_website_noticed_http_check( $site_id = array() ) {
3766 + if ( empty( $site_id ) ) {
3767 + return;
3768 + }
3769 + $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . esc_sql( $this->table_name( 'wp' ) ) . ' SET http_code_noticed = 1 WHERE id = %d', $site_id ) ); // phpcs:ignore PluginCheck.Security.DirectDB.UnpreparedSQL -- $site_id is an integer that is validated by ctype_digit() before being passed to this method.
3770 + }
3771 +
3772 + /**
2651 3773 * Get DB Sites.
2652 3774 *
2653 3775 * @since 4.6
2654 3776 *
@@ -2758,9 +3880,9 @@
2758 3880 if ( ! MainWP_System_Utility::can_edit_website( $website ) ) {
2759 3881 return false;
2760 3882 }
2761 3883
2762 - if ( ! mainwp_current_user_have_right( 'site', $websiteid ) ) {
3884 + if ( ! \mainwp_current_user_can( 'site', $websiteid ) ) {
2763 3885 return false;
2764 3886 }
2765 3887
2766 3888 return array(
@@ -2877,18 +3999,28 @@
2877 3999 *
2878 4000 * @return mixed Result
2879 4001 */
2880 4002 public function insert_lookup_item( $item_name, $item_id, $obj_name, $obj_id ) {
4003 + // get_lookup_items() and delete_lookup_items() match item_name as given, so a
4004 + // name that sanitizing would alter is refused instead of being stored under a
4005 + // value the caller can never read back or delete.
4006 + if ( ! is_string( $item_name ) || sanitize_text_field( $item_name ) !== $item_name ) {
4007 + return false;
4008 + }
2881 4009 if ( empty( $item_name ) || empty( $item_id ) || empty( $obj_name ) || empty( $obj_id ) ) {
2882 4010 return false;
2883 4011 }
2884 4012 $data = array(
2885 - 'item_name' => 'cost',
4013 + 'item_name' => $item_name,
2886 4014 'item_id' => $item_id,
2887 4015 'object_name' => $obj_name,
2888 4016 'object_id' => $obj_id,
2889 4017 );
2890 - $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data );
4018 + // wpdb refuses a value past the column length before any SQL runs, and
4019 + // insert_id would still hold the previous insert's id.
4020 + if ( false === $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data ) ) {
4021 + return false;
4022 + }
2891 4023 return $this->wpdb->insert_id; // must return lookup id.
2892 4024 }
2893 4025
2894 4026 /**
@@ -2950,19 +4082,26 @@
2950 4082 }
2951 4083
2952 4084
2953 4085 /**
2954 - * Return the user data for the given consumer_key.
4086 + * Insert a new REST API key row and return the credential payload.
2955 4087 *
4088 + * Returns an array with key_id, user_id, plaintext consumer_key,
4089 + * plaintext consumer_secret, and key_permissions on success. Returns
4090 + * false when the wpdb->insert() call fails (no current user, missing
4091 + * table, schema mismatch, etc.). Callers must check the return type
4092 + * before treating it as an array.
4093 + *
2956 4094 * @param string $consumer_key Consumer key.
2957 4095 * @param string $consumer_secret Secret key.
2958 4096 * @param string $scope scope.
2959 4097 * @param string $description description.
2960 4098 * @param int $enabled 1 or 0.
4099 + * @param array $others others.
2961 4100 *
2962 - * @return array
4101 + * @return array|false Credential payload on success, false on failure.
2963 4102 */
2964 - public function insert_rest_api_key( $consumer_key, $consumer_secret, $scope, $description, $enabled ) {
4103 + public function insert_rest_api_key( $consumer_key, $consumer_secret, $scope, $description, $enabled, $others = array() ) {
2965 4104 global $current_user;
2966 4105
2967 4106 if ( $current_user ) {
2968 4107 $user_id = $current_user->ID;
@@ -2971,11 +4110,19 @@
2971 4110 if ( empty( $user_id ) ) {
2972 4111 return false;
2973 4112 }
2974 4113
4114 + unset( $others ); // Parameter retained for signature compatibility; key_pass/key_type fields are vestigial after MWP-1544 cleanup.
4115 +
4116 + // Hash the consumer_secret with WordPress's password hasher so the
4117 + // value at rest is no longer reversible by a DB-read primitive.
4118 + // The plaintext is returned to the caller below so it can be shown
4119 + // to the admin once at creation time. See MWP-1540.
4120 + $hashed_secret = wp_hash_password( $consumer_secret );
4121 +
2975 4122 // Created API keys.
2976 - $permissions = in_array( $scope, array( 'read', 'write', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
2977 - $this->wpdb->insert(
4123 + $permissions = in_array( $scope, array( 'read', 'write', 'delete', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
4124 + $inserted = $this->wpdb->insert(
2978 4125 $this->table_name( 'api_keys' ),
2979 4126 array(
2980 4127 'user_id' => $user_id,
2981 4128 'description' => $description,
@@ -2980,9 +4127,9 @@
2980 4127 'user_id' => $user_id,
2981 4128 'description' => $description,
2982 4129 'permissions' => $permissions,
2983 4130 'consumer_key' => mainwp_api_hash( $consumer_key ),
2984 - 'consumer_secret' => $consumer_secret,
4131 + 'consumer_secret' => $hashed_secret,
2985 4132 'truncated_key' => substr( $consumer_key, -7 ),
2986 4133 'enabled' => $enabled,
2987 4134 ),
2988 4135 array(
@@ -2992,11 +4139,22 @@
2992 4139 '%s',
2993 4140 '%s',
2994 4141 '%s',
2995 4142 '%d',
2996 - )
4143 + ),
2997 4144 );
2998 4145
4146 + // wpdb->insert() returns false on failure. Without this guard the
4147 + // function would still hand back the plaintext consumer_secret plus
4148 + // $wpdb->insert_id, but that insert_id is the LAST successful insert
4149 + // on the connection (typically from earlier in the same request),
4150 + // not this row. The caller's empty-key_id check would pass and the
4151 + // operator would receive a credential that was never persisted.
4152 + // See MWP-1540 PR review feedback.
4153 + if ( false === $inserted ) {
4154 + return false;
4155 + }
4156 +
2999 4157 return array(
3000 4158 'key_id' => $this->wpdb->insert_id,
3001 4159 'user_id' => $user_id,
3002 4160 'consumer_key' => $consumer_key,
@@ -3015,9 +4173,9 @@
3015 4173 *
3016 4174 * @return array
3017 4175 */
3018 4176 public function update_rest_api_key( $key_id, $scope, $description, $enabled = 1 ) {
3019 - $permissions = in_array( $scope, array( 'read', 'write', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
4177 + $permissions = in_array( $scope, array( 'read', 'write', 'delete', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
3020 4178 return $this->wpdb->update(
3021 4179 $this->table_name( 'api_keys' ),
3022 4180 array(
3023 4181 'description' => $description,
@@ -3036,9 +4194,10 @@
3036 4194 *
3037 4195 * @return bool result.
3038 4196 */
3039 4197 public function is_existed_enabled_rest_key() {
3040 - $enabled = $this->wpdb->get_row( 'SELECT * FROM ' . $this->table_name( 'api_keys' ) . ' WHERE enabled = 1 LIMIT 1' );
4198 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4199 + $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.
3041 4200 return $enabled ? true : false;
3042 4201 }
3043 4202
3044 4203 /**
@@ -3048,9 +4207,10 @@
3048 4207 *
3049 4208 * @return array
3050 4209 */
3051 4210 public function get_rest_api_key_by( $id ) {
3052 - return $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'api_keys' ) . ' WHERE key_id = %d ', $id ) );
4211 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4212 + 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.
3053 4213 }
3054 4214
3055 4215 /**
3056 4216 * Method remove_rest_api_key().
@@ -3059,9 +4219,10 @@
3059 4219 *
3060 4220 * @return array
3061 4221 */
3062 4222 public function remove_rest_api_key( $id ) {
3063 - return $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'api_keys' ) . ' WHERE key_id = %s', $id ) );
4223 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4224 + 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.
3064 4225 }
3065 4226
3066 4227 /**
3067 4228 * Method get_rest_api_keys().
@@ -3068,7 +4229,155 @@
3068 4229 *
3069 4230 * @return array
3070 4231 */
3071 4232 public function get_rest_api_keys() {
3072 - return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'api_keys' ) . ' ORDER BY key_id DESC' );
4233 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4234 + 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.
4235 + }
4236 +
4237 +
4238 + /**
4239 + * Update regular process.
4240 + *
4241 + * @param array $data process data.
4242 + * @return mixed
4243 + */
4244 + public function update_regular_process( $data ) {
4245 + if ( isset( $data['process_id'] ) ) {
4246 + $process_id = $data['process_id'];
4247 + unset( $data['process_id'] );
4248 + return $this->wpdb->update( $this->table_name( 'schedule_processes' ), $data, array( 'process_id' => $process_id ) );
4249 + } elseif ( is_array( $data ) && isset( $data['type'] ) && isset( $data['process_slug'] ) ) {
4250 + return $this->wpdb->insert( $this->table_name( 'schedule_processes' ), $data );
4251 + }
4252 + return false;
4253 + }
4254 +
4255 + /**
4256 + * Delete regular process.
4257 + *
4258 + * @param int $process_id Process id.
4259 + * @param int $item_id Item id.
4260 + * @param string $pro_type Process type.
4261 + * @param string $pro_slug Process slug.
4262 + *
4263 + * @return mixed
4264 + */
4265 + public function delete_regular_process( $process_id = false, $item_id = false, $pro_type = false, $pro_slug = false ) {
4266 +
4267 + if ( is_numeric( $process_id ) && ! empty( $process_id ) ) {
4268 + return $this->wpdb->delete(
4269 + $this->table_name( 'schedule_processes' ),
4270 + array(
4271 + 'process_id' => $process_id,
4272 + )
4273 + );
4274 + } elseif ( ! empty( $pro_type ) || ! empty( $pro_slug ) ) {
4275 +
4276 + $data = array();
4277 +
4278 + if ( ! empty( $pro_type ) ) {
4279 + $data['type'] = $pro_type;
4280 + }
4281 +
4282 + if ( ! empty( $pro_slug ) ) {
4283 + $data['process_slug'] = $pro_slug;
4284 + }
4285 +
4286 + if ( ! empty( $item_id ) ) {
4287 + $data['item_id'] = $item_id;
4288 + }
4289 + // Bulk delete.
4290 + return $this->wpdb->delete( $this->table_name( 'schedule_processes' ), $data );
4291 + }
4292 + return false;
4293 + }
4294 +
4295 + /**
4296 + * Method get_regular_process_by_item_id_type_slug
4297 + *
4298 + * @param integer $item_id item id.
4299 + * @param string $type type.
4300 + * @param string $process_slug process slug.
4301 + *
4302 + * @return mixed result
4303 + */
4304 + public function get_regular_process_by_item_id_type_slug( $item_id, $type, $process_slug ) {
4305 + $table_name = esc_sql( $this->table_name( 'schedule_processes' ) );
4306 + 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.
4307 + }
4308 +
4309 + /**
4310 + * Log SQL queries for debugging via hook.
4311 + *
4312 + * This method provides a structured way to log SQL queries for development/debugging.
4313 + * It does NOT use error_log() directly - instead, it fires the `mainwp_log_system_query`
4314 + * action hook, allowing external listeners (logging plugins, debug tools) to handle
4315 + * the log output appropriately.
4316 + *
4317 + * To enable query logging:
4318 + * 1. Set `$params['dev_log_query'] = 1` when calling database methods
4319 + * 2. Add a listener to the `mainwp_log_system_query` action hook
4320 + *
4321 + * Example listener:
4322 + * ```php
4323 + * add_action( 'mainwp_log_system_query', function( $params, $sql, $caller ) {
4324 + * error_log( 'MainWP Query: ' . $sql );
4325 + * }, 10, 3 );
4326 + * ```
4327 + *
4328 + * @param array $params Query parameters. Set 'dev_log_query' to enable logging.
4329 + * @param string $sql The SQL query string.
4330 + * @param mixed $caller Instance of caller class (optional).
4331 + * @return void
4332 + */
4333 + public function log_system_query( $params, $sql, $caller = false ) {
4334 + $params = apply_filters( 'mainwp_log_system_query_params', $params, $sql, $caller );
4335 + if ( is_array( $params ) && ! empty( $params['dev_log_query'] ) && ! empty( $sql ) ) {
4336 + do_action( 'mainwp_log_system_query', $params, $sql, $caller );
4337 + }
4338 + }
4339 +
4340 + /**
4341 + * Method get_core_tables().
4342 + *
4343 + * Returns the list of core tables used by MainWP for validation data integrity and other purposes.
4344 + *
4345 + * @return array Core tables info
4346 + */
4347 + public function get_core_tables() {
4348 +
4349 + $core_tables = array(
4350 + $this->table_name( 'wp_clients' ),
4351 + $this->table_name( 'wp_clients_fields' ),
4352 + $this->table_name( 'wp_clients_field_values' ),
4353 + $this->table_name( 'wp_clients_contacts' ),
4354 + $this->table_name( 'monitors' ),
4355 + $this->table_name( 'monitor_heartbeat' ),
4356 + $this->table_name( 'monitor_stat_hourly' ),
4357 + $this->table_name( 'wp' ),
4358 + $this->table_name( 'wp_sync' ),
4359 + $this->table_name( 'wp_options' ),
4360 + $this->table_name( 'wp_settings_backup' ),
4361 + $this->table_name( 'users' ),
4362 + $this->table_name( 'wp_status' ),
4363 + $this->table_name( 'group' ),
4364 + $this->table_name( 'wp_group' ),
4365 + $this->table_name( 'lookup_item_objects' ),
4366 + $this->table_name( 'wp_backup_progress' ),
4367 + $this->table_name( 'wp_backup' ),
4368 + $this->table_name( 'api_keys' ),
4369 + $this->table_name( 'action_log' ),
4370 + $this->table_name( 'request_log' ),
4371 + $this->table_name( 'schedule_processes' ),
4372 + $this->table_name( 'cost_tracker' ),
4373 + );
4374 +
4375 + if ( ! defined( 'MAINWP_MODULE_LOG_ENABLED' ) || MAINWP_MODULE_LOG_ENABLED ) {
4376 + $core_tables[] = $this->table_name( 'wp_logs' );
4377 + $core_tables[] = $this->table_name( 'wp_logs_meta' );
4378 + $core_tables[] = $this->table_name( 'wp_logs_meta_archive' );
4379 + }
4380 +
4381 + return apply_filters( 'mainwp_database_core_tables', $core_tables );
3073 4382 }
3074 4383 }