How to securely integrate GitHub API repositories endpoints into WordPress custom plugins using WordPress Database Class ($wpdb)

Securing GitHub API Access in WordPress Plugins

Integrating external APIs into WordPress, especially sensitive ones like GitHub, requires a robust approach to security and data management. This guide focuses on securely fetching repository data from the GitHub API and storing it within your WordPress installation using the built-in database class, $wpdb. We’ll cover authentication, data retrieval, and secure storage, providing practical PHP code examples suitable for custom plugin development.

Authentication Strategies for GitHub API

Directly embedding API keys or tokens in your plugin’s code is a significant security risk. For GitHub, we’ll explore two primary authentication methods: Personal Access Tokens (PATs) and OAuth. For simplicity and direct repository access within a plugin context, a PAT is often sufficient. However, it’s crucial to manage these tokens securely.

Using Personal Access Tokens (PATs)

A PAT grants your plugin specific permissions to access your GitHub account or organization. It’s recommended to create a token with the minimum required scopes (e.g., repo for private repositories, or no specific scope for public ones). Never commit your PAT directly into your version control system.

The most secure way to store a PAT within WordPress is via the WordPress options API or by using environment variables if your hosting environment supports it. For this example, we’ll use the options API, which allows you to store settings in the wp_options table. This data can be managed through a plugin settings page.

Fetching Repository Data with cURL

PHP’s cURL extension is the standard for making HTTP requests. We’ll use it to interact with the GitHub API. Ensure the cURL extension is enabled on your server.

Here’s a function to fetch data from a GitHub repository endpoint, including authentication headers:

Example: Fetching Public Repository Information

This function retrieves basic information about a public repository. For private repositories, you’ll need to include your PAT in the headers.

Function to Fetch GitHub Data

function fetch_github_repo_data( $owner, $repo, $token = null ) {
    $api_url = "https://api.github.com/repos/{$owner}/{$repo}";
    $headers = array(
        'Accept: application/vnd.github.v3+json',
        'User-Agent: WordPress-Plugin-GitHub-Integration' // Recommended by GitHub API
    );

    if ( $token ) {
        $headers[] = 'Authorization: token ' . $token;
    }

    $ch = curl_init();
    curl_setopt( $ch, CURLOPT_URL, $api_url );
    curl_setopt( $ch, CURLOPT_HTTPHEADER, $headers );
    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, true ); // Important for security
    curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 2 );   // Important for security

    $response = curl_exec( $ch );
    $http_code = curl_getinfo( $ch, CURLINFO_HTTP_CODE );
    $error = curl_error( $ch );
    curl_close( $ch );

    if ( $error ) {
        error_log( "cURL Error fetching GitHub repo: " . $error );
        return false;
    }

    if ( $http_code !== 200 ) {
        error_log( "GitHub API Error: HTTP Code {$http_code}, Response: " . $response );
        return false;
    }

    return json_decode( $response, true );
}

Storing GitHub Data Securely in WordPress

Once you have the data, you’ll want to store it within WordPress for performance and to avoid excessive API calls. The $wpdb class is your primary tool for interacting with the WordPress database. It provides a safe and standardized way to query, insert, and update data.

Database Table Structure

It’s best practice to create a custom database table for your plugin’s data rather than cluttering existing WordPress tables. This makes your plugin more self-contained and manageable. You can create this table during your plugin’s activation hook.

Plugin Activation Hook for Table Creation

function my_github_plugin_activate() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'github_repos'; // e.g., wp_github_repos

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        repo_name varchar(255) NOT NULL,
        owner_name varchar(255) NOT NULL,
        data longtext NOT NULL, -- Store JSON encoded data
        last_updated datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY repo_unique (owner_name, repo_name) -- Prevent duplicate entries
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}
register_activation_hook( __FILE__, 'my_github_plugin_activate' );

In the code above:

• $wpdb->prefix ensures your table name is prefixed correctly (e.g., wp_).
• longtext is used for the data column to accommodate potentially large JSON responses.
• A UNIQUE KEY on owner_name and repo_name prevents duplicate entries for the same repository.
• dbDelta() is a WordPress function that handles table creation and updates gracefully.

Inserting and Updating Repository Data

When you fetch new data or update existing data, you’ll use $wpdb->insert() or $wpdb->update(). These methods are crucial for preventing SQL injection vulnerabilities.

Function to Save/Update Repo Data

function save_github_repo_data( $owner, $repo, $data ) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'github_repos';

    $data_json = json_encode( $data ); // Ensure data is JSON encoded

    $existing_repo = $wpdb->get_row( $wpdb->prepare(
        "SELECT id FROM {$table_name} WHERE owner_name = %s AND repo_name = %s",
        $owner,
        $repo
    ) );

    if ( $existing_repo ) {
        // Update existing record
        $result = $wpdb->update(
            $table_name,
            array(
                'data' => $data_json,
                // 'last_updated' is handled by the DB default
            ),
            array(
                'id' => $existing_repo->id
            )
        );
    } else {
        // Insert new record
        $result = $wpdb->insert(
            $table_name,
            array(
                'owner_name' => $owner,
                'repo_name'  => $repo,
                'data'       => $data_json,
            )
        );
    }

    if ( $result === false ) {
        error_log( "Failed to save GitHub repo data for {$owner}/{$repo}: " . $wpdb->last_error );
        return false;
    }

    return true;
}

Key points:

• $wpdb->prepare() is essential for sanitizing input and preventing SQL injection when querying.
• We first check if a record exists to decide whether to insert or update.
• The data is stored as a JSON string.

Retrieving Stored Repository Data

To display the data, you’ll query your custom table. You can also implement caching logic here, for example, only fetching fresh data from GitHub if the stored data is older than a certain period.

Function to Get Stored Repo Data

function get_stored_github_repo_data( $owner, $repo ) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'github_repos';

    $repo_data = $wpdb->get_row( $wpdb->prepare(
        "SELECT data FROM {$table_name} WHERE owner_name = %s AND repo_name = %s",
        $owner,
        $repo
    ) );

    if ( $repo_data && $repo_data->data ) {
        return json_decode( $repo_data->data, true );
    }

    return null;
}

Putting It All Together: A Workflow Example

Here’s a conceptual workflow for a shortcode that displays GitHub repository stars:

Shortcode Implementation

add_shortcode( 'github_repo_stars', 'github_repo_stars_shortcode' );

function github_repo_stars_shortcode( $atts ) {
    $atts = shortcode_atts( array(
        'owner' => '',
        'repo'  => '',
    ), $atts, 'github_repo_stars' );

    if ( empty( $atts['owner'] ) || empty( $atts['repo'] ) ) {
        return '
Error: GitHub owner and repository name are required.
';
    }

    $owner = sanitize_text_field( $atts['owner'] );
    $repo  = sanitize_text_field( $atts['repo'] );

    // 1. Try to get data from our custom table
    $stored_data = get_stored_github_repo_data( $owner, $repo );

    $repo_info = false;

    // Optional: Implement a cache expiration check here
    // For simplicity, we'll just use stored data if available.
    if ( $stored_data ) {
        $repo_info = $stored_data;
    }

    // 2. If not in DB or cache expired, fetch from GitHub API
    if ( ! $repo_info ) {
        // Retrieve your PAT from WordPress options (securely!)
        $github_token = get_option( 'my_github_plugin_pat' ); // Assume this is set via settings page

        $api_data = fetch_github_repo_data( $owner, $repo, $github_token );

        if ( $api_data ) {
            // 3. Save the fetched data to our custom table
            if ( save_github_repo_data( $owner, $repo, $api_data ) ) {
                $repo_info = $api_data;
            } else {
                // Fallback to trying to get it again if save failed, though unlikely
                $repo_info = get_stored_github_repo_data( $owner, $repo );
            }
        } else {
            // API fetch failed, maybe return an error or use stale data if available
            if ( $stored_data ) {
                $repo_info = $stored_data; // Use stale data as fallback
            } else {
                return '
Error: Could not fetch repository data.
';
            }
        }
    }

    // 4. Display the data
    if ( $repo_info && isset( $repo_info['stargazers_count'] ) ) {
        return '
Star count for ' . esc_html( $owner ) . '/' . esc_html( $repo ) . ': ' . intval( $repo_info['stargazers_count'] ) . '
';
    } else {
        return '
Error: Could not retrieve star count.
';
    }
}

// Helper functions (fetch_github_repo_data, save_github_repo_data, get_stored_github_repo_data)
// and activation hook (my_github_plugin_activate) should be defined elsewhere in your plugin file.
// Ensure register_activation_hook is correctly placed to point to your main plugin file.

Security Considerations and Best Practices

• Never hardcode API tokens: Use get_option() and store tokens in the WordPress options table, ideally managed via a secure settings page.
• Sanitize all user inputs: Use functions like sanitize_text_field(), esc_html(), and intval().
• Use $wpdb->prepare(): Always for database queries involving dynamic data to prevent SQL injection.
• Validate API responses: Check HTTP status codes and the structure of the JSON response before processing.
• Rate Limiting: Be mindful of GitHub’s API rate limits. Implement caching and consider using a PAT to increase your limits.
• Error Logging: Use error_log() to log issues with API requests or database operations.
• HTTPS Everywhere: Ensure all API requests are made over HTTPS.
• Token Scopes: Grant your PAT only the minimum necessary permissions.

Conclusion

By leveraging WordPress’s built-in $wpdb class for secure data storage and cURL for API interactions, you can effectively integrate GitHub repository data into your custom plugins. Prioritizing security through proper authentication, input sanitization, and prepared statements is paramount for any production-ready WordPress plugin.