Welcome to the "A03: Injection" course! In this lesson, we will explore the concept of query parameter injection, a type of injection attack that poses significant risks to web applications. Injection attacks are among the most common vulnerabilities, and understanding them is crucial for building secure applications.

By the end of this lesson, you'll be equipped with the knowledge to identify and mitigate query parameter injection vulnerabilities in your projects. Let's dive in! 🚀

To begin our journey into query parameter injection, let's first understand what it is and why it matters.

What are Query Parameters?

In a typical web application, query parameters are used to send data from the client (like a web browser) to the server. You've likely seen them in URLs, appearing after a question mark (?), such as ?term=fastapi in http://example.com/search?term=fastapi. They are a fundamental way for users to interact with an application, providing input for searches, filters, and other functions.

How Does the Injection Happen?

Query parameter injection occurs when an attacker manipulates these parameters to insert malicious code (often SQL) into a query that the application runs on its database. This vulnerability arises when the application takes the user-supplied data from a query parameter and directly embeds it into a database query string without proper sanitization or separation. By doing so, the application blurs the line between data and executable code, allowing the attacker's input to alter the intended logic of the database command.

It's important to note that having just a single vulnerable endpoint can potentially expose your entire database to attackers, making proper security measures crucial across all parts of your application.

For example, consider a search feature that directly incorporates user input into an SQL query without validation. This can allow attackers to inject malicious SQL code, potentially exposing sensitive data or compromising the database's integrity.

Now, let's examine some vulnerable code to see how this attack works in practice.

Let's examine a piece of Python code that demonstrates how query parameter injection can occur. This example uses FastAPI to define an API endpoint that searches for "snippets" in a database based on a term provided in the URL's query string.

@router.get("/find")
async def search(term: str, db: AsyncSession = Depends(get_db)):
    try:
        # VULNERABLE: raw string concatenation
        query = text(f"SELECT * FROM snippets WHERE title LIKE '%{term}%'")
        result = await db.execute(query)
        rows = [dict(r._mapping) for r in result.fetchall()]
        return rows
    except Exception as e:
        raise HTTPException(status_code=500, detail="Internal server error")

The critical vulnerability lies in this line: query = text(f"SELECT * FROM snippets WHERE title LIKE '%{term}%'"). The code uses a Python f-string to directly insert the term variable into the SQL query string. This method of string formatting is dangerous because it mixes the static SQL command with dynamic, user-controlled data. The database cannot distinguish between the intended query logic and the injected malicious commands, so it executes the entire combined string.

With this vulnerable code in mind, let's explore how an attacker might exploit it.

An attacker can exploit this vulnerability by crafting a special search term that contains SQL syntax. When the application inserts this term into its query, the attacker's code becomes part of the command executed by the database. Let's examine two attack scenarios.

Attack 1: Bypassing Filters

The attacker's goal here is to bypass the search filter and retrieve all records from the snippets table.

# SQL injection attack to retrieve all snippets regardless of title
curl -G --data-urlencode "term=%' OR 1=1 --" http://localhost:3000/api/search/find

When this curl command is executed, the term parameter is set to %' OR 1=1 --. The vulnerable code then constructs the following SQL query:

SELECT * FROM snippets WHERE title LIKE '%%' OR 1=1 --%'

Let's break down why this works:

1. LIKE '%%': The injected % completes a valid LIKE pattern. The %% pattern matches any string (even empty strings), though this condition isn't needed for the attack to succeed.
2. The injected ' closes the string literal, breaking out of the LIKE pattern.
3. OR 1=1: This adds a condition that is always true. Because the OR operator requires only one condition to be true, and 1=1 is universally true, the WHERE clause evaluates to true for every single row in the snippets table.
4. --: This is a SQL comment operator that tells the database to ignore everything that follows, including the trailing %' from the original f-string template, preventing a syntax error.

As a result, the database returns all records, completely bypassing the intended title filter.

Attack 2: Exfiltrating Database Schema Information

This more sophisticated attack aims to extract metadata about the database itself, such as table names.

# SQL injection to retrieve table names from the database schema
curl -G --data-urlencode \
"term=' UNION SELECT name, name, name, name, name FROM sqlite_master WHERE type='table'-- " \
http://localhost:3000/api/search/find

This attack uses a UNION operator to combine the results of the original query with a new, malicious one. The resulting SQL query is:

SELECT * FROM snippets WHERE title LIKE '%' UNION SELECT name, name, name, name, name FROM sqlite_master WHERE type='table'--%'

Here's the breakdown:

• ': The initial quote closes the LIKE clause prematurely.
• UNION SELECT ...: This appends a new SELECT statement. The UNION operator requires that both queries have the same number of columns. The attacker guesses that the snippets table has five columns and matches it by selecting the name column five times.
• FROM sqlite_master WHERE type='table': This queries a system table specific to SQLite (sqlite_master) that stores schema information. This part of the query asks for the names of all tables in the database.
• --: This is a comment operator in SQL. It tells the database to ignore everything that follows, including the trailing %' from the original f-string, preventing a syntax error.

This query successfully extracts and returns a list of all table names in the database, giving the attacker valuable information for further attacks.

Now that we understand how these attacks work, let's learn how to prevent them using parameterized queries.

The most effective defense against SQL injection is to never mix user-controllable data with your SQL code. Parameterized queries (also known as prepared statements) enforce this separation. With this approach, you send the SQL query template to the database first, with placeholders for the data. Then, you send the user's data separately. The database engine combines them safely, ensuring the data is treated only as data and never as executable code.

Let's modify our code to use this secure method:

@router.get("/find")
async def search(term: str, db: AsyncSession = Depends(get_db)):
    # Input validation
    if not term or len(term) > 100:
        raise HTTPException(status_code=400, detail="Invalid search term")
    try:
        # DEFENSIVE: parameterized query
        query = text("SELECT * FROM snippets WHERE title LIKE :term")
        result = await db.execute(query, {"term": f"%{term}%"})
        rows = [dict(r._mapping) for r in result.fetchall()]
        return rows
    except Exception:
        raise HTTPException(status_code=500, detail="Internal server error")

In this updated code, we've made a crucial change. The query now contains a named placeholder :term: query = text("SELECT * FROM snippets WHERE title LIKE :term")

The user input is then passed in a separate dictionary during execution: result = await db.execute(query, {"term": f"%{term}%"})

The database adapter library (SQLAlchemy in this case) handles the :term parameter securely. It ensures that the value provided for :term is properly escaped and quoted, so it cannot break out of the data context and alter the SQL statement's structure. This makes it impossible for malicious input to change the query's logic, effectively neutralizing SQL injection attacks.

While parameterized queries are the primary defense, adding input validation provides an additional layer of security, a concept known as "defense in depth." By validating user input before it even reaches the database logic, you can reject malformed or suspicious requests early.

Let's add a simple check to our function:

if not term or len(term) > 100:
    raise HTTPException(status_code=400, detail="Invalid search term")

This validation serves two purposes:

1. Security: It enforces constraints on the input, such as length, which can thwart certain types of attacks that rely on very long payloads.
2. Robustness: It ensures the application handles only data that it expects, preventing errors and improving stability. For example, it rejects empty search terms, which might be invalid for the application's business logic.

With these security measures in place, let's wrap up what we've learned and prepare for the next steps.

In this lesson, we've explored the concept of query parameter injection, identified vulnerabilities in code, and implemented defensive coding practices to secure our applications. By understanding and applying these techniques, you can protect your applications from common injection attacks.

Before practice, note that for production code, you should prefer idiomatic SQLAlchemy patterns like select() and bindparam() over raw SQL with text(), as this reduces injection risks and improves maintainability. For this lesson, we use raw SQL to clearly demonstrate injection risks and defenses.

As you move on to the practice exercises, you'll have the opportunity to apply what you've learned and reinforce your understanding. In future lessons, we'll continue to explore other critical security vulnerabilities and their mitigations. Keep up the great work, and let's continue building secure applications together! 🎉