PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 6.3
MainWP Dashboard: Self-hosted WordPress Management for Agencies v6.3
6.3 6.2.3 6.2.2 6.2.1 6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 All 157 releases
← All changes | modules/cost-tracker/classes/class-cost-tracker-db.php +425 -75 6.1.6 → 6.3 View file →
@@ -30,10 +30,22 @@
30 30 * @var string Version.
31 31 */
32 32 private $cost_tracker_db_version = '1.0.18';
33 33
34 + /**
35 + * Set once the price column is confirmed decimal(26,8) NOT NULL.
36 + *
37 + * Sites already stored at the current db version never re-enter update_db_cost().
38 + */
39 + const PRICE_NOT_NULL_OPTION = 'mainwp_module_cost_tracker_price_not_null';
34 40
35 41 /**
42 + * Unix time before which a failed price repair is not retried.
43 + */
44 + const PRICE_NOT_NULL_RETRY_OPTION = 'mainwp_module_cost_tracker_price_not_null_after';
45 +
46 +
47 + /**
36 48 * Static variable to hold the single instance of the class.
37 49 *
38 50 * @static
39 51 *
@@ -77,8 +89,9 @@
77 89 $currentVersion = false;
78 90 }
79 91
80 92 if ( $currentVersion === $this->cost_tracker_db_version ) {
93 + $this->maybe_repair_cost_tracker_price_not_null();
81 94 return;
82 95 }
83 96
84 97 $charset_collate = $wpdb->get_charset_collate();
@@ -131,15 +144,14 @@
131 144 * @return void
132 145 */
133 146 public function update_db_cost( $current_version ) { //phpcs:ignore -- NOSONAR - complex.
134 147 if ( ! empty( $current_version ) ) {
135 - if ( version_compare( $current_version, '1.0.8', '<' ) ) {
136 - $table = esc_sql( $this->table_name( 'cost_tracker' ) );
137 - $this->wpdb->query( "ALTER TABLE {$table} MODIFY COLUMN price decimal(26,8)" ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
138 - }
148 + // 1.0.8 widened price but omitted NOT NULL. MySQL drops attributes that
149 + // MODIFY does not repeat. maybe_* owns the success flag and the hourly retry.
150 + $this->maybe_repair_cost_tracker_price_not_null();
139 151 if ( version_compare( $current_version, '1.0.9', '<' ) ) {
140 152 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
141 - $this->wpdb->query( "ALTER TABLE {$table} DROP COLUMN author" ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
153 + $this->wpdb->query( "ALTER TABLE {$table} DROP COLUMN author" ); //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd and a table name cannot be a prepare() placeholder.
142 154 }
143 155
144 156 if ( version_compare( $current_version, '1.0.13', '<' ) ) {
145 157 $costs = $this->get_cost_tracker_by( 'all' );
@@ -178,8 +190,281 @@
178 190 }
179 191 }
180 192
181 193 /**
194 + * Repair a nullable price column left by the 1.0.8 precision change.
195 + *
196 + * Already-upgraded installs are stored at the current db version, so they
197 + * never re-enter update_db_cost(). A confirmed column is remembered so later
198 + * requests do not SHOW COLUMNS. A failed ALTER is retried at most hourly.
199 + *
200 + * @return bool True when the column is confirmed decimal(26,8) NOT NULL.
201 + */
202 + public function maybe_repair_cost_tracker_price_not_null() {
203 + if ( get_option( self::PRICE_NOT_NULL_OPTION ) ) {
204 + return false;
205 + }
206 +
207 + $retry_after = (int) get_option( self::PRICE_NOT_NULL_RETRY_OPTION );
208 + if ( $retry_after && time() < $retry_after ) {
209 + return false;
210 + }
211 +
212 + if ( $this->repair_cost_tracker_price_not_null() ) {
213 + update_option( self::PRICE_NOT_NULL_OPTION, 1 );
214 + delete_option( self::PRICE_NOT_NULL_RETRY_OPTION );
215 + return true;
216 + }
217 +
218 + update_option( self::PRICE_NOT_NULL_RETRY_OPTION, time() + HOUR_IN_SECONDS, false );
219 + return false;
220 + }
221 +
222 + /**
223 + * Make price decimal(26,8) NOT NULL without changing stored prices.
224 + *
225 + * NULL prices are set to 0 first so strict SQL mode can apply NOT NULL.
226 + * If the ALTER or the follow-up check fails, those prices are set back to NULL.
227 + * Rows that already have a price are left as they are.
228 + *
229 + * @return bool True when the column is decimal(26,8) NOT NULL.
230 + */
231 + public function repair_cost_tracker_price_not_null() {
232 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
233 +
234 + $this->wpdb->last_error = '';
235 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DDL introspection; table name is escaped and is not a prepare() placeholder.
236 + $column = $this->wpdb->get_row( "SHOW COLUMNS FROM {$table} LIKE 'price'" );
237 + if ( '' !== $this->wpdb->last_error ) {
238 + return false;
239 + }
240 +
241 + if ( empty( $column ) ) {
242 + return false;
243 + }
244 +
245 + if ( $this->price_column_is_not_null_decimal( $column ) ) {
246 + return true;
247 + }
248 +
249 + $zeroed_ids = array();
250 + $cache_rows = array();
251 + if ( 'YES' === $column->Null ) {
252 + $null_ids = $this->cost_tracker_null_price_ids( $table );
253 + if ( null === $null_ids ) {
254 + return false;
255 + }
256 + $cache_rows = $this->cost_tracker_cache_rows( $table, $null_ids );
257 + if ( null === $cache_rows ) {
258 + return false;
259 + }
260 + $zero_result = $this->set_cost_tracker_prices_to_zero( $table, $null_ids );
261 + $zeroed_ids = $zero_result['zeroed'];
262 + if ( ! $zero_result['ok'] ) {
263 + $this->restore_null_cost_prices( $table, $zeroed_ids );
264 + $this->invalidate_repaired_cost_prices( $zeroed_ids, $cache_rows );
265 + return false;
266 + }
267 + }
268 +
269 + $this->apply_cost_tracker_price_not_null( $table );
270 +
271 + $this->wpdb->last_error = '';
272 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DDL introspection; table name is escaped and is not a prepare() placeholder.
273 + $column = $this->wpdb->get_row( "SHOW COLUMNS FROM {$table} LIKE 'price'" );
274 + if ( '' !== $this->wpdb->last_error || empty( $column ) || ! $this->price_column_is_not_null_decimal( $column ) ) {
275 + $this->restore_null_cost_prices( $table, $zeroed_ids );
276 + $this->invalidate_repaired_cost_prices( $zeroed_ids, $cache_rows );
277 + return false;
278 + }
279 +
280 + $this->invalidate_repaired_cost_prices( $zeroed_ids, $cache_rows );
281 + return true;
282 + }
283 +
284 + /**
285 + * Ids whose price is SQL NULL, or null when the lookup failed.
286 + *
287 + * @param string $table Escaped cost tracker table name.
288 + *
289 + * @return array|null
290 + */
291 + protected function cost_tracker_null_price_ids( $table ) {
292 + $this->wpdb->last_error = '';
293 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is escaped; no user input.
294 + $ids = $this->wpdb->get_col( "SELECT id FROM {$table} WHERE price IS NULL" );
295 + if ( '' !== $this->wpdb->last_error || ! is_array( $ids ) ) {
296 + return null;
297 + }
298 +
299 + $ids = array_map( 'absint', $ids );
300 + return array_values( array_filter( $ids ) );
301 + }
302 +
303 + /**
304 + * Row fields needed to clear slug and site/client cache keys.
305 + *
306 + * @param string $table Escaped cost tracker table name.
307 + * @param array $ids Cost ids.
308 + *
309 + * @return array|null Rows keyed by int id, or null when the lookup fails or omits an id.
310 + */
311 + protected function cost_tracker_cache_rows( $table, $ids ) {
312 + if ( empty( $ids ) ) {
313 + return array();
314 + }
315 +
316 + $ids = array_values( array_filter( array_map( 'absint', $ids ) ) );
317 + $id_list = implode( ',', $ids );
318 + $this->wpdb->last_error = '';
319 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- ids are absint; table name is escaped.
320 + $rows = $this->wpdb->get_results( "SELECT id, slug, product_type, sites, clients FROM {$table} WHERE id IN ({$id_list})" );
321 + if ( '' !== $this->wpdb->last_error || ! is_array( $rows ) ) {
322 + return null;
323 + }
324 +
325 + $by_id = array();
326 + foreach ( $rows as $row ) {
327 + $by_id[ absint( $row->id ) ] = $row;
328 + }
329 +
330 + foreach ( $ids as $id ) {
331 + if ( ! isset( $by_id[ $id ] ) ) {
332 + return null;
333 + }
334 + }
335 +
336 + return $by_id;
337 + }
338 +
339 + /**
340 + * Replace captured NULL prices with 0.
341 + *
342 + * Updates one id at a time so a concurrent save to 0 is not treated as a
343 + * repair write and is not restored to NULL if the ALTER fails.
344 + *
345 + * @param string $table Escaped cost tracker table name.
346 + * @param array $ids Cost ids.
347 + *
348 + * @return array{zeroed: array<int>, ok: bool} Ids already zeroed, and whether the batch finished.
349 + */
350 + private function set_cost_tracker_prices_to_zero( $table, $ids ) {
351 + $zeroed_ids = array();
352 + if ( empty( $ids ) ) {
353 + return array(
354 + 'zeroed' => $zeroed_ids,
355 + 'ok' => true,
356 + );
357 + }
358 +
359 + foreach ( $ids as $id ) {
360 + $id = absint( $id );
361 + if ( empty( $id ) ) {
362 + continue;
363 + }
364 + $updated = $this->zero_one_null_cost_price( $table, $id );
365 + if ( false === $updated ) {
366 + return array(
367 + 'zeroed' => $zeroed_ids,
368 + 'ok' => false,
369 + );
370 + }
371 + if ( $updated > 0 ) {
372 + $zeroed_ids[] = $id;
373 + }
374 + }
375 +
376 + return array(
377 + 'zeroed' => $zeroed_ids,
378 + 'ok' => true,
379 + );
380 + }
381 +
382 + /**
383 + * Zero one still-NULL price row.
384 + *
385 + * @param string $table Escaped cost tracker table name.
386 + * @param int $id Cost id.
387 + *
388 + * @return int|false Rows changed, or false on query failure.
389 + */
390 + protected function zero_one_null_cost_price( $table, $id ) {
391 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is escaped; id is absint. price IS NULL skips a row edited after the id list was captured.
392 + return $this->wpdb->query( $this->wpdb->prepare( "UPDATE {$table} SET price = 0 WHERE id = %d AND price IS NULL", $id ) );
393 + }
394 +
395 + /**
396 + * Put repair-zeroed prices back to SQL NULL after a failed NOT NULL change.
397 + *
398 + * Only ids the zeroing UPDATE changed are restored. A user-saved price of 0
399 + * that the repair never wrote is left alone.
400 + *
401 + * @param string $table Escaped cost tracker table name.
402 + * @param array $ids Cost ids the repair zeroed.
403 + *
404 + * @return bool
405 + */
406 + private function restore_null_cost_prices( $table, $ids ) {
407 + if ( empty( $ids ) ) {
408 + return true;
409 + }
410 +
411 + $id_list = implode( ',', array_map( 'absint', $ids ) );
412 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- ids are absint; table name is escaped. price = 0 skips a row edited after it was zeroed.
413 + $restored = $this->wpdb->query( "UPDATE {$table} SET price = NULL WHERE id IN ({$id_list}) AND price = 0" );
414 + return false !== $restored;
415 + }
416 +
417 + /**
418 + * Apply decimal(26,8) NOT NULL. DDL cannot share a transaction with the price update.
419 + *
420 + * @param string $table Escaped cost tracker table name.
421 + *
422 + * @return void
423 + */
424 + protected function apply_cost_tracker_price_not_null( $table ) {
425 + $suppress = $this->wpdb->suppress_errors();
426 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DDL; table name is escaped and is not a prepare() placeholder.
427 + $this->wpdb->query( "ALTER TABLE {$table} MODIFY COLUMN price decimal(26,8) NOT NULL" );
428 + $this->wpdb->suppress_errors( $suppress );
429 + }
430 +
431 + /**
432 + * Drop cached cost rows whose price was rewritten or restored.
433 + *
434 + * @param array $ids Cost ids.
435 + * @param array $cache_rows Rows keyed by id with slug/sites/clients fields.
436 + *
437 + * @return void
438 + */
439 + private function invalidate_repaired_cost_prices( $ids, $cache_rows = array() ) {
440 + if ( empty( $ids ) ) {
441 + $this->invalidate_cost_tracker_caches();
442 + return;
443 + }
444 +
445 + foreach ( $ids as $id ) {
446 + $id = absint( $id );
447 + $row = isset( $cache_rows[ $id ] ) ? $cache_rows[ $id ] : null;
448 + $this->invalidate_cost_tracker_caches( $id, $row );
449 + }
450 + }
451 +
452 + /**
453 + * Whether SHOW COLUMNS reports the intended price definition.
454 + *
455 + * @param object $column Column row from SHOW COLUMNS.
456 + *
457 + * @return bool
458 + */
459 + private function price_column_is_not_null_decimal( $column ) {
460 + $type = strtolower( (string) $column->Type );
461 + $type = preg_replace( '/\s+/', '', $type );
462 +
463 + return 'NO' === $column->Null && 'decimal(26,8)' === $type;
464 + }
465 +
466 + /**
182 467 * Method update_cost_tracker().
183 468 *
184 469 * @param array $update Cost tracker array data.
185 470 *
@@ -240,17 +525,11 @@
240 525 // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- write operation; caching not applicable for UPDATE.
241 526 $wpdb->update( $this->table_name( 'cost_tracker' ), $update, array( 'id' => intval( $id ) ) );
242 527 $this->invalidate_cost_tracker_caches( $id );
243 528 return $this->get_cost_tracker_by( 'id', $id );
244 - } else {
245 - if ( isset( $update['id'] ) ) {
246 - unset( $update['id'] );
247 - }
248 -
249 - if ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
250 - $this->invalidate_cost_tracker_caches();
251 - return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
252 - }
529 + } elseif ( $wpdb->insert( $this->table_name( 'cost_tracker' ), $update ) ) {
530 + $this->invalidate_cost_tracker_caches();
531 + return $this->get_cost_tracker_by( 'id', $wpdb->insert_id );
253 532 }
254 533 return false;
255 534 }
256 535
@@ -269,26 +548,15 @@
269 548 if ( empty( $item_id ) ) {
270 549 return false;
271 550 }
272 551
273 - $obj_name = '';
552 + $selected_lists = array(
553 + 'site' => is_array( $selected_sites ) ? $selected_sites : array(),
554 + 'tag' => is_array( $selected_groups ) ? $selected_groups : array(),
555 + 'client' => is_array( $selected_clients ) ? $selected_clients : array(),
556 + );
274 557
275 - $new_obj_ids = array();
276 -
277 - if ( ! empty( $selected_sites ) ) {
278 - $obj_name = 'site';
279 - $new_obj_ids = $selected_sites;
280 - } elseif ( ! empty( $selected_groups ) ) {
281 - $obj_name = 'tag';
282 - $new_obj_ids = $selected_groups;
283 - } elseif ( ! empty( $selected_clients ) ) {
284 - $obj_name = 'client';
285 - $new_obj_ids = $selected_clients;
286 - }
287 -
288 - if ( ! empty( $obj_name ) ) {
289 - static::get_instance()->update_lookup_cost( $item_id, $obj_name, $new_obj_ids );
290 - } else {
558 + if ( ! array_filter( $selected_lists ) ) {
291 559 // to support saving cost without selected sites, tags, clients.
292 560 MainWP_DB::instance()->delete_lookup_items(
293 561 'object_name',
294 562 array(
@@ -296,10 +564,17 @@
296 564 'item_id' => $item_id,
297 565 'object_names' => array( 'site', 'tag', 'client' ),
298 566 )
299 567 );
568 + return true;
569 + }
300 570
571 + // A cost can be saved against sites, tags and clients at once, so each list is
572 + // synced against its own object name; an empty list prunes its own rows.
573 + foreach ( $selected_lists as $obj_name => $new_obj_ids ) {
574 + static::get_instance()->update_lookup_cost( $item_id, $obj_name, $new_obj_ids );
301 575 }
576 +
302 577 return true;
303 578 }
304 579
305 580 /**
@@ -318,10 +593,8 @@
318 593 if ( empty( $item_id ) || ! is_array( $new_obj_ids ) || ! in_array( $obj_name, $allows ) ) {
319 594 return false;
320 595 }
321 596
322 - $remove_obj_names = array_diff( $allows, array( $obj_name ) );
323 -
324 597 $found_look_ids = array();
325 598 $existed_look_id = array();
326 599
327 600 $results = MainWP_DB::instance()->get_lookup_items( 'cost', $item_id, $obj_name );
@@ -349,20 +622,8 @@
349 622 $remove_ids = array_diff( $found_look_ids, $new_look_ids );
350 623 if ( $remove_ids ) {
351 624 MainWP_DB::instance()->delete_lookup_items( 'lookup_id', array( 'lookup_ids' => $remove_ids ) );
352 625 }
353 -
354 - if ( ! empty( $remove_obj_names ) ) {
355 - MainWP_DB::instance()->delete_lookup_items(
356 - 'object_name',
357 - array(
358 - 'item_name' => 'cost',
359 - 'item_id' => $item_id,
360 - 'object_names' => $remove_obj_names,
361 -
362 - )
363 - );
364 - }
365 626 }
366 627
367 628 /**
368 629 * Method get_cost_tracker_by().
@@ -443,9 +704,9 @@
443 704 if ( ! empty( $selected_ids ) ) {
444 705 $where .= ' AND ct.id IN (' . implode( ',', $selected_ids ) . ') ';
445 706 }
446 707 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
447 - // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is escaped with esc_sql(); $where and $limit are from internal arrays only
708 + // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $table is escaped with esc_sql(); $where holds id lists from wp_parse_id_list() and values escaped through prepare_fields_array(); $limit is built from intval()ed paging.
448 709 return $wpdb->get_results( "SELECT * FROM {$table} ct WHERE 1 {$where}{$limit}" );
449 710 }
450 711
451 712 $where = '';
@@ -459,15 +720,22 @@
459 720 return $cached;
460 721 }
461 722
462 723 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
463 - $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `id`=%d ", $value );
724 + $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `id`=%d ", $value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
464 725 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
465 726 $result = $wpdb->get_row( $sql, OBJECT );
466 727 wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
467 728 return $result;
468 729 } elseif ( 'count' === $by ) {
469 - $cache_key = 'mainwp_cost_tracker_' . md5( wp_json_encode( array( 'method' => 'get_cost_tracker_by', 'by' => 'count' ) ) ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
730 + $cache_key = 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
731 + wp_json_encode(
732 + array(
733 + 'method' => 'get_cost_tracker_by',
734 + 'by' => 'count',
735 + )
736 + )
737 + );
470 738 $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
471 739 if ( false !== $cached ) {
472 740 return $cached;
473 741 }
@@ -472,9 +740,9 @@
472 740 return $cached;
473 741 }
474 742
475 743 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
476 - $sql = "SELECT count(*) FROM {$table}";
744 + $sql = "SELECT count(*) FROM {$table}";
477 745 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with static table name that is escaped with esc_sql(); no dynamic input.
478 746 $result = $wpdb->get_var( $sql );
479 747 wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
480 748 return $result;
@@ -482,13 +750,13 @@
482 750 $cost_ids = explode( ',', $value );
483 751 $cost_ids = MainWP_Utility::array_numeric_filter( $cost_ids );
484 752 if ( ! empty( $cost_ids ) ) {
485 753 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
486 - $sql = "SELECT * FROM {$table} WHERE `id` IN (" . implode( ',', $cost_ids ) . ' )';
754 + $sql = "SELECT * FROM {$table} WHERE `id` IN (" . implode( ',', $cost_ids ) . ' )';
487 755 }
488 756 } elseif ( 'site_id' === $by || 'client_id' === $by ) {
489 757 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
490 - $sql = "SELECT * FROM {$table}";
758 + $sql = "SELECT * FROM {$table}";
491 759 } elseif ( 'slug' === $by && is_string( $value ) ) {
492 760 if ( in_array( $product_type, array( 'plugin', 'theme' ), true ) ) {
493 761 $cache_key = $this->get_cache_key_for_cost_tracker( $by, $value, array( 'product_type' => $product_type ) );
494 762 $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
@@ -496,9 +764,9 @@
496 764 return $cached;
497 765 }
498 766
499 767 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
500 - $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `slug`=%s AND product_type = %s ", $value, $product_type );
768 + $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `slug`=%s AND product_type = %s ", $value, $product_type ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd.
501 769 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
502 770 $result = $wpdb->get_row( $sql, OBJECT );
503 771 wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
504 772 return $result;
@@ -507,9 +775,9 @@
507 775 if ( ! empty( $product_type ) ) {
508 776 $where = $wpdb->prepare( ' AND product_type = %s ', $product_type );
509 777 }
510 778 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
511 - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $where is itself from $wpdb->prepare(); concatenation into prepare is safe here.
779 + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is esc_sql'd; $where is itself from $wpdb->prepare(), so concatenation into prepare is safe here.
512 780 $sql = $wpdb->prepare( "SELECT * FROM {$table} WHERE `slug`=%s " . $where, $value );
513 781 }
514 782 }
515 783
@@ -596,14 +864,14 @@
596 864 $deleted = false;
597 865
598 866 if ( 'id' === $by ) {
599 867 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
600 - if ( $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE id=%d ", $value ) ) ) { //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- delete operation that invalidates caches below.
868 + if ( $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE id=%d ", $value ) ) ) { //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- delete operation that invalidates caches below; table name is esc_sql'd.
601 869 $deleted = true;
602 870 }
603 871 } else {
604 872 $table = esc_sql( $this->table_name( 'cost_tracker' ) );
605 - if ( $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE site_id=%d ", $value ) ) ) { //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- delete operation that invalidates caches below.
873 + if ( $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE site_id=%d ", $value ) ) ) { //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- delete operation that invalidates caches below; table name is esc_sql'd.
606 874 $deleted = true;
607 875 }
608 876 }
609 877
@@ -608,9 +876,9 @@
608 876 }
609 877
610 878 if ( $deleted ) {
611 879 $lookup_table = esc_sql( $this->table_name( 'lookup_item_objects' ) );
612 - $wpdb->query( $wpdb->prepare( "DELETE FROM {$lookup_table} WHERE item_id=%d AND item_name = \"cost\"", $value ) ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching -- delete operation; caching is not applicable for write operations.
880 + $wpdb->query( $wpdb->prepare( "DELETE FROM {$lookup_table} WHERE item_id=%d AND item_name = \"cost\"", $value ) ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- delete operation; caching is not applicable for write operations; table name is esc_sql'd.
613 881 $this->invalidate_cost_tracker_caches( 'id' === $by ? $value : null );
614 882 }
615 883
616 884 $snapshot_info = Cost_Tracker_Admin::get_costs_snapshot_info();
@@ -623,9 +891,8 @@
623 891 * @param bool $deleted Deleted.
624 892 * @param array $snapshot_info costs snapshot info.
625 893 *
626 894 * @since 6.0
627 - *
628 895 */
629 896 do_action( 'mainwp_cost_tracker_deleted', $by, $value, $deleted, $snapshot_info );
630 897
631 898 return $deleted;
@@ -677,10 +944,10 @@
677 944 $cache_key = 'mainwp_cost_tracker_all';
678 945 $result = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
679 946
680 947 if ( false === $result ) {
681 - $table = esc_sql( $this->table_name( 'cost_tracker' ) );
682 - $sql = "SELECT * FROM {$table}";
948 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
949 + $sql = "SELECT * FROM {$table}";
683 950 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql(); results cached immediately after retrieval
684 951 $result = $wpdb->get_results( $sql, OBJECT );
685 952 wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
686 953 }
@@ -850,10 +1117,10 @@
850 1117 $cache_key = 'mainwp_cost_tracker_all';
851 1118 $result = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
852 1119
853 1120 if ( false === $result ) {
854 - $table = esc_sql( $this->table_name( 'cost_tracker' ) );
855 - $sql = "SELECT * FROM {$table}";
1121 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
1122 + $sql = "SELECT * FROM {$table}";
856 1123 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql(); results cached immediately after retrieval
857 1124 $result = $wpdb->get_results( $sql, OBJECT );
858 1125 wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
859 1126 }
@@ -952,9 +1219,16 @@
952 1219 $sum_data = isset( $params['sum_data'] ) ? $params['sum_data'] : '';
953 1220 $where = '';
954 1221 $sql = '';
955 1222 if ( 'all' === $sum_data ) {
956 - $cache_key = 'mainwp_cost_tracker_' . md5( wp_json_encode( array( 'method' => 'get_summary_data', 'sum_data' => 'all' ) ) ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1223 + $cache_key = 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1224 + wp_json_encode(
1225 + array(
1226 + 'method' => 'get_summary_data',
1227 + 'sum_data' => 'all',
1228 + )
1229 + )
1230 + );
957 1231 $cached = wp_cache_get( $cache_key, 'mainwp_cost_tracker' );
958 1232 if ( false !== $cached ) {
959 1233 return $cached;
960 1234 }
@@ -959,10 +1233,10 @@
959 1233 return $cached;
960 1234 }
961 1235
962 1236 $where .= ' AND co.cost_status = "active" AND co.type = "subscription" ';
963 - $table = esc_sql( $this->table_name( 'cost_tracker' ) );
964 - $sql = "SELECT * FROM {$table} co WHERE 1 {$where} ORDER BY co.next_renewal ASC ";
1237 + $table = esc_sql( $this->table_name( 'cost_tracker' ) );
1238 + $sql = "SELECT * FROM {$table} co WHERE 1 {$where} ORDER BY co.next_renewal ASC ";
965 1239 // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is safely constructed with $wpdb->prepare() above; table name is escaped with esc_sql().
966 1240 $result = $wpdb->get_results( $sql );
967 1241 wp_cache_set( $cache_key, $result, 'mainwp_cost_tracker' );
968 1242 return $result;
@@ -1004,14 +1278,18 @@
1004 1278 *
1005 1279 * @return string Cache key.
1006 1280 */
1007 1281 private function get_cache_key_for_cost_tracker( $by, $value, $params = array() ) {
1282 + if ( in_array( $by, array( 'id', 'site_id', 'client_id' ), true ) && is_numeric( $value ) ) {
1283 + $value = (int) $value;
1284 + }
1285 +
1008 1286 $identifier = wp_json_encode(
1009 1287 array(
1010 - 'method' => 'get_cost_tracker_by',
1011 - 'by' => $by,
1012 - 'value' => $value,
1013 - 'params' => $params,
1288 + 'method' => 'get_cost_tracker_by',
1289 + 'by' => $by,
1290 + 'value' => $value,
1291 + 'params' => $params,
1014 1292 )
1015 1293 );
1016 1294 return 'mainwp_cost_tracker_' . md5( $identifier ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1017 1295 }
@@ -1018,19 +1296,91 @@
1018 1296
1019 1297 /**
1020 1298 * Invalidate cost tracker caches.
1021 1299 *
1022 - * @param int|null $cost_id Cost ID to invalidate specific caches, or null for all caches.
1300 + * @param int|null $cost_id Cost ID to invalidate specific caches, or null for shared caches only.
1301 + * @param object|null $row Optional row with slug, product_type, sites, and clients.
1023 1302 *
1024 1303 * @return void
1025 1304 */
1026 - private function invalidate_cost_tracker_caches( $cost_id = null ) {
1027 - wp_cache_delete( 'mainwp_cost_tracker_' . md5( wp_json_encode( array( 'method' => 'get_cost_tracker_by', 'by' => 'count' ) ) ), 'mainwp_cost_tracker' ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1305 + private function invalidate_cost_tracker_caches( $cost_id = null, $row = null ) {
1306 + wp_cache_delete(
1307 + 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1308 + wp_json_encode(
1309 + array(
1310 + 'method' => 'get_cost_tracker_by',
1311 + 'by' => 'count',
1312 + )
1313 + )
1314 + ),
1315 + 'mainwp_cost_tracker'
1316 + );
1028 1317 wp_cache_delete( 'mainwp_cost_tracker_all', 'mainwp_cost_tracker' );
1029 - wp_cache_delete( 'mainwp_cost_tracker_' . md5( wp_json_encode( array( 'method' => 'get_summary_data', 'sum_data' => 'all' ) ) ), 'mainwp_cost_tracker' ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1318 + wp_cache_delete(
1319 + 'mainwp_cost_tracker_' . md5( // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1320 + wp_json_encode(
1321 + array(
1322 + 'method' => 'get_summary_data',
1323 + 'sum_data' => 'all',
1324 + )
1325 + )
1326 + ),
1327 + 'mainwp_cost_tracker'
1328 + );
1030 1329
1031 - if ( $cost_id ) {
1032 - $cost_id = absint( $cost_id );
1033 - wp_cache_delete( 'mainwp_cost_tracker_' . md5( wp_json_encode( array( 'method' => 'get_cost_tracker_by', 'by' => 'id', 'value' => $cost_id ) ) ), 'mainwp_cost_tracker' ); // NOSONAR - MD5 used for cache key generation only, not cryptographic (security) purposes.
1330 + if ( empty( $cost_id ) ) {
1331 + return;
1332 + }
1333 +
1334 + $cost_id = absint( $cost_id );
1335 + wp_cache_delete(
1336 + $this->get_cache_key_for_cost_tracker( 'id', $cost_id, array() ),
1337 + 'mainwp_cost_tracker'
1338 + );
1339 +
1340 + if ( empty( $row ) || ! is_object( $row ) ) {
1341 + return;
1342 + }
1343 +
1344 + if ( ! empty( $row->slug ) && is_string( $row->slug ) ) {
1345 + $product_type = isset( $row->product_type ) ? (string) $row->product_type : '';
1346 + wp_cache_delete(
1347 + $this->get_cache_key_for_cost_tracker( 'slug', $row->slug, array() ),
1348 + 'mainwp_cost_tracker'
1349 + );
1350 + if ( '' !== $product_type ) {
1351 + wp_cache_delete(
1352 + $this->get_cache_key_for_cost_tracker( 'slug', $row->slug, array( 'product_type' => $product_type ) ),
1353 + 'mainwp_cost_tracker'
1354 + );
1355 + }
1356 + }
1357 +
1358 + $sites = ! empty( $row->sites ) ? json_decode( $row->sites, true ) : array();
1359 + if ( is_array( $sites ) ) {
1360 + foreach ( $sites as $site_id ) {
1361 + $site_id = absint( $site_id );
1362 + if ( empty( $site_id ) ) {
1363 + continue;
1364 + }
1365 + wp_cache_delete(
1366 + $this->get_cache_key_for_cost_tracker( 'site_id', $site_id, array() ),
1367 + 'mainwp_cost_tracker'
1368 + );
1369 + }
1370 + }
1371 +
1372 + $clients = ! empty( $row->clients ) ? json_decode( $row->clients, true ) : array();
1373 + if ( is_array( $clients ) ) {
1374 + foreach ( $clients as $client_id ) {
1375 + $client_id = absint( $client_id );
1376 + if ( empty( $client_id ) ) {
1377 + continue;
1378 + }
1379 + wp_cache_delete(
1380 + $this->get_cache_key_for_cost_tracker( 'client_id', $client_id, array() ),
1381 + 'mainwp_cost_tracker'
1382 + );
1383 + }
1034 1384 }
1035 1385 }
1036 1386 }