Gemini on Question 19, Sep 17

Here is a structured breakdown of the three primary classes of SQL Injection (SQLi) designed specifically for a developer briefing.

You can use this to explain not just how attackers steal data, but why specific coding practices are necessary to stop them.

SQL Injection (SQLi) Categories: A Developer Briefing

SQL Injection occurs when untrusted user input is dynamically appended to a database query without proper sanitization or parameterization, allowing the attacker to alter the query's logic.

Industry standard (OWASP) divides SQLi into three main categories based on how the attacker retrieves the data.

1. In-Band SQLi (Classic SQLi)

The Concept: The attacker uses the same communication channel to launch the attack and gather the results. This is the most common and easiest type of SQLi to exploit.

There are two main sub-types:

• •Error-Based SQLi: The attacker intentionally inputs characters (like a single quote ') that cause the database to generate an error. If error handling is poorly configured, the application returns the raw database error to the user's screen. These errors often leak information about the database structure, table names, or the underlying technology (e.g., MySQL vs. SQL Server).
• •UNION-Based SQLi: The attacker uses the UNION SQL operator to combine the results of the original, legitimate query with the results of a new, malicious query.
• •Example: If a product search query is vulnerable, an attacker can append UNION SELECT username, password FROM users--. The application will then render the usernames and passwords on the web page as if they were search results.

2. Inferential SQLi (Blind SQLi)

The Concept: The application is vulnerable, but it does not return database errors or data directly to the screen. Instead, the attacker sends payloads that ask the database True or False questions and "infers" the data based on how the application responds.

Because the attacker has to extract data character by character, this is slower but just as devastating. There are two sub-types:

• •Boolean-Based (Content-Based) Blind: The attacker sends an SQL query that forces the database to evaluate a condition. If the condition is TRUE, the web page loads normally. If the condition is FALSE, the web page loads differently (e.g., missing a specific image, or displaying a generic "Not Found" message).
• •Example: The attacker asks, "Is the first letter of the admin's password an 'A'?" If the page loads normally, they know the answer is Yes. If it loads differently, they move on to 'B'.
• •Time-Based Blind: The web page looks exactly the same whether the SQL query evaluates to True or False. To bypass this, the attacker injects a command that tells the database to pause (e.g., SLEEP(10) or WAITFOR DELAY) only if a condition is true.
• •Example: The attacker asks, "Is the database version MySQL 8?" If the website takes exactly 10 seconds to load, the attacker knows the answer is True.

3. Out-of-Band (OOB) SQLi

The Concept: This is the rarest form. It is used when the attacker cannot use the same channel to get results (In-Band) and Time-Based Blind is either too slow or blocked by timeouts.

Instead, the attacker forces the database server itself to make an external network request (usually via DNS or HTTP) to a server controlled by the attacker. They append the stolen data to that request.

• •Example: The attacker injects a command that forces the database server to perform a DNS lookup for [administrator_password].attacker-domain.com. The attacker then looks at their own DNS logs, sees a request for password123.attacker-domain.com, and extracts the password.
• •Note: This relies on the database having specific features enabled (like xp_cmdshell in MS SQL or LOAD_FILE() in MySQL) and the database server having outbound internet access.

Action Items for the Development Team

To prevent all three classes of SQL injection, the team must adhere to the following rules:

1. Use Parameterized Queries (Prepared Statements) [Mandatory]

This is the primary defense. Parameterized queries force the database to treat user input as data (a literal value), not as executable code. Even if a user inputs ' OR 1=1--, the database simply searches for a user whose literal name is ' OR 1=1--.

• •Do not use string concatenation or template literals to build SQL queries.

2. Use Object-Relational Mapping (ORMs) Safely

Modern ORMs (Entity Framework, Hibernate, Sequelize, etc.) use parameterized queries by default. However, developers must avoid using "raw query" escape hatches (like sequelize.query() or DB::raw()) with unsanitized user input.

3. Implement Strict Allow-Listing for Dynamic Identifiers

Parameterized queries cannot be used for column names, table names, or ORDER BY clauses. If a user can sort a table by clicking a column header, you cannot parameterize the ORDER BY [UserInput] statement.

• •Fix: Map user input to a strict allow-list in the backend code. (e.g., If user input is "price_desc", map that in the code to ORDER BY price DESC. If the input doesn't match the allow-list, reject it).

4. Enforce Least Privilege (Database Level)

Ensure the application connects to the database using an account with the absolute minimum privileges necessary. The app should not connect as sa or root. It should not have permission to drop tables, execute shell commands, or make outbound network requests unless explicitly required. This heavily mitigates Out-of-Band attacks.