CodeQL Security Lab — SQL Injection Detection

Course: Secure Software Design (CY321L) — Lab
Institution: GIKI
Status: Complete

---

Overview

This lab demonstrates static analysis for vulnerability detection using GitHub's CodeQL — the same tool used by security engineers at major tech companies to find bugs at scale.

The goal: write a custom CodeQL query to automatically detect SQL injection vulnerabilities in Python code, then verify it flags insecure code and passes secure code cleanly.

---

What is CodeQL?

CodeQL treats source code as a database. You write queries in the CodeQL language to ask questions about the code — like "is there any path where unsanitized user input reaches a SQL query?" — and it returns every matching location in the codebase.

It's used in real-world software security at companies like Microsoft, Google, and in GitHub's own Advanced Security product.

---

Repo Structure

├── my-python-db/          # CodeQL database built from the target Python code
├── SqlInjection.ql        # Custom CodeQL query to detect SQL injection
├── insecure_files.py      # Intentionally vulnerable Python code (lab target)
├── vuln.py                # Additional vulnerable code sample
├── secure_files.py        # Secure version — correctly parameterized queries
├── qlpack.yml             # CodeQL pack configuration
└── codeql-pack.lock.yml   # Dependency lock file

---

The Vulnerability

The insecure code directly interpolates user input into SQL queries:

# INSECURE — vulnerable to SQL injection
query = "SELECT * FROM users WHERE username = '" + user_input + "'"

An attacker can manipulate user_input to alter the query logic, dump the database, bypass authentication, or worse.

The secure version uses parameterized queries:

# SECURE — input is treated as data, not code
cursor.execute("SELECT * FROM users WHERE username = ?", (user_input,))

---

The CodeQL Query

SqlInjection.ql models the vulnerability as a taint tracking problem:

• Source: user-controlled input entering the program
• Sink: string passed directly into a SQL execution function
• Result: CodeQL traces every path where tainted data reaches the sink without sanitization

The query successfully flagged the vulnerabilities in insecure_files.py and vuln.py, and produced no alerts on secure_files.py.

---

How to Run

1. Install CodeQL CLI

Download from github.com/github/codeql-cli-binaries

2. Build the database

codeql database create my-python-db --language=python --source-root=.

3. Run the query

codeql query run SqlInjection.ql --database=my-python-db

4. View results

Results will show file paths, line numbers, and the taint flow path from source to sink.

---

Key Takeaways

• Static analysis can automatically find entire classes of vulnerabilities without running the code
• CodeQL's taint tracking is more powerful than simple pattern matching — it follows data flow across the entire program
• The same query logic scales to large codebases — this is how real security teams audit millions of lines of code

---

Course

Secure Software Design Lab — CY321L
Ghulam Ishaq Khan Institute of Engineering Sciences and Technology (GIKI)