SQL Injections

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.

Query parameter injection occurs when an attacker manipulates input data to execute unintended commands on a database. This can lead to unauthorized data access or even data corruption. Imagine a scenario where a user inputs a search term, and instead of just searching, the input is crafted to alter the database query. This vulnerability arises when user inputs are not properly sanitized or validated.

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 a 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 code that demonstrates how query parameter injection can occur:

const searchTerm = req.query.term;
const query = `SELECT * FROM snippets WHERE title LIKE '%${searchTerm}%'`;
const stmt = db.prepare(query);
const results = stmt.all();

In this code, the searchTerm is directly concatenated into the SQL query. This approach is risky because it allows any input to be executed as part of the query. If an attacker inputs something like "' OR '1'='1", it could return all records from the database, demonstrating a classic SQL injection vulnerability.

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

To understand the impact of this vulnerability, let's examine two attack scenarios:

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

When this attack is executed, the resulting SQL query becomes:

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

Since '1'='1' is always true, this query returns all records from the snippets table, completely bypassing the intended title filter.

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

This more sophisticated attack results in the following SQL query:

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

The UNION SELECT statement queries the SQLite system table sqlite_master, revealing all table names in the database. The multiple name columns in the SELECT statement match the number of columns in the original query, which is required for a UNION operation to work.

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

To protect against query parameter injection, we can use parameterized queries. This approach prevents the input from being executed as SQL code, effectively mitigating injection risks. Let's modify the code to use parameterized queries:

const searchTerm = req.query.term;
const query = `SELECT * FROM snippets WHERE title LIKE ?`;
const stmt = db.prepare(query);
const results = stmt.all([`%${searchTerm}%`]);

In this updated code, the ? placeholder is used in the SQL query, and the searchTerm is passed as a parameter. The database adapter library handles these parameters securely by:

1. Separating the SQL code from the data
2. Properly escaping special characters in the input
3. Ensuring the input is treated strictly as data and never as executable SQL code

This internal handling by the database adapter makes it impossible for malicious input to change the structure of the intended query, effectively preventing SQL injection attacks.

While parameterized queries provide excellent protection, we can further enhance our security by adding input validation.

Next, let's add input validation to ensure the search term is a valid string. This step further enhances security by rejecting potentially harmful inputs:

if (!searchTerm || typeof searchTerm !== 'string' || searchTerm.length > 100) {
  return res.status(400).json({ error: "Invalid search term" });
}

This validation checks that the searchTerm is a non-empty string and not excessively long, preventing malicious or malformed inputs from being processed.

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.

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