🗄️ DBMS 09: SQL Injection Prevention, Binary Protocols & Database Security

Targeted for: Juspay Elimination Round, Systems & Backend Database Architecture Interviews
Key Focus: SQL Injection (SQLi) mechanics, How Prepared Statements work at the database engine & binary wire protocol level, ORM security pitfalls, B+ Tree internals & fanout calculations, Write-Ahead Logging (WAL / ARIES), and Composite Index mechanics.

📑 Quick Navigation

SQL Injection (SQLi): Mechanics & Attack Variants
How Prepared Statements Prevent SQLi (Engine & Protocol Level)
ORM Security Pitfalls (When ORMs Fail to Protect You)
B+ Tree Deep Dive: Internal Architecture & Range Scans
Write-Ahead Logging (WAL) & Crash Recovery (ARIES)
Composite Indexes & The Leftmost Prefix Rule

1. SQL Injection (SQLi): Mechanics & Attack Variants

What is SQL Injection?

SQL Injection occurs when untrusted user input is directly concatenated into a dynamic SQL query string, allowing an attacker to manipulate the structure of the query's Abstract Syntax Tree (AST) executed by the database engine.

The Classic Classic Exploit (Authentication Bypass)

Consider a vulnerable login authentication query:

# VULNERABLE CODE (String Concatenation)
query = f"SELECT * FROM users WHERE username = '{user_input}' AND password = '{password_input}'"

If an attacker enters:

user_input = admin' --
password_input = anything

The database executes:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

The ' character closes the string literal.
-- (or # in MySQL) comments out the remainder of the query!
The database returns the admin user record without checking the password, granting full administrative access.

Another classic payload: ' OR '1'='1:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = ''

Evaluates to TRUE for every row in the table, dumping the entire user database.

The 4 Major Classes of SQLi

┌─────────────────────────────────────────────────────────────────────────────┐
│                          THE 4 MAJOR CLASSES OF SQLi                        │
└─────────────────────────────────────────────────────────────────────────────┘

 1. IN-BAND (Classic)             2. INFERENTIAL (Blind)             3. OUT-OF-BAND (OOB)
 ────────────────────             ──────────────────────             ────────────────────
 Attacker uses same channel       No data returned on screen.        Attacker forces DB to
 to launch attack and gather      Attacker infers data character-    make external DNS or HTTP
 results directly on web page     by-character via TRUE/FALSE        requests to attacker server
 (UNION-based, Error-based).      behavior or SLEEP(5) time delays.  (e.g., xp_dirtree, UTL_HTTP).

UNION-Based SQLi:
- Appends results from another table using the UNION operator:
  SELECT id, name FROM products WHERE category = 'tech' UNION SELECT id, card_number FROM credit_cards--'
Error-Based SQLi:
- Intentionally triggers runtime database errors (e.g., divide by zero, type conversion) that leak database versions, table names, or credentials in error responses.
Blind Boolean-Based SQLi:
- Asks the database true/false questions:
  SELECT * FROM users WHERE id = 1 AND (SUBSTRING((SELECT password FROM users WHERE id=1), 1, 1) = 'a')
- If the page renders normally, the first character is 'a'; if it returns 404, it is not.
Blind Time-Based SQLi:
- Injects a delay function: SELECT * FROM users WHERE id = 1; IF (1=1) WAITFOR DELAY '0:0:5'--.
- If the HTTP response takes 5 seconds longer to return, the injected condition is true.

2. What is a Prepared Statement & How It Prevents SQLi

What is a Prepared Statement? (The Simple Explanation)

In regular (vulnerable) code, we build SQL queries by gluing strings together:

# VULNERABLE: Code and user data are mixed together in one messy string
query = "SELECT * FROM users WHERE username = '" + user_input + "'"

Here, the database cannot tell where your SQL commands end and where the user's input begins. If the user types SQL commands into user_input, the database mistakenly executes them!

The Real-World Analogy: A Pre-Printed Government Form

Think of a Prepared Statement like a pre-printed government application form with blank boxes:

 ┌─────────────────────────────────────────────────────────────┐
 │ OFFICIAL APPLICATION FORM                                   │
 │ Name:   [ _______________________________________________ ] │ <── Blank Box (?)
 │ Age:    [ _______________________________________________ ] │ <── Blank Box (?)
 └─────────────────────────────────────────────────────────────┘

The Form (Query Template) is printed in permanent ink:
"SELECT * FROM users WHERE username = ? AND age = ?"
The User's Data (Parameters) goes strictly inside the blank box:
If someone writes "I am the King and give me all money" inside the Name box, does the government officer make them king? No! The officer looks at it and says:
"Okay, your name is literally 'I am the King and give me all money'."
The Key Insight:
Because the form's structure is fixed beforehand, whatever you write inside the box is treated 100% as raw text/data, never as official instructions.

What It Looks Like in Real Code

Instead of sticking strings together, you do it in two clear steps:

// STEP 1: Send the blueprint with placeholders '?' (The DB compiles the query structure)
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(sql);

// STEP 2: Fill in the blanks with actual data values
stmt.setString(1, user_input);       // e.g. "admin' OR '1'='1"
stmt.setString(2, password_input);

// STEP 3: Execute safely!
ResultSet rs = stmt.executeQuery();

Even if user_input is "admin' OR '1'='1", the database looks for a user whose username literally equals the exact text "admin' OR '1'='1". The ' is NOT treated as a SQL quotation mark, and the OR is NOT treated as a logical operator!

Why Use Prepared Statements? (The 2 Big Interview Reasons)

100% Immunity to SQL Injection: It is physically impossible for user input to change the query structure.
Performance (Query Plan Caching): If you run this query 1,000 times for different users, the database compiles the query only once and simply swaps in the new values, saving massive CPU time!

Deep Dive: What Happens Inside the Engine (The Two-Phase Protocol)

PHASE 1: PREPARATION (Compiling the AST)
========================================
Application ───[ COM_STMT_PREPARE: "SELECT * FROM users WHERE username = ? AND password = ?" ]───► Database Engine
                                                                                                        │
                                                                                               [ 1. Lexer & Parser ]
                                                                                                        │
                                                                                               [ 2. Generate Fixed AST ]
                                                                                                        │
                                                                                               [ 3. Compile Execution Plan ]
Application ◄───[ Returns Statement ID: 1042 (Query Structure is now IMMUTABLE) ]───────────────────────┘


PHASE 2: EXECUTION (Binding Literal Data)
========================================
Application ───[ COM_STMT_EXECUTE: Stmt_ID: 1042, Param 1: "admin' OR '1'='1", Param 2: "pass" ]──► Database Engine
                                                                                                        │
                                                                                               [ Injects pure binary ]
                                                                                               [ literals directly   ]
                                                                                               [ into leaf nodes of  ]
                                                                                               [ the pre-built AST   ]
                                                                                                        ▼
                                                                                               [ Executes Fixed Plan ]

1. Phase 1: `PREPARE` Statement

The application sends the SQL query template with placeholders (? or $1) over the database socket via the binary wire protocol (e.g., MySQL COM_STMT_PREPARE or PostgreSQL Parse message).
The Database Parser parses the grammar, validates table/column names, constructs the Abstract Syntax Tree (AST), and compiles an optimal physical query execution plan.
The compiled AST is locked in the database cache and assigned a Statement Handle / ID.
Crucial Invariant: The grammatical structure of the query is now 100% frozen and immutable.

2. Phase 2: `EXECUTE` (Parameter Binding)

When executing the query, the application sends only the Statement ID and the raw parameter values over a separate binary wire message (COM_STMT_EXECUTE / PostgreSQL Bind & Execute).
Parameters are transmitted in their raw native binary representations (e.g., raw integer bytes, raw UTF-8 string buffers), completely bypassing the SQL parser.
If the attacker passes admin' OR '1'='1:
- The database does not re-parse the input.
- It treats the entire string admin' OR '1'='1 as a single literal value to compare against the username column.
- The query looks for a user whose actual username is literally the string "admin' OR '1'='1".
- Finding none, it safely returns zero rows!

Emulated vs Server-Side Prepared Statements

Emulated Prepared Statements (Client-Side): Some legacy drivers (e.g., PDO in PHP with default settings) simulate prepared statements by escaping characters locally using regex and sending a regular SQL string. This can be bypassed by multibyte encoding attacks (e.g., GBK charset 0xbf27).
Real Server-Side Prepared Statements: True binary protocol separation handled by the database server. Always verify ATTR_EMULATE_PREPARES = false in production drivers.

3. ORM Security Pitfalls (When ORMs Fail to Protect You)

Object-Relational Mapping (ORM) tools (Hibernate, Prisma, TypeORM, SQLAlchemy, Sequelize) use parameterized queries by default for standard CRUD operations. However, developers frequently introduce SQLi vulnerabilities when using Raw SQL escapes:

Pitfall 1: Raw SQL Fragments with Template Literals

// VULNERABLE TYPEORM / SEQUELIZE CODE
const users = await userRepository
  .createQueryBuilder("user")
  .where(`user.name = '${req.body.name}'`) // DANGEROUS: String interpolation defeats ORM!
  .getMany();

// SECURE FIX (Parameterized)
const users = await userRepository
  .createQueryBuilder("user")
  .where("user.name = :name", { name: req.body.name }) // SAFE: Bind parameter
  .getMany();

Pitfall 2: Dynamic Column Names or `ORDER BY`

Placeholders (?) can only be used for values (literals), never for table names, column names, or sort directions (ASC/DESC):

-- INVALID SQL (Databases reject bind parameters for schema identifiers):
SELECT * FROM products ORDER BY ? ?;

When developers concatenate user input for column sorting:

# VULNERABLE:
order_by = request.GET.get('sort') # Attacker passes "(CASE WHEN (1=1) THEN id ELSE price END)"
cursor.execute(f"SELECT * FROM products ORDER BY {order_by}")

Defense: Use an explicit Allowlist / Whitelist in application code:
ALLOWED_SORT_COLUMNS = {"price", "created_at", "popularity"} if sort_col not in ALLOWED_SORT_COLUMNS: raise ValueError("Invalid sort column")

4. B+ Tree Deep Dive: Internal Architecture & Range Scans

Relational databases (MySQL InnoDB, PostgreSQL, Oracle) use B+ Trees as their primary indexing structure for disk storage (Clustered & Secondary indexes).

                              ┌───────────────┐
                              │    [ 50 ]     │  Root Node (Internal)
                              └───┬───────┬───┘
                     ┌────────────┘       └────────────┐
                     ▼                                 ▼
             ┌───────────────┐                 ┌───────────────┐
             │  [ 20 | 35 ]  │                 │  [ 65 | 80 ]  │  Internal Nodes (Keys only)
             └──┬────┬────┬──┘                 └──┬────┬────┬──┘
         ┌──────┘    │    └──────┐         ┌──────┘    │    └──────┐
         ▼           ▼           ▼         ▼           ▼           ▼
      ┌─────┐     ┌─────┐     ┌─────┐   ┌─────┐     ┌─────┐     ┌─────┐
      │10,15│<===>│20,28│<===>│35,42│<=>│50,60│<===>│65,75│<===>│80,95│ Leaf Nodes (Data/Pointers)
      └─────┘     └─────┘     └─────┘   └─────┘     └─────┘     └─────┘
      └───────────────────────── Doubly Linked List ──────────────────┘

Why B+ Trees Over B-Trees for Databases?

Higher Fanout & Shallower Tree:
- In a B-Tree, internal nodes store both keys AND data pointers/records.
- In a B+ Tree, internal nodes store only keys and child pointers.
- Result: A single 16KB database page can hold hundreds of keys ($Fanout \approx 1000$).
- A tree of height 3 can index $1000^3 = \mathbf{1\text{ Billion rows}}$ in just 3 disk I/O lookups!
Blazing Fast Range Scans:
- In a B+ Tree, all leaf nodes form a doubly linked list.
- To execute WHERE age BETWEEN 20 AND 50:
  1. Traverse the tree once ($O(\log N)$) to find the leaf containing key 20.
  2. Sequentially scan forward through the leaf node linked list until reaching 50.
  3. Zero tree re-traversals needed! (A standard B-tree requires expensive in-order tree traversal disk jumps).

5. Write-Ahead Logging (WAL) & Crash Recovery (ARIES)

The Problem: Random Disk Writes are Slow

When an UPDATE modifies a row in a table:

Writing that change directly to the table datafile on disk requires random disk I/O (slow, high seek latency).
If the server crashes mid-write, data pages become corrupted (torn writes).

The Solution: Write-Ahead Logging (WAL)

Fundamental Invariant: No data page can be written to disk until the corresponding log record describing the change has been flushed to persistent storage.

[ UPDATE accounts SET balance = 400 WHERE id = 1 ]
                      │
                      ▼
 1. Modify page in Buffer Pool (RAM) ──► Marks page as "DIRTY"
                      │
                      ▼
 2. Append change record to WAL Buffer (RAM)
                      │
                      ▼
 3. Commit Transaction ──► FLUSH WAL Buffer to disk (SEQUENTIAL I/O - Super Fast!)
                      │
                      ▼
 4. Return SUCCESS to client!
                      │
   (Hours or minutes later: Checkpoint flushes dirty RAM pages to disk in background)

The ARIES Recovery Protocol (Algorithm for Recovery and Isolation Exploiting Semantics)

If the database crashes or loses power, upon restarting it executes 3 passes:

Analysis Pass: Scans WAL forward from the last checkpoint to identify all dirty pages in memory and active in-flight transactions at the crash moment.
Redo Pass (Repeating History): Replays all changes forward from the oldest dirty page LSN (Log Sequence Number) to restore the database to the exact state it was in right before the crash.
Undo Pass: Scans backward and rolls back all transactions that were active and uncommitted at the moment of the crash, restoring atomicity.

6. Composite Indexes & The Leftmost Prefix Rule

A Composite Index is an index on multiple columns, e.g., CREATE INDEX idx_user ON users(country, state, city);.

The B+ Tree Internal Sort Order

The database orders keys hierarchically: First by country. For rows with the same country, sorted by state. For rows with the same state, sorted by city.

The Leftmost Prefix Rule:

A composite index can only satisfy queries that search on a contiguous prefix of the index columns, starting from the leftmost column:

Query Pattern	Index Usable?	Explanation
`WHERE country = 'IN'`	YES	Matches leftmost prefix column 1.
`WHERE country = 'IN' AND state = 'KA'`	YES	Matches leftmost prefix columns 1 & 2.
`WHERE country = 'IN' AND state = 'KA' AND city = 'BLR'`	YES (Full Index)	Matches all 3 columns.
`WHERE state = 'KA'`	NO (Full Table Scan)	Skips leftmost column (`country`). Keys are not globally sorted by `state`.
`WHERE city = 'BLR'`	NO (Full Table Scan)	Skips leftmost columns.
`WHERE country = 'IN' AND city = 'BLR'`	PARTIAL	Uses index for `country = 'IN'`, then manually filters rows for `city`.

🎯 Summary: Spoken Interview Checklist for Juspay

Prepared Statements: State that they work via a 2-phase binary protocol separating AST compilation from literal value binding—not via string replacement or escaping.
SQLi Types: Mention Union-based, Error-based, Blind Boolean, and Blind Time-based (SLEEP(5)).
B+ Tree: Emphasize high fanout (16KB pages holding keys only in internal nodes) and leaf-node doubly linked lists for fast range scans.
WAL / ARIES: Explain how sequential append-only logging ensures ACID Durability before lazy background flushing of dirty pages.

09-sql-injection-prevention-and-database-security.md