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.

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.

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

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 clause evaluates to true for every single row in the table.

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:

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:

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! 🎉