Case Study 1: Reviewing and Remediating Meridian's Online-Banking Portal

"The portal passed its last pen test because nobody asked it the right questions. This time we're asking all of them." — Sam Whitfield, Security Engineer, Meridian Regional Bank (constructed)

Executive Summary

Meridian's customer-facing online-banking portal is the most exposed, highest-stakes web application the bank owns: it sits on the public internet, authenticates 2.5 million customers, and moves money. After Chapter 12 established a secure-SDLC policy, CISO Dana Okafor commissioned a focused web-application security review of the portal and its back-office admin console, to be remediated before the next PCI-DSS assessment. This case study follows Sam Whitfield (engineering) and the SOC's Theo Brandt (detection) as they review the application against the attack classes of this chapter, fix what they find, and stand up the WAF and logging that catch what review misses. You will see the chapter's attack→fix→detect cycle applied end to end on a realistic codebase. The scenario, code, logs, and findings are constructed for teaching (Tier 3); all hosts use meridianbank.example and documentation IP ranges.

Skills applied: secure-code review for injection, XSS, CSRF, SSRF, and session flaws; writing the structural fixes (parameterization, output encoding, CSP, anti-CSRF tokens, session rotation); deploying a WAF as defense in depth; building log-based detections; mapping web controls to PCI-DSS; risk-ranking and sequencing remediation.

Background

The portal is a Python web application (server-rendered templates plus a JavaScript front end for the dashboard) backed by a relational database, fronted by a load balancer, and integrated with the bank's core-banking system for balances and transfers. A separate back-office admin console lets support staff look up customers and review flagged transfers; critically, it renders some customer-supplied data (display names, transfer memos) that originates from the customer-facing side. The portal had passed a light penetration test a year earlier, but that test was time-boxed and unauthenticated; Dana's instinct — Theme 5, compliance is the floor — was that "passed a pen test" was not the same as "reviewed thoroughly."

Sam frames the review with the chapter's three-layer model so nothing falls through the cracks:

   Meridian portal — the three defense layers under review

   EDGE      [ WAF ]  ──────────────►  blocks noisy attacks, virtual-patch, telemetry to SIEM
                │
   BROWSER   [ CSP, SameSite/HttpOnly/Secure cookies, security headers ]  ──► limits damage if code is wrong
                │
   CODE      [ parameterized queries, output encoding, CSRF tokens,       ──► the ONLY place bugs are fixed
               session rotation, SSRF guards ]
                │
            [ core-banking system: balances, transfers ]

🔗 Connection: The portal's HTTP security headers (CSP, HSTS, X-Content-Type-Options) were set as a baseline in Chapter 9's web-hardening standard. This review takes that baseline and makes the CSP strict and the per-endpoint controls concrete — the difference between "headers are on" and "the headers actually stop the attacks."

The Review

Phase 1 — Injection: the admin customer-lookup

Sam starts where the data is most sensitive: the admin console's customer lookup. He finds the query built by string concatenation — the §13.2 bug, in production, behind authentication but reachable by any support account (and therefore by anyone who phishes one).

# FOUND in admin_console/customers.py  — VULNERABLE
def find_customer(conn, username):
    query = "SELECT id, name, email FROM customers WHERE username = '" + username + "'"
    return conn.execute(query).fetchall()

He confirms the root cause out loud, the way the chapter taught: the username is concatenated into the command, so a value containing SQL syntax could redraw the statement's structure. He does not write an extraction payload — the finding is the concatenation itself; the proof is the pattern, not an exploit. The fix is structural:

# FIXED — parameterized query
def find_customer(conn, username):
    query = "SELECT id, name, email FROM customers WHERE username = ?"
    return conn.execute(query, (username,)).fetchall()

A grep-style sweep of the codebase for query construction (execute( with + or f-strings) turns up four more concatenated queries, including one on a customer-facing transaction-search endpoint that accepts a date range — the most dangerous of the set because it is reachable without any account. All five are converted to parameter binding. One endpoint lets users sort by a column name, which cannot be parameterized; Sam replaces the raw column interpolation with an allowlist:

# Sorting by a user-chosen column — allowlist, since identifiers can't be parameters.
ALLOWED_SORT = {"date", "amount", "description"}     # exact, known-good column names
def sort_clause(user_sort):
    if user_sort not in ALLOWED_SORT:
        raise ValueError("invalid sort field")        # fail closed
    return f"ORDER BY {user_sort}"                     # safe: value is from a fixed set

⚠️ Common Pitfall: Sam notes that the team's ORM was already in use most places — and had quietly lulled everyone into thinking injection was handled. But two of the five bugs were in raw-SQL "escape hatches" where a developer dropped out of the ORM for a complex query. The lesson for the standard: "we use an ORM" is not a control; verified parameter binding on every query, including raw-SQL exceptions is the control.

Phase 2 — XSS: the memo that reaches the admin console

Next Sam traces customer-supplied strings to where they are rendered. The transaction-search and dashboard templates use the project's auto-escaping engine — good, that is the §13.3 secure default. But the admin console renders the transfer memo field with an explicit "raw" filter that disables escaping, "to allow formatting." That is stored XSS: a customer controls the memo, and it executes in a staff member's browser when they review the transfer.

# FOUND in admin_console/templates/review.html (conceptually)
#   <td>{{ transfer.memo | raw }}</td>      # raw == no escaping == stored XSS into staff browser

# FIXED: remove the raw filter; let the auto-escaping engine encode it.
#   <td>{{ transfer.memo }}</td>            # memo now rendered as inert text

This is the most serious XSS finding precisely because of whose browser runs the script: a back-office operator has more privilege than a customer, so a customer-to-staff XSS is a privilege-escalation vector. Sam also identifies a reflected-XSS risk on a "your search: [term]" heading that, on one error path, bypassed the template and built HTML by concatenation — the same bug as the admin memo, reached a different way. Both get the same fix: encode on output, no exceptions.

For defense in depth (Theme 4 — assume some endpoint will be wrong again), Sam tightens the CSP from the Chapter 9 baseline into a strict policy and applies it to both the customer portal and the admin console:

Content-Security-Policy:
  default-src 'self';
  script-src 'self';                 # no 'unsafe-inline' -> injected inline <script> is blocked
  style-src 'self';
  object-src 'none';
  base-uri 'self';
  frame-ancestors 'self';            # clickjacking defense
  report-uri /csp-report             # violations POSTed to the SIEM via this endpoint

Enforcing script-src 'self' forces the team to move a handful of inline event handlers and scripts into served .js files — a week of unglamorous work that Sam insists on, because a CSP with 'unsafe-inline' would have left the very XSS he just fixed exploitable if it recurred.

🛡️ Defender's Lens: Theo points out the payoff for the SOC: the report-uri directive means the admin console itself will now phone home whenever the browser blocks a script — giving detection coverage on DOM-based and injected XSS that server logs alone could miss. The fix (encoding) and the telemetry (CSP reports) were designed together.

Phase 3 — CSRF and session hygiene

Sam reviews the state-changing endpoints — /transfer, /account/email, /add-payee. The framework's anti-CSRF token protection was enabled globally except on /add-payee, where a previous developer had disabled it to debug a mobile-client issue and never re-enabled it. Adding a payee is the precursor to a fraudulent transfer, so this is a real CSRF hole on a sensitive action. The fix is to re-enable token validation and to add an automated test asserting that every state-changing route rejects a request with a missing/invalid token.

He then runs the session checklist from §13.5:

The two structural fixes — rotate the session ID at login and invalidate sessions server-side on logout/password-change — close the session-fixation hole and the stolen-token-replay hole respectively. Combined with the existing HttpOnly/Secure and the new SameSite, the session is now hardened on all the axes the chapter named.

Phase 4 — SSRF: the new "logo from a URL" feature

The portal's product team is shipping a small-business feature: a business customer can brand their sub- portal by supplying a logo image URL. Sam catches it in review before launch — a textbook SSRF trap, because the server fetches a URL the customer chose. He requires the layered guard from §13.4:

# SSRF guard for the logo-fetch feature (application layer; paired with network egress controls).
import ipaddress, socket

ALLOWED_IMAGE_HOSTS = {"cdn.assets.example", "images.partner.example"}  # exact allowlist

def safe_logo_url(hostname):
    if hostname not in ALLOWED_IMAGE_HOSTS:                 # 1) allowlist first
        return False
    ip = ipaddress.ip_address(socket.gethostbyname(hostname))  # 2) resolve, then check
    if ip.is_private or ip.is_loopback or ip.is_link_local:    # blocks 169.254.*, 127.*, 10.*, ...
        return False
    return True
# plus: re-check after every redirect; defend against DNS rebinding.

Because the chapter is honest that application-layer SSRF defense is hard, Sam files a paired requirement with the cloud team (anticipating Chapter 15): the portal's application servers must be egress-filtered so they cannot reach the cloud metadata endpoint or arbitrary internal hosts even if the app guard is bypassed, and the hardened, token-required metadata service version must be enforced. Two layers, on the assumption either could fail.

Phase 5 — The WAF and the detections

With the code fixes underway, Theo stands up the third layer and the telemetry. A WAF is deployed in front of the portal using a managed core rule set. Per the §13.6 stance, high-confidence rules block; the rest alert to the SIEM; and the SOC actually reads them. Theo builds the standing detections:

-- Detection 1: web-attack probing rollup by source (15-min window).
SELECT src_ip,
       SUM(request_uri RLIKE '(?i)(union\\s+select|or\\s+1=1|--|/\\*)')           AS sqli_hits,
       SUM(request_uri RLIKE '(?i)(<script|onerror=|javascript:|<svg|%3cscript)') AS xss_hits,
       SUM(CASE WHEN status_code = 500 THEN 1 ELSE 0 END)                          AS errors
FROM web_access_logs
WHERE event_time > NOW() - INTERVAL '15' MINUTE
GROUP BY src_ip
HAVING sqli_hits > 0 OR xss_hits > 0 OR errors > 25;

-- Detection 2: CSRF — spikes of rejected tokens on state-changing routes.
SELECT request_uri, COUNT(*) AS rejected
FROM app_logs WHERE reason = 'csrf_token_invalid'
  AND event_time > NOW() - INTERVAL '1' HOUR
GROUP BY request_uri HAVING rejected > 20;

-- Detection 3: SSRF — user-influenced fetch resolving to an internal/metadata address.
SELECT * FROM app_logs
WHERE action = 'outbound_fetch'
  AND (resolved_ip RLIKE '^(10\\.|127\\.|169\\.254\\.|192\\.168\\.|172\\.(1[6-9]|2[0-9]|3[01])\\.)');

Within the first week of alert-only WAF operation, Detection 1 fires: a single source in 198.51.100.0/24 sends a burst of union select and or 1=1 patterns against the (now-parameterized) transaction-search endpoint, with a handful of 500s mixed in. This is the moment the whole approach pays off:

• The fix already landed, so the probing finds a parameterized query and gets nothing — the attacker is poking at a wall.
• The WAF blocked the high-confidence patterns at the edge (virtual patch value), and
• the detection alerted the SOC that someone is specifically targeting this endpoint, which tells Theo to prioritize a deeper review and a penetration test of nearby endpoints.

Fix, block, and know — the three layers doing exactly their jobs, in concert.

📟 War Story (constructed): Theo contrasts this with what almost happened at a peer institution (per industry reporting, generalized): a WAF in alert-only mode logged SQL-injection matches against a reporting endpoint for weeks while the underlying query stayed concatenated — and nobody read the alerts. Meridian's standard now mandates that WAF alerts feed a SOC dashboard with an ownership and a review SLA, because a control you don't monitor is a control you don't have.

Outcome and PCI-DSS mapping

The review closed 11 findings across the five classes. Each maps to a PCI-DSS expectation for systems in or adjacent to the cardholder data environment, which is how Elena (GRC) reports it to the assessor:

Dana's takeaway for the board is the chapter's thesis in one line: the portal was not made safe by the WAF; it was made safe by fixing the code, and the WAF and the logging are how we stay safe while imperfect humans keep writing it.

Discussion Questions

1. Two of the five injection bugs were in raw-SQL "escape hatches" inside an ORM-based codebase. What does this say about relying on a framework as a control, and how would you word a standard so the escape hatches are caught?
2. Sam spent a week moving inline scripts into files solely to enable script-src 'self'. Was that worth it, given the XSS was already fixed by encoding? Argue both sides using Theme 4 (assume each layer fails).
3. The stored-XSS memo executed in a staff browser. Why does that make it more dangerous than the same bug on a customer-only page, and how should that change its risk ranking?
4. The SSRF feature was caught before launch in code review. Estimate the cost difference between fixing it then versus after a breach, and connect this to "shift left" (a Chapter 12 idea, built on in Chapter 31).
5. Detection 1 fired after the code was already fixed. Some would say the alert is now "noise" since nothing is at risk. Why is that wrong — what does the alert still tell the defenders?

Your Turn

Take a small web application you own or are authorized to review (or the deliberately-vulnerable training app your instructor provides). Apply this case study's five-phase review: (1) find every place a query is built and confirm parameterization; (2) trace every place untrusted data is rendered and confirm encoding; (3) verify anti-CSRF tokens on all state-changing routes and run the session-cookie checklist; (4) find any feature where the server fetches a user-supplied URL and add an SSRF guard; (5) write one log detection for injection probing. Produce a one-page findings table with, for each finding, the root cause, the structural fix, and how you would detect attempts. Never test against systems you do not control.

Key Takeaways

• A real web review proceeds attack class by attack class, and for each does the chapter's cycle: find the root cause → apply the structural fix → stand up detection.
• Frameworks lull teams into false safety. An ORM and auto-escaping templates are great defaults, but raw-SQL escape hatches and raw/unescaped render filters reintroduce the exact bugs — so the control is "verified on every path," not "we use a framework."
• Whose browser runs the script matters. Customer-supplied data rendered unescaped in a staff console is privilege-escalating stored XSS and should be ranked accordingly.
• The three layers compose: the code fix makes you safe, the strict CSP and SameSite cookies limit damage if the code is later wrong, and the WAF plus detections block the noisy majority and tell you who is targeting you.
• A control you don't monitor is a control you don't have — WAF alerts need an owner and an SLA, or they become the next war story.
• Web findings map cleanly to PCI-DSS Req. 6, turning an engineering review into audit evidence — but the security came from the fixes, not the compliance checkbox (Theme 5).