Querying GitHub Projects V2 with GraphQL in Laravel

GitHub and GraphQL logos

Note: I previously wrote about using plain PHP to query GitHub Projects V2. In this post I offer some tips for querying using Laravel.

GitHub’s new Projects are not accessible via the older REST API. Working with them programmatically involves learning some GraphQL, which can be a headache, the first time you encounter it. Here’s my approach, using Laravel.

Authentication

Get a GitHub personal access token, limiting its permissions as appropriate to your app. Then add to your .env:

GH_TOKEN=[your token]

HTTP requests

We can standardise GitHub GraphQL queries by using an HTTP facade macro. In app/Providers/AppServiceProvider.php, add to the boot() method:

    /**
     * Make request to GitHub using the GraphQL API.
     *
     * $query - a GraphQL query string
     * $variables - an array of GraphQL variables
     *
     * Call like: $data = Http::githubGraphQL($query, $variables)->throw()->json()['data'];
     */
    Http::macro('githubGraphQL', function (string $query, array $variables) {
        return Http::withHeaders([
            'Accept' => 'application/vnd.github+json',
            'Authorization' => 'Bearer ' . env('GH_TOKEN')
        ])->post('https://api.github.com/graphql', [
            'query' => $query,
            'variables' => $variables,
        ]);
    });

GitHubProjects model

This sample app/Models/GitHubProjects.php shows you the sort of query you can run:

    <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Http;

class GitHubProjects extends Model
{
    use HasFactory;

    /**
     * getFirstN - get first N projects for the GitHub organisation, ordered by
     * title
     *
     * @param integer $count - number of projects to return
     * @return array
     */
    public static function getFirstN(int $count = 20): array
    {
        $projects = Http::githubGraphQL(
            <<<EOD
            query getFirstN(\$count: Int) {
              organization(login: "MyOrg") {
                projectsV2(first: \$count, orderBy: {field: TITLE, direction: ASC}) {
                  nodes {
                    number
                    id
                    title
                  }
                }
              }
            }
EOD,
            [ "count" => $count ]
        )->throw()->json()['data'];
        return $projects['organization']['projectsV2']['nodes'];
    }


    /**
     * getFirstNIssues
     *
     * @param integer $projectNumber
     * @param integer $count
     * @return array
     */
    public static function getFirstNIssues(int $projectNumber = 1, int $count = 20): array
    {
        $projectId = GitHubProjects::getIdByNumber($projectNumber);
        $issues = Http::githubGraphQL(
            <<<EOD
            # Exclamation mark since projectId is required
            query getFirstNIssues(\$projectId: ID!, \$count: Int) {
              node(id: \$projectId) {
                ... on ProjectV2 {
                  items(first: \$count) {
                    nodes {
                      id
                      fieldValues(first: 8) {
                        nodes {
                          ... on ProjectV2ItemFieldTextValue {
                            text
                            field {
                              ... on ProjectV2FieldCommon {
                                name
                              }
                            }
                          }
                          ... on ProjectV2ItemFieldDateValue {
                            date
                            field {
                              ... on ProjectV2FieldCommon {
                                name
                              }
                            }
                          }
                          ... on ProjectV2ItemFieldSingleSelectValue {
                            name
                            field {
                              ... on ProjectV2FieldCommon {
                                name
                              }
                            }
                          }
                        }
                      }
                      content {
                        ... on DraftIssue {
                          title
                          body
                        }
                        ... on Issue {
                          title
                          assignees(first: 10) {
                            nodes {
                              login
                            }
                          }
                        }
                        ... on PullRequest {
                          title
                          assignees(first: 10) {
                            nodes {
                              login
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
EOD,
            [
              "projectId" => $projectId,
              "count" => $count
            ]
        )->throw()->json()['data'];
        return $issues['node']['items']['nodes'];
    }


    /**
     * getByTitle - search for projects by title
     *
     * @param string $title - search for
     * @return array
     */
    public static function getByTitle(string $title): array
    {
        $projects = Http::githubGraphQL(
            <<<EOD
            query getByName(\$title: String!) {
                organization(login: "MyOrg") {
                    projectsV2(
                        first: 20
                        orderBy: { field: TITLE, direction: ASC }
                        query: \$title
                    ) {
                        nodes {
                            number
                            id
                            title
                        }
                    }
                }
            }
              
EOD,
            [ "title" => $title ]
        )->throw()->json()['data'];
        return $projects['organization']['projectsV2']['nodes'];
    }


    /**
     * getIdByNumber
     *
     * @param integer $number - the integer identifier of the project
     * @return string - the internal GitHub project ID (e.g. PVT_kwDOBWQiz84AH53W)
     */
    private static function getIdByNumber(int $number = 1)
    {
        $project = Http::githubGraphQL(
            <<<EOD
            # Exclamation mark since number is required
            query getIdByNumber(\$number: Int!) {
              organization(login: "MyOrg") {
                projectV2(number: \$number) {
                  id
                }
              }
            }
EOD,
            ['number' => $number]
        )->throw()->json()['data'];
        return $project['organization']['projectV2']['id'];
    }


    /**
     * getProjectFields - get all the (custom) fields associated to a project
     *
     * @param string $projectID - GitHub's reference for the project
     * @return array
     */
    public static function getProjectFields(string $projectID): array
    {
          $fields = Http::githubGraphQL(
            <<<EOD
            query getProjectFields(\$node: ID!) {
              node(id: \$node) {
                ... on ProjectV2 {
                  fields(first: 20) {
                    nodes {
                      ... on ProjectV2Field {
                        id
                        name
                      }
                      ... on ProjectV2IterationField {
                        id
                        name
                        configuration {
                          iterations {
                            startDate
                            id
                          }
                        }
                      }
                      ... on ProjectV2SingleSelectField {
                        id
                        name
                        options {
                          id
                          name
                        }
                      }
                    }
                  }
                }
              }
            }
              
EOD,
            [ "node" => $projectID ]
        )->throw()->json()['data'];
        return $fields['node']['fields']['nodes'];
    }
}

Controller

This is a very basic sample controller method to get you started. In practice you’ll use views:

public function index()
{
    /*
        Search for "MyProject" in projects
        Project number is $projects[0]['number'];
        Project name is $projects[0]['title'];
        Project ID is $projects[0]['id'];
    */
    define('BR', "<br />\n");
    $projects = GitHubProjects::getByTitle('MyProject');

    if (isset($projects[0])) {
        $projectNumber = $projects[0]['number'];
        $issues = GitHubProjects::getFirstNIssues($projectNumber, 50);
        echo "<h1>First 50 issues in MyProject project</h1>\n";
        foreach ($issues as $issue) {
            echo '<b>ID:</b> ' . $issue['id'] . BR;
            echo '<b>Title:</b> ' . $issue['content']['title'] . BR;
            if (isset($issue['content']['assignees']['nodes'][0])) {
                echo '<b>Assignee:</b> ' . $issue['content']['assignees']['nodes'][0]['login'] . BR;
            }
            // Field types
            foreach ($issue['fieldValues']['nodes'] as $field) {
                if (isset($field['text']) && $field['field']['name'] != "Title") {
                    echo '<b>' . $field['field']['name'] . ':</b> ' . $field['text'] . BR;
                }
                if (isset($field['date'])) {
                    echo '<b>' . $field['field']['name'] . ':</b> ' . $field['date'] . BR;
                }
                if (isset($field['name'])) {
                    echo '<b>' . $field['field']['name'] . ':</b> ' . $field['name'] . BR;
                }
            }
            echo BR;
        }
    } else {
        echo "No projects found";
    }
}

Useful resources

The following are invaluable for working with GitHub’s GraphQL API:

Querying GitHub Projects v2 with GraphQL in PHP

Update: you may prefer my post about doing this in Laravel.

GitHub’s new Projects are not accessible via the older REST API. Working with them programmatically involves learning some GraphQL, which can be a headache, the first time you encounter it. Here’s my approach, using PHP.

Set up cURL

You can certainly use an HTTP request library, but sometimes it’s easiest to get your hands dirty with cURL. Here’s the setup:

$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
    'Accept: application/vnd.github+json',
    'Authorization: Bearer '.$_ENV['GH_TOKEN']
));

// Fake user agent, to keep GitHub happy
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows NT 5.1; rv:31.0) Gecko/20100101 Firefox/31.0');

// Dev settings (PHP built-in server can't validate)
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

The “dev settings” are there since I’ve been using PHP’s built-in webserver during development. Don’t take those into production!

Generic API class

This class provides a simple wrapper around REST API and GraphQL calls. I’m passing in the cURL handler and calling most methods statically, since I’m not yet modelling the underlying objects.

class GitHub
{
    /**
     * API
     * Make request to GitHub using the standard REST API.
     * See: https://docs.github.com/en/rest
     *
     * @param [CurlHandle Object] $ch - curl handle
     * @param [string] $endpoint - the REST endpoint
     * @return array|stdClass
     */
    public static function api(\CurlHandle $ch, string $endpoint)
    {
        curl_setopt($ch, CURLOPT_URL, 'https://api.github.com/'.$endpoint);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'GET');

        $content = curl_exec($ch);
        if (curl_errno($ch)) {
            die("Error:".curl_error($ch));
        }
        return json_decode($content);
    }

    /**
     * GraphQL
     * Make request to GitHub using the newer GraphQL API.
     * See: https://docs.github.com/en/graphql/reference
     * GraphQL Explorer: https://docs.github.com/en/graphql/overview/explorer
     * GraphQL Formatter: https://jsonformatter.org/graphql-formatter
     *
     * @param CurlHandle $ch
     * @param string $query - a GraphQL query string
     * @param array $variables - an array of GraphQL variables
     * @return StdClass
     */
    public static function graphQL(\CurlHandle $ch, string $query, array $variables)
    {
        curl_setopt($ch, CURLOPT_URL, 'https://api.github.com/graphql');
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
        curl_setopt(
            $ch,
            CURLOPT_POSTFIELDS,
            json_encode(['query' => $query, 'variables' => $variables])
        );

        $content = curl_exec($ch);
        if (curl_errno($ch)) {
            die("Error:".curl_error($ch));
        }
        return json_decode($content);
    }
}

Projects class

Various simple methods for obtaining information about projects. I say “simple” but the GraphQL queries take some mastering.

class GitHubProjects
{
    /**
     * getFirstN - get first N projects for the GitHub organisation, ordered by
     * title
     *
     * @param \CurlHandle $ch
     * @param integer $count - number of projects to return
     * @return array
     */
    public static function getFirstN(\CurlHandle $ch, int $count = 20): array
    {
        $projects = GitHub::graphQL(
            $ch,
            <<<EOD
            query getFirstN(\$count: Int) {
              organization(login: "MyOrg") {
                projectsV2(first: \$count, orderBy: {field: TITLE, direction: ASC}) {
                  nodes {
                    number
                    id
                    title
                  }
                }
              }
            }
EOD,
            [ "count" => $count ]
        );
        return $projects->data->organization->projectsV2->nodes;
    }


    /**
     * getFirstNIssues
     *
     * @param \CurlHandle $ch
     * @param integer $projectNumber
     * @param integer $count
     * @return array
     */
    public static function getFirstNIssues(\CurlHandle $ch, int $projectNumber = 1, int $count = 20): array
    {
        $projectId = GitHubProjects::getIdByNumber($ch, $projectNumber);
        $issues = GitHub::graphQL(
            $ch,
            <<<EOD
            # Exclamation mark since projectId is required
            query getFirstNIssues(\$projectId: ID!, \$count: Int) {
              node(id: \$projectId) {
                ... on ProjectV2 {
                  items(first: \$count) {
                    nodes {
                      id
                      fieldValues(first: 8) {
                        nodes {
                          ... on ProjectV2ItemFieldTextValue {
                            text
                            field {
                              ... on ProjectV2FieldCommon {
                                name
                              }
                            }
                          }
                          ... on ProjectV2ItemFieldDateValue {
                            date
                            field {
                              ... on ProjectV2FieldCommon {
                                name
                              }
                            }
                          }
                          ... on ProjectV2ItemFieldSingleSelectValue {
                            name
                            field {
                              ... on ProjectV2FieldCommon {
                                name
                              }
                            }
                          }
                        }
                      }
                      content {
                        ... on DraftIssue {
                          title
                          body
                        }
                        ... on Issue {
                          title
                          assignees(first: 10) {
                            nodes {
                              login
                            }
                          }
                        }
                        ... on PullRequest {
                          title
                          assignees(first: 10) {
                            nodes {
                              login
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
EOD,
            [
              "projectId" => $projectId,
              "count" => $count
            ]
        );
        return $issues->data->node->items->nodes;
    }

    
    /**
     * getByTitle - search for projects by title
     *
     * @param \CurlHandle $ch
     * @param string $title - search for
     * @return array
     */
    public static function getByTitle(\CurlHandle $ch, string $title): array
    {
        $projects = GitHub::graphQL(
            $ch,
            <<<EOD
            query getByName(\$title: String!) {
                organization(login: "MyOrg") {
                    projectsV2(
                        first: 20
                        orderBy: { field: TITLE, direction: ASC }
                        query: \$title
                    ) {
                        nodes {
                            number
                            id
                            title
                        }
                    }
                }
            }
              
EOD,
            [ "title" => $title ]
        );
        return $projects->data->organization->projectsV2->nodes;
    }


    /**
     * getIdByNumber
     *
     * @param \CurlHandle $ch
     * @param integer $number - the integer identifier of the project
     * @return string - the internal GitHub project ID (e.g. PVT_kwDOBWQiz84AH53W)
     */
    private static function getIdByNumber(\CurlHandle $ch, int $number = 1)
    {
        $project = GitHub::graphQL(
            $ch,
            <<<EOD
            # Exclamation mark since number is required
            query getIdByNumber(\$number: Int!) {
              organization(login: "MyOrg") {
                projectV2(number: \$number) {
                  id
                }
              }
            }
EOD,
            ['number' => $number]
        );
        return $project->data->organization->projectV2->id;
    }
}

Using it

I have not included namespacing or autoloading here. In broad terms, it’s simple to use the Projects class:

$projects = Model\GitHubProjects::getByTitle($ch, 'MyProject');
if (is_array($projects) && !empty($projects)) {
    $project = $projects[0];
    var_dump($project);
}

Stop committing credentials to Github!

I mean it. STOP.

Quick orientation
In case you don’t know, Github is an online and largely open repository of code. Users can store code here for their open source projects, and track all changes to the code over time. Github code repositories usually offer public read access.

I know this is not new news. There have been multiple publicised incidents of Amazon AWS API keys being discovered in Github repositories. So we should have learnt our lesson. And yet, a security researcher told me this week that thousands of live credentials can be found within public Github repositories, if you know where to look. Using basic command line tools, commonly available on Linux/OpenBSD/Unix/MacOS, it is possible to discover live credentials for services like:

  • SMS messaging – with live credit cards attached
  • Password-based VNC connections to Internet-accessible computers. (I kid you not.)
  • AWS API keys. A favourite misuse of such keys is to set up bitcoin mining operations at someone else’s expense. My sources tells me of a hefty bill racked up at a UK business, after an employee accidentally uploaded their API keys to Github. That’s a bad day at the office.
  • KeePass files together with the master password. (You’re having a laugh.)
  • Full Netflix account details. (What the…?!)
  • Database connection credentials. That’s a huge problem if the database is so badly configured it’s public-facing.

Mitigation

  • Github has written a very useful article on removing sensitive information from repositories. Read it. (Or, you know, don’t put the sensitive information there in the first place.)
  • Have a look at this git pre-commit hook, which should help protect against this kind of mistake. (Note: read the comments on that gist.)
  • Run the script below against your own repositories (whether on Github or elsewhere) to find passwords in your code. Tweak it to search for other types of sensitive information.
  • If you find any passwords, keys or other credentials in your code, they may already be out in the wild. The safest thing to do is to change them.

Finding passwords in repositories

This shell script, kindly provided by my source, is one way to discover passwords hidden in your git commit history:


git log -p -G'password.*{5,}' | awk 'match($0,
/password.*['\''"\[?&](.*?)['\''"\]=&]/, arr) { print arr[1]}' | grep -v '^\s*$'| sort | uniq -c | sort -n

Step by step explanation:

git log: view the history of code committed
-p: show the differences only (in each new commit)
-G'password.*{5,}': match lines that contain the word “password” with at least 5 characters afterwards
awk: process through awk
'match($0,: look for matches in the entire line
/password.*: This is the start of the regular expression; match “password”, with any number of characters afterwards
['\''"\[?&]: the extra apostrophes here are because we’re already using apostrophes in the shell command line; we’re matching against any single quote ('), double quote ("), open square bracket ([), question mark (?) or ampersand (&)
(.*?): a lazy match of any character; this will generally return the passwords we’re looking for
['\''"\]=&]/,: again, the extra apostrophes here are for shell purposes; we’re matching against any single quote ('), double quote ("), closing square bracket (]), equals sign (=) or ampersand (&)
arr): put the matches into “arr”
{ print arr[1]}': give us the first element from the matched line (anything matching afterwards is likely to be spurious)
| grep: pass the result through grep
-v '^\s*$': remove any lines that consist solely of white space
| sort: sort the results (need to do this so that uniq can strip out duplicate matches)
| uniq -c: remove duplicate results and prefix the output with the number of times the result occurred
| sort -n: sort numerically (i.e. according to frequency of occurrence)

Example output follows; you’ll note that not everything returned is a password, but some definitely are:


1 ******************************
1 InsertPasswordHere
1 9^9NhV6JuVGy&VN
1 testpassword
1 text=
2 check_password
2 personspassword
3 goforit
3

4 passw0rd
4 #password-authenticate
6 n.camelCase(b)])):f
6 person_password
8 nB#KR08p

Run this script over your own repositories; you may be surprised at the results. (You can substitute “key”, “secret” or “password” for password, for more hits.) If you find anything, and your repository is hosted openly on Github, you’ll need to take action promptly. Because anyone, repeat anyone can run this script against any open Github repo. If you’re not sure what to do, take another look at Github’s helpful article.