PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / trunk
MainWP Dashboard: Self-hosted WordPress Management for Agencies vtrunk
6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 5.2 5.2.1 5.2.2 5.3 All 153 releases
← All changes | class/class-mainwp-db.php +1387 -237 5.3 → trunk View file →
@@ -8,8 +8,13 @@
8 8 */
9 9
10 10 namespace MainWP\Dashboard;
11 11
12 +// Exit if accessed directly.
13 +if ( ! defined( 'ABSPATH' ) ) {
14 + exit;
15 +}
16 +
12 17 /**
13 18 * Class MainWP_DB
14 19 *
15 20 * @package MainWP\Dashboard
@@ -78,10 +83,41 @@
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 *
118 + * @compatible function.
119 + *
84 120 * @param array $fields Extra option fields.
85 121 * @param string $view_query view query.
86 122 *
87 123 * @return array wp_options view.
@@ -95,9 +131,9 @@
95 131 $view = '(SELECT intwp.id AS wpid ';
96 132
97 133 $included_opts = array();
98 134
99 - if ( empty( $fields ) || 'default' === $view_query ) {
135 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
100 136 $view .= ',(SELECT recent_comments.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_comments WHERE recent_comments.wpid = intwp.id AND recent_comments.name = "recent_comments" LIMIT 1) AS recent_comments,
101 137 (SELECT recent_posts.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_posts WHERE recent_posts.wpid = intwp.id AND recent_posts.name = "recent_posts" LIMIT 1) AS recent_posts,
102 138 (SELECT recent_pages.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_pages WHERE recent_pages.wpid = intwp.id AND recent_pages.name = "recent_pages" LIMIT 1) AS recent_pages,
103 139 (SELECT phpversion.value FROM ' . $this->table_name( 'wp_options' ) . ' phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = "phpversion" LIMIT 1) AS phpversion,
@@ -117,8 +153,10 @@
117 153 if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
118 154 $fields[] = 'cust_site_icon_info';
119 155 }
120 156
157 + $fields = $this->filter_safe_option_names( $fields );
158 +
121 159 if ( is_array( $fields ) ) {
122 160 foreach ( $fields as $field ) {
123 161 if ( empty( $field ) ) {
124 162 continue;
@@ -126,9 +164,9 @@
126 164 if ( in_array( $field, $included_opts ) ) {
127 165 continue;
128 166 }
129 167 $view .= ', ';
130 - $view .= '(SELECT ' . $this->escape( $field ) . '.value FROM ' . $this->table_name( 'wp_options' ) . ' ' . $this->escape( $field ) . ' WHERE ' . $this->escape( $field ) . '.wpid = intwp.id AND ' . $this->escape( $field ) . '.name = "' . $this->escape( $field ) . '" LIMIT 1) AS ' . $this->escape( $field );
168 + $view .= '(SELECT `' . $this->escape( $field ) . '`.value FROM ' . $this->table_name( 'wp_options' ) . ' `' . $this->escape( $field ) . '` WHERE `' . $this->escape( $field ) . '`.wpid = intwp.id AND `' . $this->escape( $field ) . '`.name = "' . $this->escape( $field ) . '" LIMIT 1) AS `' . $this->escape( $field ) . '`';
131 169 }
132 170 }
133 171
134 172 $view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
@@ -135,11 +173,150 @@
135 173
136 174 return $view;
137 175 }
138 176
177 + /**
178 + * Method get_wp_options_join().
179 + *
180 + * @param array $fields Extra option fields.
181 + * @param string $view_query view query.
182 + * @param array<string, mixed> $params Additional parameters.
139 183
184 + *
185 + * NOTE: This method is used to improve the performance of wp_options view, as the old view with subquery for each field will cause performance issue when there are many sites, and this method will generate the SQL with LEFT JOIN which will be much faster than subquery.
186 + * The alias of wp table must be 'wp' to make sure the LEFT JOIN works, and the alias of wp_options table will be 'owp' + key of field in fields array, and the join condition is owp.wpid = wp.id AND owp.name = field name, and the select field is owp.value AS field name.
187 + *
188 + * @since 6.0.8
189 + *
190 + * @return array wp_options view.
191 + */
192 + public function get_wp_options_join( $fields = array(), $view_query = 'default', $params = array() ) {
140 193
194 + if ( ! is_array( $fields ) ) {
195 + $fields = array();
196 + }
197 +
198 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
199 + $fields[] = 'recent_comments';
200 + $fields[] = 'recent_posts';
201 + $fields[] = 'recent_pages';
202 + $fields[] = 'phpversion';
203 + $fields[] = 'added_timestamp';
204 + $fields[] = 'wp_upgrades';
205 + }
206 +
207 + if ( ! in_array( 'signature_algo', $fields ) ) {
208 + $fields[] = 'signature_algo';
209 + }
210 +
211 + if ( ! in_array( 'verify_method', $fields ) ) {
212 + $fields[] = 'verify_method';
213 + }
214 +
215 + if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
216 + $fields[] = 'cust_site_icon_info';
217 + }
218 +
219 + $fields = array_values( array_unique( array_filter( $this->filter_safe_option_names( $fields ) ) ) );
220 +
221 + $tbl_wp_options = $this->table_name( 'wp_options' );
222 +
223 + $selects = array();
224 + $joins = array();
225 +
226 + foreach ( $fields as $name ) {
227 + $alias = 'owp_' . preg_replace( '/[^a-z0-9_]/i', '_', $name );
228 +
229 + // SELECT.
230 + $selects[] = "{$alias}.value AS `" . $this->escape( $name ) . '`';
231 +
232 + // JOIN.
233 + $joins[] = "LEFT JOIN {$tbl_wp_options} {$alias}
234 + ON {$alias}.wpid = wp.id
235 + AND {$alias}.name = '" . $this->escape( $name ) . "'";
236 + }
237 +
238 + return array(
239 + 'selects' => implode( ', ', $selects ),
240 + 'joins' => implode( "\n", $joins ),
241 + );
242 + }
243 +
141 244 /**
245 + * Method get_wp_options_view().
246 + *
247 + * @param array $fields Extra option fields.
248 + * @param string $view_query view query.
249 + * @param int $siteid Site id.
250 + *
251 + * @return string SQL subquery for wp_options view.
252 + */
253 + public function get_wp_options_view( $fields = array(), $view_query = 'default', $siteid = 0 ) {
254 +
255 + if ( ! is_array( $fields ) ) {
256 + $fields = array();
257 + }
258 +
259 + $where_site = '';
260 +
261 + if ( ! empty( $siteid ) && is_numeric( $siteid ) ) {
262 + $where_site = ' AND wpid = ' . intval( $siteid ) . ' ';
263 + }
264 +
265 + $view = '(SELECT wpid ';
266 +
267 + $included_opts = array();
268 +
269 + if ( empty( $fields ) || 'default' === $view_query || 'manage_site' === $view_query ) {
270 + $view .= ',
271 + MAX(CASE WHEN name = "recent_comments" THEN value END) AS recent_comments,
272 + MAX(CASE WHEN name = "recent_posts" THEN value END) AS recent_posts,
273 + MAX(CASE WHEN name = "recent_pages" THEN value END) AS recent_pages,
274 + MAX(CASE WHEN name = "phpversion" THEN value END) AS phpversion,
275 + MAX(CASE WHEN name = "added_timestamp" THEN value END) AS added_timestamp,
276 + MAX(CASE WHEN name = "wp_upgrades" THEN value END) AS wp_upgrades ';
277 + $included_opts = array( 'recent_comments', 'recent_posts', 'recent_pages', 'phpversion', 'added_timestamp', 'wp_upgrades' );
278 + }
279 +
280 + if ( ! in_array( 'signature_algo', $fields ) ) {
281 + $fields[] = 'signature_algo';
282 + }
283 +
284 + if ( ! in_array( 'verify_method', $fields ) ) {
285 + $fields[] = 'verify_method';
286 + }
287 +
288 + if ( ! in_array( 'cust_site_icon_info', $fields, true ) ) {
289 + $fields[] = 'cust_site_icon_info';
290 + }
291 +
292 + $fields = $this->filter_safe_option_names( $fields );
293 +
294 + if ( is_array( $fields ) ) {
295 + foreach ( $fields as $field ) {
296 + if ( empty( $field ) ) {
297 + continue;
298 + }
299 + if ( in_array( $field, $included_opts ) ) {
300 + continue;
301 + }
302 + $view .= ', ';
303 + $view .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
304 +
305 + $included_opts[] = $this->escape( $field );
306 + }
307 + }
308 +
309 + $view .= ' FROM ' . $this->table_name( 'wp_options' ) .
310 + " WHERE 1 {$where_site} AND name IN ('" . implode( "','", $included_opts ) . "')
311 + GROUP BY wpid ) ";
312 +
313 + return $view;
314 + }
315 +
316 +
317 +
318 + /**
142 319 * Get SQL to get child sites for current user.
143 320 *
144 321 * @since 5.2.
145 322 * @param array $params other params.
@@ -150,8 +327,16 @@
150 327
151 328 if ( ! is_array( $params ) ) {
152 329 $params = array();
153 330 }
331 +
332 + /**
333 + * The hook mainwp_get_sql_websites_by_params
334 + *
335 + * @since 5.5
336 + */
337 + $params = apply_filters( 'mainwp_get_sql_websites_by_params', $params );
338 +
154 339 $view = isset( $params['view'] ) ? $params['view'] : 'default';
155 340 $with_clients = isset( $params['with_clients'] ) && $params['with_clients'] ? true : false;
156 341
157 342 // legacy support.
@@ -157,9 +342,10 @@
157 342 // legacy support.
158 343 $selectgroups = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
159 344 $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
160 345 $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
161 - $rowcount = isset( $params['rowcount'] ) && $params['rowcount'] ? true : false;
346 + $rowcount = isset( $params['rowcount'] ) ? (int) $params['rowcount'] : false;
347 + $count_sql = isset( $params['count_sql'] ) && $params['count_sql'] ? true : false;
162 348 $extraWhere = isset( $params['where'] ) ? $params['where'] : null; // NOTE: without 'AND' at begining and ending of 'where'.
163 349 $for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
164 350 $others_fields = isset( $params['others_fields'] ) && is_array( $params['others_fields'] ) ? $params['others_fields'] : array( 'favi_icon' );
165 351 $is_staging = isset( $params['is_staging'] ) && in_array( $params['is_staging'], array( 'yes', 'no' ) ) ? $params['is_staging'] : 'no';
@@ -164,8 +350,10 @@
164 350 $others_fields = isset( $params['others_fields'] ) && is_array( $params['others_fields'] ) ? $params['others_fields'] : array( 'favi_icon' );
165 351 $is_staging = isset( $params['is_staging'] ) && in_array( $params['is_staging'], array( 'yes', 'no' ) ) ? $params['is_staging'] : 'no';
166 352 $limit = isset( $params['limit'] ) ? intval( $params['limit'] ) : '';
167 353
354 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
355 +
168 356 $s = isset( $params['s'] ) ? $params['s'] : '';
169 357 $exclude = isset( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
170 358 $include = isset( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
171 359 $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
@@ -171,8 +359,14 @@
171 359 $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
172 360 $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
173 361 $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
174 362
363 + // This parameter is used to enable caching in certain cases.
364 + $_included_cache_ids = isset( $params['_included_cache_ids'] ) ? wp_parse_id_list( $params['_included_cache_ids'] ) : array();
365 +
366 + $select_wpfields = isset( $params['select_wp_fields'] ) ? wp_parse_list( $params['select_wp_fields'] ) : '';
367 + $select_syncfields = isset( $params['select_sync_fields'] ) ? wp_parse_list( $params['select_sync_fields'] ) : '';
368 +
175 369 $where = '';
176 370
177 371 if ( ! empty( $extraWhere ) ) {
178 372 $where .= ' AND ' . $extraWhere;
@@ -190,9 +384,17 @@
190 384 $connected_sql = ' AND wp_sync.sync_errors <> "" ';
191 385 }
192 386
193 387 if ( ! empty( $s ) ) {
194 - $where .= ' AND ( wp.id LIKE "%' . $this->escape( $s ) . '%" OR wp.name LIKE "%' . $this->escape( $s ) . '%" OR wp.url LIKE "%' . $this->escape( $s ) . '%" ) ';
388 + $s = trim( $s );
389 + // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
390 + $like_pattern = '%' . $this->wpdb->esc_like( $s ) . '%';
391 + $where .= $this->wpdb->prepare(
392 + ' AND ( wp.id LIKE %s OR wp.name LIKE %s OR wp.url LIKE %s ) ',
393 + $like_pattern,
394 + $like_pattern,
395 + $like_pattern
396 + );
195 397 }
196 398
197 399 if ( ! empty( $exclude ) ) {
198 400 $where .= ' AND wp.id NOT IN (' . implode( ',', $exclude ) . ') ';
@@ -201,14 +403,35 @@
201 403 if ( ! empty( $include ) ) {
202 404 $where .= ' AND wp.id IN (' . implode( ',', $include ) . ') ';
203 405 }
204 406
407 + if ( ! empty( $_included_cache_ids ) ) {
408 + $where .= ' AND wp.id IN (' . implode( ',', $_included_cache_ids ) . ') ';
409 + }
410 +
411 + $specific_wp_fields = '';
412 + if ( ! empty( $select_wpfields ) ) {
413 + foreach ( $select_wpfields as $_field ) {
414 + $specific_wp_fields .= 'wp.' . $this->escape( $_field ) . ',';
415 + }
416 + $specific_wp_fields = rtrim( $specific_wp_fields, ',' );
417 + }
418 +
419 + $specific_sync_fields = '';
420 + if ( ! empty( $select_syncfields ) ) {
421 + foreach ( $select_syncfields as $_field ) {
422 + $specific_sync_fields .= 'wp_sync.' . $this->escape( $_field ) . ',';
423 + }
424 + $specific_sync_fields = rtrim( $specific_sync_fields, ',' );
425 + }
426 +
205 427 // any, connected, disconnected, suspended, available_update.
206 428 if ( ! empty( $status ) && is_array( $status ) && ! in_array( 'any', $status ) ) {
207 429 $status_conds = array();
208 430 if ( in_array( 'available_update', $status ) ) {
209 431 $available_sql = " ( wp.plugin_upgrades <> '' && wp.plugin_upgrades <> '[]' ) OR ( wp.theme_upgrades <> '' && wp.theme_upgrades <> '[]' ) OR ( wp.translation_upgrades <> '' && wp.translation_upgrades <> '[]' ) OR ( wp.premium_upgrades <> '' && wp.premium_upgrades <> '[]' ) ";
210 - $results = $this->wpdb->get_results( 'SELECT wpid FROM ' . $this->table_name( 'wp_options' ) . " WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]' " );
432 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
433 + $results = $this->wpdb->get_results( "SELECT wpid FROM {$table_name} WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the rest of the query is a literal.
211 434 if ( $results ) {
212 435 $wp_ids = array();
213 436 foreach ( $results as $item ) {
214 437 if ( ! empty( $item->wpid ) ) {
@@ -223,9 +446,9 @@
223 446 $status_conds[] = ' ( ' . $available_sql . ') ';
224 447 }
225 448
226 449 if ( in_array( 'connected', $status ) ) {
227 - $status_conds[] = ' ( wp_sync.sync_errors == "" ) ';
450 + $status_conds[] = ' ( wp_sync.sync_errors = "" ) ';
228 451 }
229 452 if ( in_array( 'disconnected', $status ) ) {
230 453 $status_conds[] = " wp_sync.sync_errors <> '' ";
231 454 }
@@ -236,8 +459,11 @@
236 459
237 460 if ( ! empty( $status_conds ) ) {
238 461 $where .= ' AND ( ' . implode( ' OR ', $status_conds ) . ' ) ';
239 462 }
463 + if ( in_array( 'unsuspended', $status ) && ! in_array( 'suspended', $status ) ) { // to sure not conflict the suspended status.
464 + $where .= ' AND wp.suspended = 0 ';
465 + }
240 466 }
241 467
242 468 if ( ! empty( $page ) && ! empty( $per_page ) ) {
243 469 $limit = ( $page - 1 ) * $per_page . ',' . $per_page;
@@ -251,9 +477,10 @@
251 477 $join_clients = '';
252 478
253 479 if ( $with_clients ) {
254 480 $select_clients = ', wpclient.name as client_name ';
255 - $join_clients = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
481 + $clients_table = esc_sql( $this->table_name( 'wp_clients' ) );
482 + $join_clients = " LEFT JOIN {$clients_table} wpclient ON wp.client_id = wpclient.client_id ";
256 483 }
257 484
258 485 $base_fields = array(
259 486 'wp.id',
@@ -268,9 +495,8 @@
268 495 'wp.privkey',
269 496 'wp.pubkey',
270 497 'wp.wpe',
271 498 'wp.is_staging',
272 - 'wp.pubkey',
273 499 'wp.force_use_ipv4',
274 500 'wp.siteurl',
275 501 'wp.suspended',
276 502 'wp.mainwpdir',
@@ -282,8 +508,9 @@
282 508 'wp.userid',
283 509 'wp.plugins',
284 510 'wp.themes',
285 511 'wp.offline_check_result', // 1 - online, -1 offline.
512 + 'wp.automatic_update',
286 513 );
287 514
288 515 $select = ' wp.*,wp_sync.* ';
289 516 if ( 'base_view' === $view ) {
@@ -299,35 +526,72 @@
299 526 );
300 527 $select = implode( ',', array_merge( $updates_fields, $base_fields ) );
301 528 }
302 529
303 - $select .= ',wp_optionview.* '; // to fix bug.
530 + $view_selects = '';
531 + $view_joins = '';
304 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 +
305 544 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
306 545 if ( $selectgroups ) {
307 - $qry = 'SELECT ' . $select . ', GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids, GROUP_CONCAT(gr.color ORDER BY gr.name SEPARATOR ",") as wpgroups_colors,
308 - ' . $select_clients . '
309 - FROM ' . $this->table_name( 'wp' ) . ' wp
546 + $select_qry = 'SELECT ' . $select . $view_selects . ', GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids, GROUP_CONCAT(gr.color ORDER BY gr.name SEPARATOR ",") as wpgroups_colors ' .
547 + $select_clients;
548 +
549 + $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
310 550 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
311 551 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
312 552 ' . $join_clients . '
313 553 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
314 - JOIN ' . $this->get_option_view_by( $view, $others_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid
315 - WHERE 1 ' . $where . $connected_sql . '
554 + ' . $view_joins . '
555 + WHERE 1 ' . $where . $connected_sql;
556 + $group_qry = '
316 557 GROUP BY wp.id, wp_sync.sync_id
317 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;
318 576 } else {
319 - $qry = 'SELECT ' . $select .
320 - $select_clients . '
321 - FROM ' . $this->table_name( 'wp' ) . ' wp
577 + $select_qry = 'SELECT ' . $select . $view_selects . $select_clients;
578 + $qry = ' FROM ' . $this->table_name( 'wp' ) . ' wp
322 579 ' . $join_clients . '
323 580 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
324 - JOIN ' . $this->get_option_view_by( $view, $others_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid
325 - WHERE 1 ' . $where . $connected_sql . '
581 + ' . $view_joins . '
582 + WHERE 1 ' . $where . $connected_sql;
583 + $group_qry = '
326 584 GROUP BY wp.id, wp_sync.sync_id
327 585 ORDER BY ' . $orderBy;
328 586 }
329 587
588 + if ( $count_sql ) {
589 + return 'SELECT COUNT(DISTINCT wp.id) ' . $qry;
590 + }
591 +
592 + $qry = $select_qry . $qry . $group_qry;
593 +
330 594 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
331 595 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
332 596 } elseif ( false !== $rowcount ) {
333 597 $qry .= ' LIMIT ' . $rowcount;
@@ -343,14 +607,96 @@
343 607 $qry .= ' LIMIT ' . intval( $start ) . ', ' . intval( $limit_sites );
344 608 }
345 609 }
346 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 ) );
347 615 return $qry;
348 616 }
349 617
350 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 + /**
351 695 * Get wp_options database table view.
352 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 + *
353 699 * @param array $view Option view.
354 700 * @param array $other_fields Extra option fields.
355 701 *
356 702 * @return array wp_options view.
@@ -365,16 +711,23 @@
365 711 'added_timestamp',
366 712 'wp_upgrades',
367 713 );
368 714
715 + $fields = array();
716 +
369 717 if ( 'updates_view' === $view ) {
370 718 $fields = array(
371 719 'wp_upgrades',
372 720 'ignored_wp_upgrades',
721 + 'ignored_trans_updates',
373 722 );
374 723 } elseif ( in_array( $view, array( 'simple_view', 'base_view', 'monitor_view', 'ping_view', 'uptime_notification' ) ) ) {
375 724 $fields = array();
376 - } else {
725 + if ( 'monitor_view' === $view || 'ping_view' === $view ) {
726 + $fields[] = 'health_site_status';
727 + $fields[] = 'bypass_cache';
728 + }
729 + } elseif ( 'custom_view' !== $view ) {
377 730 $fields = $default;
378 731 }
379 732
380 733 if ( is_array( $other_fields ) && ! empty( $other_fields ) ) {
@@ -380,17 +733,21 @@
380 733 if ( is_array( $other_fields ) && ! empty( $other_fields ) ) {
381 734 $fields = array_unique( array_merge( $fields, $other_fields ) );
382 735 }
383 736
384 - $view_query = '(SELECT intwp.id AS wpid ';
737 + $view_query = '(SELECT wpid ';
385 738
386 - if ( ! in_array( 'signature_algo', $fields ) ) {
387 - $fields[] = 'signature_algo';
739 + if ( 'custom_view' !== $view ) {
740 + if ( ! in_array( 'signature_algo', $fields ) ) {
741 + $fields[] = 'signature_algo';
742 + }
743 +
744 + if ( ! in_array( 'verify_method', $fields ) ) {
745 + $fields[] = 'verify_method';
746 + }
388 747 }
389 748
390 - if ( ! in_array( 'verify_method', $fields ) ) {
391 - $fields[] = 'verify_method';
392 - }
749 + $fields = $this->filter_safe_option_names( $fields );
393 750
394 751 foreach ( $fields as $field ) {
395 752
396 753 if ( empty( $field ) ) {
@@ -397,12 +754,14 @@
397 754 continue;
398 755 }
399 756
400 757 $view_query .= ', ';
401 - $view_query .= '(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 );
758 + $view_query .= 'MAX(CASE WHEN name = "' . $this->escape( $field ) . '" THEN value END) AS `' . $this->escape( $field ) . '`';
402 759 }
403 760
404 - $view_query .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
761 + $view_query .= ' FROM ' . $this->table_name( 'wp_options' ) .
762 + " WHERE name IN ('" . implode( "','", $fields ) . "')
763 + GROUP BY wpid ) ";
405 764
406 765 return $view_query;
407 766 }
408 767
@@ -428,18 +787,20 @@
428 787 *
429 788 * @return array $connected_sites Array of connected sites.
430 789 */
431 790 public function get_connected_websites( $sites_ids = false ) {
432 - $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' ) );
433 794
434 - $sql = 'SELECT wp.*,wp_sync.*
435 - FROM ' . $this->table_name( 'wp' ) . ' wp
436 - 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
437 798 ON wp.id = wp_sync.wpid
438 - 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 = \"\") " .
439 800 $where;
440 801
441 - $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()
442 803 $connected_sites = array();
443 804 if ( $websites ) {
444 805 foreach ( $websites as $website ) {
445 806
@@ -464,18 +825,20 @@
464 825 *
465 826 * @return array $disc_sites Array of disonnected sites.
466 827 */
467 828 public function get_disconnected_websites( $sites_ids = false ) {
468 - $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' ) );
469 832
470 - $sql = 'SELECT wp.*,wp_sync.*
471 - FROM ' . $this->table_name( 'wp' ) . ' wp
472 - 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
473 836 ON wp.id = wp_sync.wpid
474 - 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 <> \"\") " .
475 838 $where;
476 839
477 - $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()
478 841 $disc_sites = array();
479 842 if ( $websites ) {
480 843 foreach ( $websites as $website ) {
481 844
@@ -501,8 +864,12 @@
501 864 *
502 865 * @return int Child site count.
503 866 *
504 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.
505 872 */
506 873 public function get_websites_count( $userId = null, $all_access = false ) {
507 874 static $total_sites;
508 875 if ( null !== $total_sites ) { // NOSONAR -- static value.
@@ -518,15 +885,16 @@
518 885 global $current_user;
519 886
520 887 $userId = $current_user->ID;
521 888 }
522 - $where = ( null === $userId ? '' : ' wp.userid = ' . $userId );
889 + $where = ( null === $userId ? '' : ' wp.userid = ' . intval( $userId ) );
523 890 if ( ! $all_access ) {
524 891 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
525 892 }
526 - $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}";
527 895
528 - $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()
529 897 $total_sites = $total;// NOSONAR -- static value.
530 898 return $total;
531 899 }
532 900
@@ -554,9 +922,9 @@
554 922
555 923 $select_stats = ' ( SELECT COUNT(wp.id) as count_all ';
556 924 if ( ! empty( $params['count_disconnected'] ) ) {
557 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 ';
558 - $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 ';
559 927 }
560 928 if ( ! empty( $params['count_suspended'] ) ) {
561 929 $select_stats .= ',( SELECT COUNT(wp_suspended.id) FROM ' . $this->table_name( 'wp' ) . ' wp_suspended WHERE wp_suspended.suspended = 1 ) as count_suspended ';
562 930 }
@@ -605,9 +973,10 @@
605 973 } else {
606 974 return false;
607 975 }
608 976
609 - $value = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $site_id ) );
977 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
978 + $value = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT value FROM {$table_name} WHERE wpid = %d AND name = %s", $site_id, $option ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
610 979
611 980 if ( null === $value && null !== $default_value ) {
612 981 return $default_value;
613 982 }
@@ -683,13 +1052,12 @@
683 1052 if ( empty( $get_options ) ) {
684 1053 return $arr_options; // all options.
685 1054 }
686 1055
687 - $options_name = implode( "','", $get_options );
688 - $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.
689 1059
690 - $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 ) );
691 -
692 1060 $fill_options = array(
693 1061 'primary_lasttime_backup',
694 1062 );
695 1063
@@ -724,9 +1092,10 @@
724 1092 } else {
725 1093 $site_id = $website->id;
726 1094 }
727 1095
728 - $rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $site_id ) );
1096 + $table_name = esc_sql( $this->table_name( 'wp_options' ) );
1097 + $rslt = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT name FROM {$table_name} WHERE wpid = %d AND name = %s", $site_id, $option ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
729 1098 if ( empty( $rslt ) ) {
730 1099 $this->wpdb->insert(
731 1100 $this->table_name( 'wp_options' ),
732 1101 array(
@@ -748,8 +1117,37 @@
748 1117 }
749 1118
750 1119
751 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 + /**
752 1150 * Get general Child site option.
753 1151 *
754 1152 * @param mixed $option Child Site option name.
755 1153 *
@@ -764,9 +1162,10 @@
764 1162 } else {
765 1163 static::$general_options[] = array();
766 1164 }
767 1165
768 - $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.
769 1168
770 1169 static::$general_options[ $option ] = $val;
771 1170 return $val;
772 1171 }
@@ -801,12 +1200,15 @@
801 1200 $diff_options[] = $opt;
802 1201 }
803 1202 }
804 1203
805 - $options_name = implode( "','", $diff_options );
806 - $options_name = "'" . $options_name . "'";
1204 + if ( empty( $diff_options ) ) {
1205 + return $return_options;
1206 + }
807 1207
808 - $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.
809 1211
810 1212 foreach ( (array) $options_db as $o ) {
811 1213 $return_options[ $o->name ] = $o->value;
812 1214 static::$general_options[ $o->name ] = $o->value;
@@ -836,9 +1238,10 @@
836 1238 static::$general_options[] = array();
837 1239 }
838 1240 static::$general_options[ $option ] = $value;
839 1241
840 - $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.
841 1244
842 1245 if ( empty( $rslt ) ) {
843 1246 $this->wpdb->insert(
844 1247 $this->table_name( 'wp_options' ),
@@ -903,13 +1306,23 @@
903 1306 */
904 1307 public function get_sql_websites() {
905 1308 $where = $this->get_sql_where_allow_access_sites( 'wp' );
906 1309
907 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
1310 + $view_selects = '';
1311 + $view_joins = '';
1312 +
1313 + $opts_view = $this->get_wp_options_join();
1314 +
1315 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
1316 + $view_selects = ',' . $opts_view['selects'];
1317 + $view_joins = $opts_view['joins'];
1318 + }
1319 +
1320 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
908 1321 FROM ' . $this->table_name( 'wp' ) . ' wp
909 1322 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
910 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
911 - WHERE 1 ' . $where;
1323 + ' . $view_joins . '
1324 + WHERE 1 ' . $where . ' ORDER BY wp.id';
912 1325 }
913 1326
914 1327 /**
915 1328 * Get child sites by user id via SQL.
@@ -934,24 +1347,34 @@
934 1347 }
935 1348
936 1349 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
937 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 +
938 1361 if ( $selectgroups ) {
939 - $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
940 1363 FROM ' . $this->table_name( 'wp' ) . ' wp
941 1364 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
942 1365 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
943 1366 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
944 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1367 + ' . $view_joins . '
945 1368 WHERE wp.userid = ' . $userid . "
946 1369 $where
947 1370 GROUP BY wp.id, wp_sync.sync_id
948 1371 ORDER BY " . $orderBy;
949 1372 } else {
950 - $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
1373 + $qry = 'SELECT wp.*,wp_sync.*' . $view_selects . '
951 1374 FROM ' . $this->table_name( 'wp' ) . ' wp
952 1375 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
953 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1376 + ' . $view_joins . '
954 1377 WHERE wp.userid = ' . $userid . "
955 1378 $where
956 1379 ORDER BY " . $orderBy;
957 1380 }
@@ -998,8 +1421,15 @@
998 1421 $is_staging = 'no',
999 1422 $params = array()
1000 1423 ) {
1001 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 +
1002 1432 $where = '';
1003 1433 if ( MainWP_System::instance()->is_multi_user() ) {
1004 1434
1005 1435 /**
@@ -1013,9 +1443,15 @@
1013 1443 }
1014 1444
1015 1445 if ( null !== $search_site ) {
1016 1446 $search_site = trim( $search_site );
1017 - $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 + );
1018 1454 }
1019 1455
1020 1456 if ( ! empty( $extraWhere ) ) {
1021 1457 $where .= ' AND ' . $extraWhere . ' ';
@@ -1032,8 +1468,10 @@
1032 1468 } elseif ( is_array( $params ) && isset( $params['connected'] ) && 'no' === $params['connected'] ) {
1033 1469 $connected_sql = ' AND wp_sync.sync_errors <> "" ';
1034 1470 }
1035 1471
1472 + $use_comp_subquery = false;
1473 +
1036 1474 $limit = '';
1037 1475 if ( $params && is_array( $params ) ) {
1038 1476 $s = isset( $params['s'] ) ? $params['s'] : '';
1039 1477 $exclude = isset( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
@@ -1041,10 +1479,19 @@
1041 1479 $status = isset( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
1042 1480 $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
1043 1481 $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
1044 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 +
1045 1486 if ( ! empty( $s ) ) {
1046 - $where .= ' AND ( wp.id LIKE "%' . $this->escape( $s ) . '%" OR wp.name LIKE "%' . $this->escape( $s ) . '%" OR wp.url LIKE "%' . $this->escape( $s ) . '%" ) ';
1487 + $s = trim( $s );
1488 + // Note: This SQL is executed via m_query() which bypasses wpdb, so we can't
1489 + // use wpdb->prepare() (its placeholders won't be resolved). Instead, escape
1490 + // LIKE wildcards and the value manually. First escape LIKE special chars,
1491 + // then SQL escape the result.
1492 + $like_value = '%' . $this->escape( $this->wpdb->esc_like( $s ) ) . '%';
1493 + $where .= " AND ( wp.id LIKE '{$like_value}' OR wp.name LIKE '{$like_value}' OR wp.url LIKE '{$like_value}' ) ";
1047 1494 }
1048 1495
1049 1496 if ( ! empty( $exclude ) ) {
1050 1497 $where .= ' AND wp.id NOT IN (' . implode( ',', $exclude ) . ') ';
@@ -1053,14 +1500,19 @@
1053 1500 if ( ! empty( $include ) ) {
1054 1501 $where .= ' AND wp.id IN (' . implode( ',', $include ) . ') ';
1055 1502 }
1056 1503
1504 + if ( ! empty( $_included_cache_ids ) ) {
1505 + $where .= ' AND wp.id IN (' . implode( ',', $_included_cache_ids ) . ') ';
1506 + }
1507 +
1057 1508 // any, connected, disconnected, suspended, available_update.
1058 1509 if ( ! empty( $status ) && is_array( $status ) && ! in_array( 'any', $status ) ) {
1059 1510 $status_conds = array();
1060 1511 if ( in_array( 'available_update', $status ) ) {
1061 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 <> '[]' ) ";
1062 - $results = $this->wpdb->get_results( 'SELECT wpid FROM ' . $this->table_name( 'wp_options' ) . " WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]' " );
1513 + $options_table = esc_sql( $this->table_name( 'wp_options' ) );
1514 + $results = $this->wpdb->get_results( "SELECT wpid FROM {$options_table} WHERE name = 'wp_upgrades' AND value <> '' AND value <> '[]'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the rest of the query is a literal.
1063 1515 if ( $results ) {
1064 1516 $wp_ids = array();
1065 1517 foreach ( $results as $item ) {
1066 1518 if ( ! empty( $item->wpid ) ) {
@@ -1075,9 +1527,9 @@
1075 1527 $status_conds[] = ' ( ' . $available_sql . ') ';
1076 1528 }
1077 1529
1078 1530 if ( in_array( 'connected', $status ) ) {
1079 - $status_conds[] = ' ( wp_sync.sync_errors == "" ) ';
1531 + $status_conds[] = ' ( wp_sync.sync_errors = "" ) ';
1080 1532 }
1081 1533 if ( in_array( 'disconnected', $status ) ) {
1082 1534 $status_conds[] = " wp_sync.sync_errors <> '' ";
1083 1535 }
@@ -1088,13 +1540,18 @@
1088 1540
1089 1541 if ( ! empty( $status_conds ) ) {
1090 1542 $where .= ' AND ( ' . implode( ' OR ', $status_conds ) . ' ) ';
1091 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 + }
1092 1548 }
1093 1549
1094 1550 if ( ! empty( $page ) && ! empty( $per_page ) ) {
1095 1551 $limit = ( $page - 1 ) * $per_page . ',' . $per_page;
1096 1552 }
1553 + $use_comp_subquery = ! empty( $params['use_compatible_subquery'] ) ? true : false;
1097 1554 }
1098 1555
1099 1556 if ( 'wp.url' === $orderBy ) {
1100 1557 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
@@ -1099,11 +1556,26 @@
1099 1556 if ( 'wp.url' === $orderBy ) {
1100 1557 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
1101 1558 }
1102 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 +
1103 1575 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1104 1576 if ( $selectgroups ) {
1105 - $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,
1106 1578 wpclient.name as client_name
1107 1579 FROM ' . $this->table_name( 'wp' ) . ' wp
1108 1580 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1109 1581 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
@@ -1108,18 +1580,18 @@
1108 1580 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1109 1581 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1110 1582 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1111 1583 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1112 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1584 + ' . $view_joins . '
1113 1585 WHERE 1 ' . $where . $connected_sql . '
1114 1586 GROUP BY wp.id, wp_sync.sync_id
1115 1587 ORDER BY ' . $orderBy;
1116 1588 } else {
1117 - $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
1118 1590 FROM ' . $this->table_name( 'wp' ) . ' wp
1119 1591 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1120 1592 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1121 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1593 + ' . $view_joins . '
1122 1594 WHERE 1 ' . $where . $connected_sql . '
1123 1595 GROUP BY wp.id, wp_sync.sync_id
1124 1596 ORDER BY ' . $orderBy;
1125 1597 }
@@ -1140,13 +1612,17 @@
1140 1612 $qry .= ' LIMIT ' . intval( $start ) . ', ' . intval( $limit_sites );
1141 1613 }
1142 1614 }
1143 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 +
1144 1621 return $qry;
1145 1622 }
1146 1623
1147 1624
1148 -
1149 1625 /**
1150 1626 * Get SQL to get wp child sites for current user.
1151 1627 *
1152 1628 * @since 4.3
@@ -1159,17 +1635,18 @@
1159 1635 if ( ! is_array( $params ) ) {
1160 1636 $params = array();
1161 1637 }
1162 1638
1163 - $selectgroups = ! empty( $params['select_groups'] ) ? true : false;
1164 - $search_site = isset( $params['search_site'] ) && ! empty( $params['search_site'] ) ? $params['search_site'] : null;
1165 - $orderBy = isset( $params['order_by'] ) && ! empty( $params['order_by'] ) ? $params['order_by'] : 'wp.url';
1166 - $offset = isset( $params['offset'] ) ? $params['offset'] : false;
1167 - $rowcount = isset( $params['row_count'] ) ? $params['row_count'] : false;
1168 - $for_manager = isset( $params['for_manager'] ) ? $params['for_manager'] : false;
1169 - $extraWhere = isset( $params['extra_where'] ) && ! empty( $params['extra_where'] ) ? $params['extra_where'] : null;
1170 - $extra_view = isset( $params['extra_view'] ) && is_array( $params['extra_view'] ) && ! empty( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
1171 - $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;
1172 1649
1173 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();
1174 1651 $extra_select_sql_fields = isset( $params['extra_select_sql_fields'] ) && ! empty( $params['extra_select_sql_fields'] ) ? $params['extra_select_sql_fields'] : '';
1175 1652
@@ -1179,9 +1656,15 @@
1179 1656 $where = '';
1180 1657
1181 1658 if ( null !== $search_site ) {
1182 1659 $search_site = trim( $search_site );
1183 - $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 + );
1184 1667 }
1185 1668
1186 1669 if ( null !== $extraWhere ) {
1187 1670 $where .= ' AND ' . $extraWhere;
@@ -1200,8 +1683,22 @@
1200 1683 if ( ! empty( $extra_select_sql_fields ) ) {
1201 1684 $extra_select_sql_fields = ',' . $extra_select_sql_fields;
1202 1685 }
1203 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 +
1204 1701 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1205 1702 if ( $selectgroups ) {
1206 1703 if ( $count_only ) {
1207 1704 $select = ' COUNT(DISTINCT(wp.id)) ';
@@ -1207,9 +1704,9 @@
1207 1704 $select = ' COUNT(DISTINCT(wp.id)) ';
1208 1705 } else {
1209 1706 $select = $select_wp_fields . '
1210 1707 ' . $extra_select_sql_fields . '
1211 - ,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 ';
1212 1709 }
1213 1710 $qry = 'SELECT ' . $select . '
1214 1711 FROM ' . $this->table_name( 'wp' ) . ' wp
1215 1712 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
@@ -1215,10 +1712,10 @@
1215 1712 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1216 1713 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1217 1714 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1218 1715 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1219 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
1220 - $extra_join . '
1716 + ' . $view_joins . '
1717 + ' . $extra_join . '
1221 1718 WHERE 1 ' . $where;
1222 1719 if ( ! $count_only ) {
1223 1720 $qry .= ' GROUP BY wp.id, wp_sync.sync_id';
1224 1721 }
@@ -1228,16 +1725,16 @@
1228 1725 $select = ' COUNT(DISTINCT(wp.id)) ';
1229 1726 } else {
1230 1727 $select = $select_wp_fields . '
1231 1728 ' . $extra_select_sql_fields . '
1232 - ,wp_sync.sync_errors,wp_optionview.*, wpclient.name as client_name ';
1729 + ,wp_sync.sync_errors' . $view_selects . ', wpclient.name as client_name ';
1233 1730 }
1234 1731 $qry = 'SELECT ' . $select . '
1235 1732 FROM ' . $this->table_name( 'wp' ) . ' wp
1236 1733 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
1237 1734 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1238 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
1239 - $extra_join . '
1735 + ' . $view_joins . '
1736 + ' . $extra_join . '
1240 1737 WHERE 1 ' . $where;
1241 1738 if ( ! $count_only ) {
1242 1739 $qry .= ' GROUP BY wp.id, wp_sync.sync_id';
1243 1740 }
@@ -1348,9 +1845,9 @@
1348 1845 $format = isset( $params['format'] ) ? $params['format'] : '';
1349 1846 $clients = isset( $params['client'] ) ? $params['client'] : '';
1350 1847 $fields = isset( $params['fields'] ) && is_array( $params['fields'] ) ? $params['fields'] : array();
1351 1848
1352 - $for_manager = false;
1849 + $for_manager = isset( $params['no_perm_check'] ) && true === $params['no_perm_check'];
1353 1850
1354 1851 $urlsWhere = '';
1355 1852
1356 1853 if ( isset( $params['urls'] ) && ! empty( $params['urls'] ) ) {
@@ -1465,9 +1962,12 @@
1465 1962 $data = array_unique( array_merge( $fields, $data ) ); // to prevent difference fields name.
1466 1963 }
1467 1964
1468 1965 $dbwebsites = array();
1469 - $websites = $this->query( $this->get_sql_websites_for_current_user( $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging, $args ) );
1966 +
1967 + $sql = $this->get_sql_websites_for_current_user( $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging, $args );
1968 + $websites = $this->query( $sql );
1969 +
1470 1970 while ( $websites && ( $website = static::fetch_object( $websites ) ) ) {
1471 1971
1472 1972 $obj_data = MainWP_Utility::map_site( $website, $data );
1473 1973
@@ -1472,9 +1972,12 @@
1472 1972 $obj_data = MainWP_Utility::map_site( $website, $data );
1473 1973
1474 1974 if ( $full_data ) {
1475 1975 $sum_upgrades = 0;
1476 - 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 ) ) {
1477 1980 $plugin_upgrades = json_decode( $obj_data->plugin_upgrades, true );
1478 1981 if ( is_array( $plugin_upgrades ) ) {
1479 1982 $sum_upgrades += count( $plugin_upgrades );
1480 1983 }
@@ -1479,9 +1982,9 @@
1479 1982 $sum_upgrades += count( $plugin_upgrades );
1480 1983 }
1481 1984 }
1482 1985
1483 - if ( '' !== $obj_data->theme_upgrades ) {
1986 + if ( ! empty( $obj_data->theme_upgrades ) ) {
1484 1987 $theme_upgrades = json_decode( $obj_data->theme_upgrades, true );
1485 1988 if ( is_array( $theme_upgrades ) ) {
1486 1989 $sum_upgrades += count( $theme_upgrades );
1487 1990 }
@@ -1486,9 +1989,9 @@
1486 1989 $sum_upgrades += count( $theme_upgrades );
1487 1990 }
1488 1991 }
1489 1992
1490 - if ( '' !== $obj_data->wp_upgrades ) {
1993 + if ( ! empty( $obj_data->wp_upgrades ) ) {
1491 1994 $wp_upgrades = json_decode( $obj_data->wp_upgrades, true );
1492 1995 if ( is_array( $wp_upgrades ) ) {
1493 1996 $sum_upgrades += count( $wp_upgrades );
1494 1997 }
@@ -1506,8 +2009,126 @@
1506 2009 return $dbwebsites;
1507 2010 }
1508 2011
1509 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 + /**
1510 2131 * Get the child sites the current user has searched for.
1511 2132 *
1512 2133 * @param array $params Query parameters.
1513 2134 *
@@ -1520,11 +2141,11 @@
1520 2141 if ( ! is_array( $params ) ) {
1521 2142 $params = array();
1522 2143 }
1523 2144
1524 - $view = isset( $params['view'] ) ? $params['view'] : 'default'; // must be default to compatible with get_option_view().
2145 + $view = isset( $params['view'] ) ? $params['view'] : 'default'; // must be default.
1525 2146 $selectgroups = isset( $params['selectgroups'] ) && $params['selectgroups'] ? true : false;
1526 - $search_site = isset( $params['search'] ) ? $this->escape( trim( $params['search'] ) ) : null;
2147 + $search_site = isset( $params['search'] ) ? trim( $params['search'] ) : null;
1527 2148 $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
1528 2149 $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
1529 2150 $rowcount = isset( $params['rowcount'] ) ? intval( $params['rowcount'] ) : false;
1530 2151 $extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null; // without AND prefix.
@@ -1533,11 +2154,16 @@
1533 2154 $is_staging = isset( $params['is_staging'] ) && 'yes' === $params['is_staging'] ? 'yes' : 'no';
1534 2155 $is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
1535 2156 $group_ids = isset( $params['group_id'] ) && ! empty( $params['group_id'] ) ? $params['group_id'] : array();
1536 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';
1537 2159 $is_not = isset( $params['isnot'] ) && ! empty( $params['isnot'] ) ? true : false;
1538 2160 $selected_sites = isset( $params['selected_sites'] ) ? $params['selected_sites'] : array();
1539 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 +
1540 2166 if ( ! is_array( $group_ids ) ) {
1541 2167 $group_ids = array();
1542 2168 }
1543 2169
@@ -1595,19 +2221,46 @@
1595 2221
1596 2222 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
1597 2223 }
1598 2224
1599 - if ( ! empty( $selected_sites ) ) {
1600 - $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 + }
1601 2235 }
1602 2236
1603 - // for searching.
2237 + // Search filtering.
1604 2238 if ( null !== $search_site && '' !== $search_site ) {
1605 - $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 + }
1606 2258 }
1607 2259
1608 - if ( null !== $extraWhere ) {
1609 - $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';
1610 2263 }
1611 2264
1612 2265 if ( ! $for_manager ) {
1613 2266 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
@@ -1622,10 +2275,11 @@
1622 2275 if ( ! empty( $orderBy ) ) {
1623 2276 $orderBy = ' ORDER BY ' . $orderBy;
1624 2277 }
1625 2278
1626 - $join_group = '';
1627 - $where_group = '';
2279 + $join_group = '';
2280 + $where_group = '';
2281 + $having_group = '';
1628 2282
1629 2283 if ( in_array( 'nogroups', $group_ids ) ) {
1630 2284 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1631 2285 $group_ids = array_filter(
@@ -1634,14 +2288,21 @@
1634 2288 return 'nogroups' !== $e;
1635 2289 }
1636 2290 );
1637 2291 if ( ! empty( $group_ids ) ) {
1638 - $groups = implode( ',', $group_ids );
2292 + $groups = implode( ',', $group_ids );
2293 + $groups_count = count( $group_ids );
1639 2294 if ( $is_not ) {
1640 - $where_group = ' AND wpgroup.groupid IS NOT NULL AND wpgroup.groupid NOT IN (' . $groups . ') ';
1641 - // to fix.
1642 - $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 . ') ';
1643 - $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 ';
1644 2305 } else {
1645 2306 $where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
1646 2307 }
1647 2308 } elseif ( $is_not ) {
@@ -1649,18 +2310,26 @@
1649 2310 } else {
1650 2311 $where_group = ' AND wpgroup.groupid IS NULL ';
1651 2312 }
1652 2313 } elseif ( $group_ids ) {
1653 - $groups = implode( ',', $group_ids );
2314 + $groups = implode( ',', $group_ids );
2315 + $groups_count = count( $group_ids );
1654 2316 if ( $is_not ) {
1655 2317 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1656 - $where_group = ' AND ( wpgroup.groupid NOT IN (' . $groups . ') OR wpgroup.groupid IS NULL ) ';
1657 - // to fix.
1658 - $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 . ') ';
1659 - $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 + }
1660 2326 } else {
1661 2327 $join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1662 2328 $where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
2329 + if ( 'and' === $group_logic ) {
2330 + $having_group = 'COUNT(DISTINCT wpgroup.groupid) = ' . $groups_count;
2331 + }
1663 2332 }
1664 2333 }
1665 2334
1666 2335 $select_groups_belong = '';
@@ -1670,9 +2339,16 @@
1670 2339 }
1671 2340
1672 2341 $join_client = '';
1673 2342 $where_client = '';
1674 -
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 + }
1675 2351 if ( in_array( 'noclients', $client_ids ) ) {
1676 2352 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1677 2353 $client_ids = array_filter(
1678 2354 $client_ids,
@@ -1720,9 +2396,8 @@
1720 2396 'wp.privkey',
1721 2397 'wp.pubkey',
1722 2398 'wp.wpe',
1723 2399 'wp.is_staging',
1724 - 'wp.pubkey',
1725 2400 'wp.force_use_ipv4',
1726 2401 'wp.siteurl',
1727 2402 'wp.suspended',
1728 2403 'wp.mainwpdir',
@@ -1736,9 +2411,8 @@
1736 2411
1737 2412 $legacy_status_fields = array(
1738 2413 'wp.offline_check_result', // 1 - online, -1 offline.
1739 2414 'wp.http_response_code',
1740 - 'wp.http_code_noticed',
1741 2415 'wp.offline_checks_last',
1742 2416 );
1743 2417
1744 2418 $light_fields = array_merge( $light_fields, $legacy_status_fields );
@@ -1754,13 +2428,42 @@
1754 2428 $select_fields = $light_fields;
1755 2429 } elseif ( 'monitor_view' === $view ) {
1756 2430 $select_fields = $light_fields;
1757 2431 $select_fields[] = 'mo.*';
1758 - $join_monitors = ' LEFT JOIN ' . $this->table_name( 'monitors' ) . ' mo ON wp.id = mo.wpid AND mo.issub = 0 ';
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 +
1759 2452 }
1760 2453
1761 2454 $select = implode( ',', $select_fields );
1762 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 +
1763 2466 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1764 2467 if ( $selectgroups ) {
1765 2468
1766 2469 if ( empty( $join_group ) ) {
@@ -1766,9 +2469,9 @@
1766 2469 if ( empty( $join_group ) ) {
1767 2470 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1768 2471 }
1769 2472
1770 - $qry = 'SELECT ' . $select . ', 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 ' .
1771 2474 $select_groups_belong . ' FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1772 2475 $join_client . ' ' .
1773 2476 $join_group .
1774 2477 $join_monitors . '
@@ -1774,34 +2477,36 @@
1774 2477 $join_monitors . '
1775 2478 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgroup.groupid = gr.id
1776 2479
1777 2480 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1778 - JOIN ' . $this->get_option_view( $extra_view, $view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1779 - WHERE 1 ' . $where . $where_group . $where_client . '
1780 - GROUP BY wp.id, wp_sync.sync_id ' .
2481 + ' . $view_joins . '
2482 + WHERE 1 ' . $where_cache_ids . $where . $where_group . $where_client . $group_by .
1781 2483 $orderBy;
1782 2484 } else {
1783 - $qry = 'SELECT ' . $select . ', wp_optionview.*, wpclient.name as client_name ' .
2485 + $qry = 'SELECT ' . $select . $view_selects . ', wpclient.name as client_name ' .
1784 2486 $select_groups_belong . ' FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1785 2487 $join_group . ' ' .
1786 2488 $join_client .
1787 2489 $join_monitors . '
1788 2490 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1789 - JOIN ' . $this->get_option_view( $extra_view, $view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1790 - WHERE 1 ' . $where . $where_group . $where_client . '
1791 - GROUP BY wp.id, wp_sync.sync_id ' .
2491 + ' . $view_joins . '
2492 + WHERE 1 ' . $where_cache_ids . $where . $where_group . $where_client . $group_by .
1792 2493 $orderBy;
1793 2494 }
1794 2495
1795 2496 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
1796 - $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;
1797 2501 } elseif ( false !== $rowcount ) {
1798 2502 $qry .= ' LIMIT ' . $rowcount;
1799 2503 }
1800 2504
1801 - if ( ! empty( $params['dev_log_query'] ) ) {
1802 - error_log( $qry ); //phpcs:ignore -- NOSONAR - for dev.
2505 + if ( ! empty( $_included_cache_ids ) ) {
2506 + MainWP_Logger::instance()->log_events( 'cache-metrics', sprintf( '[sql search websites=%s]', $qry ) );
1803 2507 }
2508 + MainWP_Logger::instance()->log_events( 'db-queries', sprintf( '[sql search websites=%s]', $qry ) );
1804 2509
1805 2510 return $qry;
1806 2511 }
1807 2512
@@ -1985,24 +2690,41 @@
1985 2690 $view_fields = array();
1986 2691 }
1987 2692
1988 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 +
1989 2711 $where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
1990 2712 if ( $select_groups ) {
1991 - 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
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
1992 2714 FROM ' . $this->table_name( 'wp' ) . ' wp
1993 2715 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1994 2716 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1995 2717 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1996 - JOIN ' . $this->get_option_view_by( $view, $view_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2718 + ' . $view_joins . '
1997 2719 WHERE wp.id = ' . $id . $where . '
1998 2720 GROUP BY wp.id, wp_sync.sync_id';
1999 2721 }
2000 2722
2001 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
2723 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
2002 2724 FROM ' . $this->table_name( 'wp' ) . ' wp
2003 2725 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2004 - JOIN ' . $this->get_option_view_by( $view, $view_fields ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2726 + ' . $view_joins . '
2005 2727 WHERE id = ' . $id . $where;
2006 2728 }
2007 2729 return null;
2008 2730 }
@@ -2038,24 +2760,35 @@
2038 2760 $extra_view = array( 'favi_icon', 'site_info' );
2039 2761 }
2040 2762
2041 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 +
2042 2775 $where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
2043 2776 if ( $selectGroups ) {
2044 - 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
2045 2778 FROM ' . $this->table_name( 'wp' ) . ' wp
2046 2779 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
2047 2780 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
2048 2781 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2049 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2782 + ' . $view_joins . '
2050 2783 WHERE wp.id = ' . $id . $where . '
2051 2784 GROUP BY wp.id, wp_sync.sync_id';
2052 2785 }
2053 2786
2054 - return 'SELECT wp.*,wp_sync.*,wp_optionview.*
2787 + return 'SELECT wp.*,wp_sync.*' . $view_selects . '
2055 2788 FROM ' . $this->table_name( 'wp' ) . ' wp
2056 2789 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2057 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2790 + ' . $view_joins . '
2058 2791 WHERE id = ' . $id . $where;
2059 2792 }
2060 2793
2061 2794 return null;
@@ -2093,11 +2826,22 @@
2093 2826 return ( is_numeric( $e ) && 0 < $e ) ? true : false;
2094 2827 }
2095 2828 );
2096 2829
2097 - $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;
2098 2835
2099 - 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
2100 2844 }
2101 2845
2102 2846 /**
2103 2847 * Get child sites by groups IDs.
@@ -2150,17 +2894,107 @@
2150 2894
2151 2895 /**
2152 2896 * Get child sites by group ID.
2153 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 + *
2154 2938 * @param int $id Group ID.
2155 2939 *
2156 - * @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()
2157 2943 */
2158 - public function get_websites_by_group_id( $id ) {
2159 - 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.
2160 2967 }
2161 2968
2969 +
2162 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 + /**
2163 2997 * Get child sites by group id via SQL.
2164 2998 *
2165 2999 * @param int $id Group ID.
2166 3000 * @param bool $selectgroups Selected groups. Default: false.
@@ -2195,33 +3029,58 @@
2195 3029 }
2196 3030
2197 3031 $where_search = '';
2198 3032 if ( ! empty( $search_site ) ) {
2199 - $search_site = trim( $search_site );
2200 - $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 + );
2201 3041 }
2202 3042
2203 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' );
2204 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 +
2205 3064 if ( MainWP_Utility::ctype_digit( $id ) ) {
2206 3065 $where_allowed = $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
2207 3066 if ( $selectgroups ) {
2208 - $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
2209 3068 FROM ' . $this->table_name( 'wp' ) . ' wp
2210 3069 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2211 3070 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
2212 3071 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
2213 3072 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2214 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
3073 + ' . $view_joins . '
2215 3074 WHERE wpgroup.groupid = ' . $id . ' ' .
2216 3075 ( empty( $where ) ? '' : ' AND ' . $where ) . $where_allowed . $where_search . '
2217 3076 GROUP BY wp.id, wp_sync.sync_id
2218 3077 ORDER BY ' . $orderBy;
2219 3078 } else {
2220 - $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
2221 3080 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2222 3081 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2223 - JOIN ' . $this->get_option_view( $extra_view, 'group' ) . ' wp_optionview ON wp.id = wp_optionview.wpid
3082 + ' . $view_joins . '
2224 3083 WHERE wpgroup.groupid = ' . $id . ' ' . $where_allowed . $where_search .
2225 3084 ( empty( $where ) ? '' : ' AND ' . $where ) . ' ORDER BY ' . $orderBy;
2226 3085 }
2227 3086 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
@@ -2270,13 +3129,23 @@
2270 3129
2271 3130 $userid = $current_user->ID;
2272 3131 }
2273 3132
2274 - $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
2275 3144 INNER JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
2276 3145 JOIN ' . $this->table_name( 'group' ) . ' g ON wpgroup.groupid = g.id
2277 3146 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2278 - JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
3147 + ' . $view_joins . '
2279 3148 WHERE g.name="' . $this->escape( $groupname ) . '"';
2280 3149 if ( null !== $userid ) {
2281 3150 $sql .= ' AND g.userid = "' . intval( $userid ) . '"';
2282 3151 }
@@ -2291,9 +3160,10 @@
2291 3160 *
2292 3161 * @return string|null Child site IP address or null on failure.
2293 3162 */
2294 3163 public function get_wp_ip( $wpid ) {
2295 - 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.
2296 3166 }
2297 3167
2298 3168 /**
2299 3169 * Add website to the MainWP Dashboard.
@@ -2325,9 +3195,9 @@
2325 3195 }
2326 3196
2327 3197 $groupids = isset( $params['groupids'] ) ? $params['groupids'] : array();
2328 3198 $groupnames = isset( $params['groupnames'] ) ? $params['groupnames'] : array();
2329 - $verifyCertificate = isset( $params['verifyCertificate'] ) ? (int) $params['verifyCertificate'] : 1;
3199 + $verifyCertificate = isset( $params['verifyCertificate'] ) ? (int) $params['verifyCertificate'] : 2;
2330 3200 $uniqueId = isset( $params['uniqueId'] ) ? $params['uniqueId'] : '';
2331 3201 $http_user = isset( $params['http_user'] ) ? $params['http_user'] : null;
2332 3202 $http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
2333 3203 $sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
@@ -2332,9 +3202,25 @@
2332 3202 $http_pass = isset( $params['http_pass'] ) ? $params['http_pass'] : null;
2333 3203 $sslVersion = isset( $params['sslVersion'] ) ? $params['sslVersion'] : 0;
2334 3204 $wpe = isset( $params['wpe'] ) ? $params['wpe'] : 0;
2335 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;
2336 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 +
2337 3223 if ( MainWP_Utility::ctype_digit( $userid ) ) {
2338 3224 if ( '/' !== substr( $url, - 1 ) ) {
2339 3225 $url .= '/';
2340 3226 }
@@ -2360,8 +3246,9 @@
2360 3246 'plugin_upgrades' => '',
2361 3247 'theme_upgrades' => '',
2362 3248 'translation_upgrades' => '',
2363 3249 'securityIssues' => '',
3250 + 'premium_upgrades' => '',
2364 3251 'themes' => '',
2365 3252 'ignored_themes' => '',
2366 3253 'plugins' => '',
2367 3254 'ignored_plugins' => '',
@@ -2373,14 +3260,18 @@
2373 3260 'verify_certificate' => intval( $verifyCertificate ),
2374 3261 'ssl_version' => $sslVersion,
2375 3262 'uniqueId' => $uniqueId,
2376 3263 'mainwpdir' => 0,
2377 - 'http_user' => $http_user,
2378 - 'http_pass' => $http_pass,
3264 + 'http_user' => $encrypted_http_user,
3265 + 'http_pass' => $encrypted_http_pass,
2379 3266 'wpe' => $wpe,
2380 3267 'is_staging' => $isStaging,
2381 3268 );
2382 3269
3270 + if ( null !== $force_use_ipv4 ) {
3271 + $values['force_use_ipv4'] = $force_use_ipv4;
3272 + }
3273 +
2383 3274 $syncValues = array(
2384 3275 'dtsSync' => 0,
2385 3276 'dtsSyncStart' => 0,
2386 3277 'dtsAutomaticSync' => 0,
@@ -2390,11 +3281,13 @@
2390 3281 'sync_errors' => '',
2391 3282 );
2392 3283 if ( $this->wpdb->insert( $this->table_name( 'wp' ), $values ) ) {
2393 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.
2394 3286 MainWP_Encrypt_Data_Lib::instance()->encrypt_save_keys( $websiteid, $en_pk_data );
2395 3287 $syncValues['wpid'] = $websiteid;
2396 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() ) );
2397 3290 $this->wpdb->insert(
2398 3291 $this->table_name( 'wp_settings_backup' ),
2399 3292 array(
2400 3293 'wpid' => $websiteid,
@@ -2423,9 +3316,9 @@
2423 3316 'groupid' => $groupid,
2424 3317 )
2425 3318 );
2426 3319 }
2427 -
3320 + MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
2428 3321 return $websiteid;
2429 3322 }
2430 3323 }
2431 3324
@@ -2442,14 +3335,15 @@
2442 3335 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
2443 3336 */
2444 3337 public function remove_website( $websiteid ) {
2445 3338 if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
2446 - $nr = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp' ) . ' WHERE id=%d', $websiteid ) );
2447 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
2448 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_sync' ) . ' WHERE wpid=%d', $websiteid ) );
2449 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid=%d', $websiteid ) );
3339 + $nr = $this->wpdb->delete( $this->table_name( 'wp' ), array( 'id' => $websiteid ) );
3340 + $this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
3341 + $this->wpdb->delete( $this->table_name( 'wp_sync' ), array( 'wpid' => $websiteid ) );
3342 + $this->wpdb->delete( $this->table_name( 'wp_options' ), array( 'wpid' => $websiteid ) );
2450 3343 MainWP_Encrypt_Data_Lib::remove_key_file( $websiteid );
2451 3344 MainWP_DB_Uptime_Monitoring::instance()->delete_monitor( array( 'wpid' => $websiteid ) );
3345 + MainWP_Manage_Sites_List_Table::invalidate_manage_sites_cache();
2452 3346 return $nr;
2453 3347 }
2454 3348
2455 3349 return false;
@@ -2464,11 +3358,31 @@
2464 3358 * @return int|boolean The number of rows updated, or false on error.
2465 3359 */
2466 3360 public function update_website_values( $websiteid, $fields ) {
2467 3361 if ( ! empty( $fields ) ) {
3362 + // MWP-1548: encrypt http_user / http_pass at rest if either
3363 + // is present in $fields. Generic-fields updaters (clone
3364 + // flow in extensions-handler, callers that pass arbitrary
3365 + // column subsets) must go through the same fail-closed
3366 + // contract as add_website / update_website.
3367 + if ( array_key_exists( 'http_user', $fields ) ) {
3368 + $encrypted = MainWP_Credential_Storage::encrypt_credential( $fields['http_user'], 'http_user' );
3369 + if ( false === $encrypted ) {
3370 + return false;
3371 + }
3372 + $fields['http_user'] = $encrypted;
3373 + }
3374 + if ( array_key_exists( 'http_pass', $fields ) ) {
3375 + $encrypted = MainWP_Credential_Storage::encrypt_credential( $fields['http_pass'], 'http_pass' );
3376 + if ( false === $encrypted ) {
3377 + return false;
3378 + }
3379 + $fields['http_pass'] = $encrypted;
3380 + }
2468 3381 // Lock the data stream to prevent other processes from updating at the same time.
2469 - $sql = $this->wpdb->prepare(
2470 - 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE id = %d FOR UPDATE',
3382 + $table_name = esc_sql( $this->table_name( 'wp' ) );
3383 + $sql = $this->wpdb->prepare(
3384 + "SELECT * FROM {$table_name} WHERE id = %d FOR UPDATE", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
2471 3385 $websiteid
2472 3386 );
2473 3387 $this->wpdb->get_row( $sql );
2474 3388
@@ -2515,9 +3429,9 @@
2515 3429 * @param string $http_pass HTTP Basic Authentication password.
2516 3430 * @param int $sslVersion SSL Version.
2517 3431 * @param bool $disableHealthChecking Disable Site health threshold.
2518 3432 * @param int $healthThreshold Site health threshold.
2519 - * @param int $wpe Is it WP Engine hosted site.
3433 + * @param string $backup_method Primary backup method.
2520 3434 *
2521 3435 * @return boolean ture on success or false on failure.
2522 3436 *
2523 3437 * @uses \MainWP\Dashboard\MainWP_System_Utility::can_edit_website()
@@ -2541,25 +3455,67 @@
2541 3455 $http_user = null,
2542 3456 $http_pass = null,
2543 3457 $sslVersion = 0,
2544 3458 $disableHealthChecking = 1,
2545 - $healthThreshold = 80,
2546 - $wpe = 0
3459 + $healthThreshold = 0,
3460 + $backup_method = 'global'
2547 3461 ) {
2548 3462
3463 + $wpe = 0; // going to update when sync.
3464 +
2549 3465 if ( MainWP_Utility::ctype_digit( $websiteid ) && MainWP_Utility::ctype_digit( $userid ) ) {
2550 3466 $website = $this->get_website_by_id( $websiteid );
2551 3467 if ( MainWP_System_Utility::can_edit_website( $website ) ) {
3468 + // MWP-1548: encrypt http_user / http_pass at rest. Same
3469 + // fail-closed contract as add_website -- a non-empty
3470 + // value that fails to encrypt aborts the update so we
3471 + // never overwrite an existing encrypted row with
3472 + // plaintext when the encryption layer is unhealthy.
3473 + $encrypted_http_user = MainWP_Credential_Storage::encrypt_credential( $http_user, 'http_user' );
3474 + $encrypted_http_pass = MainWP_Credential_Storage::encrypt_credential( $http_pass, 'http_pass' );
3475 + if ( false === $encrypted_http_user || false === $encrypted_http_pass ) {
3476 + return false;
3477 + }
2552 3478 // update admin.
2553 - $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_health_check="' . $this->escape( $disableHealthChecking ) . '", health_threshold="' . $this->escape( $healthThreshold ) . '" WHERE id=%d', $websiteid ) );
2554 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp_settings_backup' ) . ' SET archiveFormat = "' . $this->escape( $archiveFormat ) . '" WHERE wpid=%d', $websiteid ) );
3479 + $this->wpdb->update(
3480 + $this->table_name( 'wp' ),
3481 + array(
3482 + 'url' => $url,
3483 + 'name' => wp_strip_all_tags( $name ),
3484 + 'adminname' => $siteadmin,
3485 + 'pluginDir' => $pluginDir,
3486 + 'verify_certificate' => intval( $verifyCertificate ),
3487 + 'ssl_version' => intval( $sslVersion ),
3488 + 'wpe' => intval( $wpe ),
3489 + 'uniqueId' => $uniqueId,
3490 + 'http_user' => $encrypted_http_user,
3491 + 'http_pass' => $encrypted_http_pass,
3492 + 'disable_health_check' => $disableHealthChecking,
3493 + 'health_threshold' => $healthThreshold,
3494 + 'primary_backup_method' => $backup_method,
3495 + ),
3496 + array( 'id' => $websiteid )
3497 + );
3498 + $this->wpdb->update(
3499 + $this->table_name( 'wp_settings_backup' ),
3500 + array( 'archiveFormat' => $archiveFormat ),
3501 + array( 'wpid' => $websiteid )
3502 + );
2555 3503
2556 3504 if ( get_option( 'mainwp_enableLegacyBackupFeature' ) ) {
2557 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET maximumFileDescriptorsOverride = ' . ( $maximumFileDescriptorsOverride ? 1 : 0 ) . ',maximumFileDescriptorsAuto= ' . ( $maximumFileDescriptorsAuto ? 1 : 0 ) . ',maximumFileDescriptors = ' . $maximumFileDescriptors . ' WHERE id=%d', $websiteid ) );
3505 + $this->wpdb->update(
3506 + $this->table_name( 'wp' ),
3507 + array(
3508 + 'maximumFileDescriptorsOverride' => (int) $maximumFileDescriptorsOverride,
3509 + 'maximumFileDescriptorsAuto' => (int) $maximumFileDescriptorsAuto,
3510 + 'maximumFileDescriptors' => (int) $maximumFileDescriptors,
3511 + ),
3512 + array( 'id' => $websiteid )
3513 + );
2558 3514 }
2559 3515
2560 3516 // remove groups.
2561 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
3517 + $this->wpdb->delete( $this->table_name( 'wp_group' ), array( 'wpid' => $websiteid ) );
2562 3518 // Remove GA stats.
2563 3519 $showErrors = $this->wpdb->hide_errors();
2564 3520
2565 3521 /**
@@ -2646,9 +3602,11 @@
2646 3602 public function get_websites_by_url( $url ) {
2647 3603 if ( '/' !== substr( $url, - 1 ) ) {
2648 3604 $url .= '/';
2649 3605 }
2650 - $results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s ', $this->escape( $url ) ), OBJECT );
3606 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3607 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3608 + $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2651 3609 if ( $results ) {
2652 3610 return $results;
2653 3611 }
2654 3612
@@ -2660,9 +3618,9 @@
2660 3618 $url = str_replace( 'https://', 'https://www.', $url );
2661 3619 $url = str_replace( 'http://', 'http://www.', $url );
2662 3620 }
2663 3621
2664 - $results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s ', $this->escape( $url ) ), OBJECT );
3622 + $results = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.url = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2665 3623 if ( $results ) {
2666 3624 return $results;
2667 3625 }
2668 3626
@@ -2667,36 +3625,68 @@
2667 3625 }
2668 3626
2669 3627 $url = str_replace( array( 'https://www.', 'http://www.', 'https://', 'http://', 'www.' ), array( '', '', '', '', '' ), $url );
2670 3628
2671 - return $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . " wp_sync ON wp.id = wp_sync.wpid WHERE replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s ", $this->escape( $url ) ), OBJECT );
3629 + return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT * FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s", $url ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2672 3630 }
2673 3631
2674 3632 /**
3633 + * Get recently-synced child sites whose stored url differs from the
3634 + * child-reported siteurl.
2675 3635 *
2676 - * Get websites offline status.
3636 + * The trailing slash must be normalized in SQL: stored urls always end
3637 + * with a slash while child-reported siteurl values never do, so a plain
3638 + * inequality would match nearly every site.
2677 3639 *
2678 - * @deprecated see new compatible uptime monitoring.
3640 + * @since 6.2
2679 3641 *
2680 - * @since 5.3.
3642 + * @param int $days Freshness gate: only sites synced within this many days.
2681 3643 *
2682 - * @return array Child site monitoring status.
3644 + * @return array|object|null Rows with id, url, siteurl or null on failure.
2683 3645 */
2684 - public function get_websites_offline_status_to_send_notice() {
2685 - $where = $this->get_sql_where_allow_access_sites( 'wp' );
2686 - $extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
3646 + public function get_websites_with_url_siteurl_mismatch( $days = 30 ) {
3647 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3648 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3649 + $minimum_sync = time() - intval( $days ) * DAY_IN_SECONDS;
2687 3650
2688 - return $this->wpdb->get_results(
2689 - 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2690 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2691 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2692 - 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.
2693 - $where,
2694 - OBJECT
2695 - );
3651 + return $this->wpdb->get_results( $this->wpdb->prepare( "SELECT wp.id, wp.url, wp.siteurl FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid WHERE wp.siteurl <> '' AND TRIM(TRAILING '/' FROM wp.url) <> TRIM(TRAILING '/' FROM wp.siteurl) AND wp_sync.dtsSync >= %d", $minimum_sync ), OBJECT ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd.
2696 3652 }
2697 3653
2698 3654 /**
3655 + * Get IDs of child sites that have a non-empty value for a website option.
3656 + *
3657 + * @since 6.2
3658 + *
3659 + * @param string $option_name Website option name.
3660 + * @param int $limit Maximum number of IDs to return.
3661 + *
3662 + * @return array Site IDs.
3663 + */
3664 + public function get_website_ids_with_nonempty_option( $option_name, $limit = 3 ) {
3665 + $options_table = esc_sql( $this->table_name( 'wp_options' ) );
3666 +
3667 + return $this->wpdb->get_col( $this->wpdb->prepare( "SELECT wpid FROM {$options_table} WHERE name = %s AND value <> '' ORDER BY wpid ASC LIMIT %d", $option_name, max( 1, intval( $limit ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3668 + }
3669 +
3670 + /**
3671 + * Get one child site whose stored url exactly matches the given URL,
3672 + * excluding a site ID. Used as the duplicate guard before a URL correction.
3673 + *
3674 + * @since 6.2
3675 + *
3676 + * @param string $url URL to match ( trailing slash enforced ).
3677 + * @param int $exclude_id Site ID to exclude.
3678 + *
3679 + * @return object|null Matching row ( id, url ) or null.
3680 + */
3681 + public function get_website_by_exact_url_excluding_id( $url, $exclude_id ) {
3682 + $url = rtrim( (string) $url, '/' );
3683 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3684 +
3685 + return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT id, url FROM {$wp_table} WHERE TRIM(TRAILING '/' FROM url) = %s AND id <> %d", $url, intval( $exclude_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3686 + }
3687 +
3688 + /**
2699 3689 * Method get_websites_to_notice_health_threshold()
2700 3690 *
2701 3691 * Get websites to notice site health.
2702 3692 *
@@ -2717,14 +3707,23 @@
2717 3707
2718 3708 $where_site_threshold = ' ( wp.health_threshold = 80 AND wp_sync.health_value < 80 ) '; // should-be-improved site health.
2719 3709 $where_site_threshold .= ' OR ( wp.health_threshold = 100 AND wp_sync.health_value >= 80 ) '; // good site health.
2720 3710
2721 - return $this->wpdb->get_results(
2722 - 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2723 - JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2724 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2725 - 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 ' .
2726 - $where,
3711 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
3712 + $wp_sync_table = esc_sql( $this->table_name( 'wp_sync' ) );
3713 +
3714 + $view_selects = '';
3715 + $view_joins = '';
3716 +
3717 + $opts_view = $this->get_wp_options_join( $extra_view );
3718 +
3719 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3720 + $view_selects = ',' . $opts_view['selects'];
3721 + $view_joins = $opts_view['joins'];
3722 + }
3723 + return $this->wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $option_view is a validated SQL subquery
3724 + "SELECT wp.*,wp_sync.* {$view_selects} FROM {$wp_table} wp JOIN {$wp_sync_table} wp_sync ON wp.id = wp_sync.wpid {$view_joins} WHERE wp.disable_health_check <> 1 AND wp.offline_check_result = 1 AND ( {$where_global_threshold} OR{$where_site_threshold} ) AND wp_sync.health_site_noticed = 0 " . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names are esc_sql'd, the view fragments come from get_wp_options_join(), which allowlists option names, and both threshold fragments are literals picked above.
3725 + $where . ' GROUP BY wp.id ',
2727 3726 OBJECT
2728 3727 );
2729 3728 }
2730 3729
@@ -2735,19 +3734,43 @@
2735 3734 */
2736 3735 public function get_websites_http_check_status() {
2737 3736 $where = $this->get_sql_where_allow_access_sites( 'wp' );
2738 3737 $extra_view = array( 'settings_notification_emails' );
3738 + $wp_table = esc_sql( $this->table_name( 'wp' ) );
2739 3739
3740 + $view_selects = '';
3741 + $view_joins = '';
3742 +
3743 + $opts_view = $this->get_wp_options_join( $extra_view );
3744 +
3745 + if ( is_array( $opts_view ) && ! empty( $opts_view['selects'] ) ) {
3746 + $view_selects = ',' . $opts_view['selects'];
3747 + $view_joins = $opts_view['joins'];
3748 + }
3749 +
3750 + // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- is a validated SQL subquery.
2740 3751 return $this->wpdb->get_results(
2741 - 'SELECT wp.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2742 - JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2743 - WHERE wp.disable_status_check <> 1 AND wp.offline_check_result = -1' . // offline checked status.
2744 - $where,
3752 + "SELECT wp.*{$view_selects} FROM {$wp_table} wp {$view_joins}" . ' WHERE wp.suspended = 0 AND wp.http_code_noticed = 0 AND wp.offline_check_result = -1 ' . // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; the view fragments come from get_wp_options_join(), which allowlists option names.
3753 + $where . ' GROUP BY wp.id ',
2745 3754 OBJECT
2746 3755 );
2747 3756 }
2748 3757
2749 3758 /**
3759 + * Method set_website_noticed_http_check().
3760 + *
3761 + * @param array $site_id The site id .
3762 + *
3763 + * @return void
3764 + */
3765 + public function set_website_noticed_http_check( $site_id = array() ) {
3766 + if ( empty( $site_id ) ) {
3767 + return;
3768 + }
3769 + $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . esc_sql( $this->table_name( 'wp' ) ) . ' SET http_code_noticed = 1 WHERE id = %d', $site_id ) ); // phpcs:ignore PluginCheck.Security.DirectDB.UnpreparedSQL -- $site_id is an integer that is validated by ctype_digit() before being passed to this method.
3770 + }
3771 +
3772 + /**
2750 3773 * Get DB Sites.
2751 3774 *
2752 3775 * @since 4.6
2753 3776 *
@@ -2976,18 +3999,28 @@
2976 3999 *
2977 4000 * @return mixed Result
2978 4001 */
2979 4002 public function insert_lookup_item( $item_name, $item_id, $obj_name, $obj_id ) {
4003 + // get_lookup_items() and delete_lookup_items() match item_name as given, so a
4004 + // name that sanitizing would alter is refused instead of being stored under a
4005 + // value the caller can never read back or delete.
4006 + if ( ! is_string( $item_name ) || sanitize_text_field( $item_name ) !== $item_name ) {
4007 + return false;
4008 + }
2980 4009 if ( empty( $item_name ) || empty( $item_id ) || empty( $obj_name ) || empty( $obj_id ) ) {
2981 4010 return false;
2982 4011 }
2983 4012 $data = array(
2984 - 'item_name' => 'cost',
4013 + 'item_name' => $item_name,
2985 4014 'item_id' => $item_id,
2986 4015 'object_name' => $obj_name,
2987 4016 'object_id' => $obj_id,
2988 4017 );
2989 - $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data );
4018 + // wpdb refuses a value past the column length before any SQL runs, and
4019 + // insert_id would still hold the previous insert's id.
4020 + if ( false === $this->wpdb->insert( $this->table_name( 'lookup_item_objects' ), $data ) ) {
4021 + return false;
4022 + }
2990 4023 return $this->wpdb->insert_id; // must return lookup id.
2991 4024 }
2992 4025
2993 4026 /**
@@ -3049,10 +4082,16 @@
3049 4082 }
3050 4083
3051 4084
3052 4085 /**
3053 - * Return the user data for the given consumer_key.
4086 + * Insert a new REST API key row and return the credential payload.
3054 4087 *
4088 + * Returns an array with key_id, user_id, plaintext consumer_key,
4089 + * plaintext consumer_secret, and key_permissions on success. Returns
4090 + * false when the wpdb->insert() call fails (no current user, missing
4091 + * table, schema mismatch, etc.). Callers must check the return type
4092 + * before treating it as an array.
4093 + *
3055 4094 * @param string $consumer_key Consumer key.
3056 4095 * @param string $consumer_secret Secret key.
3057 4096 * @param string $scope scope.
3058 4097 * @param string $description description.
@@ -3058,9 +4097,9 @@
3058 4097 * @param string $description description.
3059 4098 * @param int $enabled 1 or 0.
3060 4099 * @param array $others others.
3061 4100 *
3062 - * @return array
4101 + * @return array|false Credential payload on success, false on failure.
3063 4102 */
3064 4103 public function insert_rest_api_key( $consumer_key, $consumer_secret, $scope, $description, $enabled, $others = array() ) {
3065 4104 global $current_user;
3066 4105
@@ -3071,18 +4110,19 @@
3071 4110 if ( empty( $user_id ) ) {
3072 4111 return false;
3073 4112 }
3074 4113
3075 - if ( ! is_array( $others ) ) {
3076 - $others = array();
3077 - }
4114 + unset( $others ); // Parameter retained for signature compatibility; key_pass/key_type fields are vestigial after MWP-1544 cleanup.
3078 4115
3079 - $pass = isset( $others['key_pass'] ) ? $others['key_pass'] : '';
3080 - $type = isset( $others['key_type'] ) ? intval( $others['key_type'] ) : 0;
4116 + // Hash the consumer_secret with WordPress's password hasher so the
4117 + // value at rest is no longer reversible by a DB-read primitive.
4118 + // The plaintext is returned to the caller below so it can be shown
4119 + // to the admin once at creation time. See MWP-1540.
4120 + $hashed_secret = wp_hash_password( $consumer_secret );
3081 4121
3082 4122 // Created API keys.
3083 - $permissions = in_array( $scope, array( 'read', 'write', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
3084 - $this->wpdb->insert(
4123 + $permissions = in_array( $scope, array( 'read', 'write', 'delete', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
4124 + $inserted = $this->wpdb->insert(
3085 4125 $this->table_name( 'api_keys' ),
3086 4126 array(
3087 4127 'user_id' => $user_id,
3088 4128 'description' => $description,
@@ -3087,13 +4127,11 @@
3087 4127 'user_id' => $user_id,
3088 4128 'description' => $description,
3089 4129 'permissions' => $permissions,
3090 4130 'consumer_key' => mainwp_api_hash( $consumer_key ),
3091 - 'consumer_secret' => $consumer_secret,
4131 + 'consumer_secret' => $hashed_secret,
3092 4132 'truncated_key' => substr( $consumer_key, -7 ),
3093 4133 'enabled' => $enabled,
3094 - 'key_pass' => $pass,
3095 - 'key_type' => $type,
3096 4134 ),
3097 4135 array(
3098 4136 '%d',
3099 4137 '%s',
@@ -3101,20 +4139,29 @@
3101 4139 '%s',
3102 4140 '%s',
3103 4141 '%s',
3104 4142 '%d',
3105 - '%s',
3106 - '%d',
3107 4143 ),
3108 4144 );
3109 4145
3110 - return array(
3111 - 'key_id' => $this->wpdb->insert_id,
3112 - 'user_id' => $user_id,
3113 - 'consumer_key' => $consumer_key,
3114 - 'consumer_secret' => $consumer_secret,
3115 - 'key_permissions' => $permissions,
3116 - );
4146 + // wpdb->insert() returns false on failure. Without this guard the
4147 + // function would still hand back the plaintext consumer_secret plus
4148 + // $wpdb->insert_id, but that insert_id is the LAST successful insert
4149 + // on the connection (typically from earlier in the same request),
4150 + // not this row. The caller's empty-key_id check would pass and the
4151 + // operator would receive a credential that was never persisted.
4152 + // See MWP-1540 PR review feedback.
4153 + if ( false === $inserted ) {
4154 + return false;
4155 + }
4156 +
4157 + return array(
4158 + 'key_id' => $this->wpdb->insert_id,
4159 + 'user_id' => $user_id,
4160 + 'consumer_key' => $consumer_key,
4161 + 'consumer_secret' => $consumer_secret,
4162 + 'key_permissions' => $permissions,
4163 + );
3117 4164 }
3118 4165
3119 4166 /**
3120 4167 * Update rest api key.
@@ -3126,9 +4173,9 @@
3126 4173 *
3127 4174 * @return array
3128 4175 */
3129 4176 public function update_rest_api_key( $key_id, $scope, $description, $enabled = 1 ) {
3130 - $permissions = in_array( $scope, array( 'read', 'write', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
4177 + $permissions = in_array( $scope, array( 'read', 'write', 'delete', 'read_write' ), true ) ? sanitize_text_field( $scope ) : 'read';
3131 4178 return $this->wpdb->update(
3132 4179 $this->table_name( 'api_keys' ),
3133 4180 array(
3134 4181 'description' => $description,
@@ -3147,9 +4194,10 @@
3147 4194 *
3148 4195 * @return bool result.
3149 4196 */
3150 4197 public function is_existed_enabled_rest_key() {
3151 - $enabled = $this->wpdb->get_row( 'SELECT * FROM ' . $this->table_name( 'api_keys' ) . ' WHERE enabled = 1 LIMIT 1' );
4198 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4199 + $enabled = $this->wpdb->get_row( "SELECT * FROM {$table_name} WHERE enabled = 1 LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3152 4200 return $enabled ? true : false;
3153 4201 }
3154 4202
3155 4203 /**
@@ -3159,9 +4207,10 @@
3159 4207 *
3160 4208 * @return array
3161 4209 */
3162 4210 public function get_rest_api_key_by( $id ) {
3163 - return $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'api_keys' ) . ' WHERE key_id = %d ', $id ) );
4211 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4212 + return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$table_name} WHERE key_id = %d", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3164 4213 }
3165 4214
3166 4215 /**
3167 4216 * Method remove_rest_api_key().
@@ -3170,9 +4219,10 @@
3170 4219 *
3171 4220 * @return array
3172 4221 */
3173 4222 public function remove_rest_api_key( $id ) {
3174 - return $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'api_keys' ) . ' WHERE key_id = %s', $id ) );
4223 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4224 + return $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$table_name} WHERE key_id = %s", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3175 4225 }
3176 4226
3177 4227 /**
3178 4228 * Method get_rest_api_keys().
@@ -3179,9 +4229,10 @@
3179 4229 *
3180 4230 * @return array
3181 4231 */
3182 4232 public function get_rest_api_keys() {
3183 - return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'api_keys' ) . ' ORDER BY key_id DESC' );
4233 + $table_name = esc_sql( $this->table_name( 'api_keys' ) );
4234 + return $this->wpdb->get_results( "SELECT * FROM {$table_name} ORDER BY key_id DESC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3184 4235 }
3185 4236
3186 4237
3187 4238 /**
@@ -3190,21 +4241,58 @@
3190 4241 * @param array $data process data.
3191 4242 * @return mixed
3192 4243 */
3193 4244 public function update_regular_process( $data ) {
3194 - if ( is_array( $data ) ) {
3195 - if ( isset( $data['process_id'] ) ) {
3196 - $process_id = $data['process_id'];
3197 - unset( $data['process_id'] );
3198 - return $this->wpdb->update( $this->table_name( 'schedule_processes' ), $data, array( 'process_id' => $process_id ) );
3199 - } else {
3200 - return $this->wpdb->insert( $this->table_name( 'schedule_processes' ), $data );
4245 + if ( isset( $data['process_id'] ) ) {
4246 + $process_id = $data['process_id'];
4247 + unset( $data['process_id'] );
4248 + return $this->wpdb->update( $this->table_name( 'schedule_processes' ), $data, array( 'process_id' => $process_id ) );
4249 + } elseif ( is_array( $data ) && isset( $data['type'] ) && isset( $data['process_slug'] ) ) {
4250 + return $this->wpdb->insert( $this->table_name( 'schedule_processes' ), $data );
4251 + }
4252 + return false;
4253 + }
4254 +
4255 + /**
4256 + * Delete regular process.
4257 + *
4258 + * @param int $process_id Process id.
4259 + * @param int $item_id Item id.
4260 + * @param string $pro_type Process type.
4261 + * @param string $pro_slug Process slug.
4262 + *
4263 + * @return mixed
4264 + */
4265 + public function delete_regular_process( $process_id = false, $item_id = false, $pro_type = false, $pro_slug = false ) {
4266 +
4267 + if ( is_numeric( $process_id ) && ! empty( $process_id ) ) {
4268 + return $this->wpdb->delete(
4269 + $this->table_name( 'schedule_processes' ),
4270 + array(
4271 + 'process_id' => $process_id,
4272 + )
4273 + );
4274 + } elseif ( ! empty( $pro_type ) || ! empty( $pro_slug ) ) {
4275 +
4276 + $data = array();
4277 +
4278 + if ( ! empty( $pro_type ) ) {
4279 + $data['type'] = $pro_type;
3201 4280 }
4281 +
4282 + if ( ! empty( $pro_slug ) ) {
4283 + $data['process_slug'] = $pro_slug;
4284 + }
4285 +
4286 + if ( ! empty( $item_id ) ) {
4287 + $data['item_id'] = $item_id;
4288 + }
4289 + // Bulk delete.
4290 + return $this->wpdb->delete( $this->table_name( 'schedule_processes' ), $data );
3202 4291 }
3203 4292 return false;
3204 4293 }
3205 4294
3206 -
3207 4295 /**
3208 4296 * Method get_regular_process_by_item_id_type_slug
3209 4297 *
3210 4298 * @param integer $item_id item id.
@@ -3213,21 +4301,83 @@
3213 4301 *
3214 4302 * @return mixed result
3215 4303 */
3216 4304 public function get_regular_process_by_item_id_type_slug( $item_id, $type, $process_slug ) {
3217 - return $this->wpdb->get_row( $this->wpdb->prepare( ' SELECT pr.* FROM ' . $this->table_name( 'schedule_processes' ) . ' pr WHERE pr.item_id = %d AND pr.type = %s AND pr.process_slug = %s', $item_id, $type, $process_slug ) );
4305 + $table_name = esc_sql( $this->table_name( 'schedule_processes' ) );
4306 + return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT pr.* FROM {$table_name} pr WHERE pr.item_id = %d AND pr.type = %s AND pr.process_slug = %s", $item_id, $type, $process_slug ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
3218 4307 }
3219 4308
3220 4309 /**
3221 - * Method log_system_query
4310 + * Log SQL queries for debugging via hook.
3222 4311 *
3223 - * @param array $params params.
3224 - * @param string $sql query.
4312 + * This method provides a structured way to log SQL queries for development/debugging.
4313 + * It does NOT use error_log() directly - instead, it fires the `mainwp_log_system_query`
4314 + * action hook, allowing external listeners (logging plugins, debug tools) to handle
4315 + * the log output appropriately.
4316 + *
4317 + * To enable query logging:
4318 + * 1. Set `$params['dev_log_query'] = 1` when calling database methods
4319 + * 2. Add a listener to the `mainwp_log_system_query` action hook
4320 + *
4321 + * Example listener:
4322 + * ```php
4323 + * add_action( 'mainwp_log_system_query', function( $params, $sql, $caller ) {
4324 + * error_log( 'MainWP Query: ' . $sql );
4325 + * }, 10, 3 );
4326 + * ```
4327 + *
4328 + * @param array $params Query parameters. Set 'dev_log_query' to enable logging.
4329 + * @param string $sql The SQL query string.
4330 + * @param mixed $caller Instance of caller class (optional).
3225 4331 * @return void
3226 4332 */
3227 - public function log_system_query( $params, $sql ) {
4333 + public function log_system_query( $params, $sql, $caller = false ) {
4334 + $params = apply_filters( 'mainwp_log_system_query_params', $params, $sql, $caller );
3228 4335 if ( is_array( $params ) && ! empty( $params['dev_log_query'] ) && ! empty( $sql ) ) {
3229 - error_log( $sql ); //phpcs:ignore -- NOSONAR - for dev.
3230 - do_action( 'mainwp_log_system_query', $params, $sql );
4336 + do_action( 'mainwp_log_system_query', $params, $sql, $caller );
3231 4337 }
4338 + }
4339 +
4340 + /**
4341 + * Method get_core_tables().
4342 + *
4343 + * Returns the list of core tables used by MainWP for validation data integrity and other purposes.
4344 + *
4345 + * @return array Core tables info
4346 + */
4347 + public function get_core_tables() {
4348 +
4349 + $core_tables = array(
4350 + $this->table_name( 'wp_clients' ),
4351 + $this->table_name( 'wp_clients_fields' ),
4352 + $this->table_name( 'wp_clients_field_values' ),
4353 + $this->table_name( 'wp_clients_contacts' ),
4354 + $this->table_name( 'monitors' ),
4355 + $this->table_name( 'monitor_heartbeat' ),
4356 + $this->table_name( 'monitor_stat_hourly' ),
4357 + $this->table_name( 'wp' ),
4358 + $this->table_name( 'wp_sync' ),
4359 + $this->table_name( 'wp_options' ),
4360 + $this->table_name( 'wp_settings_backup' ),
4361 + $this->table_name( 'users' ),
4362 + $this->table_name( 'wp_status' ),
4363 + $this->table_name( 'group' ),
4364 + $this->table_name( 'wp_group' ),
4365 + $this->table_name( 'lookup_item_objects' ),
4366 + $this->table_name( 'wp_backup_progress' ),
4367 + $this->table_name( 'wp_backup' ),
4368 + $this->table_name( 'api_keys' ),
4369 + $this->table_name( 'action_log' ),
4370 + $this->table_name( 'request_log' ),
4371 + $this->table_name( 'schedule_processes' ),
4372 + $this->table_name( 'cost_tracker' ),
4373 + );
4374 +
4375 + if ( ! defined( 'MAINWP_MODULE_LOG_ENABLED' ) || MAINWP_MODULE_LOG_ENABLED ) {
4376 + $core_tables[] = $this->table_name( 'wp_logs' );
4377 + $core_tables[] = $this->table_name( 'wp_logs_meta' );
4378 + $core_tables[] = $this->table_name( 'wp_logs_meta_archive' );
4379 + }
4380 +
4381 + return apply_filters( 'mainwp_database_core_tables', $core_tables );
3232 4382 }
3233 4383 }