Understanding SQL Injection (SQLi) Fundamentals

The Anatomy of an Injection

SQL Injection is a vulnerability that occurs when an application insecurely combines user-supplied data with a backend SQL query. Instead of treating the user's input strictly as data, the application allows it to be interpreted as part of the SQL command itself. This allows an attacker to alter the query's logic, leading to severe consequences.

To understand the mechanics, let's start with a foundational reading from PortSwigger's Web Security Academy.

The core issue is almost always the direct concatenation of input into a query string. Consider a backend that queries a product database like this:

String query = "SELECT * FROM products WHERE category = '" + userInput + "'";

If a user provides Gifts, the query is harmless:
SELECT * FROM products WHERE category = 'Gifts'

But what if an attacker provides a crafted input? This is where the injection happens.

Subverting Logic: The Authentication Bypass

The most classic SQLi example is bypassing a login form. The application expects a username and password and checks them against a users table with a query like this:

SELECT * FROM users WHERE username = 'some_user' AND password = 'some_password'

An attacker can exploit this by providing a username that alters the query's logic. A common payload is administrator'--.

Let's break this down:

1. administrator: This is the target username.
2. ': This single quote closes the opening quote around the username field in the SQL query. The query now looks like ... WHERE username = 'administrator'.
3. --: This is a comment indicator in most SQL dialects. Everything after it on the same line is ignored by the database.

The resulting query becomes:
SELECT * FROM users WHERE username = 'administrator'--' AND password = '...ignored...'

The database executes the query SELECT * FROM users WHERE username = 'administrator'. Since the password check has been commented out, the query returns the administrator's user record, and the attacker is logged in without needing a password.

This video from David Bombal, featuring security researcher Rana Khalil, provides an excellent practical demonstration of this exact attack.

Retrieving Data with In-Band SQLi

Beyond bypassing logic, SQLi can be used to extract data from the database. This is often done through "In-Band" SQLi, where the data is returned in the same channel—the web response—as the attack was sent.

Modifying the WHERE Clause

A simple way to extract more data is to make the WHERE clause universally true. An attacker can append ' OR 1=1-- to an input parameter.

If our original query was:
SELECT * FROM products WHERE category = 'Gifts' AND released = 1

The injected query becomes:
SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND released = 1

Since 1=1 is always true, the WHERE clause evaluates to true for every single product, causing the application to return all products, including those that are not yet released.

Using UNION for Cross-Table Extraction

The UNION operator is even more powerful. It allows an attacker to combine the results of the original, legitimate query with the results of a second, completely separate query. This is the primary method for extracting data from other tables, such as a users table.

To use UNION successfully, an attacker must follow two rules:

1. The UNION query must have the same number of columns as the original query.
2. The data types of the columns in both queries must be compatible.

An attacker would systematically determine the column count using the ORDER BY clause (e.g., ' ORDER BY 1--, ' ORDER BY 2--, etc., until an error occurs) and then craft a UNION payload.

For a query that returns two string columns (e.g., product name and description), the payload might look like this:
' UNION SELECT username, password FROM users--

The application would execute the original query for the product category and then append the results of SELECT username, password FROM users, displaying all usernames and passwords on the page.

The following resources provide a detailed walkthrough of this entire process.

For a more hands-on perspective, this video demonstrates the UNION attack against a vulnerable application, showing the step-by-step process of identifying the column count and extracting credentials.

When the Application is Silent: Blind SQL Injection

What happens if the application is vulnerable to SQLi but doesn't display any query results or database errors in its response? This is known as Blind SQL Injection. Exploiting it is more complex and relies on inference.

There are two main types:

• Boolean-based Blind SQLi: The attacker asks a series of true/false questions and observes a difference in the application's response. For example, injecting ' AND (SELECT 'a'='a') might result in a "Welcome back!" message, while ' AND (SELECT 'a'='b') does not. By asking questions like "Is the first character of the admin password 'a'?", an attacker can slowly reconstruct data.
• Time-based Blind SQLi: The attacker injects a command that tells the database to pause for a few seconds if a certain condition is true (e.g., WAITFOR DELAY '0:0:5'). By measuring the server's response time, the attacker can infer the answer to their question.

This process is extremely tedious to perform manually and is almost always automated with scripts. Given your background in coding, you may find the automation aspect particularly interesting. The following video segment demonstrates this advanced technique and the Python script used to automate it.

How to Prevent SQL Injection

While our focus is on exploitation, it's crucial to understand the defense. The primary method for preventing SQLi is the use of parameterized queries, also known as prepared statements. This technique rigorously separates the SQL code from the user-supplied data.

Vulnerable Code (Java example):

String query = "SELECT * FROM products WHERE category = '" + input + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

Secure Code (using a prepared statement):

PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");
statement.setString(1, input);
ResultSet resultSet = statement.executeQuery();

In the secure version, the query structure is defined first with a placeholder (?). The user input is then supplied as a parameter. No matter what the input contains (' OR 1=1--), it is treated purely as a string value for the category field and can never alter the query's logic.

Conclusion

In this lesson, we've unpacked the basic mechanics of SQL injection. You've seen how simple, unsanitized user input can be weaponized to manipulate backend database queries, leading to devastating breaches of confidentiality and integrity.

Key takeaways from our exploration include:

• SQLi's Root Cause: The unsafe concatenation of user input into database queries.
• Logic Subversion: Payloads like '-- can comment out parts of a query to bypass security checks, such as password verification.
• In-Band Data Exfiltration: Techniques like modifying WHERE clauses with OR 1=1 or using UNION SELECT can read and return data from the database directly in the HTTP response.
• Blind SQLi: When direct output isn't available, attackers can infer data by observing application behavior changes (boolean-based) or response delays (time-based).
• Primary Defense: The use of parameterized queries is the most effective way to prevent SQLi by ensuring user input is always treated as data, not executable code.

We've now covered your first major web vulnerability. We focused on attacking the application's server-side logic and its data store. In our next lesson, we will pivot to the client-side and examine Cross-Site Scripting (XSS), a vulnerability where the attacker's target is not the database, but another user's browser.