PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 6.2.2
MainWP Dashboard: Self-hosted WordPress Management for Agencies v6.2.2
6.2.2 6.2.1 6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 5.2 5.2.1 All 155 releases
← All changes | class/class-mainwp-db-client.php +379 -90 5.1.1 → 6.2.2 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_Client
14 19 *
15 20 * @package MainWP\Dashboard
@@ -136,9 +141,9 @@
136 141 facebook varchar(255) NOT NULL default "",
137 142 twitter varchar(255) NOT NULL default "",
138 143 instagram varchar(255) NOT NULL default "",
139 144 linkedin varchar(255) NOT NULL default "",
140 - UNIQUE KEY contact_email(contact_email)';
145 + UNIQUE KEY contact_email (contact_email)';
141 146
142 147 if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.69', '<=' ) ) {
143 148 $tbl .= ',
144 149 PRIMARY KEY (contact_id)';
@@ -168,14 +173,16 @@
168 173 $convert_client_reports_dt = true;
169 174 }
170 175
171 176 if ( ! $convert_pro_reports_dt && ! $convert_client_reports_dt ) {
172 - $rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'pro_reports_token' ) . "'" );
177 + $table_token = esc_sql( $this->table_name( 'pro_reports_token' ) );
178 + $rslt = $this->query( 'SHOW TABLES LIKE ' . $this->wpdb->prepare( '%s', $table_token ) );
173 179 if ( static::num_rows( $rslt ) ) {
174 180 $convert_pro_reports_dt = true;
175 181 }
176 182
177 - $rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'client_report_token' ) . "'" );
183 + $table_report_token = esc_sql( $this->table_name( 'client_report_token' ) );
184 + $rslt = $this->query( 'SHOW TABLES LIKE ' . $this->wpdb->prepare( '%s', $table_report_token ) );
178 185 if ( static::num_rows( $rslt ) ) {
179 186 $convert_client_reports_dt = true;
180 187 }
181 188 }
@@ -548,35 +555,36 @@
548 555 * @return mixed $result results.
549 556 */
550 557 public function get_wp_client_contact_by( $by = 'contact_id', $value = null, $obj = OBJECT, $params = array() ) { // phpcs:ignore -- NOSONAR - complex.
551 558
552 - $sql = '';
559 + $sql = '';
560 + $table_contacts = esc_sql( $this->table_name( 'wp_clients_contacts' ) );
553 561
554 562 if ( 'client_id' === $by && ! empty( $value ) ) {
555 - $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' wc WHERE wc.contact_client_id=%d ', $value );
563 + $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $table_contacts . ' wc WHERE wc.contact_client_id=%d ', $value );
556 564 }
557 565
558 566 if ( ! empty( $sql ) ) {
559 567 if ( OBJECT === $obj ) {
560 - $result = $this->wpdb->get_results( $sql, OBJECT );
568 + $result = $this->wpdb->get_results( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
561 569 } else {
562 - $result = $this->wpdb->get_results( $sql, ARRAY_A );
570 + $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
563 571 }
564 572 return $result;
565 573 }
566 574
567 575 if ( 'contact_id' === $by && is_numeric( $value ) ) {
568 - $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' wc WHERE wc.contact_id=%d ', $value );
576 + $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $table_contacts . ' wc WHERE wc.contact_id=%d ', $value );
569 577 } elseif ( 'contact_email' === $by && ! empty( $value ) ) {
570 - $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' wc WHERE wc.contact_email=%s ', $value );
578 + $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $table_contacts . ' wc WHERE wc.contact_email=%s ', $value );
571 579 }
572 580
573 581 $result = null;
574 582 if ( ! empty( $sql ) ) {
575 583 if ( OBJECT === $obj ) {
576 - $result = $this->wpdb->get_row( $sql, OBJECT );
584 + $result = $this->wpdb->get_row( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
577 585 } else {
578 - $result = $this->wpdb->get_row( $sql, ARRAY_A );
586 + $result = $this->wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
579 587 }
580 588 }
581 589 return $result;
582 590 }
@@ -592,10 +600,11 @@
592 600 *
593 601 * @return bool Results.
594 602 */
595 603 public function delete_client_contact( $client_id, $contact_id ) {
596 - $current = $this->get_wp_client_contact_by( 'contact_id', $contact_id );
597 - if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' WHERE contact_client_id=%d AND contact_id = %d', $client_id, $contact_id ) ) ) {
604 + $current = $this->get_wp_client_contact_by( 'contact_id', $contact_id );
605 + $table_contacts = esc_sql( $this->table_name( 'wp_clients_contacts' ) );
606 + if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_contacts . ' WHERE contact_client_id=%d AND contact_id = %d', $client_id, $contact_id ) ) ) {
598 607 if ( ! empty( $current->contact_image ) ) {
599 608 $dirs = MainWP_System_Utility::get_mainwp_dir( 'client-images', true );
600 609 $base_dir = $dirs[0];
601 610 $old_file = $base_dir . '/' . $current->contact_image;
@@ -639,9 +648,15 @@
639 648 *
640 649 * @param string $by by.
641 650 * @param mixed $value by value.
642 651 * @param mixed $obj Format data.
643 - * @param bool $params Others params.
652 + * @param array $params Others params. Supports:
653 + * - 'with_selected_sites' (bool) Include selected sites.
654 + * - 'with_tags' (bool) Include tags info.
655 + * - 'by_tags' (array) Filter by tag IDs.
656 + * - 'offset' (int) SQL offset for pagination.
657 + * - 'limit' (int) SQL limit for pagination.
658 + * - 'count_only' (bool) Return only count instead of results.
644 659 *
645 660 * @return mixed $result results.
646 661 */
647 662 public function get_wp_client_by( $by = 'client_id', $value = null, $obj = OBJECT, $params = array() ) { // phpcs:ignore -- NOSONAR - complexex function. Current complexity is the only way to achieve desired results, pull request solutions appreciated.
@@ -648,8 +663,11 @@
648 663
649 664 $with_selected_sites = isset( $params['with_selected_sites'] ) && $params['with_selected_sites'] ? true : false;
650 665 $with_tags = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
651 666 $group_ids = isset( $params['by_tags'] ) ? $params['by_tags'] : '';
667 + $count_only = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
668 + $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : null;
669 + $limit = isset( $params['limit'] ) ? intval( $params['limit'] ) : null;
652 670
653 671 // valid group ids.
654 672 if ( is_array( $group_ids ) ) {
655 673 $group_ids = array_filter(
@@ -670,10 +688,16 @@
670 688 $where_clients = '';
671 689
672 690 $sql = '';
673 691
692 + $table_wp = esc_sql( $this->table_name( 'wp' ) );
693 + $table_wp_group = esc_sql( $this->table_name( 'wp_group' ) );
694 + $table_group = esc_sql( $this->table_name( 'group' ) );
695 + $table_clients = esc_sql( $this->table_name( 'wp_clients' ) );
696 + $table_clients_contacts = esc_sql( $this->table_name( 'wp_clients_contacts' ) );
697 +
674 698 if ( $with_selected_sites ) {
675 - $select_sites .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $this->table_name( 'wp' ) . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ';
699 + $select_sites .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $table_wp . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ';
676 700 }
677 701
678 702 if ( ! empty( $group_ids ) ) {
679 703
@@ -680,9 +704,9 @@
680 704 $join_group = '';
681 705 $where_group = '';
682 706
683 707 if ( in_array( 'nogroups', $group_ids ) ) {
684 - $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
708 + $join_group = ' LEFT JOIN ' . $table_wp_group . ' wpgroup ON wp.id = wpgroup.wpid ';
685 709 $group_ids = array_filter(
686 710 $group_ids,
687 711 function ( $e ) {
688 712 return 'nogroups' !== $e;
@@ -688,30 +712,30 @@
688 712 return 'nogroups' !== $e;
689 713 }
690 714 );
691 715 if ( ! empty( $group_ids ) ) {
692 - $groups = implode( ',', $group_ids );
716 + $groups = implode( ',', array_map( 'intval', $group_ids ) );
693 717 $where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
694 718 } else {
695 719 $where_group = ' AND wpgroup.groupid IS NULL ';
696 720 }
697 721 } else {
698 - $groups = implode( ',', $group_ids );
699 - $join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
722 + $groups = implode( ',', array_map( 'intval', $group_ids ) );
723 + $join_group = ' JOIN ' . $table_wp_group . ' wpgroup ON wp.id = wpgroup.wpid ';
700 724 $where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
701 725 }
702 726
703 - $sql_tags = ' SELECT wp.client_id FROM ' . $this->table_name( 'wp' ) . ' wp ';
727 + $sql_tags = ' SELECT wp.client_id FROM ' . $table_wp . ' wp ';
704 728 $sql_tags .= $join_group;
705 729 $sql_tags .= ' WHERE 1 ' . $where_group;
706 - $result_tags = $this->wpdb->get_results( $sql_tags );
730 + $result_tags = $this->wpdb->get_results( $sql_tags ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql_tags built with escaped table names and validated numeric IDs, safe to use directly.
707 731
708 732 if ( empty( $result_tags ) ) {
709 - return array();
733 + return $count_only ? 0 : array();
710 734 } else {
711 735 $cli_ids = array();
712 736 foreach ( $result_tags as $item ) {
713 - $cli_ids[] = $item->client_id;
737 + $cli_ids[] = (int) $item->client_id;
714 738 }
715 739 $cli_ids = array_values( array_unique( $cli_ids ) );
716 740 $where_clients = ' AND wc.client_id IN (' . implode( ',', $cli_ids ) . ') ';
717 741 }
@@ -717,50 +741,63 @@
717 741 }
718 742 }
719 743
720 744 if ( $with_tags ) {
721 - $select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
722 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
723 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
745 + $select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $table_wp . ' wp ';
746 + $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
747 + $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
724 748 $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroups ';
725 749
726 - $select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
727 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
728 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
750 + $select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $table_wp . ' wp ';
751 + $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
752 + $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
729 753 $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroupids ';
730 754 }
731 755
732 - $join_contact = ' LEFT JOIN ' . $this->table_name( 'wp_clients_contacts' ) . ' cc ON wc.primary_contact_id = cc.contact_id ';
756 + $join_contact = ' LEFT JOIN ' . $table_clients_contacts . ' cc ON wc.primary_contact_id = cc.contact_id ';
733 757
734 758 if ( 'all' === $by ) {
759 + // Handle count-only mode for efficient pagination total.
760 + if ( $count_only ) {
761 + $sql = ' SELECT COUNT(*) ';
762 + $sql .= ' FROM ' . esc_sql( $this->table_name( 'wp_clients' ) ) . ' wc ';
763 + $sql .= ' WHERE 1 ' . $where_clients;
764 + return (int) $this->wpdb->get_var( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
765 + }
766 +
735 767 $sql = ' SELECT wc.*, cc.* ' . $select_sites . $select_tags;
736 - $sql .= ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ';
768 + $sql .= ' FROM ' . esc_sql( $this->table_name( 'wp_clients' ) ) . ' wc ';
737 769 $sql .= $join_contact;
738 770 $sql .= ' WHERE 1 ' . $where_clients;
739 771 $sql .= ' ORDER BY wc.name';
772 +
773 + // Add pagination if offset and limit are provided.
774 + if ( null !== $offset && null !== $limit && $limit > 0 ) {
775 + $sql .= $this->wpdb->prepare( ' LIMIT %d, %d', $offset, $limit );
776 + }
740 777 }
741 778
742 779 if ( ! empty( $sql ) ) {
743 780 if ( OBJECT === $obj ) {
744 - $result = $this->wpdb->get_results( $sql, OBJECT );
781 + $result = $this->wpdb->get_results( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
745 782 } else {
746 - $result = $this->wpdb->get_results( $sql, ARRAY_A );
783 + $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
747 784 }
748 785 return $result;
749 786 }
750 787
751 788 if ( 'client_id' === $by && is_numeric( $value ) ) {
752 - $sql = $this->wpdb->prepare( 'SELECT wc.*, cc.* ' . $select_sites . $select_tags . ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ' . $join_contact . ' WHERE wc.client_id=%d ', $value );
789 + $sql = $this->wpdb->prepare( 'SELECT wc.*, cc.* ' . $select_sites . $select_tags . ' FROM ' . $table_clients . ' wc ' . $join_contact . ' WHERE wc.client_id=%d ', $value );
753 790 } elseif ( 'client_email' === $by && ! empty( $value ) ) {
754 - $sql = $this->wpdb->prepare( 'SELECT wc.*, cc.* ' . $select_sites . $select_tags . ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ' . $join_contact . ' WHERE wc.client_email=%s ', $value );
791 + $sql = $this->wpdb->prepare( 'SELECT wc.*, cc.* ' . $select_sites . $select_tags . ' FROM ' . $table_clients . ' wc ' . $join_contact . ' WHERE wc.client_email=%s ', $value );
755 792 }
756 793
757 794 $result = null;
758 795 if ( ! empty( $sql ) ) {
759 796 if ( OBJECT === $obj ) {
760 - $result = $this->wpdb->get_row( $sql, OBJECT );
797 + $result = $this->wpdb->get_row( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
761 798 } else {
762 - $result = $this->wpdb->get_row( $sql, ARRAY_A );
799 + $result = $this->wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
763 800 }
764 801 }
765 802 return $result;
766 803 }
@@ -778,16 +815,29 @@
778 815
779 816 $custom_field = false;
780 817 $with_tags = false;
781 818 $with_contacts = false;
819 + $count_only = false;
782 820
821 + $s = '';
822 + $exclude = array();
823 + $include = array();
824 + $limit = '';
825 +
783 826 $where = '';
784 827 $select_tags = '';
785 828
829 + $table_wp = esc_sql( $this->table_name( 'wp' ) );
830 + $table_wp_group = esc_sql( $this->table_name( 'wp_group' ) );
831 + $table_group = esc_sql( $this->table_name( 'group' ) );
832 + $table_clients = esc_sql( $this->table_name( 'wp_clients' ) );
833 +
786 834 if ( $params && is_array( $params ) ) {
787 835 $client = isset( $params['client'] ) ? sanitize_text_field( wp_unslash( $params['client'] ) ) : '';
788 836 if ( '' !== $client ) {
789 - $clients = explode( ';', $client );
837 + $clients = preg_split( '/[;,]/', $client );
838 + $clients = array_filter( array_map( 'trim', $clients ) );
839 +
790 840 $clientWhere = '';
791 841 foreach ( $clients as $clt ) {
792 842 if ( is_numeric( $clt ) ) {
793 843 $clientWhere .= intval( $clt ) . ', ';
@@ -796,32 +846,95 @@
796 846 $clientWhere = rtrim( $clientWhere, ', ' );
797 847
798 848 $where .= ' AND wc.client_id IN (' . $clientWhere . ') ';
799 849 }
800 - $custom_field = $params['custom_fields'] ? true : false;
850 + $custom_field = isset( $params['custom_fields'] ) && $params['custom_fields'] ? true : false;
801 851
802 852 $with_tags = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
803 853 $with_contacts = isset( $params['with_contacts'] ) && $params['with_contacts'] ? true : false;
854 + $count_only = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
855 +
856 + $s = isset( $params['s'] ) ? $params['s'] : '';
857 + $exclude = isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
858 + $include = isset( $params['include'] ) && ! empty( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
859 + $status = isset( $params['status'] ) && ! empty( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
860 + $page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
861 + $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
862 +
863 + if ( ! empty( $s ) ) {
864 + $s = trim( $s );
865 + // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
866 + $like = '%' . $this->wpdb->esc_like( $s ) . '%';
867 + $where .= $this->wpdb->prepare(
868 + ' AND ( wc.client_id LIKE %s OR wc.name LIKE %s OR wc.client_email LIKE %s ) ',
869 + $like,
870 + $like,
871 + $like
872 + );
873 + }
874 +
875 + if ( ! empty( $exclude ) ) {
876 + $where .= ' AND wc.client_id NOT IN (' . implode( ',', $exclude ) . ') ';
877 + }
878 +
879 + if ( ! empty( $include ) ) {
880 + $where .= ' AND wc.client_id IN (' . implode( ',', $include ) . ') ';
881 + }
882 +
883 + if ( ! empty( $status ) ) {
884 + $suspended_status = array_map(
885 + function ( $value ) {
886 + $map_status_db = array(
887 + 'active' => 0,
888 + 'suspended' => 1,
889 + 'lead' => 2,
890 + 'lost' => 3,
891 + );
892 + return isset( $map_status_db[ $value ] ) ? $map_status_db[ $value ] : '';
893 + },
894 + $status
895 + );
896 + $suspended_status = array_filter(
897 + $suspended_status,
898 + function ( $value ) {
899 + return '' !== $value;
900 + }
901 + );
902 + if ( ! empty( $suspended_status ) ) {
903 + $where .= ' AND wc.suspended IN (' . implode( ',', $suspended_status ) . ') ';
904 + }
905 + }
906 +
907 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
908 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
909 + }
804 910 }
805 911
806 912 if ( $with_tags ) {
807 - $select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
808 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
809 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
913 + $select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $table_wp . ' wp ';
914 + $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
915 + $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
810 916 $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroups ';
811 917
812 - $select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
813 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
814 - $select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
918 + $select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $table_wp . ' wp ';
919 + $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
920 + $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
815 921 $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroupids ';
816 922 }
817 923
818 - $sql = ' SELECT wc.* ';
819 - $sql .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $this->table_name( 'wp' ) . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ' . $select_tags;
820 - $sql .= ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ';
821 - $sql .= ' WHERE 1 ' . $where . ' ORDER BY wc.name';
924 + if ( $count_only ) {
925 + $sql = ' SELECT count(*) ';
926 + $sql .= ' FROM ' . $table_clients . ' wc ';
927 + $sql .= ' WHERE 1 ' . $where;
928 + return $this->wpdb->get_var( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with escaped table names, safe to use directly.
929 + } else {
930 + $sql = ' SELECT wc.* ';
931 + $sql .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $table_wp . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ' . $select_tags;
932 + $sql .= ' FROM ' . $table_clients . ' wc ';
933 + $sql .= ' WHERE 1 ' . $where . ' ORDER BY wc.name ' . $limit;
934 + }
822 935
823 - $result = $this->wpdb->get_results( $sql, ARRAY_A );
936 + $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with escaped table names, safe to use directly.
824 937
825 938 $contact_fields = array(
826 939 'contact_id',
827 940 'contact_client_id',
@@ -903,14 +1016,17 @@
903 1016 return ( is_numeric( $e ) && 0 < $e ) ? true : false;
904 1017 }
905 1018 );
906 1019
1020 + $table_wp = esc_sql( $this->table_name( 'wp' ) );
1021 +
907 1022 if ( is_array( $site_ids ) && ! empty( $site_ids ) ) {
908 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=0 WHERE client_id=%d AND id NOT IN (' . implode( ',', $site_ids ) . ')', $client_id ) );
909 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=%d WHERE id IN (' . implode( ',', $site_ids ) . ')', $client_id ) );
1023 + $safe_site_ids = implode( ',', array_map( 'intval', $site_ids ) );
1024 + $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=0 WHERE client_id=%d AND id NOT IN (' . $safe_site_ids . ')', $client_id ) );
1025 + $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=%d WHERE id IN (' . $safe_site_ids . ')', $client_id ) );
910 1026 return true;
911 1027 } else {
912 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
1028 + $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
913 1029 return true;
914 1030 }
915 1031 }
916 1032
@@ -923,16 +1039,21 @@
923 1039 * @return bool Results.
924 1040 */
925 1041 public function delete_client( $client_id ) {
926 1042
927 - $current = $this->get_wp_client_by( 'client_id', $client_id );
928 - if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients' ) . ' WHERE client_id=%d ', $client_id ) ) ) {
929 - $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
1043 + $current = $this->get_wp_client_by( 'client_id', $client_id );
1044 + $table_clients = esc_sql( $this->table_name( 'wp_clients' ) );
1045 + $table_wp = esc_sql( $this->table_name( 'wp' ) );
1046 + $table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1047 + $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1048 +
1049 + if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_clients . ' WHERE client_id=%d ', $client_id ) ) ) {
1050 + $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
930 1051 $fields = $this->get_client_fields_by( 'client_id', $client_id );
931 1052 if ( $fields ) {
932 1053 foreach ( $fields as $field ) {
933 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE field_id=%d ', $field->field_id ) );
934 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id=%d AND value_client_id=%d ', $field->field_id, $client_id ) );
1054 + $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_fields . ' WHERE field_id=%d ', $field->field_id ) );
1055 + $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_field_values . ' WHERE field_id=%d AND value_client_id=%d ', $field->field_id, $client_id ) );
935 1056 }
936 1057 }
937 1058
938 1059 if ( ! empty( $current->image ) ) {
@@ -943,8 +1064,10 @@
943 1064 }
944 1065
945 1066 $this->delete_client_contacts_by_client_id( $client_id );
946 1067
1068 + $snapshot_info = $this->get_clients_snapshot_info();
1069 +
947 1070 /**
948 1071 * Delete client
949 1072 *
950 1073 * Fires after delete a client.
@@ -951,10 +1074,12 @@
951 1074 *
952 1075 * @param object $current client deleted.
953 1076 *
954 1077 * @since 4.5.1.1
1078 + *
1079 + * @since 6.0 Added $snapshot_info parameter.
955 1080 */
956 - do_action( 'mainwp_client_deleted', $current );
1081 + do_action( 'mainwp_client_deleted', $current, $snapshot_info );
957 1082
958 1083 return true;
959 1084 }
960 1085 return false;
@@ -959,14 +1084,46 @@
959 1084 }
960 1085 return false;
961 1086 }
962 1087
1088 +
963 1089 /**
1090 + * Get clients snapshot info.
1091 + *
1092 + * @return array
1093 + */
1094 + public function get_clients_snapshot_info() {
1095 +
1096 + $total_clients = $this->get_wp_clients( array( 'count_only' => true ) );
1097 + $total_active_clients = $this->get_wp_clients(
1098 + array(
1099 + 'status' => '0',
1100 + 'count_only' => true,
1101 + )
1102 + );
1103 + $total_suspended = $this->get_wp_clients(
1104 + array(
1105 + 'status' => '1',
1106 + 'count_only' => true,
1107 + )
1108 + );
1109 + $count_sites = MainWP_DB::instance()->get_websites_count();
1110 + $avg_sites_per_client = $total_clients && $count_sites ? round( $count_sites / $total_clients, 2 ) : 0;
1111 +
1112 + return array(
1113 + 'clients_active_count' => $total_active_clients,
1114 + 'clients_inactive_count' => $total_suspended,
1115 + 'avg_sites_per_client' => $avg_sites_per_client,
1116 + );
1117 + }
1118 +
1119 +
1120 + /**
964 1121 * Method get_websites_by_client_ids.
965 1122 *
966 1123 * Get websites by client.
967 1124 *
968 - * @param int $client_ids Client ids.
1125 + * @param array $client_ids Client ids.
969 1126 * @param array $params other params.
970 1127 *
971 1128 * @return mixed $result Results.
972 1129 */
@@ -1011,9 +1168,10 @@
1011 1168 public function get_number_sites_of_client( $client_id ) {
1012 1169 if ( empty( $client_id ) ) {
1013 1170 return false;
1014 1171 }
1015 - return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT COUNT(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp WHERE wp.client_id =%d', $client_id ) );
1172 + $table_wp = esc_sql( $this->table_name( 'wp' ) );
1173 + return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT COUNT(wp.id) FROM ' . $table_wp . ' wp WHERE wp.client_id =%d', $client_id ) );
1016 1174 }
1017 1175
1018 1176 /**
1019 1177 * Method count_total_clients.
@@ -1022,9 +1180,10 @@
1022 1180 *
1023 1181 * @return int Total number.
1024 1182 */
1025 1183 public function count_total_clients() {
1026 - return $this->wpdb->get_var( 'SELECT COUNT(client_id) FROM ' . $this->table_name( 'wp_clients' ) );
1184 + $table_clients = esc_sql( $this->table_name( 'wp_clients' ) );
1185 + return $this->wpdb->get_var( 'SELECT COUNT(client_id) FROM ' . $table_clients );
1027 1186 }
1028 1187
1029 1188
1030 1189 /**
@@ -1036,9 +1195,12 @@
1036 1195 *
1037 1196 * @return mixed bool|results.
1038 1197 */
1039 1198 public function add_client_field( $field ) {
1040 - if ( ! empty( $field['field_name'] ) && isset( $field['client_id'] ) ) {
1199 + // "0" is a name the column stores, so the write is refused only for a name that is missing
1200 + // or empty, not for every name PHP reads as falsy. A name that is not a string is refused
1201 + // outright: false is set and is not '', and it would reach the column as an empty name.
1202 + if ( isset( $field['field_name'] ) && is_string( $field['field_name'] ) && '' !== $field['field_name'] && isset( $field['client_id'] ) ) {
1041 1203 if ( $this->get_client_fields_by( 'field_name', $field['field_name'], $field['client_id'] ) ) { // field name existed for the client can not create.
1042 1204 return false;
1043 1205 }
1044 1206 if ( $this->wpdb->insert( $this->table_name( 'wp_clients_fields' ), $field ) ) {
@@ -1058,9 +1220,11 @@
1058 1220 *
1059 1221 * @return mixed|false field.
1060 1222 */
1061 1223 public function update_client_field( $field_id, $field ) {
1062 - if ( $field_id && ! empty( $field['field_name'] ) ) {
1224 + // Same as the add above: a rename to "0" is a rename, not a name left out, and a name that
1225 + // is not a string would be written over the stored name as an empty one.
1226 + if ( $field_id && isset( $field['field_name'] ) && is_string( $field['field_name'] ) && '' !== $field['field_name'] ) {
1063 1227 $current = $this->get_client_fields_by( 'field_id', $field_id );
1064 1228 if ( $current && $this->wpdb->update( $this->table_name( 'wp_clients_fields' ), $field, array( 'field_id' => $field_id ) ) ) {
1065 1229 return $this->get_client_fields_by( 'field_id', $current->field_id );
1066 1230 }
@@ -1080,17 +1244,18 @@
1080 1244 * @return mixed|false field data.
1081 1245 */
1082 1246 public function update_client_field_value( $field_id, $value, $client_id ) {
1083 1247 if ( ! empty( $field_id ) ) {
1084 - $row = $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id = %d AND value_client_id=%d ', $field_id, $client_id ) );
1248 + $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1249 + $row = $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $table_field_values . ' WHERE field_id = %d AND value_client_id=%d ', $field_id, $client_id ) );
1085 1250 if ( $row ) {
1086 1251 if ( $row->field_value != $value ) { //phpcs:ignore -- to valid.
1087 - $this->wpdb->update( $this->table_name( 'wp_clients_field_values' ), array( 'field_value' => $value ), array( 'value_id' => $row->value_id ) );
1252 + $this->wpdb->update( $table_field_values, array( 'field_value' => $value ), array( 'value_id' => $row->value_id ) );
1088 1253 }
1089 1254 return true;
1090 1255 } else {
1091 1256 return $this->wpdb->insert(
1092 - $this->table_name( 'wp_clients_field_values' ),
1257 + $table_field_values,
1093 1258 array(
1094 1259 'field_id' => $field_id,
1095 1260 'field_value' => $value,
1096 1261 'value_client_id' => $client_id,
@@ -1113,17 +1278,24 @@
1113 1278 * @return mixed|null $field field data.
1114 1279 */
1115 1280 public function get_client_fields_by( $by = 'field_id', $value = null, $client_id = 0 ) {
1116 1281
1117 - if ( empty( $by ) || empty( $value ) ) {
1282 + // "0" is a name the column stores, so a falsy test here would make a field named "0" unfindable
1283 + // and let a second one be created under the same name. Only the name lookup takes that
1284 + // exception: every other column keeps refusing a falsy value, so a client_id of 0 stays "no
1285 + // client" instead of matching every general field.
1286 + $missing = 'field_name' === $by ? ( null === $value || '' === $value ) : empty( $value );
1287 +
1288 + if ( empty( $by ) || $missing ) {
1118 1289 return null;
1119 1290 }
1120 1291
1121 - $sql = '';
1292 + $sql = '';
1293 + $table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1122 1294
1123 1295 if ( 'client_id' === $by ) {
1124 - $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE `client_id`=%d ', $value );
1125 - return $this->wpdb->get_results( $sql );
1296 + $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `client_id`=%d ', $value );
1297 + return $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() with escaped table name, safe to use directly.
1126 1298 }
1127 1299
1128 1300 if ( 'field_name' === $by ) {
1129 1301 $value = str_replace( array( '[', ']' ), '', $value );
@@ -1129,17 +1301,17 @@
1129 1301 $value = str_replace( array( '[', ']' ), '', $value );
1130 1302 }
1131 1303
1132 1304 if ( 'field_id' === $by ) {
1133 - $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE `field_id`=%d', $value );
1305 + $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `field_id`=%d', $value );
1134 1306 } elseif ( 'field_name' === $by ) {
1135 - $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE `field_name` = %s AND `client_id`=%d ', $value, $client_id );
1307 + $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `field_name` = %s AND `client_id`=%d ', $value, $client_id );
1136 1308 }
1137 1309
1138 1310 $field = null;
1139 1311
1140 1312 if ( ! empty( $sql ) ) {
1141 - $field = $this->wpdb->get_row( $sql );
1313 + $field = $this->wpdb->get_row( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() with escaped table name, safe to use directly.
1142 1314 }
1143 1315
1144 1316 return $field;
1145 1317 }
@@ -1144,8 +1316,116 @@
1144 1316 return $field;
1145 1317 }
1146 1318
1147 1319 /**
1320 + * Method get_client_fields_by_params.
1321 + *
1322 + * Get client fields by params.
1323 + *
1324 + * @param array $params params.
1325 + * @param string $obj OBJECT|ARRAY_A.
1326 + *
1327 + * @return mixed|null $fields fields data.
1328 + */
1329 + public function get_client_fields_by_params( $params = array(), $obj = OBJECT ) { // phpcs:ignore -- NOSONAR - complex.
1330 +
1331 + if ( ! is_array( $params ) ) {
1332 + $params = array();
1333 + }
1334 +
1335 + // Initialize custom_where if it does not exist.
1336 + $where = '';
1337 + $limit = '';
1338 + $orderby = 'clients_fields.field_id DESC';
1339 +
1340 + // Parse exclude and include lists.
1341 + $parse_lists = static function ( $lists ) {
1342 + // Convert string to array.
1343 + if ( is_string( $lists ) ) {
1344 + $lists = array_map( 'trim', explode( ',', $lists ) );
1345 + }
1346 +
1347 + // Out default.
1348 + $out = array(
1349 + 'ids' => array(),
1350 + 'names' => array(),
1351 + );
1352 +
1353 + foreach ( (array) $lists as $v ) {
1354 + if ( '' === $v || null === $v ) {
1355 + continue;
1356 + }
1357 +
1358 + if ( is_int( $v ) || ( is_string( $v ) && ctype_digit( $v ) ) ) {
1359 + $out['ids'][] = (int) $v;
1360 + } else {
1361 + $out['names'][] = (string) $v;
1362 + }
1363 + }
1364 + return $out;
1365 + };
1366 +
1367 + // Handle exclude.
1368 + if ( isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ) {
1369 + ['ids' => $ids, 'names' => $names] = $parse_lists( $params['exclude'] );
1370 + $sub_conditions = array();
1371 + $sub_conditions[] = empty( $ids ) ? '' : $this->wpdb->prepare( 'clients_fields.field_id NOT IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', ...$ids );
1372 + $sub_conditions[] = empty( $names ) ? '' : $this->wpdb->prepare( 'clients_fields.field_name NOT IN (' . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')', ...$names );
1373 + $sub_conditions = array_filter( $sub_conditions );
1374 + $where .= empty( $sub_conditions ) ? '' : ' AND (' . implode( ' AND ', $sub_conditions ) . ')';
1375 + }
1376 +
1377 + // Handle include.
1378 + if ( isset( $params['include'] ) && ! empty( $params['include'] ) ) {
1379 + ['ids' => $ids, 'names' => $names] = $parse_lists( $params['include'] );
1380 + $sub_conditions = array();
1381 + $sub_conditions[] = empty( $ids ) ? '' : $this->wpdb->prepare( 'clients_fields.field_id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', ...$ids );
1382 + $sub_conditions[] = empty( $names ) ? '' : $this->wpdb->prepare( 'clients_fields.field_name IN (' . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')', ...$names );
1383 + $sub_conditions = array_filter( $sub_conditions );
1384 + $where .= empty( $sub_conditions ) ? '' : ' AND (' . implode( ' AND ', $sub_conditions ) . ')';
1385 + }
1386 +
1387 + // Handle search.
1388 + if ( isset( $params['search'] ) && ! empty( $params['search'] ) ) {
1389 + $search_term = $this->escape( htmlspecialchars( $params['search'] ) );
1390 + $where .= ' AND clients_fields.field_name LIKE "%' . $search_term . '%"';
1391 + }
1392 +
1393 + // Handle exact field name. The value is queried as it is stored, with no bracket stripping, so
1394 + // the column collation decides equality the same way the unique index does. An empty string
1395 + // filters on an empty name; pass null to leave the filter off.
1396 + if ( isset( $params['field_name_exact'] ) && is_string( $params['field_name_exact'] ) ) {
1397 + $where .= $this->wpdb->prepare( ' AND clients_fields.field_name = %s', $params['field_name_exact'] );
1398 + }
1399 +
1400 + // Handle client id.
1401 + if ( isset( $params['client_id'] ) && ! empty( $params['client_id'] ) ) {
1402 + $client_ids = wp_parse_id_list( $params['client_id'] );
1403 + $where .= empty( $client_ids ) ? '' : ' AND clients_fields.client_id IN (' . implode( ',', $client_ids ) . ')';
1404 + }
1405 +
1406 + // Handel page and per_page.
1407 + $page = '';
1408 + $per_page = '';
1409 + if ( isset( $params['page'] ) && ! empty( $params['page'] ) ) {
1410 + $page = (int) $params['page'];
1411 + }
1412 +
1413 + if ( isset( $params['per_page'] ) && ! empty( $params['per_page'] ) ) {
1414 + $per_page = (int) $params['per_page'];
1415 + }
1416 +
1417 + if ( ! empty( $page ) && ! empty( $per_page ) ) {
1418 + $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ', ' . $per_page;
1419 + }
1420 +
1421 + // Build SQL.
1422 + $sql = 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' clients_fields WHERE 1=1 ' . $where . ' ORDER BY ' . $orderby . $limit;
1423 +
1424 + return $this->wpdb->get_results( $sql, $obj ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with wpdb->prepare() and escaped table name, safe to use directly.
1425 + }
1426 +
1427 + /**
1148 1428 * Method get_client_fields()
1149 1429 *
1150 1430 * Get client fields.
1151 1431 *
@@ -1171,24 +1451,25 @@
1171 1451 if ( '' === $where ) {
1172 1452 return false;
1173 1453 }
1174 1454
1175 - $gene_results = array();
1455 + $gene_results = array();
1456 + $table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1457 + $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1176 1458
1177 1459 if ( $general ) {
1178 - // to fix: missing general fields for client without client fields values.
1179 - $gene_sql = 'SELECT f.* FROM ' . $this->table_name( 'wp_clients_fields' ) . ' f WHERE f.client_id = 0 ORDER BY f.field_name ';
1180 - $gene_results = $this->wpdb->get_results( $gene_sql );
1460 + $gene_sql = 'SELECT f.* FROM ' . $table_fields . ' f WHERE f.client_id = 0 ORDER BY f.field_name ';
1461 + $gene_results = $this->wpdb->get_results( $gene_sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $gene_sql built with escaped table names and hardcoded WHERE condition, safe to use directly.
1181 1462 }
1182 1463
1183 1464 if ( $client_id ) { // get fields values of client.
1184 - $sql = 'SELECT f.*, v.field_value as field_value FROM ' . $this->table_name( 'wp_clients_fields' ) . ' f LEFT JOIN ' .
1185 - $this->table_name( 'wp_clients_field_values' ) . ' v ON f.field_id = v.field_id WHERE ' . $where . ' ORDER BY f.field_name ';
1465 + $sql = 'SELECT f.*, v.field_value as field_value FROM ' . $table_fields . ' f LEFT JOIN ' .
1466 + $table_field_values . ' v ON f.field_id = v.field_id WHERE ' . $where . ' ORDER BY f.field_name ';
1186 1467 } else {
1187 - $sql = 'SELECT f.* FROM ' . $this->table_name( 'wp_clients_fields' ) . ' f WHERE ' . $where . ' ORDER BY f.field_name '; // value_client_id to compatible result.
1468 + $sql = 'SELECT f.* FROM ' . $table_fields . ' f WHERE ' . $where . ' ORDER BY f.field_name '; // value_client_id to compatible result.
1188 1469 }
1189 1470
1190 - $results = $this->wpdb->get_results( $sql );
1471 + $results = $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with escaped table names and validated numeric values in $where clause, safe to use directly.
1191 1472
1192 1473 $fields_list = array();
1193 1474 if ( $field_name_index ) {
1194 1475 if ( $results ) {
@@ -1236,9 +1517,10 @@
1236 1517 public function suspend_unsuspend_websites_by_client_id( $client_id, $suspend_val ) {
1237 1518 if ( empty( $client_id ) ) {
1238 1519 return false;
1239 1520 }
1240 - return $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET suspended=%d WHERE client_id=%d ', $suspend_val, $client_id ) );
1521 + $table_wp = esc_sql( $this->table_name( 'wp' ) );
1522 + return $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET suspended=%d WHERE client_id=%d ', $suspend_val, $client_id ) );
1241 1523 }
1242 1524
1243 1525 /**
1244 1526 * Method delete_client_field_by.
@@ -1252,13 +1534,16 @@
1252 1534 * @return bool Deleted or not.
1253 1535 */
1254 1536 public function delete_client_field_by( $by = 'field_id', $value = false, $client_id = false ) {
1255 1537
1256 - if ( 'field_id' === $by && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE field_id=%d AND client_id=%d', $value, $client_id ) ) ) { // delete individual or general client field.
1538 + $table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1539 + $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1540 +
1541 + if ( 'field_id' === $by && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_fields . ' WHERE field_id=%d AND client_id=%d', $value, $client_id ) ) ) { // delete individual or general client field.
1257 1542 if ( $client_id > 0 ) { // individual field value.
1258 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id=%d AND value_client_id=%d', $value, $client_id ) ); // delete individual tokens values, for one client.
1543 + $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_field_values . ' WHERE field_id=%d AND value_client_id=%d', $value, $client_id ) ); // delete individual tokens values, for one client.
1259 1544 } else {
1260 - $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id=%d', $value ) ); // delete general tokens values, multi clients (value_client_id).
1545 + $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_field_values . ' WHERE field_id=%d', $value ) ); // delete general tokens values, multi clients (value_client_id).
1261 1546 }
1262 1547 return true;
1263 1548 }
1264 1549
@@ -1272,9 +1557,10 @@
1272 1557 *
1273 1558 * @return mixed results.
1274 1559 */
1275 1560 public function pro_reports_get_tokens() {
1276 - return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'pro_reports_token' ) . ' WHERE 1 = 1 ORDER BY type DESC, token_name ASC' );
1561 + $table_token = esc_sql( $this->table_name( 'pro_reports_token' ) );
1562 + return $this->wpdb->get_results( 'SELECT * FROM ' . $table_token . ' WHERE 1 = 1 ORDER BY type DESC, token_name ASC' );
1277 1563 }
1278 1564
1279 1565 /**
1280 1566 * Method pro_reports_get_site_token_values()
@@ -1283,9 +1569,10 @@
1283 1569 *
1284 1570 * @return mixed results.
1285 1571 */
1286 1572 public function pro_reports_get_site_token_values() {
1287 - return $this->wpdb->get_results( ' SELECT * FROM ' . $this->table_name( 'pro_reports_site_token' ) . ' WHERE 1 ' );
1573 + $table_site_token = esc_sql( $this->table_name( 'pro_reports_site_token' ) );
1574 + return $this->wpdb->get_results( ' SELECT * FROM ' . $table_site_token . ' WHERE 1 ' );
1288 1575 }
1289 1576
1290 1577 /**
1291 1578 * Method client_reports_get_tokens()
@@ -1294,9 +1581,10 @@
1294 1581 *
1295 1582 * @return mixed results.
1296 1583 */
1297 1584 public function client_reports_get_tokens() {
1298 - return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'client_report_token' ) . ' WHERE 1 = 1 ORDER BY token_name ASC' );
1585 + $table_token = esc_sql( $this->table_name( 'client_report_token' ) );
1586 + return $this->wpdb->get_results( 'SELECT * FROM ' . $table_token . ' WHERE 1 = 1 ORDER BY token_name ASC' );
1299 1587 }
1300 1588
1301 1589 /**
1302 1590 * Method client_reports_get_site_token_values()
@@ -1305,7 +1593,8 @@
1305 1593 *
1306 1594 * @return mixed results.
1307 1595 */
1308 1596 public function client_reports_get_site_token_values() {
1309 - return $this->wpdb->get_results( ' SELECT * FROM ' . $this->table_name( 'client_report_site_token' ) . ' WHERE 1 ' );
1597 + $table_site_token = esc_sql( $this->table_name( 'client_report_site_token' ) );
1598 + return $this->wpdb->get_results( ' SELECT * FROM ' . $table_site_token . ' WHERE 1 ' );
1310 1599 }
1311 1600 }