Что такое SQL-инъекция?

┌──────────────────────────────────────────────────────────────────────────────┐
│                    THE BANK TRANSFER TRANSACTION                             │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  BEGIN;                                                                      │
│                                                                              │
│  ┌─────────────────┐          ┌─────────────────┐                           │
│  │ Alice: $500     │          │ Bob:   $300     │                           │
│  │                 │          │                 │                           │
│  │  [- $100]       │────┐     │                 │                           │
│  │  $400           │    │     │  [+ $100]       │                           │
│  │                 │◄───┘     │  $400           │                           │
│  └─────────────────┘          └─────────────────┘                           │
│                                                                              │
│  ↓ If everything succeeds:          ↓ If anything fails:                     │
│  COMMIT;                            ROLLBACK;                                │
│  (Permanent)                        (Undo everything)                        │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│                         ACID PROPERTIES                                      │
├──────────────┬───────────────────────────────────────────────────────────────┤
│ ATOMICITY    │ All operations complete, or none do.                        │
│              │ "All or nothing." No partial commits.                       │
│              │ Analogy: Wedding vows — both say "I do" or neither does.    │
├──────────────┼───────────────────────────────────────────────────────────────┤
│ CONSISTENCY  │ Database moves from one valid state to another.             │
│              │ Constraints, triggers, and cascades must all succeed.       │
│              │ Analogy: A legal contract — every clause must be valid.     │
├──────────────┼───────────────────────────────────────────────────────────────┤
│ ISOLATION    │ Concurrent transactions don't interfere with each other.    │
│              │ Each transaction sees a consistent snapshot.                │
│              │ Analogy: Private changing rooms — you don't see others.     │
├──────────────┼───────────────────────────────────────────────────────────────┤
│ DURABILITY   │ Once committed, data survives power loss/crashes.           │
│              │ Written to disk / WAL (Write-Ahead Log).                    │
│              │ Analogy: Etching in stone — permanent once done.            │
└──────────────┴───────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐
│               THE THREE READ PHENOMENA (Bad Things That Happen)              │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  1. DIRTY READ                    2. NON-REPEATABLE READ                     │
│                                                                              │
│  T1 reads uncommitted data.      T1 reads a row. T2 updates it. T1          │
│  T1: SELECT → 100                reads again → different value!              │
│  T2: UPDATE → 200 (not committed)                                            │
│  T1: SELECT → 200  ← DIRTY!                                                  │
│                                                                              │
│  3. PHANTOM READ                                                             │
│  T1: SELECT WHERE age > 30 → 5 rows                                          │
│  T2: INSERT new person age 35 (committed)                                    │
│  T1: SELECT WHERE age > 30 → 6 rows  ← PHANTOM!                              │
│  The "ghost" row appeared.                                                   │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

-- Check/set isolation level (MySQL)
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- PostgreSQL
SHOW default_transaction_isolation;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

┌──────────────────────────────────────────────────────────────────────────────┐
│                         DEADLOCK ILLUSTRATION                                │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  Transaction A              Transaction B                                    │
│  ─────────────              ─────────────                                    │
│  LOCK row 1                 LOCK row 2                                       │
│       ↓                          ↓                                           │
│  WAIT for row 2 ←──── BLOCKED ─→ WAIT for row 1                              │
│       ↓                          ↓                                           │
│  💀 DEADLOCK 💀                                                              │
│                                                                              │
│  Database detects this and kills one transaction (ROLLBACK).                 │
│  Your app should catch deadlock errors and retry.                            │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

-- Explicit locking (PostgreSQL)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Optimistic locking with version column
UPDATE accounts 
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;  -- If version changed, 0 rows updated → retry