Case Study: The Security Hole Copilot Suggested

The Setup

It was a routine Wednesday afternoon. Raj was adding a user search feature to an internal admin dashboard. The feature was straightforward: administrators could search users by email or username. The backend was Python/FastAPI with PostgreSQL. The code would only be accessible to internal staff behind the company's VPN. It was not a public-facing endpoint.

Raj was moving quickly. The core of the search functionality was simple — a database lookup with a filter. He typed a comment:

# Search users by email or username, return list of user dicts

And started the function:

async def search_users(query: str, db: AsyncSession) -> list[dict]:

Copilot suggested the following implementation:

async def search_users(query: str, db: AsyncSession) -> list[dict]:
    sql = f"SELECT id, email, username, created_at FROM users WHERE email LIKE '%{query}%' OR username LIKE '%{query}%'"
    result = await db.execute(text(sql))
    rows = result.fetchall()
    return [{"id": r.id, "email": r.email, "username": r.username, "created_at": str(r.created_at)} for r in rows]

Raj accepted the suggestion with a single Tab press and moved on to the next function.

He almost shipped it.

Catching the Bug

Two days later, Raj was doing a final review pass before creating the pull request. He was reading through the new admin dashboard code when the line caught his eye:

sql = f"SELECT id, email, username, created_at FROM users WHERE email LIKE '%{query}%' OR username LIKE '%{query}%'"

The f string. The {query} directly interpolated.

He stopped.

In the two days since he had accepted the suggestion, he had forgotten writing it. Reading it fresh, the vulnerability was obvious: the query parameter was being inserted directly into the SQL string with no escaping or parameterization. This was a textbook SQL injection vulnerability.

An attacker who could reach the endpoint — or a malicious insider at the company — could supply a query string like:

%' OR '1'='1

Which would transform the query to:

SELECT id, email, username, created_at FROM users WHERE email LIKE '%%' OR '1'='1%' OR username LIKE '%%' OR '1'='1%'

This would return all users in the database. More sophisticated injection strings could extract data from other tables, modify data, or (depending on database permissions) execute administrative operations.

The internal-only, VPN-protected framing was irrelevant. SQL injection from a malicious insider is still SQL injection. The defense posture of "it's internal" is no defense at all.

Why He Had Accepted It

Raj spent some time thinking about why he had accepted this suggestion without catching the vulnerability in the moment.

Speed and context switching: He was moving quickly through several functions in sequence. He reviewed the suggestion for whether it did the right thing — searched users, returned the right fields — not for how it did it.

Visual plausibility: The code looked reasonable. It used text() from SQLAlchemy (which Raj recognized as the raw SQL execution pattern), it selected the right columns, it returned the right structure. Nothing looked syntactically wrong.

The internal-use rationalization: Even if he had noticed the interpolation, he might have dismissed it: "this is just an internal admin tool, not worth the overhead of parameterization." This is a common rationalization that creates a security debt that eventually comes due.

No visual signal: Copilot presented the vulnerable code and the secure code identically — both as confident, correctly formatted suggestions. There was nothing in the interface that flagged the security concern.

The Fix

The fix was straightforward:

async def search_users(query: str, db: AsyncSession) -> list[dict]:
    search_pattern = f"%{query}%"
    sql = text(
        "SELECT id, email, username, created_at FROM users "
        "WHERE email LIKE :pattern OR username LIKE :pattern"
    )
    result = await db.execute(sql, {"pattern": search_pattern})
    rows = result.fetchall()
    return [
        {
            "id": r.id,
            "email": r.email,
            "username": r.username,
            "created_at": str(r.created_at)
        }
        for r in rows
    ]

The key change: named parameters (:pattern) in the SQL string, passed as a separate dict. SQLAlchemy's text() with named parameters handles escaping correctly. The user-provided query string never touches the SQL string directly.

Raj also added two other improvements while he was there: a maximum length check on the query parameter (unbounded LIKE queries can be slow for large tables) and a minimum length requirement (single-character searches could return an enormous result set).

Building a Trust Calibration Protocol

After this incident, Raj developed a formal protocol for reviewing AI-generated code that handles data. He wrote it down and pinned it to his monitor.

The Protocol

Step 1: Identify the data context Before reviewing generated code, answer: Does this code handle user-provided input? Does it touch a database? Does it return sensitive data? If yes to any of these, trigger the security review mode.

Step 2: Trace every user input Follow every variable that originated from user input. Where does it go? Does it ever touch a SQL string, a shell command, an HTML template, a file path? Any contact point is a potential injection vulnerability.

Step 3: Check string construction in data operations Specifically look for f-strings, .format(), % formatting, or any string concatenation that includes variables in SQL, shell commands, or templated output. These are the construction patterns that create injection vulnerabilities.

Step 4: Verify sanitization functions If the code calls a sanitization or validation function, verify that function actually does what you expect. Do not assume — check. A function called sanitize_input might or might not provide SQL injection protection.

Step 5: Check the parameterization For database operations, verify that any query with variable data uses parameterized queries (bound parameters, prepared statements, or ORM methods that handle parameterization). The pattern cursor.execute(sql_string, (param,)) is safe; cursor.execute(f"... {param} ...") is not.

Step 6: Consider the "malicious insider" threat Do not reason about security based on who is expected to use the code. Consider what a motivated attacker with legitimate access would do. "It's internal" is not a defense.

Applying the Protocol

The protocol changes how Raj reads code, not just what he looks for. When he encounters any function that takes external input and does something with it, he now has a mental checklist rather than a general sense of "looks okay."

He also added a step to his pre-PR checklist:

For any function that accepts parameters and uses them in database operations, file operations, or system calls: trace the parameter from input to use and verify no interpolation into executable strings.

What the Incident Revealed About Copilot and Security

This incident was not a one-off. In the weeks after, Raj began paying closer attention to Copilot's data-handling suggestions and found a pattern.

Copilot does not have a security mode. It does not apply extra scrutiny to data-handling code. It generates what it predicts is most likely based on training data, and insecure patterns appear frequently enough in public repositories to get predicted regularly.

LIKE queries with direct interpolation are particularly common in training data. They appear in countless tutorials, Stack Overflow answers, and small-project codebases. Copilot has seen them thousands of times. It generates them confidently.

The vulnerability looks identical to the non-vulnerable version at a glance. This is the insidious nature of injection vulnerabilities: they are not visually distinctive. The difference between f"... {query}..." and "... :query" is subtle to a tired developer reviewing quickly.

Internal tools are frequently less secure than external ones. This is a general security problem, not an AI-specific one. But AI code assistants may exacerbate it by generating code quickly that looks fine and gets merged without the scrutiny an external-facing feature would receive.

The Broader Lesson

The SQL injection vulnerability in Raj's search function did not make it to production. Raj caught it during review, fixed it, and shipped the secure version. The incident was caught by exactly the process it was supposed to be caught by.

But Raj was doing his review pass while also thinking about the next feature. He almost missed it. And if the codebase had been larger, the function more obscure, or the review less careful, it might have shipped.

The lesson is not "do not use Copilot for data-handling code." Raj still uses Copilot for database operations — with the protocol he developed. The lesson is that the review discipline required for security-sensitive code is non-negotiable regardless of how the code was generated.

AI coding assistants are productivity tools, not security tools. The responsibility for secure code remains with the developer. The developer who accepts that responsibility — who maintains review discipline even when moving quickly, even for internal tools, even when the suggestion looks plausible — will get the productivity benefits of AI assistance without the security costs.

The developer who treats Copilot's confidence as a substitute for their own scrutiny will eventually ship a vulnerability. It is only a matter of time and context.

Raj's protocol is available as a reference in the exercises for this chapter. It is not the only approach to reviewing AI-generated code for security. But it is a concrete starting point for any developer who wants to build the habit.

The key insight: the same tools and behaviors that make AI code assistants productive — fast suggestion acceptance, reduced cognitive overhead — are exactly the behaviors that create security risk. Calibrating between speed and scrutiny based on task type is the skill that makes AI coding tools valuable rather than dangerous.