# GitHub Advanced Security : CodeScanning using CodeQL
---
## Important links:
- [GitHub Code Security: Documentation](https://docs.github.com/en/code-security)
- [GitHub CodeScanning: Documentation](https://docs.github.com/en/code-security/code-scanning/automatically-scanning-your-code-for-vulnerabilities-and-errors/about-code-scanning)
- [Website for webhooks](https://webhook.site)
- [API to create webhook](https://docs.github.com/en/rest/webhooks/repos?apiVersion=2022-11-28#create-a-repository-webhook)
- [Base64 Converter](https://www.base64encode.org/)
- [API to create file in repository](https://docs.github.com/en/rest/repos/contents?apiVersion=2022-11-28#create-or-update-file-contents)
- [API to list out vulnerability alerts](https://docs.github.com/en/rest/code-scanning?apiVersion=2022-11-28#list-code-scanning-alerts-for-a-repository)
- [API to list out a single alert](https://docs.github.com/en/rest/code-scanning?apiVersion=2022-11-28#get-a-code-scanning-alert)
- [API for updating alerts](https://docs.github.com/en/rest/code-scanning?apiVersion=2022-11-28#update-a-code-scanning-alert)
- [Guide to trigger workflow using API](https://dev.to/rikurouvila/how-to-trigger-a-github-action-with-an-htt-request-545)
---

## Steps to be followed:
1. Generate a Fine Grained Personal Access Token.
2. Create a repository webhook.
3. Do appropriate changes to the template workflow yaml file.
4. Convert the file to base64.
5. Call an API to create YAML file in the .github/workflow folder and trigger the scan.
6. Call an API to list all/single vulnerability alert.
7. Call an API to update status of a vulnerability alert.

---

### Step 1: Generate a Fine Grained Personal Access Token.
There are two ways to do this, the first one being:
- Go to settings: 
- Under settings, go to personal access token and select fine grained tokens and give an appropriate name: 
- Select the following repository permissions: 
- Generate the token: 
- 

The second way of doing this task:
- OAuth Apps's can also help you to generate token from API. By creating an OAuth app and obtaining client credentials like client_secret and client_id (which we can also get from the secret manager), you can authenticate and authorize your app to access specific resources on the platform.
- With the obtained access token, you can make API requests to access further resources.
- 

---

### Step 2: Create a repository webhook
- Go to webhook.site to get a payload url and copy it.
- Now call an API with the following data:

  POST Request
  Headers "Accept: application/vnd.github+json", "Authorization: Bearer <YOUR-TOKEN>", "X-GitHub-Api-Version: 2022-11-28"
  URL: https://api.github.com/repos/OWNER/REPO/hooks
  Body: {"name":"web","active":true,"events":["code_scanning_alert"],"config":{"url":"Payload_URL","content_type":"json","insecure_ssl":"0"}}

- This creates a repository webhook activated by any changes to the code scanning alerts: 

---

### Step 3: Do appropriate changes to the template workflow yaml file.
- Here is the template file (Also provided with the json files):

# For most projects, this workflow file will not need changing; you simply need
# to commit it to your repository.
#
# You may wish to alter this file to override the set of languages analyzed,
# or to provide custom queries or build logic.
#
# ******** NOTE ********
# We have attempted to detect the languages in your repository. Please check
# the `language` matrix defined below to confirm you have the correct set of
# supported CodeQL languages.
#
name: "CodeQL"

on:
  push:
    branches: [ "main" ]
  pull_request:
    # The branches below must be a subset of the branches above
    branches: [ "main" ]
  schedule:
    - cron: "0 */12 * * *"
  repository_dispatch:
  
jobs:
  analyze:
    name: Analyze
    runs-on: ${{ (matrix.language == 'swift' && 'macos-latest') || 'ubuntu-latest' }}
    timeout-minutes: ${{ (matrix.language == 'swift' && 120) || 360 }}
    permissions:
      actions: read
      contents: read
      security-events: write

    strategy:
      fail-fast: false
      matrix:
        language: [ 'python' ]
        # CodeQL supports [ 'cpp', 'csharp', 'go', 'java', 'javascript', 'python', 'ruby', 'swift' ]
        # Use only 'java' to analyze code written in Java, Kotlin or both
        # Use only 'javascript' to analyze code written in JavaScript, TypeScript or both
        # Learn more about CodeQL language support at https://aka.ms/codeql-docs/language-support

    steps:
    - name: Checkout repository
      uses: actions/checkout@v3

    # Initializes the CodeQL tools for scanning.
    - name: Initialize CodeQL
      uses: github/codeql-action/init@v2
      with:
        languages: ${{ matrix.language }}
        # If you wish to specify custom queries, you can do so here or in a config file.
        # By default, queries listed here will override any specified in a config file.
        # Prefix the list here with "+" to use these queries and those in the config file.

        # For more details on CodeQL's query packs, refer to: https://docs.github.com/en/code-security/code-scanning/automatically-scanning-your-code-for-vulnerabilities-and-errors/configuring-code-scanning#using-queries-in-ql-packs
        # queries: security-extended,security-and-quality


    # Autobuild attempts to build any compiled languages (C/C++, C#, Go, Java, or Swift).
    # If this step fails, then you should remove it and run the build manually (see below)
    - name: Autobuild
      uses: github/codeql-action/autobuild@v2

    # ℹ️ Command-line programs to run using the OS shell.
    # 📚 See https://docs.github.com/en/actions/using-workflows/workflow-syntax-for-github-actions#jobsjob_idstepsrun

    #   If the Autobuild fails above, remove it and uncomment the following three lines.
    #   modify them (or add more) to build your code if your project, please refer to the EXAMPLE below for guidance.

    # - run: |
    #     echo "Run, Build Application using script"
    #     ./location_of_script_within_repo/buildscript.sh

    - name: Perform CodeQL Analysis
      uses: github/codeql-action/analyze@v2
      with:
        category: "/language:${{matrix.language}}"

- To run the scan automatcally at certain intervals we can set up the **cron job on line 21** of the yaml file.
- To avoid a code scan being triggered on specific pull requests targeted against the default branch, irrespective of which files have been changed. We can configure this by specifying `on:pull_request:paths-ignore` or `on:pull_request:paths` in the code scanning workflow. Eg:

    on:
      push:
        branches: [main, protected]
      pull_request:
        branches: [main]
        paths-ignore:
          - '**/*.md'
          - '**/*.txt'

- To change the language being analyzed, we can set up **languages on line 37** of the yaml file.
- Refer to the following documentation to add/remove check queries, dependencies, etc. Documentation: [Code Scan customizations](https://docs.github.com/en/code-security/code-scanning/automatically-scanning-your-code-for-vulnerabilities-and-errors/customizing-code-scanning)
---
### Step 4: Convert the file to base64
- Use the following website for the task: 
- Or use the following postman function for the same: 

---

### Step 5: Call an API to create YAML file in the .github/workflow folder and trigger the scan
- Call an API with the following data:

PUT Request
Headers: "Accept: application/vnd.github+json", "Authorization: Bearer <YOUR-TOKEN>", "X-GitHub-Api-Version: 2022-11-28".
URL: https://api.github.com/repos/OWNER/REPO/contents/.github/workflows/FileName.yaml
Body: {"message":"my commit message","committer":{"name":"NAME","email":"EMAIL"},"content":"YAML template converted to base64"}

- This adds the codescanning workflow to our repository: 
- The initial run of the workflow is also triggered: 
- On doing the above, we will automatically trigger the initial scan on the repository.
- Now, if we want to trigger the CodeQL (Code Scanning) Workflow again at any point of time, we can call an API for the same:
- Screenshots showing the API Call and response:  
- Here we can see that we triggered the workflow run again through our api call: 
- Here is the API to trigger the CodeQL (Code Scanning) Workflow:

POST Request
URL: 'https://api.github.com/repos/<USERNAME>/<REPO>/dispatches'
Header: 'authorization: Bearer <TOKEN>'
Data: {"event_type": "hello"}

- **Note:** Ascertain that the YAML workflow file contains `on:repository_dispatch:` otherwise this step will not work.

---

### Step 6: Call an API to list all/single vulnerability alert.
- We can see the vulnerability alerts in GitHub using UI: 
- We can also see the vulnerability alerts on the webhook: 
- Or we can call an API to list down all the alerts:  
- Here is the API to list out all vulnerability alerts:

GET request
Headers: "Accept: application/vnd.github+json", "Authorization: Bearer <YOUR-TOKEN>", "X-GitHub-Api-Version: 2022-11-28".
URL: https://api.github.com/repos/OWNER/REPO/code-scanning/alerts

- Here is the JSON response body (Also provided in the json files):

[
    {
        "number": 2,
        "created_at": "2023-06-20T09:53:25Z",
        "updated_at": "2023-06-20T09:53:25Z",
        "url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/2",
        "html_url": "https://github.com/Arnav-Barman/GHAS-CodeScanning-POC/security/code-scanning/2",
        "state": "open",
        "fixed_at": null,
        "dismissed_by": null,
        "dismissed_at": null,
        "dismissed_reason": null,
        "dismissed_comment": null,
        "rule": {
            "id": "py/sql-injection",
            "severity": "error",
            "description": "SQL query built from user-controlled sources",
            "name": "py/sql-injection",
            "tags": [
                "external/cwe/cwe-089",
                "security"
            ],
            "security_severity_level": "high"
        },
        "tool": {
            "name": "CodeQL",
            "guid": null,
            "version": "2.13.3"
        },
        "most_recent_instance": {
            "ref": "refs/heads/main",
            "analysis_key": ".github/workflows/codeql_workflow.yml:analyze",
            "environment": "{\"language\":\"python\"}",
            "category": "/language:python",
            "state": "open",
            "commit_sha": "7aefeb59e0175a9657565316fbe188b7b285b94d",
            "message": {
                "text": "This SQL query depends on a user-provided value."
            },
            "location": {
                "path": "server/routes.py",
                "start_line": 22,
                "end_line": 22,
                "start_column": 13,
                "end_column": 71
            },
            "classifications": []
        },
        "instances_url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/2/instances"
    },
    {
        "number": 1,
        "created_at": "2023-06-20T09:53:25Z",
        "updated_at": "2023-06-20T09:53:25Z",
        "url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/1",
        "html_url": "https://github.com/Arnav-Barman/GHAS-CodeScanning-POC/security/code-scanning/1",
        "state": "open",
        "fixed_at": null,
        "dismissed_by": null,
        "dismissed_at": null,
        "dismissed_reason": null,
        "dismissed_comment": null,
        "rule": {
            "id": "py/sql-injection",
            "severity": "error",
            "description": "SQL query built from user-controlled sources",
            "name": "py/sql-injection",
            "tags": [
                "external/cwe/cwe-089",
                "security"
            ],
            "security_severity_level": "high"
        },
        "tool": {
            "name": "CodeQL",
            "guid": null,
            "version": "2.13.3"
        },
        "most_recent_instance": {
            "ref": "refs/heads/main",
            "analysis_key": ".github/workflows/codeql_workflow.yml:analyze",
            "environment": "{\"language\":\"python\"}",
            "category": "/language:python",
            "state": "open",
            "commit_sha": "7aefeb59e0175a9657565316fbe188b7b285b94d",
            "message": {
                "text": "This SQL query depends on a user-provided value."
            },
            "location": {
                "path": "server/routes.py",
                "start_line": 16,
                "end_line": 16,
                "start_column": 13,
                "end_column": 67
            },
            "classifications": []
        },
        "instances_url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/1/instances"
    }
]

- We can also call an API to list out single results at a time:  
- Here is the API to list out a single vulnerability alerts:

GET request
Headers: "Accept: application/vnd.github+json", "Authorization: Bearer <YOUR-TOKEN>", "X-GitHub-Api-Version: 2022-11-28".
URL: https://api.github.com/repos/OWNER/REPO/code-scanning/alerts/ALERT_NUMBER

- Here is the JSON response body (Also provided in the json files):

{
    "number": 1,
    "created_at": "2023-06-20T09:53:25Z",
    "updated_at": "2023-06-20T09:53:25Z",
    "url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/1",
    "html_url": "https://github.com/Arnav-Barman/GHAS-CodeScanning-POC/security/code-scanning/1",
    "state": "open",
    "fixed_at": null,
    "dismissed_by": null,
    "dismissed_at": null,
    "dismissed_reason": null,
    "dismissed_comment": null,
    "rule": {
        "id": "py/sql-injection",
        "severity": "error",
        "description": "SQL query built from user-controlled sources",
        "name": "py/sql-injection",
        "tags": [
            "external/cwe/cwe-089",
            "security"
        ],
        "full_description": "Building a SQL query from user-controlled sources is vulnerable to insertion of malicious SQL code by the user.",
        "help": "# SQL query built from user-controlled sources\nIf a database query (such as a SQL or NoSQL query) is built from user-provided data without sufficient sanitization, a user may be able to run malicious database queries.\n\nThis also includes using the `TextClause` class in the `[SQLAlchemy](https://pypi.org/project/SQLAlchemy/)` PyPI package, which is used to represent a literal SQL fragment and is inserted directly into the final SQL when used in a query built using the ORM.\n\n\n## Recommendation\nMost database connector libraries offer a way of safely embedding untrusted data into a query by means of query parameters or prepared statements.\n\n\n## Example\nIn the following snippet, a user is fetched from the database using three different queries.\n\nIn the first case, the query string is built by directly using string formatting from a user-supplied request parameter. The parameter may include quote characters, so this code is vulnerable to a SQL injection attack.\n\nIn the second case, the user-supplied request attribute is passed to the database using query parameters. The database connector library will take care of escaping and inserting quotes as needed.\n\nIn the third case, the placeholder in the SQL string has been manually quoted. Since most databaseconnector libraries will insert their own quotes, doing so yourself will make the code vulnerable to SQL injection attacks. In this example, if `username` was `; DROP ALL TABLES -- `, the final SQL query would be `SELECT * FROM users WHERE username = ''; DROP ALL TABLES -- ''`\n\n\n```python\nfrom django.conf.urls import url\nfrom django.db import connection\n\n\ndef show_user(request, username):\n    with connection.cursor() as cursor:\n        # BAD -- Using string formatting\n        cursor.execute(\"SELECT * FROM users WHERE username = '%s'\" % username)\n        user = cursor.fetchone()\n\n        # GOOD -- Using parameters\n        cursor.execute(\"SELECT * FROM users WHERE username = %s\", username)\n        user = cursor.fetchone()\n\n        # BAD -- Manually quoting placeholder (%s)\n        cursor.execute(\"SELECT * FROM users WHERE username = '%s'\", username)\n        user = cursor.fetchone()\n\nurlpatterns = [url(r'^users/(?P<username>[^/]+)$', show_user)]\n\n```\n\n## References\n* Wikipedia: [SQL injection](https://en.wikipedia.org/wiki/SQL_injection).\n* OWASP: [SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html).\n* [SQLAlchemy documentation for TextClause](https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.text.params.text).\n* Common Weakness Enumeration: [CWE-89](https://cwe.mitre.org/data/definitions/89.html).\n",
        "security_severity_level": "high"
    },
    "tool": {
        "name": "CodeQL",
        "guid": null,
        "version": "2.13.3"
    },
    "most_recent_instance": {
        "ref": "refs/heads/main",
        "analysis_key": ".github/workflows/codeql_workflow.yml:analyze",
        "environment": "{\"language\":\"python\"}",
        "category": "/language:python",
        "state": "open",
        "commit_sha": "7aefeb59e0175a9657565316fbe188b7b285b94d",
        "message": {
            "text": "This SQL query depends on a user-provided value."
        },
        "location": {
            "path": "server/routes.py",
            "start_line": 16,
            "end_line": 16,
            "start_column": 13,
            "end_column": 67
        },
        "classifications": []
    },
    "instances_url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/1/instances"
}

**Difference between listing out all and separately**
- On calling the APIs for both, we see a difference in their JSON Response bodies. There are two fields that differ, we have **Full Description, and Help** field extra in the separate listing.

---

### Step 7: Call an API to update status of a vulnerability alert
- Screenshots showing the API Call and response:   
- Here is the API to update status of a vulnerability alert:

PATCH request
Headers: "Accept: application/vnd.github+json", "Authorization: Bearer <YOUR-TOKEN>", "X-GitHub-Api-Version: 2022-11-28".
URL: https://api.github.com/repos/OWNER/REPO/code-scanning/alerts/ALERT_NUMBER
Body: {"state":"dismissed","dismissed_reason":"false positive","dismissed_comment":"Comment to list down the reason for flagging as dismissed."}

- Here is the JSON response body (Also provided in the json files):

{
    "number": 1,
    "created_at": "2023-06-20T09:53:25Z",
    "updated_at": "2023-06-20T10:03:00Z",
    "url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/1",
    "html_url": "https://github.com/Arnav-Barman/GHAS-CodeScanning-POC/security/code-scanning/1",
    "state": "dismissed",
    "fixed_at": null,
    "dismissed_by": {
        "login": "Arnav-Barman",
        "id": 86536812,
        "node_id": "MDQ6VXNlcjg2NTM2ODEy",
        "avatar_url": "https://avatars.githubusercontent.com/u/86536812?v=4",
        "gravatar_id": "",
        "url": "https://api.github.com/users/Arnav-Barman",
        "html_url": "https://github.com/Arnav-Barman",
        "followers_url": "https://api.github.com/users/Arnav-Barman/followers",
        "following_url": "https://api.github.com/users/Arnav-Barman/following{/other_user}",
        "gists_url": "https://api.github.com/users/Arnav-Barman/gists{/gist_id}",
        "starred_url": "https://api.github.com/users/Arnav-Barman/starred{/owner}{/repo}",
        "subscriptions_url": "https://api.github.com/users/Arnav-Barman/subscriptions",
        "organizations_url": "https://api.github.com/users/Arnav-Barman/orgs",
        "repos_url": "https://api.github.com/users/Arnav-Barman/repos",
        "events_url": "https://api.github.com/users/Arnav-Barman/events{/privacy}",
        "received_events_url": "https://api.github.com/users/Arnav-Barman/received_events",
        "type": "User",
        "site_admin": false
    },
    "dismissed_at": "2023-06-20T10:03:00Z",
    "dismissed_reason": "false positive",
    "dismissed_comment": "Reason for flagging the alert as false positive!",
    "rule": {
        "id": "py/sql-injection",
        "severity": "error",
        "description": "SQL query built from user-controlled sources",
        "name": "py/sql-injection",
        "tags": [
            "external/cwe/cwe-089",
            "security"
        ],
        "full_description": "Building a SQL query from user-controlled sources is vulnerable to insertion of malicious SQL code by the user.",
        "help": "# SQL query built from user-controlled sources\nIf a database query (such as a SQL or NoSQL query) is built from user-provided data without sufficient sanitization, a user may be able to run malicious database queries.\n\nThis also includes using the `TextClause` class in the `[SQLAlchemy](https://pypi.org/project/SQLAlchemy/)` PyPI package, which is used to represent a literal SQL fragment and is inserted directly into the final SQL when used in a query built using the ORM.\n\n\n## Recommendation\nMost database connector libraries offer a way of safely embedding untrusted data into a query by means of query parameters or prepared statements.\n\n\n## Example\nIn the following snippet, a user is fetched from the database using three different queries.\n\nIn the first case, the query string is built by directly using string formatting from a user-supplied request parameter. The parameter may include quote characters, so this code is vulnerable to a SQL injection attack.\n\nIn the second case, the user-supplied request attribute is passed to the database using query parameters. The database connector library will take care of escaping and inserting quotes as needed.\n\nIn the third case, the placeholder in the SQL string has been manually quoted. Since most databaseconnector libraries will insert their own quotes, doing so yourself will make the code vulnerable to SQL injection attacks. In this example, if `username` was `; DROP ALL TABLES -- `, the final SQL query would be `SELECT * FROM users WHERE username = ''; DROP ALL TABLES -- ''`\n\n\n```python\nfrom django.conf.urls import url\nfrom django.db import connection\n\n\ndef show_user(request, username):\n    with connection.cursor() as cursor:\n        # BAD -- Using string formatting\n        cursor.execute(\"SELECT * FROM users WHERE username = '%s'\" % username)\n        user = cursor.fetchone()\n\n        # GOOD -- Using parameters\n        cursor.execute(\"SELECT * FROM users WHERE username = %s\", username)\n        user = cursor.fetchone()\n\n        # BAD -- Manually quoting placeholder (%s)\n        cursor.execute(\"SELECT * FROM users WHERE username = '%s'\", username)\n        user = cursor.fetchone()\n\nurlpatterns = [url(r'^users/(?P<username>[^/]+)$', show_user)]\n\n```\n\n## References\n* Wikipedia: [SQL injection](https://en.wikipedia.org/wiki/SQL_injection).\n* OWASP: [SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html).\n* [SQLAlchemy documentation for TextClause](https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.text.params.text).\n* Common Weakness Enumeration: [CWE-89](https://cwe.mitre.org/data/definitions/89.html).\n",
        "security_severity_level": "high"
    },
    "tool": {
        "name": "CodeQL",
        "guid": null,
        "version": "2.13.3"
    },
    "most_recent_instance": {
        "ref": "refs/heads/main",
        "analysis_key": ".github/workflows/codeql_workflow.yml:analyze",
        "environment": "{\"language\":\"python\"}",
        "category": "/language:python",
        "state": "dismissed",
        "commit_sha": "7aefeb59e0175a9657565316fbe188b7b285b94d",
        "message": {
            "text": "This SQL query depends on a user-provided value."
        },
        "location": {
            "path": "server/routes.py",
            "start_line": 16,
            "end_line": 16,
            "start_column": 13,
            "end_column": 67
        },
        "classifications": []
    },
    "instances_url": "https://api.github.com/repos/Arnav-Barman/GHAS-CodeScanning-POC/code-scanning/alerts/1/instances"
}

- We can see the changes through webhook: 
- Or we can again list out the vulnerability alerts through the API: 

---

## Difference between the GitHub Free and Enterprise versions

- **GitHub Free** for personal accounts includes **2,000 GitHub Actions minutes per month**.
- **GitHub Enterprise** accounts enable us to manage billing and settings, enforce policy, and audit the people with access to the enterprise's resources. We get GitHub Support via email and additional security, compliance, and deployment controls. We also get **50,000 GitHub Actions minutes per month** and a service level agreement for 99.9% monthly uptime. There is an option to centrally manage policy and billing for multiple GitHub organizations with an enterprise account. And also the option to provision and manage the user accounts for developers, by using Enterprise Managed Users.

---