Exercise: Introduction to CodeQL

[github-actions]

Introduction to CodeQL

👋 Hey there @TaronAr! Welcome to your Skills exercise!

Learn to use CodeQL to find security vulnerabilities in your code.

---

✨ This is an interactive, hands-on GitHub Skills exercise!

As you complete each step, I’ll leave updates in the comments:

• ✅ Check your work and guide you forward
• 💡 Share helpful tips and resources
• 🚀 Celebrate your progress and completion

Let’s get started - good luck and have fun!

_{— Mona}

[github-actions]

Step 1: Enable Code Scanning

Let's start by learning a bit about code scanning with CodeQL and how it helps secure your code.

What is GitHub Code Scanning?

Code scanning is part of the GitHub Advanced Security (GHAS) product suite. It allows development teams to integrate security testing tools directly into the same process you already use for shipping code. It supports many types such as SAST, container, and infrastructure as code. And, the best part is that the results can also live directly in GitHub next to your code. No need for context switching! 🎉

Tip

All features of GitHub Advanced Security are free for public repositories. However, private repositories require a compatible paid account.

What is CodeQL?

CodeQL is a static analysis testing tool that helps you identify security weaknesses such as SQL injection, cross-site scripting, and code injection issues.

Typically CodeQL queries are collected into query suites to cover multiple patterns. When combined well, this can be a very powerful! To help with this, teams of security experts have pre-populated suites for many common scenarios and programming languages.

In many cases, taking advantage of CodeQL is as simple as accepting the default suite, but you can also select the extended suite or customize your own with GitHub Actions.

Here are some of the options the default configuration provides:

• Languages: Supported languages will automatically detected in your repository and scanning will be enabled.

• Query suites: A list of the available suites of patterns that will be used. The Default or Extended are provided automatically.

• Events: Triggers for running a CodeQL scan. It's common to run before merging and on a schedule for production code.

⌨️ Activity: Enable code scanning with CodeQL

1. Open a second tab and navigate to this repository. Ensure you are on the Code tab.

2. In the top navigation, select the the Settings tab.

3. In the left navigation, fine the Security section and select Advanced Security.

4. Scroll down and find the Code scanning area.

5. In the CodeQL setting, click the Set up dropdown menu and choose Default.

   

6. Click Enable CodeQL.

   💡 Tip: This will trigger a first run of CodeQL. You can view the progress in the Actions tab.

7. With CodeQL now enabled, Mona will check your progress and share the next steps.

[github-actions]

Please, follow the steps above.
I'll watch your progress in the background to provide feedback. 🧐

[github-actions]

🎉🎉🎉 Nice work! Everything is perfect! 🎉🎉🎉
Preparing content for step 2! One moment... 🤓

[github-actions]

Step 2: Detect Vulnerabilities in a Pull Request

To see how Code Scanning works, we will introduce a vulnerability into the routes.py file to trigger an alert.

⌨️ Activity: Create a vulnerability

1. In the top navigation, select the Code tab.

2. Navigate to the server folder and select the routes.py file.

3. In the top right of the preview, click the Edit button.

   

4. Navigate to about line 16 and modify it to the below.

   "SELECT * FROM books WHERE name LIKE '%" + name + "%'"

5. Above the editor in the top-right, click the Commit changes... button. In the prompt window, select the radio button for the Create a new branch option. DO NOT commit to the main branch.

6. Click the Propose changes option and click Create pull request. Use the below branch name.

   learning-codeql

7. On the new page, below the pull request description, press the Create pull request button.

⌨️ Activity: Review pull request

1. If needed, navigate to the newly created pull request from the previous activity.

2. Scroll to the bottom of the pull request and search for a check named CodeQL. This is the analysis job scanning the proposed code changes in the pull request.

   

3. If the job is still running, wait a few minutes for it to complete.

4. Search the comments to find the results of the analysis.

   • Notice that the results found a SQL injection vulnerability. It also suggests a fix.
   • Don't worry about responding to this or resolving the problem (yet).
   

   💡 Tip: Clicking the Show paths link will provide additional insights about the alert's data flow from user input (source), through the application, and when it is acted on (sink).

⌨️ Activity: View the CodeQL scanning logs

1. In the top navigation, select the Actions tab.

2. In the left navigation, select the CodeQL entry to filter the workflow runs.

   

3. Click on the workflow run with the name PR Fix SQL query to use wildcard for name search #2 to open a page with more details.

   

4. Expand the run jobs by clicking Show all jobs then click on the Analyze (python) entry. The list of all workflow steps is now shown.

   

5. Find the analysis entry and consider reviewing the logs.

   

6. With the pull request started and CodeQL scan finished, Mona will check your progress and share the next steps.

Tip

Check out the Triage code scanning alerts in pull requests page to learn more about integration of code scanning into pull requests.

[github-actions]

Please, follow the steps above.
I'll watch your progress in the background to provide feedback. 🧐

[github-actions]

🎉🎉🎉 Nice work! Everything is perfect! 🎉🎉🎉
Preparing content for step 3! One moment... 🤓

[github-actions]

Step 3: Review and Triage CodeQL Alerts

With our pull request changes now reviewed by CodeQL, we now have some results to view.Let's learn about managing alerts.

GitHub provides a dedicated Security tab for securely managing all security related issues. CodeQL saves alerts using the same standard as many other analysis tools with the results showing up under the Code scanning area.

What information do alerts provide?

The main area of an alert provides the resolution status, affected branch, code location, and classification information like severity and CVE identification number.

After the status information, a detailed description of the issue, recommended solutions, and suggested code changes are provided.

What is CWE?

Many of the patterns CodeQL scans for come from existing databases of vulnerabilities, which are categorized for easier understanding.

The Common Weakness Enumeration (CWE) is a category system for hardware and software weaknesses and vulnerabilities. Think of it as a way to describe and categorize security issues in an application's source code. For more information on CWEs, see the Wikipedia article Common Weakness Enumeration.

⌨️ Activity: View existing alerts

1. In the top navigation, select the Security tab.

2. In the left navigation, find the Vulnerability alerts area and select the Code scanning option.

   • Notice that there are no alerts. This is expected since the vulnerable code on the pull request has not been merged yet.

3. Return to the recently created pull request. Ignore the failed check and click the Merge pull request button.

   

4. Click the Delete branch. It is not needed anymore.

5. Wait a moment for CodeQL to analyze the new changes to the main branch.

6. Return the the Security tab.

7. In the left navigation, notice that the Code Scanning option now has a 1 entry next to it, informing us of an open alert.

   

⌨️ Activity: Review an Alert

1. In the left navigation, select the Code scanning option.

2. Click on the open alert.

3. Notice the description, vulnerability description, and a recommended solution.

   

4. Notice the audit trail provides the source of the vulnerability and shows that it came from our pull request.

⌨️ Activity: Dismiss and Reopen an Alert

1. In the top right, click Dismiss alert dropdown.

2. Select the Used in tests option and enter the below description.

   This is a playground repository for learning about CodeQL alerts.

   

3. Click the Dismiss alert button.

   • The alert state will change to Dismissed.
   • A read-only entry was added with the description to the audit trail showing who closed it.
   

4. In the top right, click the Reopen alert button.

   • The alert state will change back to Open.
   • A read-only entry was added to the audit trail showing who opened it.

5. With an alert closed and reopened, add a comment to this issue asking Mona to check our progress and share the next steps.

   Hey @professortocat, I've closed an reopened an alert. What is the next step?

[github-actions]

Please, follow the steps above.
I'll watch your progress in the background to provide feedback. 🧐

[TaronAr]

Hey @professortocat, I've closed an reopened an alert. What is the next step?

[github-actions]

🎉🎉🎉 Nice work! Everything is perfect! 🎉🎉🎉
Preparing content for step 4! One moment... 🤓

[github-actions]

Step 4: Fix Security Vulnerabilities

Finally, let's use the information provided by CodeQL to better understand the vulnerability and fix it.

⌨️ Activity: Resolve an open alert

1. Review the open alert and become familiar with the recommended changes.

2. In the top navigation, select the Code tab.

3. Ensure you are on the main branch then navigate to the server folder and select the routes.py file.

4. In the top right of the preview, click the Edit button.

   

5. Navigate to about line 16 and modify it to the below.

   "SELECT * FROM books WHERE name LIKE %s", name

6. Above the editor in the top-right, click the Commit changes... button. Use the defaults options to commit directly to the main branch.

   • CodeQL will now initiate a another scan.

7. Wait a moment for the CodeQL workflow to finish.

8. Return the the Security tab and Code Scanning area.

   • There should be no open alerts and 1 closed alerts. Nice work! 🎉
   • Feel free to review the closed alerts, especially the audit trail.

9. Click on Closed to show our recently resolved alert.

   

10. Open our alert and notice the audit trail now includes how the alert was fixed.

    

11. With our alert fixed, Mona will check your progress and share a final review. Nice work! You are all done! 🥳

[github-actions]

Please, follow the steps above.
I'll watch your progress in the background to provide feedback. 🧐

[github-actions]

🎉🎉🎉 Nice work! Everything is perfect! 🎉🎉🎉
Now, let's do a quick review!

[github-actions]

Review

Great job completing the exercise! You have now:

• Enabled Code Scanning with CodeQL in your repository.
• Introduced and detected a vulnerability using a pull request.
• Reviewed and triaged CodeQL alerts.
• Fixed a security vulnerability and verified the alert was resolved.

By following these steps, you’ve learned how to use GitHub’s security features to keep your codebase safe. Remember, regularly reviewing and addressing security alerts is an important part of maintaining healthy projects.

What's next?

• Take another Skills exercise..
• Visit the CodeQL documentation to learn about about customizing your code scanning.
• Check out the code scanning documentation to learn about connecting 3rd party scanning tools
• Explore the CodeQL CLI & VS Code extension to run and write custom queries locally.
• Read the triaging code scanning alerts guide for best practices on investigating alerts.
• Learn about advanced CodeQL query features to build complex custom analyses.

[github-actions]

Congratulations @TaronAr! You finished the exercise! 🎉🎉🎉

We've updated the repository with a couple changes to highlight your success!

Return to the repository home page to see your progress!