Building secure B2B pricing grids with custom WordPress Database Class ($wpdb) endpoints and role overrides

Leveraging $wpdb for Secure B2B Pricing Grids

Building dynamic, role-specific pricing grids within WordPress for B2B clients demands a robust and secure approach. Relying solely on front-end logic or basic user meta is insufficient for sensitive data like pricing. This guide details how to create custom WordPress REST API endpoints that interact directly with the database via the global $wpdb object, implementing granular role-based access control to ensure data integrity and security.

Database Schema Design for Pricing Grids

A well-structured database is paramount. For a B2B pricing grid, we’ll consider a simplified schema. We’ll need a table to store product information, another for pricing tiers (which can be linked to user roles or custom meta), and potentially a mapping table if pricing is complex (e.g., per-customer pricing). For this example, we’ll assume a `products` table and a `pricing_tiers` table, where `pricing_tiers` can be associated with specific WordPress roles.

Let’s define the SQL for our hypothetical tables. This would typically be managed via a plugin’s activation hook.

Creating Product and Pricing Tables

-- Table for products
CREATE TABLE wp_b2b_products (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    PRIMARY KEY  (id),
    KEY sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for pricing tiers
CREATE TABLE wp_b2b_pricing_tiers (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    role VARCHAR(64) NOT NULL, -- Corresponds to WordPress user roles (e.g., 'wholesale_customer', 'reseller')
    product_id BIGINT(20) UNSIGNED NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    min_quantity INT(11) UNSIGNED DEFAULT 1,
    PRIMARY KEY  (id),
    FOREIGN KEY (product_id) REFERENCES wp_b2b_products(id) ON DELETE CASCADE,
    UNIQUE KEY role_product (role, product_id) -- Ensure only one price per role/product
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Registering Custom REST API Endpoints

We’ll use the `register_rest_route` function to expose our pricing data. This function is the cornerstone of extending the WordPress REST API. We’ll create an endpoint to fetch product pricing based on the current user’s role.

Endpoint for Fetching Pricing Data

This PHP code should be placed within your plugin’s main file or an included file. It registers a route under `/myplugin/v1/pricing/products`.

add_action( 'rest_api_init', function () {
    register_rest_route( 'myplugin/v1', '/pricing/products', array(
        'methods'  => 'GET',
        'callback' => 'myplugin_get_b2b_pricing',
        'permission_callback' => function () {
            // This is crucial for security. Only authenticated users can access.
            // Further role checks will be done in the callback.
            return is_user_logged_in();
        }
    ) );
});

function myplugin_get_b2b_pricing( WP_REST_Request $request ) {
    global $wpdb;
    $table_products = $wpdb->prefix . 'b2b_products';
    $table_pricing = $wpdb->prefix . 'b2b_pricing_tiers';

    // Get current user's roles
    $user_id = get_current_user_id();
    $user_roles = wp_get_current_user()->roles;

    // Determine the most specific role for pricing lookup.
    // This logic can be complex. For simplicity, we'll take the first role found that has pricing defined.
    // A more robust system might prioritize certain roles (e.g., 'administrator' > 'reseller' > 'wholesale_customer').
    $pricing_role = null;
    if ( ! empty( $user_roles ) ) {
        foreach ( $user_roles as $role ) {
            // Check if this role has any pricing defined in our table
            $role_exists = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table_pricing} WHERE role = %s LIMIT 1", $role ) );
            if ( $role_exists ) {
                $pricing_role = $role;
                break; // Found a role with pricing, use it.
            }
        }
    }

    // If no specific pricing role is found, maybe fall back to a default or deny access.
    // For this example, we'll return an empty array if no pricing role is identified.
    if ( ! $pricing_role ) {
        return new WP_Error( 'rest_forbidden', esc_html__( 'No applicable pricing tier found for your account.', 'myplugin' ), array( 'status' => 403 ) );
    }

    // Fetch all products and their prices for the determined role
    $sql = $wpdb->prepare(
        "SELECT
            p.id,
            p.name,
            p.sku,
            p.description,
            pt.price,
            pt.min_quantity
        FROM {$table_products} AS p
        JOIN {$table_pricing} AS pt ON p.id = pt.product_id
        WHERE pt.role = %s",
        $pricing_role
    );

    $results = $wpdb->get_results( $sql );

    if ( $wpdb->last_error ) {
        return new WP_Error( 'db_error', esc_html__( 'Database query failed.', 'myplugin' ), array( 'status' => 500 ) );
    }

    if ( empty( $results ) ) {
        return new WP_Error( 'no_data', esc_html__( 'No pricing data available for your role.', 'myplugin' ), array( 'status' => 404 ) );
    }

    // Format the results for the API response
    $formatted_results = array_map( function( $item ) {
        return array(
            'id'         => (int) $item->id,
            'name'       => $item->name,
            'sku'        => $item->sku,
            'description' => $item->description,
            'price'      => (float) $item->price,
            'min_quantity' => (int) $item->min_quantity,
        );
    }, $results );

    return rest_ensure_response( $formatted_results );
}

Implementing Role-Based Access Control (RBAC)

The security of this endpoint hinges on two key areas: the `permission_callback` and the logic within the main callback function. The `permission_callback` ensures only authenticated users can even attempt to access the endpoint. The main callback then performs the crucial role-checking.

Advanced Role Prioritization and Fallbacks

In a real-world B2B scenario, a user might have multiple roles (e.g., ‘administrator’ and ‘reseller’). The logic for determining which pricing tier to apply needs to be sophisticated. A common strategy is to define a priority order for roles. For instance, if a user is both an ‘administrator’ and a ‘reseller’, you might want to apply the ‘administrator’ pricing if it’s more favorable or strictly defined.

Here’s an enhanced version of the role determination logic:

function myplugin_get_b2b_pricing( WP_REST_Request $request ) {
    global $wpdb;
    $table_products = $wpdb->prefix . 'b2b_products';
    $table_pricing = $wpdb->prefix . 'b2b_pricing_tiers';

    $user_id = get_current_user_id();
    $user_roles = wp_get_current_user()->roles;

    // Define a priority order for roles. Higher index means higher priority.
    // Ensure these roles exist in your WordPress setup.
    $role_priority = array(
        'administrator'      => 10,
        'super_admin'        => 11, // WordPress core role
        'wholesale_customer' => 5,
        'reseller'           => 7,
        // Add other custom roles as needed
    );

    $best_match_role = null;
    $highest_priority = -1;

    if ( ! empty( $user_roles ) ) {
        foreach ( $user_roles as $role ) {
            if ( isset( $role_priority[$role] ) && $role_priority[$role] > $highest_priority ) {
                // Check if this role actually has pricing defined
                $pricing_exists = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table_pricing} WHERE role = %s LIMIT 1", $role ) );
                if ( $pricing_exists ) {
                    $highest_priority = $role_priority[$role];
                    $best_match_role = $role;
                }
            }
        }
    }

    // If no role with defined pricing is found, return an error.
    if ( ! $best_match_role ) {
        return new WP_Error( 'rest_forbidden', esc_html__( 'No applicable pricing tier found for your account.', 'myplugin' ), array( 'status' => 403 ) );
    }

    // Now use $best_match_role for the query
    $sql = $wpdb->prepare(
        "SELECT
            p.id,
            p.name,
            p.sku,
            p.description,
            pt.price,
            pt.min_quantity
        FROM {$table_products} AS p
        JOIN {$table_pricing} AS pt ON p.id = pt.product_id
        WHERE pt.role = %s",
        $best_match_role // Use the determined highest priority role
    );

    // ... rest of the function (query execution, error handling, formatting) ...
    $results = $wpdb->get_results( $sql );

    if ( $wpdb->last_error ) {
        return new WP_Error( 'db_error', esc_html__( 'Database query failed.', 'myplugin' ), array( 'status' => 500 ) );
    }

    if ( empty( $results ) ) {
        // This case should ideally not be hit if $best_match_role was found, but good for safety.
        return new WP_Error( 'no_data', esc_html__( 'No pricing data available for your role.', 'myplugin' ), array( 'status' => 404 ) );
    }

    $formatted_results = array_map( function( $item ) {
        return array(
            'id'         => (int) $item->id,
            'name'       => $item->name,
            'sku'        => $item->sku,
            'description' => $item->description,
            'price'      => (float) $item->price,
            'min_quantity' => (int) $item->min_quantity,
        );
    }, $results );

    return rest_ensure_response( $formatted_results );
}

Securing $wpdb Queries

Directly using $wpdb requires meticulous attention to security, primarily SQL injection prevention. The `prepare()` method is your best friend here. It sanitizes and escapes data before it’s inserted into SQL queries, preventing malicious input from altering query logic.

Best Practices for $wpdb Usage

• Always use $wpdb->prepare() for any dynamic data in your queries, especially user-supplied or role-based data.
• Use appropriate placeholders: %s for strings, %d for integers, %f for floats.
• Be mindful of table prefixes. Always use $wpdb->prefix to ensure your plugin works correctly across different WordPress installations.
• Sanitize any data that might be written back to the database (though this example focuses on reading). Use functions like sanitize_text_field(), sanitize_email(), etc.
• Validate input from the REST API request parameters, even if not used directly in this read-only example.

Frontend Integration and Usage

On the frontend, you can use JavaScript (e.g., with jQuery or vanilla JS) to fetch data from this endpoint. The fetch request should include appropriate authentication headers if your WordPress site uses nonces for REST API authentication (which it should for logged-in users).

Example JavaScript Fetch Request

jQuery(document).ready(function($) {
    // Ensure you have the nonce available, typically passed from PHP
    // Example: wp_localize_script( 'my-script', 'myPluginData', array( 'rest_url' => rest_url(), 'nonce' => wp_create_nonce( 'wp_rest' ) ) );
    var restUrl = myPluginData.rest_url + 'myplugin/v1/pricing/products';
    var nonce = myPluginData.nonce;

    $.ajax({
        url: restUrl,
        method: 'GET',
        beforeSend: function (xhr) {
            xhr.setRequestHeader('X-WP-Nonce', nonce);
        },
        success: function(data) {
            if (data && data.length > 0) {
                console.log('Pricing Data:', data);
                // Render the pricing grid here
                var pricingHtml = '<table><thead><tr><th>Product</th><th>SKU</th><th>Price</th><th>Min Qty</th></tr></thead><tbody>';
                data.forEach(function(item) {
                    pricingHtml += '<tr>';
                    pricingHtml += '<td>' + item.name + '</td>';
                    pricingHtml += '<td>' + item.sku + '</td>';
                    pricingHtml += '<td>' + item.price.toFixed(2) + '</td>'; // Format price
                    pricingHtml += '<td>' + item.min_quantity + '</td>';
                    pricingHtml += '</tr>';
                });
                pricingHtml += '</tbody></table>';
                $('#pricing-grid-container').html(pricingHtml);
            } else {
                $('#pricing-grid-container').html('<p>No pricing information available.</p>');
            }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            console.error('Error fetching pricing data:', textStatus, errorThrown, jqXHR.responseJSON);
            var errorMessage = 'Could not load pricing information.';
            if (jqXHR.responseJSON && jqXHR.responseJSON.message) {
                errorMessage = jqXHR.responseJSON.message;
            }
            $('#pricing-grid-container').html('<p style="color: red;">' + errorMessage + '</p>');
        }
    });
});

Conclusion

By combining the power of the WordPress REST API with direct, secure $wpdb interactions and robust role-based access control, you can build sophisticated and secure B2B pricing solutions. Always prioritize security by using $wpdb->prepare(), validating user roles, and ensuring proper authentication for your API endpoints. This approach provides a scalable and maintainable foundation for managing complex pricing structures within WordPress.