PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 6.2.1
MainWP Dashboard: Self-hosted WordPress Management for Agencies v6.2.1
6.2.2 6.2.1 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 All 155 releases
← All changes | class/class-mainwp-db.php +2111 -279 5.1.1 → 6.2.1 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 }
@@ -93,15 +129,18 @@
93 129 }
94 130
95 131 $view = '(SELECT intwp.id AS wpid ';
96 132
97 - if ( empty( $fields ) || $default_value ) {
98 - $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,
133 + $included_opts = array();
134 +
135 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
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,
99 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,
100 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,
101 139 (SELECT phpversion.value FROM ' . $this->table_name( 'wp_options' ) . ' phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = "phpversion" LIMIT 1) AS phpversion,
102 140 (SELECT added_timestamp.value FROM ' . $this->table_name( 'wp_options' ) . ' added_timestamp WHERE added_timestamp.wpid = intwp.id AND added_timestamp.name = "added_timestamp" LIMIT 1) AS added_timestamp,
103 141 (SELECT wp_upgrades.value FROM ' . $this->table_name( 'wp_options' ) . ' wp_upgrades WHERE wp_upgrades.wpid = intwp.id AND wp_upgrades.name = "wp_upgrades" LIMIT 1) AS wp_upgrades ';
142 + $included_opts = array( 'recent_comments', 'recent_posts', 'recent_pages', 'phpversion', 'added_timestamp', 'wp_upgrades' );
104 143 }
105 144
106 145 if ( ! in_array( 'signature_algo', $fields ) ) {
107 146 $fields[] = 'signature_algo';
@@ -114,15 +153,20 @@
114 153 if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
115 154 $fields[] = 'cust_site_icon_info';
116 155 }
117 156
157 + $fields = $this->filter_safe_option_names( $fields );
158 +
118 159 if ( is_array( $fields ) ) {
119 160 foreach ( $fields as $field ) {
120 161 if ( empty( $field ) ) {
121 162 continue;
122 163 }
164 + if ( in_array( $field, $included_opts ) ) {
165 + continue;
166 + }
123 167 $view .= ', ';
124 - $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 ) . '`';
125 169 }
126 170 }
127 171
128 172 $view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
@@ -130,8 +174,599 @@
130 174 return $view;
131 175 }
132 176
133 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.
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() ) {
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 +
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 + /**
319 + * Get SQL to get child sites for current user.
320 + *
321 + * @since 5.2.
322 + * @param array $params other params.
323 + *
324 + * @return object|null Database query results or null on failure.
325 + */
326 + public function get_sql_websites_for_current_user_by_params( $params = array() ) { // phpcs:ignore -- NOSONAR - complex.
327 +
328 + if ( ! is_array( $params ) ) {
329 + $params = array();
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 +
339 + $view = isset( $params['view'] ) ? $params['view'] : 'default';
340 + $with_clients = isset( $params['with_clients'] ) && $params['with_clients'] ? true : false;
341 +
342 + // legacy support.
343 + $selectgroups = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
344 + $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
345 + $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
346 + $rowcount = isset( $params['rowcount'] ) ? (int) $params['rowcount'] : false;
347 + $count_sql = isset( $params['count_sql'] ) && $params['count_sql'] ? true : false;
348 + $extraWhere = isset( $params['where'] ) ? $params['where'] : null; // NOTE: without 'AND' at begining and ending of 'where'.
349 + $for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
350 + $others_fields = isset( $params['others_fields'] ) && is_array( $params['others_fields'] ) ? $params['others_fields'] : array( 'favi_icon' );
351 + $is_staging = isset( $params['is_staging'] ) && in_array( $params['is_staging'], array( 'yes', 'no' ) ) ? $params['is_staging'] : 'no';
352 + $limit = isset( $params['limit'] ) ? intval( $params['limit'] ) : '';
353 +
354 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
355 +
356 + $s = isset( $params['s'] ) ? $params['s'] : '';
357 + $exclude = isset( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
358 + $include = isset( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
359 + $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
360 + $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
361 + $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
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 +
369 + $where = '';
370 +
371 + if ( ! empty( $extraWhere ) ) {
372 + $where .= ' AND ' . $extraWhere;
373 + }
374 +
375 + if ( ! $for_manager ) {
376 + $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
377 + }
378 +
379 + $connected_sql = '';
380 +
381 + if ( is_array( $params ) && isset( $params['connected'] ) && 'yes' === $params['connected'] ) {
382 + $connected_sql = ' AND wp_sync.sync_errors = "" ';
383 + } elseif ( is_array( $params ) && isset( $params['connected'] ) && 'no' === $params['connected'] ) {
384 + $connected_sql = ' AND wp_sync.sync_errors <> "" ';
385 + }
386 +
387 + if ( ! empty( $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 + );
397 + }
398 +
399 + if ( ! empty( $exclude ) ) {
400 + $where .= ' AND wp.id NOT IN (' . implode( ',', $exclude ) . ') ';
401 + }
402 +
403 + if ( ! empty( $include ) ) {
404 + $where .= ' AND wp.id IN (' . implode( ',', $include ) . ') ';
405 + }
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 +
427 + // any, connected, disconnected, suspended, available_update.
428 + if ( ! empty( $status ) && is_array( $status ) && ! in_array( 'any', $status ) ) {
429 + $status_conds = array();
430 + if ( in_array( 'available_update', $status ) ) {
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 <> '[]' ) ";
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.
434 + if ( $results ) {
435 + $wp_ids = array();
436 + foreach ( $results as $item ) {
437 + if ( ! empty( $item->wpid ) ) {
438 + $wp_ids[] = $item->wpid;
439 + }
440 + }
441 + $wp_ids = ! empty( $wp_ids ) ? array_unique( $wp_ids ) : array();
442 + if ( ! empty( $wp_ids ) ) {
443 + $available_sql .= ' OR wp.id IN ( ' . implode( ',', $wp_ids ) . ' )';
444 + }
445 + }
446 + $status_conds[] = ' ( ' . $available_sql . ') ';
447 + }
448 +
449 + if ( in_array( 'connected', $status ) ) {
450 + $status_conds[] = ' ( wp_sync.sync_errors = "" ) ';
451 + }
452 + if ( in_array( 'disconnected', $status ) ) {
453 + $status_conds[] = " wp_sync.sync_errors <> '' ";
454 + }
455 +
456 + if ( in_array( 'suspended', $status ) ) {
457 + $status_conds[] = ' wp.suspended = 1 ';
458 + }
459 +
460 + if ( ! empty( $status_conds ) ) {
461 + $where .= ' AND ( ' . implode( ' OR ', $status_conds ) . ' ) ';
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 + }
466 + }
467 +
468 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
469 + $limit = ( $page - 1 ) * $per_page . ',' . $per_page;
470 + }
471 +
472 + if ( 'wp.url' === $orderBy ) {
473 + $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
474 + }
475 +
476 + $select_clients = '';
477 + $join_clients = '';
478 +
479 + if ( $with_clients ) {
480 + $select_clients = ', wpclient.name as client_name ';
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 ";
483 + }
484 +
485 + $base_fields = array(
486 + 'wp.id',
487 + 'wp.url',
488 + 'wp.name',
489 + 'wp.client_id',
490 + 'wp.verify_certificate',
491 + 'wp.http_user',
492 + 'wp.http_pass',
493 + 'wp.ssl_version',
494 + 'wp.adminname',
495 + 'wp.privkey',
496 + 'wp.pubkey',
497 + 'wp.wpe',
498 + 'wp.is_staging',
499 + 'wp.force_use_ipv4',
500 + 'wp.siteurl',
501 + 'wp.suspended',
502 + 'wp.mainwpdir',
503 + 'wp.is_ignoreCoreUpdates',
504 + 'wp.is_ignorePluginUpdates',
505 + 'wp.is_ignoreThemeUpdates',
506 + 'wp_sync.sync_errors',
507 + 'wp.backup_before_upgrade',
508 + 'wp.userid',
509 + 'wp.plugins',
510 + 'wp.themes',
511 + 'wp.offline_check_result', // 1 - online, -1 offline.
512 + 'wp.automatic_update',
513 + );
514 +
515 + $select = ' wp.*,wp_sync.* ';
516 + if ( 'base_view' === $view ) {
517 + $select = implode( ',', $base_fields );
518 + } elseif ( 'updates_view' === $view ) {
519 + $updates_fields = array(
520 + 'wp.plugin_upgrades',
521 + 'wp.theme_upgrades',
522 + 'wp.translation_upgrades',
523 + 'wp.premium_upgrades',
524 + 'wp.ignored_themes',
525 + 'wp.ignored_plugins',
526 + );
527 + $select = implode( ',', array_merge( $updates_fields, $base_fields ) );
528 + }
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 +
544 + // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
545 + if ( $selectgroups ) {
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
550 + LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
551 + LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
552 + ' . $join_clients . '
553 + JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
554 + ' . $view_joins . '
555 + WHERE 1 ' . $where . $connected_sql;
556 + $group_qry = '
557 + GROUP BY wp.id, wp_sync.sync_id
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;
576 + } else {
577 + $select_qry = 'SELECT ' . $select . $view_selects . $select_clients;
578 + $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
579 + ' . $join_clients . '
580 + JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
581 + ' . $view_joins . '
582 + WHERE 1 ' . $where . $connected_sql;
583 + $group_qry = '
584 + GROUP BY wp.id, wp_sync.sync_id
585 + ORDER BY ' . $orderBy;
586 + }
587 +
588 + if ( $count_sql ) {
589 + return 'SELECT COUNT(DISTINCT wp.id) ' . $qry;
590 + }
591 +
592 + $qry = $select_qry . $qry . $group_qry;
593 +
594 + if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
595 + $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
596 + } elseif ( false !== $rowcount ) {
597 + $qry .= ' LIMIT ' . $rowcount;
598 + } elseif ( ! empty( $limit ) ) {
599 + $qry .= ' LIMIT ' . $limit;
600 + } else {
601 + // load all sites so check to support limit sites loading.
602 + $limit_sites = ! empty( $params['limit_sites'] ) ? intval( $params['limit_sites'] ) : 0;
603 + if ( ! empty( $limit_sites ) ) {
604 + $current_page = (int) get_option( 'mainwp_manage_updates_limit_current_page', 0 );
605 + $current_page = $current_page > 0 ? $current_page - 1 : 0;
606 + $start = $current_page * $limit_sites;
607 + $qry .= ' LIMIT ' . intval( $start ) . ', ' . intval( $limit_sites );
608 + }
609 + }
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 ) );
615 + return $qry;
616 + }
617 +
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 + /**
695 + * Get wp_options database table view.
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 + *
699 + * @param array $view Option view.
700 + * @param array $other_fields Extra option fields.
701 + *
702 + * @return array wp_options view.
703 + */
704 + public function get_option_view_by( $view = '', $other_fields = array() ) {
705 +
706 + $default = array(
707 + 'recent_comments',
708 + 'recent_posts',
709 + 'recent_pages',
710 + 'phpversion',
711 + 'added_timestamp',
712 + 'wp_upgrades',
713 + );
714 +
715 + $fields = array();
716 +
717 + if ( 'updates_view' === $view ) {
718 + $fields = array(
719 + 'wp_upgrades',
720 + 'ignored_wp_upgrades',
721 + 'ignored_trans_updates',
722 + );
723 + } elseif ( in_array( $view, array( 'simple_view', 'base_view', 'monitor_view', 'ping_view', 'uptime_notification' ) ) ) {
724 + $fields = array();
725 + if ( 'monitor_view' === $view || 'ping_view' === $view ) {
726 + $fields[] = 'health_site_status';
727 + $fields[] = 'bypass_cache';
728 + }
729 + } elseif ( 'custom_view' !== $view ) {
730 + $fields = $default;
731 + }
732 +
733 + if ( is_array( $other_fields ) && ! empty( $other_fields ) ) {
734 + $fields = array_unique( array_merge( $fields, $other_fields ) );
735 + }
736 +
737 + $view_query = '(SELECT wpid ';
738 +
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 + }
747 + }
748 +
749 + $fields = $this->filter_safe_option_names( $fields );
750 +
751 + foreach ( $fields as $field ) {
752 +
753 + if ( empty( $field ) ) {
754 + continue;
755 + }
756 +
757 + $view_query .= ', ';
758 + $view_query .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
759 + }
760 +
761 + $view_query .= ' FROM ' . $this->table_name( 'wp_options' ) .
762 + " WHERE name IN ('" . implode( "','", $fields ) . "')
763 + GROUP BY wpid ) ";
764 +
765 + return $view_query;
766 + }
767 +
768 + /**
134 769 * Method get_select_groups_belong().
135 770 *
136 771 * @return string sql.
137 772 */
@@ -152,18 +787,20 @@
152 787 *
153 788 * @return array $connected_sites Array of connected sites.
154 789 */
155 790 public function get_connected_websites( $sites_ids = false ) {
156 - $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' ) );
157 794
158 - $sql = 'SELECT wp.*,wp_sync.*
159 - FROM ' . $this->table_name( 'wp' ) . ' wp
160 - 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
161 798 ON wp.id = wp_sync.wpid
162 - 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 = \"\") " .
163 800 $where;
164 801
165 - $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()
166 803 $connected_sites = array();
167 804 if ( $websites ) {
168 805 foreach ( $websites as $website ) {
169 806
@@ -188,18 +825,20 @@
188 825 *
189 826 * @return array $disc_sites Array of disonnected sites.
190 827 */
191 828 public function get_disconnected_websites( $sites_ids = false ) {
192 - $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' ) );
193 832
194 - $sql = 'SELECT wp.*,wp_sync.*
195 - FROM ' . $this->table_name( 'wp' ) . ' wp
196 - 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
197 836 ON wp.id = wp_sync.wpid
198 - 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 <> \"\") " .
199 838 $where;
200 839
201 - $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()
202 841 $disc_sites = array();
203 842 if ( $websites ) {
204 843 foreach ( $websites as $website ) {
205 844
@@ -225,8 +864,12 @@
225 864 *
226 865 * @return int Child site count.
227 866 *
228 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.
229 872 */
230 873 public function get_websites_count( $userId = null, $all_access = false ) {
231 874 static $total_sites;
232 875 if ( null !== $total_sites ) { // NOSONAR -- static value.
@@ -242,15 +885,16 @@
242 885 global $current_user;
243 886
244 887 $userId = $current_user->ID;
245 888 }
246 - $where = ( null === $userId ? '' : ' wp.userid = ' . $userId );
889 + $where = ( null === $userId ? '' : ' wp.userid = ' . intval( $userId ) );
247 890 if ( ! $all_access ) {
248 891 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
249 892 }
250 - $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}";
251 895
252 - $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()
253 897 $total_sites = $total;// NOSONAR -- static value.
254 898 return $total;
255 899 }
256 900
@@ -278,9 +922,9 @@
278 922
279 923 $select_stats = ' ( SELECT COUNT(wp.id) as count_all ';
280 924 if ( ! empty( $params['count_disconnected'] ) ) {
281 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 ';
282 - $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 ';
283 927 }
284 928 if ( ! empty( $params['count_suspended'] ) ) {
285 929 $select_stats .= ',( SELECT COUNT(wp_suspended.id) FROM ' . $this->table_name( 'wp' ) . ' wp_suspended WHERE wp_suspended.suspended = 1 ) as count_suspended ';
286 930 }
@@ -295,21 +939,34 @@
295 939 *
296 940 * @param array $website Child Site array.
297 941 * @param mixed $option Child Site wp_options table name.
298 942 * @param mixed $default_value default value.
943 + * @param mixed $json_format Is json format value.
299 944 *
300 945 * @return string|null Database query result (as string), or null on failure.
301 946 */
302 - public function get_website_option( $website, $option, $default_value = null ) {
947 + public function get_website_option( $website, $option, $default_value = null, $json_format = false ) { //phpcs:ignore -- NOSONAR - complex.
303 948
304 949 if ( is_array( $website ) ) {
305 950 if ( isset( $website[ $option ] ) ) {
306 - return $website[ $option ];
951 + $value = $website[ $option ];
952 + if ( true === $json_format ) {
953 + $value = ! empty( $value ) ? json_decode( $value, true ) : array();
954 + return is_array( $value ) ? $value : array();
955 + } else {
956 + return $value;
957 + }
307 958 }
308 959 $site_id = $website['id'];
309 960 } elseif ( is_object( $website ) ) {
310 961 if ( property_exists( $website, $option ) ) {
311 - return $website->{$option};
962 + $value = $website->{$option};
963 + if ( true === $json_format ) {
964 + $value = ! empty( $value ) ? json_decode( $value, true ) : array();
965 + return is_array( $value ) ? $value : array();
966 + } else {
967 + return $value;
968 + }
312 969 }
313 970 $site_id = $website->id;
314 971 } elseif ( is_numeric( $website ) ) { // to support $site_id = 0, for global options.
315 972 $site_id = $website;
@@ -316,17 +973,39 @@
316 973 } else {
317 974 return false;
318 975 }
319 976
320 - $var = $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.
321 979
322 - if ( null === $var && null !== $default_value ) {
323 - $var = $default_value;
980 + if ( null === $value && null !== $default_value ) {
981 + return $default_value;
324 982 }
325 - return $var;
983 +
984 + if ( true === $json_format ) {
985 + $value = ! empty( $value ) ? json_decode( $value, true ) : array();
986 + return is_array( $value ) ? $value : array();
987 + } else {
988 + return $value;
989 + }
326 990 }
327 991
328 992 /**
993 + * Get Child site wp_options json value.
994 + *
995 + * @since 5.1.1
996 + *
997 + * @param array $website Child Site array.
998 + * @param mixed $option Child Site wp_options table name.
999 + * @param mixed $default_value default value.
1000 + *
1001 + * @return string|null Database query result (as string), or null on failure.
1002 + */
1003 + public function get_json_website_option( $website, $option, $default_value = null ) {
1004 + return $this->get_website_option( $website, $option, $default_value, true );
1005 + }
1006 +
1007 + /**
329 1008 * Get child site options.
330 1009 *
331 1010 * @param array $website Child site.
332 1011 * @param mixed $options Child site options name.
@@ -373,13 +1052,12 @@
373 1052 if ( empty( $get_options ) ) {
374 1053 return $arr_options; // all options.
375 1054 }
376 1055
377 - $options_name = implode( "','", $get_options );
378 - $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.
379 1059
380 - $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 ) );
381 -
382 1060 $fill_options = array(
383 1061 'primary_lasttime_backup',
384 1062 );
385 1063
@@ -407,14 +1085,22 @@
407 1085 * @param mixed $option Option to update.
408 1086 * @param mixed $value Value to update with.
409 1087 */
410 1088 public function update_website_option( $website, $option, $value ) {
411 - $rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $website->id ) );
1089 +
1090 + if ( is_numeric( $website ) ) {
1091 + $site_id = intval( $website );
1092 + } else {
1093 + $site_id = $website->id;
1094 + }
1095 +
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.
412 1098 if ( empty( $rslt ) ) {
413 1099 $this->wpdb->insert(
414 1100 $this->table_name( 'wp_options' ),
415 1101 array(
416 - 'wpid' => $website->id,
1102 + 'wpid' => $site_id,
417 1103 'name' => $option,
418 1104 'value' => $value,
419 1105 )
420 1106 );
@@ -422,9 +1108,9 @@
422 1108 $this->wpdb->update(
423 1109 $this->table_name( 'wp_options' ),
424 1110 array( 'value' => $value ),
425 1111 array(
426 - 'wpid' => $website->id,
1112 + 'wpid' => $site_id,
427 1113 'name' => $option,
428 1114 )
429 1115 );
430 1116 }
@@ -431,8 +1117,37 @@
431 1117 }
432 1118
433 1119
434 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 + /**
435 1150 * Get general Child site option.
436 1151 *
437 1152 * @param mixed $option Child Site option name.
438 1153 *
@@ -447,9 +1162,10 @@
447 1162 } else {
448 1163 static::$general_options[] = array();
449 1164 }
450 1165
451 - $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.
452 1168
453 1169 static::$general_options[ $option ] = $val;
454 1170 return $val;
455 1171 }
@@ -484,12 +1200,15 @@
484 1200 $diff_options[] = $opt;
485 1201 }
486 1202 }
487 1203
488 - $options_name = implode( "','", $diff_options );
489 - $options_name = "'" . $options_name . "'";
1204 + if ( empty( $diff_options ) ) {
1205 + return $return_options;
1206 + }
490 1207
491 - $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.
492 1211
493 1212 foreach ( (array) $options_db as $o ) {
494 1213 $return_options[ $o->name ] = $o->value;
495 1214 static::$general_options[ $o->name ] = $o->value;
@@ -519,9 +1238,10 @@
519 1238 static::$general_options[] = array();
520 1239 }
521 1240 static::$general_options[ $option ] = $value;
522 1241
523 - $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.
524 1244
525 1245 if ( empty( $rslt ) ) {
526 1246 $this->wpdb->insert(
527 1247 $this->table_name( 'wp_options' ),
@@ -586,47 +1306,23 @@
586 1306 */
587 1307 public function get_sql_websites() {
588 1308 $where = $this->get_sql_where_allow_access_sites( 'wp' );
589 1309
590 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
591 - FROM ' . $this->table_name( 'wp' ) . ' wp
592 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
593 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
594 - WHERE 1 ' . $where;
595 - }
1310 + $view_selects = '';
1311 + $view_joins = '';
596 1312
597 - /**
598 - * Get child sites to run the status check process.
599 - *
600 - * @param int $last_check Time of the last check.
601 - * @param int $count Number of websites.
602 - *
603 - * @return string SQL string.
604 - */
605 - public function get_sql_websites_to_check_status( $last_check, $count = 20 ) {
606 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
607 - return 'SELECT wp.*
608 - FROM ' . $this->table_name( 'wp' ) . ' wp
609 - WHERE wp.disable_status_check <> 1 AND ( wp.status_check_interval = 0 AND wp.offline_checks_last < ' . intval( $last_check ) . ' )' .
610 - $where . '
611 - LIMIT ' . intval( $count );
612 - }
1313 + $opts_view = $this->get_wp_options_join();
613 1314
1315 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
1316 + $view_selects = ',' . $opts_view['selects'];
1317 + $view_joins = $opts_view['joins'];
1318 + }
614 1319
615 - /**
616 - * Get child sites to run the status individual check process.
617 - *
618 - * @param int $count Number of websites.
619 - *
620 - * @return string SQL string.
621 - */
622 - public function get_sql_websites_to_check_individual_status( $count = 20 ) {
623 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
624 - return 'SELECT wp.*
1320 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
625 1321 FROM ' . $this->table_name( 'wp' ) . ' wp
626 - WHERE wp.disable_status_check <> 1 AND ( wp.status_check_interval <> 0 AND ( wp.offline_checks_last + wp.status_check_interval * 60 < UNIX_TIMESTAMP() ) )' .
627 - $where . '
628 - 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';
629 1325 }
630 1326
631 1327 /**
632 1328 * Get child sites by user id via SQL.
@@ -651,24 +1347,34 @@
651 1347 }
652 1348
653 1349 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
654 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 +
655 1361 if ( $selectgroups ) {
656 - $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
657 1363 FROM ' . $this->table_name( 'wp' ) . ' wp
658 1364 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
659 1365 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
660 1366 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
661 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1367 + ' . $view_joins . '
662 1368 WHERE wp.userid = ' . $userid . "
663 1369 $where
664 1370 GROUP BY wp.id, wp_sync.sync_id
665 1371 ORDER BY " . $orderBy;
666 1372 } else {
667 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
1373 + $qry = 'SELECT wp.*,wp_sync.*' . $view_selects . '
668 1374 FROM ' . $this->table_name( 'wp' ) . ' wp
669 1375 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
670 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1376 + ' . $view_joins . '
671 1377 WHERE wp.userid = ' . $userid . "
672 1378 $where
673 1379 ORDER BY " . $orderBy;
674 1380 }
@@ -715,8 +1421,15 @@
715 1421 $is_staging = 'no',
716 1422 $params = array()
717 1423 ) {
718 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 +
719 1432 $where = '';
720 1433 if ( MainWP_System::instance()->is_multi_user() ) {
721 1434
722 1435 /**
@@ -730,13 +1443,19 @@
730 1443 }
731 1444
732 1445 if ( null !== $search_site ) {
733 1446 $search_site = trim( $search_site );
734 - $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 + );
735 1454 }
736 1455
737 - if ( null !== $extraWhere ) {
738 - $where .= ' AND ' . $extraWhere;
1456 + if ( ! empty( $extraWhere ) ) {
1457 + $where .= ' AND ' . $extraWhere . ' ';
739 1458 }
740 1459
741 1460 if ( ! $for_manager ) {
742 1461 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
@@ -749,15 +1468,114 @@
749 1468 } elseif ( is_array( $params ) && isset( $params['connected'] ) && 'no' === $params['connected'] ) {
750 1469 $connected_sql = ' AND wp_sync.sync_errors <> "" ';
751 1470 }
752 1471
1472 + $use_comp_subquery = false;
1473 +
1474 + $limit = '';
1475 + if ( $params && is_array( $params ) ) {
1476 + $s = isset( $params['s'] ) ? $params['s'] : '';
1477 + $exclude = isset( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
1478 + $include = isset( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
1479 + $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
1480 + $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
1481 + $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
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 +
1486 + if ( ! empty( $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}' ) ";
1494 + }
1495 +
1496 + if ( ! empty( $exclude ) ) {
1497 + $where .= ' AND wp.id NOT IN (' . implode( ',', $exclude ) . ') ';
1498 + }
1499 +
1500 + if ( ! empty( $include ) ) {
1501 + $where .= ' AND wp.id IN (' . implode( ',', $include ) . ') ';
1502 + }
1503 +
1504 + if ( ! empty( $_included_cache_ids ) ) {
1505 + $where .= ' AND wp.id IN (' . implode( ',', $_included_cache_ids ) . ') ';
1506 + }
1507 +
1508 + // any, connected, disconnected, suspended, available_update.
1509 + if ( ! empty( $status ) && is_array( $status ) && ! in_array( 'any', $status ) ) {
1510 + $status_conds = array();
1511 + if ( in_array( 'available_update', $status ) ) {
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 <> '[]' ) ";
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.
1515 + if ( $results ) {
1516 + $wp_ids = array();
1517 + foreach ( $results as $item ) {
1518 + if ( ! empty( $item->wpid ) ) {
1519 + $wp_ids[] = $item->wpid;
1520 + }
1521 + }
1522 + $wp_ids = ! empty( $wp_ids ) ? array_unique( $wp_ids ) : array();
1523 + if ( ! empty( $wp_ids ) ) {
1524 + $available_sql .= ' OR wp.id IN ( ' . implode( ',', $wp_ids ) . ' )';
1525 + }
1526 + }
1527 + $status_conds[] = ' ( ' . $available_sql . ') ';
1528 + }
1529 +
1530 + if ( in_array( 'connected', $status ) ) {
1531 + $status_conds[] = ' ( wp_sync.sync_errors = "" ) ';
1532 + }
1533 + if ( in_array( 'disconnected', $status ) ) {
1534 + $status_conds[] = " wp_sync.sync_errors <> '' ";
1535 + }
1536 +
1537 + if ( in_array( 'suspended', $status ) ) {
1538 + $status_conds[] = ' wp.suspended = 1 ';
1539 + }
1540 +
1541 + if ( ! empty( $status_conds ) ) {
1542 + $where .= ' AND ( ' . implode( ' OR ', $status_conds ) . ' ) ';
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 + }
1548 + }
1549 +
1550 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
1551 + $limit = ( $page - 1 ) * $per_page . ',' . $per_page;
1552 + }
1553 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
1554 + }
1555 +
753 1556 if ( 'wp.url' === $orderBy ) {
754 1557 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
755 1558 }
756 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 +
757 1575 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
758 1576 if ( $selectgroups ) {
759 - $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,
760 1578 wpclient.name as client_name
761 1579 FROM ' . $this->table_name( 'wp' ) . ' wp
762 1580 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
763 1581 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
@@ -762,18 +1580,18 @@
762 1580 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
763 1581 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
764 1582 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
765 1583 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
766 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1584 + ' . $view_joins . '
767 1585 WHERE 1 ' . $where . $connected_sql . '
768 1586 GROUP BY wp.id, wp_sync.sync_id
769 1587 ORDER BY ' . $orderBy;
770 1588 } else {
771 - $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
772 1590 FROM ' . $this->table_name( 'wp' ) . ' wp
773 1591 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
774 1592 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
775 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1593 + ' . $view_joins . '
776 1594 WHERE 1 ' . $where . $connected_sql . '
777 1595 GROUP BY wp.id, wp_sync.sync_id
778 1596 ORDER BY ' . $orderBy;
779 1597 }
@@ -781,13 +1599,30 @@
781 1599 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
782 1600 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
783 1601 } elseif ( false !== $rowcount ) {
784 1602 $qry .= ' LIMIT ' . $rowcount;
1603 + } elseif ( ! empty( $limit ) ) {
1604 + $qry .= ' LIMIT ' . $limit;
1605 + } else {
1606 + // load all sites so check to support limit sites loading.
1607 + $limit_sites = ! empty( $params['limit_sites'] ) ? intval( $params['limit_sites'] ) : 0;
1608 + if ( ! empty( $limit_sites ) ) {
1609 + $current_page = (int) get_option( 'mainwp_manage_updates_limit_current_page', 0 );
1610 + $current_page = $current_page > 0 ? $current_page - 1 : 0;
1611 + $start = $current_page * $limit_sites;
1612 + $qry .= ' LIMIT ' . intval( $start ) . ', ' . intval( $limit_sites );
1613 + }
785 1614 }
786 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 +
787 1621 return $qry;
788 1622 }
789 1623
1624 +
790 1625 /**
791 1626 * Get SQL to get wp child sites for current user.
792 1627 *
793 1628 * @since 4.3
@@ -800,17 +1635,18 @@
800 1635 if ( ! is_array( $params ) ) {
801 1636 $params = array();
802 1637 }
803 1638
804 - $selectgroups = ! empty( $params['select_groups'] ) ? true : false;
805 - $search_site = isset( $params['search_site'] ) && ! empty( $params['search_site'] ) ? $params['search_site'] : null;
806 - $orderBy = isset( $params['order_by'] ) && ! empty( $params['order_by'] ) ? $params['order_by'] : 'wp.url';
807 - $offset = isset( $params['offset'] ) ? $params['offset'] : false;
808 - $rowcount = isset( $params['row_count'] ) ? $params['row_count'] : false;
809 - $for_manager = isset( $params['for_manager'] ) ? $params['for_manager'] : false;
810 - $extraWhere = isset( $params['extra_where'] ) && ! empty( $params['extra_where'] ) ? $params['extra_where'] : null;
811 - $extra_view = isset( $params['extra_view'] ) && is_array( $params['extra_view'] ) && ! empty( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
812 - $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;
813 1649
814 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();
815 1651 $extra_select_sql_fields = isset( $params['extra_select_sql_fields'] ) && ! empty( $params['extra_select_sql_fields'] ) ? $params['extra_select_sql_fields'] : '';
816 1652
@@ -820,9 +1656,15 @@
820 1656 $where = '';
821 1657
822 1658 if ( null !== $search_site ) {
823 1659 $search_site = trim( $search_site );
824 - $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 + );
825 1667 }
826 1668
827 1669 if ( null !== $extraWhere ) {
828 1670 $where .= ' AND ' . $extraWhere;
@@ -841,8 +1683,22 @@
841 1683 if ( ! empty( $extra_select_sql_fields ) ) {
842 1684 $extra_select_sql_fields = ',' . $extra_select_sql_fields;
843 1685 }
844 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 +
845 1701 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
846 1702 if ( $selectgroups ) {
847 1703 if ( $count_only ) {
848 1704 $select = ' COUNT(DISTINCT(wp.id)) ';
@@ -848,9 +1704,9 @@
848 1704 $select = ' COUNT(DISTINCT(wp.id)) ';
849 1705 } else {
850 1706 $select = $select_wp_fields . '
851 1707 ' . $extra_select_sql_fields . '
852 - ,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 ';
853 1709 }
854 1710 $qry = 'SELECT ' . $select . '
855 1711 FROM ' . $this->table_name( 'wp' ) . ' wp
856 1712 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
@@ -856,10 +1712,10 @@
856 1712 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
857 1713 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
858 1714 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
859 1715 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
860 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
861 - $extra_join . '
1716 + ' . $view_joins . '
1717 + ' . $extra_join . '
862 1718 WHERE 1 ' . $where;
863 1719 if ( ! $count_only ) {
864 1720 $qry .= ' GROUP BY wp.id, wp_sync.sync_id';
865 1721 }
@@ -869,16 +1725,16 @@
869 1725 $select = ' COUNT(DISTINCT(wp.id)) ';
870 1726 } else {
871 1727 $select = $select_wp_fields . '
872 1728 ' . $extra_select_sql_fields . '
873 - ,wp_sync.sync_errors,wp_optionview.*, wpclient.name as client_name ';
1729 + ,wp_sync.sync_errors' . $view_selects . ', wpclient.name as client_name ';
874 1730 }
875 1731 $qry = 'SELECT ' . $select . '
876 1732 FROM ' . $this->table_name( 'wp' ) . ' wp
877 1733 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
878 1734 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
879 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
880 - $extra_join . '
1735 + ' . $view_joins . '
1736 + ' . $extra_join . '
881 1737 WHERE 1 ' . $where;
882 1738 if ( ! $count_only ) {
883 1739 $qry .= ' GROUP BY wp.id, wp_sync.sync_id';
884 1740 }
@@ -906,13 +1762,11 @@
906 1762 public function get_sql_select_wp_valid_fields( $other_fields = array() ) {
907 1763
908 1764 $allow_other_fields = array(
909 1765 'offline_checks_last',
910 - 'offline_check_result',
1766 + 'offline_check_result', // 1 - online, -1 offline.
911 1767 'http_response_code',
912 - 'disable_status_check',
913 1768 'disable_health_check',
914 - 'status_check_interval',
915 1769 'health_threshold',
916 1770 'note',
917 1771 'statsUpdate',
918 1772 'directories',
@@ -989,10 +1843,11 @@
989 1843 $full_data = isset( $params['full_data'] ) && $params['full_data'] && ( 'no' !== $params['full_data'] ) ? true : false;
990 1844 $select_data = isset( $params['select_data'] ) && is_array( $params['select_data'] ) ? $params['select_data'] : false;
991 1845 $format = isset( $params['format'] ) ? $params['format'] : '';
992 1846 $clients = isset( $params['client'] ) ? $params['client'] : '';
1847 + $fields = isset( $params['fields'] ) && is_array( $params['fields'] ) ? $params['fields'] : array();
993 1848
994 - $for_manager = false;
1849 + $for_manager = isset( $params['no_perm_check'] ) && true === $params['no_perm_check'];
995 1850
996 1851 $urlsWhere = '';
997 1852
998 1853 if ( isset( $params['urls'] ) && ! empty( $params['urls'] ) ) {
@@ -1036,8 +1891,17 @@
1036 1891 $extraWhere = $extraWhere . ' AND ' . $clientWhere;
1037 1892 }
1038 1893 }
1039 1894
1895 + $args = array(
1896 + 's' => isset( $params['s'] ) ? $params['s'] : '',
1897 + 'exclude' => isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array(),
1898 + 'include' => isset( $params['include'] ) && ! empty( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array(),
1899 + 'status' => isset( $params['status'] ) && ! empty( $params['status'] ) ? wp_parse_list( $params['status'] ) : '',
1900 + 'page' => isset( $params['paged'] ) ? intval( $params['paged'] ) : false,
1901 + 'per_page' => isset( $params['items_per_page'] ) ? intval( $params['items_per_page'] ) : false,
1902 + );
1903 +
1040 1904 $data = array( 'id', 'url', 'name', 'client_id' );
1041 1905
1042 1906 if ( $full_data ) {
1043 1907 $data = array(
@@ -1044,15 +1908,14 @@
1044 1908 'id',
1045 1909 'url',
1046 1910 'name',
1047 1911 'offline_checks_last',
1048 - 'offline_check_result',
1912 + 'offline_check_result', // 1 - online, -1 offline.
1049 1913 'http_response_code',
1050 - 'disable_status_check',
1051 1914 'disable_health_check',
1052 - 'status_check_interval',
1053 1915 'health_threshold',
1054 1916 'note',
1917 + 'dbsize',
1055 1918 'plugin_upgrades',
1056 1919 'theme_upgrades',
1057 1920 'translation_upgrades',
1058 1921 'securityIssues',
@@ -1077,8 +1940,9 @@
1077 1940 'ssl_version',
1078 1941 'signature_algo',
1079 1942 'verify_method',
1080 1943 'verify_certificate',
1944 + 'suspended',
1081 1945 );
1082 1946
1083 1947 if ( ! in_array( 'security_stats', $extra_view ) ) {
1084 1948 $extra_view[] = 'security_stats';
@@ -1090,18 +1954,30 @@
1090 1954 }
1091 1955
1092 1956 if ( $selectgroups ) {
1093 1957 $data[] = 'wpgroups';
1958 + $data[] = 'wpgroupids';
1094 1959 }
1095 1960
1961 + if ( ! empty( $fields ) ) {
1962 + $data = array_unique( array_merge( $fields, $data ) ); // to prevent difference fields name.
1963 + }
1964 +
1096 1965 $dbwebsites = array();
1097 - $websites = $this->query( $this->get_sql_websites_for_current_user( $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging ) );
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 +
1098 1970 while ( $websites && ( $website = static::fetch_object( $websites ) ) ) {
1971 +
1099 1972 $obj_data = MainWP_Utility::map_site( $website, $data );
1100 1973
1101 1974 if ( $full_data ) {
1102 1975 $sum_upgrades = 0;
1103 - 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 ) ) {
1104 1980 $plugin_upgrades = json_decode( $obj_data->plugin_upgrades, true );
1105 1981 if ( is_array( $plugin_upgrades ) ) {
1106 1982 $sum_upgrades += count( $plugin_upgrades );
1107 1983 }
@@ -1106,9 +1982,9 @@
1106 1982 $sum_upgrades += count( $plugin_upgrades );
1107 1983 }
1108 1984 }
1109 1985
1110 - if ( '' !== $obj_data->theme_upgrades ) {
1986 + if ( ! empty( $obj_data->theme_upgrades ) ) {
1111 1987 $theme_upgrades = json_decode( $obj_data->theme_upgrades, true );
1112 1988 if ( is_array( $theme_upgrades ) ) {
1113 1989 $sum_upgrades += count( $theme_upgrades );
1114 1990 }
@@ -1113,9 +1989,9 @@
1113 1989 $sum_upgrades += count( $theme_upgrades );
1114 1990 }
1115 1991 }
1116 1992
1117 - if ( '' !== $obj_data->wp_upgrades ) {
1993 + if ( ! empty( $obj_data->wp_upgrades ) ) {
1118 1994 $wp_upgrades = json_decode( $obj_data->wp_upgrades, true );
1119 1995 if ( is_array( $wp_upgrades ) ) {
1120 1996 $sum_upgrades += count( $wp_upgrades );
1121 1997 }
@@ -1133,8 +2009,126 @@
1133 2009 return $dbwebsites;
1134 2010 }
1135 2011
1136 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 + /**
1137 2131 * Get the child sites the current user has searched for.
1138 2132 *
1139 2133 * @param array $params Query parameters.
1140 2134 *
@@ -1147,14 +2141,15 @@
1147 2141 if ( ! is_array( $params ) ) {
1148 2142 $params = array();
1149 2143 }
1150 2144
2145 + $view = isset( $params['view'] ) ? $params['view'] : 'default'; // must be default.
1151 2146 $selectgroups = isset( $params['selectgroups'] ) && $params['selectgroups'] ? true : false;
1152 - $search_site = isset( $params['search'] ) ? $this->escape( trim( $params['search'] ) ) : null;
2147 + $search_site = isset( $params['search'] ) ? trim( $params['search'] ) : null;
1153 2148 $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
1154 2149 $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
1155 2150 $rowcount = isset( $params['rowcount'] ) ? intval( $params['rowcount'] ) : false;
1156 - $extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null;
2151 + $extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null; // without AND prefix.
1157 2152 $for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
1158 2153 $extra_view = isset( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
1159 2154 $is_staging = isset( $params['is_staging'] ) && 'yes' === $params['is_staging'] ? 'yes' : 'no';
1160 2155 $is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
@@ -1159,11 +2154,16 @@
1159 2154 $is_staging = isset( $params['is_staging'] ) && 'yes' === $params['is_staging'] ? 'yes' : 'no';
1160 2155 $is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
1161 2156 $group_ids = isset( $params['group_id'] ) && ! empty( $params['group_id'] ) ? $params['group_id'] : array();
1162 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';
1163 2159 $is_not = isset( $params['isnot'] ) && ! empty( $params['isnot'] ) ? true : false;
1164 2160 $selected_sites = isset( $params['selected_sites'] ) ? $params['selected_sites'] : array();
1165 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 +
1166 2166 if ( ! is_array( $group_ids ) ) {
1167 2167 $group_ids = array();
1168 2168 }
1169 2169
@@ -1221,19 +2221,46 @@
1221 2221
1222 2222 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
1223 2223 }
1224 2224
1225 - if ( ! empty( $selected_sites ) ) {
1226 - $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 + }
1227 2235 }
1228 2236
1229 - // for searching.
2237 + // Search filtering.
1230 2238 if ( null !== $search_site && '' !== $search_site ) {
1231 - $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 + }
1232 2258 }
1233 2259
1234 - if ( null !== $extraWhere ) {
1235 - $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';
1236 2263 }
1237 2264
1238 2265 if ( ! $for_manager ) {
1239 2266 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
@@ -1248,10 +2275,11 @@
1248 2275 if ( ! empty( $orderBy ) ) {
1249 2276 $orderBy = ' ORDER BY ' . $orderBy;
1250 2277 }
1251 2278
1252 - $join_group = '';
1253 - $where_group = '';
2279 + $join_group = '';
2280 + $where_group = '';
2281 + $having_group = '';
1254 2282
1255 2283 if ( in_array( 'nogroups', $group_ids ) ) {
1256 2284 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1257 2285 $group_ids = array_filter(
@@ -1260,14 +2288,21 @@
1260 2288 return 'nogroups' !== $e;
1261 2289 }
1262 2290 );
1263 2291 if ( ! empty( $group_ids ) ) {
1264 - $groups = implode( ',', $group_ids );
2292 + $groups = implode( ',', $group_ids );
2293 + $groups_count = count( $group_ids );
1265 2294 if ( $is_not ) {
1266 - $where_group = ' AND wpgroup.groupid IS NOT NULL AND wpgroup.groupid NOT IN (' . $groups . ') ';
1267 - // to fix.
1268 - $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 . ') ';
1269 - $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 ';
1270 2305 } else {
1271 2306 $where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
1272 2307 }
1273 2308 } elseif ( $is_not ) {
@@ -1275,18 +2310,26 @@
1275 2310 } else {
1276 2311 $where_group = ' AND wpgroup.groupid IS NULL ';
1277 2312 }
1278 2313 } elseif ( $group_ids ) {
1279 - $groups = implode( ',', $group_ids );
2314 + $groups = implode( ',', $group_ids );
2315 + $groups_count = count( $group_ids );
1280 2316 if ( $is_not ) {
1281 2317 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1282 - $where_group = ' AND ( wpgroup.groupid NOT IN (' . $groups . ') OR wpgroup.groupid IS NULL ) ';
1283 - // to fix.
1284 - $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 . ') ';
1285 - $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 + }
1286 2326 } else {
1287 2327 $join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1288 2328 $where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
2329 + if ( 'and' === $group_logic ) {
2330 + $having_group = 'COUNT(DISTINCT wpgroup.groupid) = ' . $groups_count;
2331 + }
1289 2332 }
1290 2333 }
1291 2334
1292 2335 $select_groups_belong = '';
@@ -1296,9 +2339,16 @@
1296 2339 }
1297 2340
1298 2341 $join_client = '';
1299 2342 $where_client = '';
1300 -
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 + }
1301 2351 if ( in_array( 'noclients', $client_ids ) ) {
1302 2352 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1303 2353 $client_ids = array_filter(
1304 2354 $client_ids,
@@ -1331,8 +2381,89 @@
1331 2381
1332 2382 if ( '' === $join_client ) {
1333 2383 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1334 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 +
1335 2466 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1336 2467 if ( $selectgroups ) {
1337 2468
1338 2469 if ( empty( $join_group ) ) {
@@ -1338,38 +2469,45 @@
1338 2469 if ( empty( $join_group ) ) {
1339 2470 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1340 2471 }
1341 2472
1342 - $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 ' .
1343 2474 $select_groups_belong . ' FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1344 2475 $join_client . ' ' .
1345 - $join_group . '
2476 + $join_group .
2477 + $join_monitors . '
1346 2478 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgroup.groupid = gr.id
1347 2479
1348 2480 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1349 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1350 - WHERE 1 ' . $where . $where_group . $where_client . '
1351 - GROUP BY wp.id, wp_sync.sync_id ' .
2481 + ' . $view_joins . '
2482 + WHERE 1 ' . $where_cache_ids . $where . $where_group . $where_client . $group_by .
1352 2483 $orderBy;
1353 2484 } else {
1354 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, wpclient.name as client_name ' .
2485 + $qry = 'SELECT ' . $select . $view_selects . ', wpclient.name as client_name ' .
1355 2486 $select_groups_belong . ' FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1356 2487 $join_group . ' ' .
1357 - $join_client . '
2488 + $join_client .
2489 + $join_monitors . '
1358 2490 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1359 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1360 - WHERE 1 ' . $where . $where_group . $where_client . '
1361 - GROUP BY wp.id, wp_sync.sync_id ' .
2491 + ' . $view_joins . '
2492 + WHERE 1 ' . $where_cache_ids . $where . $where_group . $where_client . $group_by .
1362 2493 $orderBy;
1363 2494 }
1364 2495
1365 2496 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
1366 - $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;
1367 2501 } elseif ( false !== $rowcount ) {
1368 2502 $qry .= ' LIMIT ' . $rowcount;
1369 2503 }
1370 - //phpcs:ignore
1371 - // 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 +
1372 2510 return $qry;
1373 2511 }
1374 2512
1375 2513 /**
@@ -1512,19 +2650,98 @@
1512 2650 return ' AND 0 ';
1513 2651 }
1514 2652 }
1515 2653
2654 +
1516 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 + /**
1517 2733 * Get child site by id.
1518 2734 *
1519 2735 * @param int $id Child site ID.
1520 2736 * @param array $selectGroups Select groups.
1521 2737 * @param array $extra_view Get extra option fields.
2738 + * @param int $obj OBJECT|ARRAY_A.
1522 2739 *
1523 2740 * @return object|null Database query results or null on failure.
1524 2741 */
1525 - public function get_website_by_id( $id, $selectGroups = false, $extra_view = array() ) {
1526 - 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 );
1527 2744 }
1528 2745
1529 2746 /**
1530 2747 * Get child site by id via SQL.
@@ -1543,24 +2760,35 @@
1543 2760 $extra_view = array( 'favi_icon', 'site_info' );
1544 2761 }
1545 2762
1546 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 +
1547 2775 $where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
1548 2776 if ( $selectGroups ) {
1549 - 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
1550 2778 FROM ' . $this->table_name( 'wp' ) . ' wp
1551 2779 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1552 2780 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1553 2781 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1554 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2782 + ' . $view_joins . '
1555 2783 WHERE wp.id = ' . $id . $where . '
1556 2784 GROUP BY wp.id, wp_sync.sync_id';
1557 2785 }
1558 2786
1559 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
2787 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
1560 2788 FROM ' . $this->table_name( 'wp' ) . ' wp
1561 2789 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1562 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2790 + ' . $view_joins . '
1563 2791 WHERE id = ' . $id . $where;
1564 2792 }
1565 2793
1566 2794 return null;
@@ -1598,11 +2826,22 @@
1598 2826 return ( is_numeric( $e ) && 0 < $e ) ? true : false;
1599 2827 }
1600 2828 );
1601 2829
1602 - $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;
1603 2835
1604 - 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
1605 2844 }
1606 2845
1607 2846 /**
1608 2847 * Get child sites by groups IDs.
@@ -1608,14 +2847,15 @@
1608 2847 * Get child sites by groups IDs.
1609 2848 *
1610 2849 * @param array $ids Groups IDs.
1611 2850 * @param int $userId User ID.
2851 + * @param array $fields array fields .
1612 2852 *
1613 2853 * @return object|null Database query result or null on failure.
1614 2854 *
1615 2855 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
1616 2856 */
1617 - public function get_websites_by_group_ids( $ids, $userId = null ) {
2857 + public function get_websites_by_group_ids( $ids, $userId = null, $fields = array() ) {
1618 2858 if ( empty( $ids ) ) {
1619 2859 return array();
1620 2860 }
1621 2861 if ( ( null === $userId ) && MainWP_System::instance()->is_multi_user() ) {
@@ -1637,23 +2877,124 @@
1637 2877 return is_numeric( $e ) ? true : false;
1638 2878 }
1639 2879 );
1640 2880
1641 - return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . implode( ',', $group_ids ) . ') ' . ( null !== $userId ? ' AND wp.userid = ' . intval( $userId ) : '' ), OBJECT );
2881 + $select = '*';
2882 + if ( ! empty( $fields ) && is_array( $fields ) ) {
2883 + $fields = array_filter( array_map( 'trim', $fields ) );
2884 + if ( $fields ) {
2885 + $select = '';
2886 + foreach ( $fields as $field ) {
2887 + $select .= $this->escape( $field ) . ',';
2888 + }
2889 + $select = rtrim( $select, ',' );
2890 + }
2891 + }
2892 + return $this->wpdb->get_results( 'SELECT ' . $select . ' FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . implode( ',', $group_ids ) . ') ' . ( null !== $userId ? ' AND wp.userid = ' . intval( $userId ) : '' ), OBJECT );
1642 2893 }
1643 2894
1644 2895 /**
1645 2896 * Get child sites by group ID.
1646 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 + *
1647 2938 * @param int $id Group ID.
1648 2939 *
1649 - * @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()
1650 2943 */
1651 - public function get_websites_by_group_id( $id ) {
1652 - 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.
1653 2967 }
1654 2968
2969 +
1655 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 + /**
1656 2997 * Get child sites by group id via SQL.
1657 2998 *
1658 2999 * @param int $id Group ID.
1659 3000 * @param bool $selectgroups Selected groups. Default: false.
@@ -1688,33 +3029,58 @@
1688 3029 }
1689 3030
1690 3031 $where_search = '';
1691 3032 if ( ! empty( $search_site ) ) {
1692 - $search_site = trim( $search_site );
1693 - $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 + );
1694 3041 }
1695 3042
1696 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' );
1697 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 +
1698 3064 if ( MainWP_Utility::ctype_digit( $id ) ) {
1699 3065 $where_allowed = $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
1700 3066 if ( $selectgroups ) {
1701 - $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
1702 3068 FROM ' . $this->table_name( 'wp' ) . ' wp
1703 3069 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1704 3070 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1705 3071 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1706 3072 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1707 - JOIN ' . $this->get_option_view( $extra_view, true ) . ' wp_optionview ON wp.id = wp_optionview.wpid
3073 + ' . $view_joins . '
1708 3074 WHERE wpgroup.groupid = ' . $id . ' ' .
1709 3075 ( empty( $where ) ? '' : ' AND ' . $where ) . $where_allowed . $where_search . '
1710 3076 GROUP BY wp.id, wp_sync.sync_id
1711 3077 ORDER BY ' . $orderBy;
1712 3078 } else {
1713 - $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
1714 3080 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1715 3081 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1716 - JOIN ' . $this->get_option_view( $extra_view, false ) . ' wp_optionview ON wp.id = wp_optionview.wpid
3082 + ' . $view_joins . '
1717 3083 WHERE wpgroup.groupid = ' . $id . ' ' . $where_allowed . $where_search .
1718 3084 ( empty( $where ) ? '' : ' AND ' . $where ) . ' ORDER BY ' . $orderBy;
1719 3085 }
1720 3086 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
@@ -1763,13 +3129,23 @@
1763 3129
1764 3130 $userid = $current_user->ID;
1765 3131 }
1766 3132
1767 - $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
1768 3144 INNER JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1769 3145 JOIN ' . $this->table_name( 'group' ) . ' g ON wpgroup.groupid = g.id
1770 3146 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1771 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
3147 + ' . $view_joins . '
1772 3148 WHERE g.name="' . $this->escape( $groupname ) . '"';
1773 3149 if ( null !== $userid ) {
1774 3150 $sql .= ' AND g.userid = "' . intval( $userid ) . '"';
1775 3151 }
@@ -1784,9 +3160,10 @@
1784 3160 *
1785 3161 * @return string|null Child site IP address or null on failure.
1786 3162 */
1787 3163 public function get_wp_ip( $wpid ) {
1788 - 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.
1789 3166 }
1790 3167
1791 3168 /**
1792 3169 * Add website to the MainWP Dashboard.
@@ -1818,9 +3195,9 @@
1818 3195 }
1819 3196
1820 3197 $groupids = isset( $params['groupids'] ) ? $params['groupids'] : array();
1821 3198 $groupnames = isset( $params['groupnames'] ) ? $params['groupnames'] : array();
1822 - $verifyCertificate = isset( $params['verifyCertificate'] ) ? (int) $params['verifyCertificate'] : 1;
3199 + $verifyCertificate = isset( $params['verifyCertificate'] ) ? (int) $params['verifyCertificate'] : 2;
1823 3200 $uniqueId = isset( $params['uniqueId'] ) ? $params['uniqueId'] : '';
1824 3201 $http_user = isset( $params['http_user'] ) ? $params['http_user'] : null;
1825 3202 $http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
1826 3203 $sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
@@ -1825,13 +3202,33 @@
1825 3202 $http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
1826 3203 $sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
1827 3204 $wpe = isset( $params['wpe'] ) ? $params['wpe'] : 0;
1828 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;
1829 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 +
1830 3223 if ( MainWP_Utility::ctype_digit( $userid ) ) {
1831 3224 if ( '/' !== substr( $url, - 1 ) ) {
1832 3225 $url .= '/';
1833 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 +
1834 3231 $values = array(
1835 3232 'userid' => $userid,
1836 3233 'adminname' => $this->escape( $admin ),
1837 3234 'name' => $this->escape( wp_strip_all_tags( $name ) ),
@@ -1836,9 +3233,9 @@
1836 3233 'adminname' => $this->escape( $admin ),
1837 3234 'name' => $this->escape( wp_strip_all_tags( $name ) ),
1838 3235 'url' => $this->escape( $url ),
1839 3236 'pubkey' => $this->escape( $pubkey ),
1840 - 'privkey' => $this->escape( $privkey ),
3237 + 'privkey' => $this->escape( base64_encode( $en_privkey ) ), // phpcs:ignore -- NOSONAR - trust.
1841 3238 'siteurl' => '',
1842 3239 'ga_id' => '',
1843 3240 'gas_id' => 0,
1844 3241 'offline_checks_last' => 0,
@@ -1849,8 +3246,9 @@
1849 3246 'plugin_upgrades' => '',
1850 3247 'theme_upgrades' => '',
1851 3248 'translation_upgrades' => '',
1852 3249 'securityIssues' => '',
3250 + 'premium_upgrades' => '',
1853 3251 'themes' => '',
1854 3252 'ignored_themes' => '',
1855 3253 'plugins' => '',
1856 3254 'ignored_plugins' => '',
@@ -1862,14 +3260,18 @@
1862 3260 'verify_certificate' => intval( $verifyCertificate ),
1863 3261 'ssl_version' => $sslVersion,
1864 3262 'uniqueId' => $uniqueId,
1865 3263 'mainwpdir' => 0,
1866 - 'http_user' => $http_user,
1867 - 'http_pass' => $http_pass,
3264 + 'http_user' => $encrypted_http_user,
3265 + 'http_pass' => $encrypted_http_pass,
1868 3266 'wpe' => $wpe,
1869 3267 'is_staging' => $isStaging,
1870 3268 );
1871 3269
3270 + if ( null !== $force_use_ipv4 ) {
3271 + $values['force_use_ipv4'] = $force_use_ipv4;
3272 + }
3273 +
1872 3274 $syncValues = array(
1873 3275 'dtsSync' => 0,
1874 3276 'dtsSyncStart' => 0,
1875 3277 'dtsAutomaticSync' => 0,
@@ -1878,11 +3280,14 @@
1878 3280 'extauth' => '',
1879 3281 'sync_errors' => '',
1880 3282 );
1881 3283 if ( $this->wpdb->insert( $this->table_name( 'wp' ), $values ) ) {
1882 - $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 );
1883 3287 $syncValues['wpid'] = $websiteid;
1884 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() ) );
1885 3290 $this->wpdb->insert(
1886 3291 $this->table_name( 'wp_settings_backup' ),
1887 3292 array(
1888 3293 'wpid' => $websiteid,
@@ -1912,8 +3317,26 @@
1912 3317 )
1913 3318 );
1914 3319 }
1915 3320
3321 + // Add primary monitor, use global settings.
3322 + $success = MainWP_DB_Uptime_Monitoring::instance()->update_wp_monitor(
3323 + array(
3324 + 'wpid' => $websiteid,
3325 + 'suburl' => '',
3326 + )
3327 + );
3328 +
3329 + $opt_name = 'mainwp_primary_monitor_repair_attempts_for_wp_' . $websiteid;
3330 +
3331 + if ( ! $success ) {
3332 + update_option( $opt_name, 1 );
3333 + MainWP_Utility::unhide_mainwp_message( 'failed-attempt-create-primary-monitor-' . $websiteid );
3334 + } elseif ( get_option( $opt_name ) ) {
3335 + delete_option( $opt_name );
3336 + }
3337 +
3338 + MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
1916 3339 return $websiteid;
1917 3340 }
1918 3341 }
1919 3342
@@ -1930,14 +3353,15 @@
1930 3353 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
1931 3354 */
1932 3355 public function remove_website( $websiteid ) {
1933 3356 if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
1934 - $nr = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp' ) . ' WHERE id=%d', $websiteid ) );
1935 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
1936 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_sync' ) . ' WHERE wpid=%d', $websiteid ) );
1937 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid=%d', $websiteid ) );
1938 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_status' ) . ' WHERE wpid=%d', $websiteid ) );
1939 -
3357 + $nr = $this->wpdb->delete( $this->table_name( 'wp' ), array( 'id' => $websiteid ) );
3358 + $this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
3359 + $this->wpdb->delete( $this->table_name( 'wp_sync' ), array( 'wpid' => $websiteid ) );
3360 + $this->wpdb->delete( $this->table_name( 'wp_options' ), array( 'wpid' => $websiteid ) );
3361 + MainWP_Encrypt_Data_Lib::remove_key_file( $websiteid );
3362 + MainWP_DB_Uptime_Monitoring::instance()->delete_site_monitors( $websiteid );
3363 + MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
1940 3364 return $nr;
1941 3365 }
1942 3366
1943 3367 return false;
@@ -1952,8 +3376,35 @@
1952 3376 * @return int|boolean The number of rows updated, or false on error.
1953 3377 */
1954 3378 public function update_website_values( $websiteid, $fields ) {
1955 3379 if ( ! empty( $fields ) ) {
3380 + // MWP-1548: encrypt http_user / http_pass at rest if either
3381 + // is present in $fields. Generic-fields updaters (clone
3382 + // flow in extensions-handler, callers that pass arbitrary
3383 + // column subsets) must go through the same fail-closed
3384 + // contract as add_website / update_website.
3385 + if ( array_key_exists( 'http_user', $fields ) ) {
3386 + $encrypted = MainWP_Credential_Storage::encrypt_credential( $fields['http_user'], 'http_user' );
3387 + if ( false === $encrypted ) {
3388 + return false;
3389 + }
3390 + $fields['http_user'] = $encrypted;
3391 + }
3392 + if ( array_key_exists( 'http_pass', $fields ) ) {
3393 + $encrypted = MainWP_Credential_Storage::encrypt_credential( $fields['http_pass'], 'http_pass' );
3394 + if ( false === $encrypted ) {
3395 + return false;
3396 + }
3397 + $fields['http_pass'] = $encrypted;
3398 + }
3399 + // Lock the data stream to prevent other processes from updating at the same time.
3400 + $table_name = esc_sql( $this->table_name( 'wp' ) );
3401 + $sql = $this->wpdb->prepare(
3402 + "SELECT * FROM {$table_name} WHERE id = %d FOR UPDATE", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3403 + $websiteid
3404 + );
3405 + $this->wpdb->get_row( $sql );
3406 +
1956 3407 return $this->wpdb->update( $this->table_name( 'wp' ), $fields, array( 'id' => $websiteid ) );
1957 3408 }
1958 3409
1959 3410 return false;
@@ -1994,13 +3445,11 @@
1994 3445 * @param string $uniqueId Unique security ID.
1995 3446 * @param string $http_user HTTP Basic Authentication username.
1996 3447 * @param string $http_pass HTTP Basic Authentication password.
1997 3448 * @param int $sslVersion SSL Version.
1998 - * @param int $disableChecking Wether or not disable sites status checking.
1999 - * @param int $checkInterval Status checking interval.
2000 3449 * @param bool $disableHealthChecking Disable Site health threshold.
2001 3450 * @param int $healthThreshold Site health threshold.
2002 - * @param int $wpe Is it WP Engine hosted site.
3451 + * @param string $backup_method Primary backup method.
2003 3452 *
2004 3453 * @return boolean ture on success or false on failure.
2005 3454 *
2006 3455 * @uses \MainWP\Dashboard\MainWP_System_Utility::can_edit_website()
@@ -2023,28 +3472,68 @@
2023 3472 $uniqueId = '',
2024 3473 $http_user = null,
2025 3474 $http_pass = null,
2026 3475 $sslVersion = 0,
2027 - $disableChecking = 1,
2028 - $checkInterval = 1440,
2029 3476 $disableHealthChecking = 1,
2030 - $healthThreshold = 80,
2031 - $wpe = 0
3477 + $healthThreshold = 0,
3478 + $backup_method = 'global'
2032 3479 ) {
2033 3480
3481 + $wpe = 0; // going to update when sync.
3482 +
2034 3483 if ( MainWP_Utility::ctype_digit( $websiteid ) && MainWP_Utility::ctype_digit( $userid ) ) {
2035 3484 $website = $this->get_website_by_id( $websiteid );
2036 3485 if ( MainWP_System_Utility::can_edit_website( $website ) ) {
3486 + // MWP-1548: encrypt http_user / http_pass at rest. Same
3487 + // fail-closed contract as add_website -- a non-empty
3488 + // value that fails to encrypt aborts the update so we
3489 + // never overwrite an existing encrypted row with
3490 + // plaintext when the encryption layer is unhealthy.
3491 + $encrypted_http_user = MainWP_Credential_Storage::encrypt_credential( $http_user, 'http_user' );
3492 + $encrypted_http_pass = MainWP_Credential_Storage::encrypt_credential( $http_pass, 'http_pass' );
3493 + if ( false === $encrypted_http_user || false === $encrypted_http_pass ) {
3494 + return false;
3495 + }
2037 3496 // update admin.
2038 - $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 ) );
2039 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp_settings_backup' ) . ' SET archiveFormat = "' . $this->escape( $archiveFormat ) . '" WHERE wpid=%d', $websiteid ) );
3497 + $this->wpdb->update(
3498 + $this->table_name( 'wp' ),
3499 + array(
3500 + 'url' => $url,
3501 + 'name' => wp_strip_all_tags( $name ),
3502 + 'adminname' => $siteadmin,
3503 + 'pluginDir' => $pluginDir,
3504 + 'verify_certificate' => intval( $verifyCertificate ),
3505 + 'ssl_version' => intval( $sslVersion ),
3506 + 'wpe' => intval( $wpe ),
3507 + 'uniqueId' => $uniqueId,
3508 + 'http_user' => $encrypted_http_user,
3509 + 'http_pass' => $encrypted_http_pass,
3510 + 'disable_health_check' => $disableHealthChecking,
3511 + 'health_threshold' => $healthThreshold,
3512 + 'primary_backup_method' => $backup_method,
3513 + ),
3514 + array( 'id' => $websiteid )
3515 + );
3516 + $this->wpdb->update(
3517 + $this->table_name( 'wp_settings_backup' ),
3518 + array( 'archiveFormat' => $archiveFormat ),
3519 + array( 'wpid' => $websiteid )
3520 + );
2040 3521
2041 3522 if ( get_option( 'mainwp_enableLegacyBackupFeature' ) ) {
2042 - $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 ) );
3523 + $this->wpdb->update(
3524 + $this->table_name( 'wp' ),
3525 + array(
3526 + 'maximumFileDescriptorsOverride' => (int) $maximumFileDescriptorsOverride,
3527 + 'maximumFileDescriptorsAuto' => (int) $maximumFileDescriptorsAuto,
3528 + 'maximumFileDescriptors' => (int) $maximumFileDescriptors,
3529 + ),
3530 + array( 'id' => $websiteid )
3531 + );
2043 3532 }
2044 3533
2045 3534 // remove groups.
2046 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
3535 + $this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
2047 3536 // Remove GA stats.
2048 3537 $showErrors = $this->wpdb->hide_errors();
2049 3538
2050 3539 /**
@@ -2091,58 +3580,10 @@
2091 3580
2092 3581 return false;
2093 3582 }
2094 3583
2095 - /**
2096 - * Get websites check updates count.
2097 - *
2098 - * @param int $lasttime_start Lasttime start automatic update.
2099 - *
2100 - * @return int Child sites update count.
2101 - */
2102 - public function get_websites_check_updates_count( $lasttime_start ) {
2103 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
2104 3584
2105 - return $this->wpdb->get_var( 'SELECT count(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE ( wp_sync.dtsAutomaticSyncStart = 0 OR wp_sync.dtsAutomaticSyncStart < ' . intval( $lasttime_start ) . ')' . $where );
2106 - }
2107 -
2108 3585 /**
2109 - * Get child site count where date & time Session sync is smaller then start.
2110 - *
2111 - * @param int $lasttime_start Last time start automatic.
2112 - *
2113 - * @return int Returned child site count.
2114 - */
2115 - public function get_websites_count_where_dts_automatic_sync_smaller_then_start( $lasttime_start ) {
2116 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
2117 -
2118 - return $this->wpdb->get_var( 'SELECT count(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE (( wp_sync.dtsAutomaticSync < wp_sync.dtsAutomaticSyncStart AND wp_sync.dtsAutomaticSyncStart > ' . intval( $lasttime_start ) . ') OR (wp_sync.dtsAutomaticSyncStart = 0)) ' . $where );
2119 - }
2120 -
2121 - /**
2122 - * Get child site last automatic sync date & time.
2123 - *
2124 - * @return string Date and time of last automatic sync.
2125 - */
2126 - public function get_websites_last_automatic_sync() {
2127 - return $this->wpdb->get_var( 'SELECT MAX(wp_sync.dtsAutomaticSync) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid' );
2128 - }
2129 -
2130 - /**
2131 - * Get child sites check updates.
2132 - *
2133 - * @param int $limit Query limit.
2134 - * @param int $lasttime_start Lasttime start automatic update.
2135 - *
2136 - * @return object|null Database query result or null on failure.
2137 - */
2138 - public function get_websites_check_updates( $limit, $lasttime_start ) {
2139 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
2140 -
2141 - return $this->wpdb->get_results( 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid WHERE ( wp_sync.dtsAutomaticSync = 0 OR wp_sync.dtsAutomaticSyncStart = 0 OR wp_sync.dtsAutomaticSyncStart < ' . intval( $lasttime_start ) . ' ) ' . $where . ' ORDER BY wp_sync.dtsAutomaticSyncStart ASC LIMIT ' . $limit, OBJECT );
2142 - }
2143 -
2144 - /**
2145 3586 * Get website update stats via SQL.
2146 3587 *
2147 3588 * @return object|null Database query result of null on failure.
2148 3589 */
@@ -2179,9 +3620,11 @@
2179 3620 public function get_websites_by_url( $url ) {
2180 3621 if ( '/' !== substr( $url, - 1 ) ) {
2181 3622 $url .= '/';
2182 3623 }
2183 - $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 );
3624 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3625 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3626 + $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2184 3627 if ( $results ) {
2185 3628 return $results;
2186 3629 }
2187 3630
@@ -2193,9 +3636,9 @@
2193 3636 $url = str_replace( 'https://', 'https://www.', $url );
2194 3637 $url = str_replace( 'http://', 'http://www.', $url );
2195 3638 }
2196 3639
2197 - $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 );
3640 + $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2198 3641 if ( $results ) {
2199 3642 return $results;
2200 3643 }
2201 3644
@@ -2200,31 +3643,68 @@
2200 3643 }
2201 3644
2202 3645 $url = str_replace( array( 'https://www.', 'http://www.', 'https://', 'http://', 'www.' ), array( '', '', '', '', '' ), $url );
2203 3646
2204 - 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 );
3647 + return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2205 3648 }
2206 3649
2207 3650 /**
2208 - * Get websites offline status.
3651 + * Get recently-synced child sites whose stored url differs from the
3652 + * child-reported siteurl.
2209 3653 *
2210 - * @return array Child site monitoring status.
3654 + * The trailing slash must be normalized in SQL: stored urls always end
3655 + * with a slash while child-reported siteurl values never do, so a plain
3656 + * inequality would match nearly every site.
3657 + *
3658 + * @since 6.2
3659 + *
3660 + * @param int $days Freshness gate: only sites synced within this many days.
3661 + *
3662 + * @return array|object|null Rows with id, url, siteurl or null on failure.
2211 3663 */
2212 - public function get_websites_offline_status_to_send_notice() {
2213 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
2214 - $extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
3664 + public function get_websites_with_url_siteurl_mismatch( $days = 30 ) {
3665 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3666 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3667 + $minimum_sync = time() - intval( $days ) * DAY_IN_SECONDS;
2215 3668
2216 - return $this->wpdb->get_results(
2217 - 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2218 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2219 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2220 - 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.
2221 - $where,
2222 - OBJECT
2223 - );
3669 + return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT wp.id, wp.url, wp.siteurl FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.siteurl <> '' AND TRIM(TRAILING '/' FROM wp.url) <> TRIM(TRAILING '/' FROM wp.siteurl) AND wp_sync.dtsSync >= %d", $minimum_sync ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2224 3670 }
2225 3671
2226 3672 /**
3673 + * Get IDs of child sites that have a non-empty value for a website option.
3674 + *
3675 + * @since 6.2
3676 + *
3677 + * @param string $option_name Website option name.
3678 + * @param int $limit Maximum number of IDs to return.
3679 + *
3680 + * @return array Site IDs.
3681 + */
3682 + public function get_website_ids_with_nonempty_option( $option_name, $limit = 3 ) {
3683 + $options_table = esc_sql( $this->table_name( 'wp_options' ) );
3684 +
3685 + return $this->wpdb->get_col( $this->wpdb->prepare( "SELECT wpid FROM {$options_table} WHERE name = %s AND value <> '' ORDER BY wpid ASC LIMIT %d", $option_name, max( 1, intval( $limit ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3686 + }
3687 +
3688 + /**
3689 + * Get one child site whose stored url exactly matches the given URL,
3690 + * excluding a site ID. Used as the duplicate guard before a URL correction.
3691 + *
3692 + * @since 6.2
3693 + *
3694 + * @param string $url URL to match ( trailing slash enforced ).
3695 + * @param int $exclude_id Site ID to exclude.
3696 + *
3697 + * @return object|null Matching row ( id, url ) or null.
3698 + */
3699 + public function get_website_by_exact_url_excluding_id( $url, $exclude_id ) {
3700 + $url = rtrim( (string) $url, '/' );
3701 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3702 +
3703 + return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT id, url FROM {$wp_table} WHERE TRIM(TRAILING '/' FROM url) = %s AND id <> %d", $url, intval( $exclude_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3704 + }
3705 +
3706 + /**
2227 3707 * Method get_websites_to_notice_health_threshold()
2228 3708 *
2229 3709 * Get websites to notice site health.
2230 3710 *
@@ -2245,14 +3725,23 @@
2245 3725
2246 3726 $where_site_threshold = ' ( wp.health_threshold = 80 AND wp_sync.health_value < 80 ) '; // should-be-improved site health.
2247 3727 $where_site_threshold .= ' OR ( wp.health_threshold = 100 AND wp_sync.health_value >= 80 ) '; // good site health.
2248 3728
2249 - return $this->wpdb->get_results(
2250 - 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2251 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2252 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2253 - 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 ' .
2254 - $where,
3729 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3730 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3731 +
3732 + $view_selects = '';
3733 + $view_joins = '';
3734 +
3735 + $opts_view = $this->get_wp_options_join( $extra_view );
3736 +
3737 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3738 + $view_selects = ',' . $opts_view['selects'];
3739 + $view_joins = $opts_view['joins'];
3740 + }
3741 + return $this->wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $option_view is a validated SQL subquery
3742 + "SELECT wp.*,wp_sync.* {$view_selects} FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid {$view_joins} WHERE wp.disable_health_check <> 1 AND wp.offline_check_result = 1 AND ( {$where_global_threshold} OR{$where_site_threshold} ) AND wp_sync.health_site_noticed = 0 " . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd, the view fragments come from get_wp_options_join(), which allowlists option names, and both threshold fragments are literals picked above.
3743 + $where . ' GROUP BY wp.id ',
2255 3744 OBJECT
2256 3745 );
2257 3746 }
2258 3747
@@ -2260,22 +3749,46 @@
2260 3749 * Get websites offline status.
2261 3750 *
2262 3751 * @return array Sites with offline status.
2263 3752 */
2264 - public function get_websites_offline_check_status() {
3753 + public function get_websites_http_check_status() {
2265 3754 $where = $this->get_sql_where_allow_access_sites( 'wp' );
2266 3755 $extra_view = array( 'settings_notification_emails' );
3756 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
2267 3757
3758 + $view_selects = '';
3759 + $view_joins = '';
3760 +
3761 + $opts_view = $this->get_wp_options_join( $extra_view );
3762 +
3763 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3764 + $view_selects = ',' . $opts_view['selects'];
3765 + $view_joins = $opts_view['joins'];
3766 + }
3767 +
3768 + // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- is a validated SQL subquery.
2268 3769 return $this->wpdb->get_results(
2269 - 'SELECT wp.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2270 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2271 - WHERE wp.disable_status_check <> 1 AND wp.offline_check_result = -1' . // offline checked status.
2272 - $where,
3770 + "SELECT wp.*{$view_selects} FROM {$wp_table} wp {$view_joins}" . ' WHERE wp.suspended = 0 AND wp.http_code_noticed = 0 AND wp.offline_check_result = -1 ' . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the view fragments come from get_wp_options_join(), which allowlists option names.
3771 + $where . ' GROUP BY wp.id ',
2273 3772 OBJECT
2274 3773 );
2275 3774 }
2276 3775
2277 3776 /**
3777 + * Method set_website_noticed_http_check().
3778 + *
3779 + * @param array $site_id The site id .
3780 + *
3781 + * @return void
3782 + */
3783 + public function set_website_noticed_http_check( $site_id = array() ) {
3784 + if ( empty( $site_id ) ) {
3785 + return;
3786 + }
3787 + $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.
3788 + }
3789 +
3790 + /**
2278 3791 * Get DB Sites.
2279 3792 *
2280 3793 * @since 4.6
2281 3794 *
@@ -2282,9 +3795,9 @@
2282 3795 * @param mixed $params params.
2283 3796 *
2284 3797 * @return array $dbwebsites.
2285 3798 */
2286 - public static function get_db_sites( $params = array() ) { // phpcs:ignore -- NOSONAR - complex.
3799 + public function get_db_sites( $params = array() ) { // phpcs:ignore -- NOSONAR - complex.
2287 3800
2288 3801 $dbwebsites = array();
2289 3802
2290 3803 $data_fields = MainWP_System_Utility::get_default_map_site_fields();
@@ -2290,14 +3803,22 @@
2290 3803 $data_fields = MainWP_System_Utility::get_default_map_site_fields();
2291 3804 $data_fields[] = 'verify_certificate';
2292 3805 $data_fields[] = 'client_id';
2293 3806
2294 - $fields = isset( $params['fields'] ) && is_array( $params['fields'] ) ? $params['fields'] : array();
2295 - $sites = isset( $params['sites'] ) && is_array( $params['sites'] ) ? $params['sites'] : array();
2296 - $groups = isset( $params['groups'] ) && is_array( $params['groups'] ) ? $params['groups'] : array();
2297 - $clients = isset( $params['clients'] ) && is_array( $params['clients'] ) ? $params['clients'] : array();
3807 + $fields = isset( $params['fields'] ) && is_array( $params['fields'] ) ? $params['fields'] : array();
3808 + $sites = isset( $params['sites'] ) && is_array( $params['sites'] ) ? $params['sites'] : array();
3809 + $groups = isset( $params['groups'] ) && is_array( $params['groups'] ) ? $params['groups'] : array();
3810 + $clients = isset( $params['clients'] ) && is_array( $params['clients'] ) ? $params['clients'] : array();
3811 + $schema_fields = isset( $params['schema_fields'] ) && is_array( $params['schema_fields'] ) ? $params['schema_fields'] : array(); // since 5.2.
3812 + $selectgroups = isset( $params['selectgroups'] ) && ! empty( $params['selectgroups'] ) ? true : false; // since 5.2.
2298 3813
2299 - if ( is_array( $fields ) ) {
3814 + if ( ! empty( $schema_fields ) ) { // since 5.2.
3815 + foreach ( $schema_fields as $field_name ) {
3816 + if ( ! in_array( $field_name, $data_fields ) ) {
3817 + $data_fields[] = $field_name;
3818 + }
3819 + }
3820 + } elseif ( is_array( $fields ) ) {
2300 3821 foreach ( $fields as $field_indx => $field_name ) {
2301 3822
2302 3823 $get_field = $field_name;
2303 3824 if ( is_numeric( $get_field ) || is_bool( $get_field ) ) { // to compatible fix.
@@ -2312,9 +3833,9 @@
2312 3833
2313 3834 if ( ! empty( $sites ) ) {
2314 3835 foreach ( $sites as $v ) {
2315 3836 if ( MainWP_Utility::ctype_digit( $v ) ) {
2316 - $website = static::instance()->get_website_by_id( $v );
3837 + $website = static::instance()->get_website_by_id( $v, $selectgroups );
2317 3838 if ( empty( $website ) ) {
2318 3839 continue;
2319 3840 }
2320 3841 $dbwebsites[ $website->id ] = MainWP_Utility::map_site( $website, $data_fields );
@@ -2324,9 +3845,9 @@
2324 3845
2325 3846 if ( ! empty( $groups ) ) {
2326 3847 foreach ( $groups as $v ) {
2327 3848 if ( MainWP_Utility::ctype_digit( $v ) ) {
2328 - $websites = static::instance()->query( static::instance()->get_sql_websites_by_group_id( $v ) );
3849 + $websites = static::instance()->query( static::instance()->get_sql_websites_by_group_id( $v, $selectgroups ) );
2329 3850 while ( $websites && ( $website = static::fetch_object( $websites ) ) ) {
2330 3851 $dbwebsites[ $website->id ] = MainWP_Utility::map_site( $website, $data_fields );
2331 3852 }
2332 3853 static::free_result( $websites );
@@ -2334,9 +3855,10 @@
2334 3855 }
2335 3856 }
2336 3857
2337 3858 $params = array(
2338 - 'full_data' => true,
3859 + 'full_data' => true,
3860 + 'selectgroups' => $selectgroups,
2339 3861 );
2340 3862 $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $clients, $params );
2341 3863 if ( $client_sites ) {
2342 3864 foreach ( $client_sites as $website ) {
@@ -2376,9 +3898,9 @@
2376 3898 if ( ! MainWP_System_Utility::can_edit_website( $website ) ) {
2377 3899 return false;
2378 3900 }
2379 3901
2380 - if ( ! mainwp_current_user_have_right( 'site', $websiteid ) ) {
3902 + if ( ! \mainwp_current_user_can( 'site', $websiteid ) ) {
2381 3903 return false;
2382 3904 }
2383 3905
2384 3906 return array(
@@ -2495,18 +4017,28 @@
2495 4017 *
2496 4018 * @return mixed Result
2497 4019 */
2498 4020 public function insert_lookup_item( $item_name, $item_id, $obj_name, $obj_id ) {
4021 + // get_lookup_items() and delete_lookup_items() match item_name as given, so a
4022 + // name that sanitizing would alter is refused instead of being stored under a
4023 + // value the caller can never read back or delete.
4024 + if ( ! is_string( $item_name ) || sanitize_text_field( $item_name ) !== $item_name ) {
4025 + return false;
4026 + }
2499 4027 if ( empty( $item_name ) || empty( $item_id ) || empty( $obj_name ) || empty( $obj_id ) ) {
2500 4028 return false;
2501 4029 }
2502 4030 $data = array(
2503 - 'item_name' => 'cost',
4031 + 'item_name' => $item_name,
2504 4032 'item_id' => $item_id,
2505 4033 'object_name' => $obj_name,
2506 4034 'object_id' => $obj_id,
2507 4035 );
2508 - $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data );
4036 + // wpdb refuses a value past the column length before any SQL runs, and
4037 + // insert_id would still hold the previous insert's id.
4038 + if ( false === $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data ) ) {
4039 + return false;
4040 + }
2509 4041 return $this->wpdb->insert_id; // must return lookup id.
2510 4042 }
2511 4043
2512 4044 /**
@@ -2564,6 +4096,306 @@
2564 4096 $this->wpdb->query( 'DELETE FROM ' . $this->table_name( 'lookup_item_objects' ) . ' WHERE lookup_id IN (' . implode( ',', $lookup_ids ) . ') ' ); //phpcs:ignore -- ok.
2565 4097 return true;
2566 4098 }
2567 4099 return false;
4100 + }
4101 +
4102 +
4103 + /**
4104 + * Insert a new REST API key row and return the credential payload.
4105 + *
4106 + * Returns an array with key_id, user_id, plaintext consumer_key,
4107 + * plaintext consumer_secret, and key_permissions on success. Returns
4108 + * false when the wpdb->insert() call fails (no current user, missing
4109 + * table, schema mismatch, etc.). Callers must check the return type
4110 + * before treating it as an array.
4111 + *
4112 + * @param string $consumer_key Consumer key.
4113 + * @param string $consumer_secret Secret key.
4114 + * @param string $scope scope.
4115 + * @param string $description description.
4116 + * @param int $enabled 1 or 0.
4117 + * @param array $others others.
4118 + *
4119 + * @return array|false Credential payload on success, false on failure.
4120 + */
4121 + public function insert_rest_api_key( $consumer_key, $consumer_secret, $scope, $description, $enabled, $others = array() ) {
4122 + global $current_user;
4123 +
4124 + if ( $current_user ) {
4125 + $user_id = $current_user->ID;
4126 + }
4127 +
4128 + if ( empty( $user_id ) ) {
4129 + return false;
4130 + }
4131 +
4132 + unset( $others ); // Parameter retained for signature compatibility; key_pass/key_type fields are vestigial after MWP-1544 cleanup.
4133 +
4134 + // Hash the consumer_secret with WordPress's password hasher so the
4135 + // value at rest is no longer reversible by a DB-read primitive.
4136 + // The plaintext is returned to the caller below so it can be shown
4137 + // to the admin once at creation time. See MWP-1540.
4138 + $hashed_secret = wp_hash_password( $consumer_secret );
4139 +
4140 + // Created API keys.
4141 + $permissions = in_array( $scope, array( 'read', 'write', 'delete', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
4142 + $inserted = $this->wpdb->insert(
4143 + $this->table_name( 'api_keys' ),
4144 + array(
4145 + 'user_id' => $user_id,
4146 + 'description' => $description,
4147 + 'permissions' => $permissions,
4148 + 'consumer_key' => mainwp_api_hash( $consumer_key ),
4149 + 'consumer_secret' => $hashed_secret,
4150 + 'truncated_key' => substr( $consumer_key, -7 ),
4151 + 'enabled' => $enabled,
4152 + ),
4153 + array(
4154 + '%d',
4155 + '%s',
4156 + '%s',
4157 + '%s',
4158 + '%s',
4159 + '%s',
4160 + '%d',
4161 + ),
4162 + );
4163 +
4164 + // wpdb->insert() returns false on failure. Without this guard the
4165 + // function would still hand back the plaintext consumer_secret plus
4166 + // $wpdb->insert_id, but that insert_id is the LAST successful insert
4167 + // on the connection (typically from earlier in the same request),
4168 + // not this row. The caller's empty-key_id check would pass and the
4169 + // operator would receive a credential that was never persisted.
4170 + // See MWP-1540 PR review feedback.
4171 + if ( false === $inserted ) {
4172 + return false;
4173 + }
4174 +
4175 + return array(
4176 + 'key_id' => $this->wpdb->insert_id,
4177 + 'user_id' => $user_id,
4178 + 'consumer_key' => $consumer_key,
4179 + 'consumer_secret' => $consumer_secret,
4180 + 'key_permissions' => $permissions,
4181 + );
4182 + }
4183 +
4184 + /**
4185 + * Update rest api key.
4186 + *
4187 + * @param int $key_id Consumer key.
4188 + * @param string $scope scope.
4189 + * @param string $description description.
4190 + * @param int $enabled Enabled.
4191 + *
4192 + * @return array
4193 + */
4194 + public function update_rest_api_key( $key_id, $scope, $description, $enabled = 1 ) {
4195 + $permissions = in_array( $scope, array( 'read', 'write', 'delete', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
4196 + return $this->wpdb->update(
4197 + $this->table_name( 'api_keys' ),
4198 + array(
4199 + 'description' => $description,
4200 + 'permissions' => $permissions,
4201 + 'enabled' => $enabled ? 1 : 0,
4202 + ),
4203 + array(
4204 + 'key_id' => $key_id,
4205 + )
4206 + );
4207 + }
4208 +
4209 +
4210 + /**
4211 + * Method is_existed_enabled_rest_key().
4212 + *
4213 + * @return bool result.
4214 + */
4215 + public function is_existed_enabled_rest_key() {
4216 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4217 + $enabled = $this->wpdb->get_row( "SELECT * FROM {$table_name} WHERE enabled = 1 LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4218 + return $enabled ? true : false;
4219 + }
4220 +
4221 + /**
4222 + * Method get_rest_api_key_by().
4223 + *
4224 + * @param int $id To get key.
4225 + *
4226 + * @return array
4227 + */
4228 + public function get_rest_api_key_by( $id ) {
4229 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4230 + return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$table_name} WHERE key_id = %d", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4231 + }
4232 +
4233 + /**
4234 + * Method remove_rest_api_key().
4235 + *
4236 + * @param string $id to delete.
4237 + *
4238 + * @return array
4239 + */
4240 + public function remove_rest_api_key( $id ) {
4241 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4242 + return $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$table_name} WHERE key_id = %s", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4243 + }
4244 +
4245 + /**
4246 + * Method get_rest_api_keys().
4247 + *
4248 + * @return array
4249 + */
4250 + public function get_rest_api_keys() {
4251 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4252 + return $this->wpdb->get_results( "SELECT * FROM {$table_name} ORDER BY key_id DESC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4253 + }
4254 +
4255 +
4256 + /**
4257 + * Update regular process.
4258 + *
4259 + * @param array $data process data.
4260 + * @return mixed
4261 + */
4262 + public function update_regular_process( $data ) {
4263 + if ( isset( $data['process_id'] ) ) {
4264 + $process_id = $data['process_id'];
4265 + unset( $data['process_id'] );
4266 + return $this->wpdb->update( $this->table_name( 'schedule_processes' ), $data, array( 'process_id' => $process_id ) );
4267 + } elseif ( is_array( $data ) && isset( $data['type'] ) && isset( $data['process_slug'] ) ) {
4268 + return $this->wpdb->insert( $this->table_name( 'schedule_processes' ), $data );
4269 + }
4270 + return false;
4271 + }
4272 +
4273 + /**
4274 + * Delete regular process.
4275 + *
4276 + * @param int $process_id Process id.
4277 + * @param int $item_id Item id.
4278 + * @param string $pro_type Process type.
4279 + * @param string $pro_slug Process slug.
4280 + *
4281 + * @return mixed
4282 + */
4283 + public function delete_regular_process( $process_id = false, $item_id = false, $pro_type = false, $pro_slug = false ) {
4284 +
4285 + if ( is_numeric( $process_id ) && ! empty( $process_id ) ) {
4286 + return $this->wpdb->delete(
4287 + $this->table_name( 'schedule_processes' ),
4288 + array(
4289 + 'process_id' => $process_id,
4290 + )
4291 + );
4292 + } elseif ( ! empty( $pro_type ) || ! empty( $pro_slug ) ) {
4293 +
4294 + $data = array();
4295 +
4296 + if ( ! empty( $pro_type ) ) {
4297 + $data['type'] = $pro_type;
4298 + }
4299 +
4300 + if ( ! empty( $pro_slug ) ) {
4301 + $data['process_slug'] = $pro_slug;
4302 + }
4303 +
4304 + if ( ! empty( $item_id ) ) {
4305 + $data['item_id'] = $item_id;
4306 + }
4307 + // Bulk delete.
4308 + return $this->wpdb->delete( $this->table_name( 'schedule_processes' ), $data );
4309 + }
4310 + return false;
4311 + }
4312 +
4313 + /**
4314 + * Method get_regular_process_by_item_id_type_slug
4315 + *
4316 + * @param integer $item_id item id.
4317 + * @param string $type type.
4318 + * @param string $process_slug process slug.
4319 + *
4320 + * @return mixed result
4321 + */
4322 + public function get_regular_process_by_item_id_type_slug( $item_id, $type, $process_slug ) {
4323 + $table_name = esc_sql( $this->table_name( 'schedule_processes' ) );
4324 + return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT pr.* FROM {$table_name} pr WHERE pr.item_id = %d AND pr.type = %s AND pr.process_slug = %s", $item_id, $type, $process_slug ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
4325 + }
4326 +
4327 + /**
4328 + * Log SQL queries for debugging via hook.
4329 + *
4330 + * This method provides a structured way to log SQL queries for development/debugging.
4331 + * It does NOT use error_log() directly - instead, it fires the `mainwp_log_system_query`
4332 + * action hook, allowing external listeners (logging plugins, debug tools) to handle
4333 + * the log output appropriately.
4334 + *
4335 + * To enable query logging:
4336 + * 1. Set `$params['dev_log_query'] = 1` when calling database methods
4337 + * 2. Add a listener to the `mainwp_log_system_query` action hook
4338 + *
4339 + * Example listener:
4340 + * ```php
4341 + * add_action( 'mainwp_log_system_query', function( $params, $sql, $caller ) {
4342 + * error_log( 'MainWP Query: ' . $sql );
4343 + * }, 10, 3 );
4344 + * ```
4345 + *
4346 + * @param array $params Query parameters. Set 'dev_log_query' to enable logging.
4347 + * @param string $sql The SQL query string.
4348 + * @param mixed $caller Instance of caller class (optional).
4349 + * @return void
4350 + */
4351 + public function log_system_query( $params, $sql, $caller = false ) {
4352 + $params = apply_filters( 'mainwp_log_system_query_params', $params, $sql, $caller );
4353 + if ( is_array( $params ) && ! empty( $params['dev_log_query'] ) && ! empty( $sql ) ) {
4354 + do_action( 'mainwp_log_system_query', $params, $sql, $caller );
4355 + }
4356 + }
4357 +
4358 + /**
4359 + * Method get_core_tables().
4360 + *
4361 + * Returns the list of core tables used by MainWP for validation data integrity and other purposes.
4362 + *
4363 + * @return array Core tables info
4364 + */
4365 + public function get_core_tables() {
4366 +
4367 + $core_tables = array(
4368 + $this->table_name( 'wp_clients' ),
4369 + $this->table_name( 'wp_clients_fields' ),
4370 + $this->table_name( 'wp_clients_field_values' ),
4371 + $this->table_name( 'wp_clients_contacts' ),
4372 + $this->table_name( 'monitors' ),
4373 + $this->table_name( 'monitor_heartbeat' ),
4374 + $this->table_name( 'monitor_stat_hourly' ),
4375 + $this->table_name( 'wp' ),
4376 + $this->table_name( 'wp_sync' ),
4377 + $this->table_name( 'wp_options' ),
4378 + $this->table_name( 'wp_settings_backup' ),
4379 + $this->table_name( 'users' ),
4380 + $this->table_name( 'wp_status' ),
4381 + $this->table_name( 'group' ),
4382 + $this->table_name( 'wp_group' ),
4383 + $this->table_name( 'lookup_item_objects' ),
4384 + $this->table_name( 'wp_backup_progress' ),
4385 + $this->table_name( 'wp_backup' ),
4386 + $this->table_name( 'api_keys' ),
4387 + $this->table_name( 'action_log' ),
4388 + $this->table_name( 'request_log' ),
4389 + $this->table_name( 'schedule_processes' ),
4390 + $this->table_name( 'cost_tracker' ),
4391 + );
4392 +
4393 + if ( ! defined( 'MAINWP_MODULE_LOG_ENABLED' ) || MAINWP_MODULE_LOG_ENABLED ) {
4394 + $core_tables[] = $this->table_name( 'wp_logs' );
4395 + $core_tables[] = $this->table_name( 'wp_logs_meta' );
4396 + $core_tables[] = $this->table_name( 'wp_logs_meta_archive' );
4397 + }
4398 +
4399 + return apply_filters( 'mainwp_database_core_tables', $core_tables );
2568 4400 }
2569 4401 }