Server-side programming explained: HSC Software Engineering Module 2

from flask import Flask, request, jsonify, abort
import sqlite3

app = Flask(__name__)

def db():
    conn = sqlite3.connect("notes.db")
    conn.row_factory = sqlite3.Row
    return conn

def current_user(headers):
    token = headers.get("Authorization", "").replace("Bearer ", "")
    return verify_token(token)

@app.post("/api/notes")
def create_note():
    user_id = current_user(request.headers)
    if not user_id:
        abort(401)
    data = request.get_json(silent=True) or {}
    title = (data.get("title") or "").strip()
    body = (data.get("body") or "").strip()
    if not (1 <= len(title) <= 200) or not (1 <= len(body) <= 5000):
        abort(400)
    with db() as conn:
        cur = conn.execute(
            "INSERT INTO notes (user_id, title, body) VALUES (?, ?, ?)",
            (user_id, title, body),
        )
        return jsonify(id=cur.lastrowid), 201

Security controls (any two):

1. Authentication via Authorization header; the endpoint aborts with 401 if the token is missing or invalid.
2. Input validation of title and body lengths; rejects empty or oversized input with 400.
3. Parameterised query for the INSERT prevents SQL injection.
4. Per-user isolation: each note is stored against user_id, so subsequent GET endpoints can scope to the owner.

Markers reward a real route definition, a request body parse, input validation, a parameterised database call, a correct status code (201 for creation), and at least two named security controls.

(a) 404 Not Found. (b) 200 OK (the login succeeded and a token/body is returned, but no new resource was created, so 201 is not appropriate here).

Marking criteria: 1 mark for each correct status code with a brief justification.

@app.get("/api/health")
def health():
    return jsonify(status="ok"), 200

Marking criteria: 1 mark for the correct route decorator and path, 1 mark for returning the correct JSON body, 1 mark for the explicit 200 status code.

As the diagram shows, authentication sits before the database layer in the request pipeline. A request that fails authentication (no valid token, or an invalid one) should be rejected immediately with a 401 Unauthorized response, and the handler should stop processing right there, never continuing on to validation or the database query.

This matters because every later stage assumes a known, verified user identity: validation rules and, critically, object-level authorisation checks (such as WHERE user_id = ?) rely on user_id being trustworthy. If an unauthenticated request were allowed to reach the database layer, an attacker could potentially read or modify data belonging to any user, since there would be no verified identity to scope the query against.

Marking criteria: 1 mark for stating the correct response (401, request halted), 1 mark for correctly placing authentication before the database layer using the diagram, 1 mark for explaining that later stages depend on a trustworthy user identity, 1 mark for linking this to preventing unauthorised data access.

This handler is vulnerable to SQL injection because the id path parameter is interpolated directly into the SQL string using an f-string. A request such as /api/notes/1 OR 1=1 (or a more malicious payload using UNION SELECT) changes the structure of the query, potentially returning every row in the table or data from other tables entirely.

Safe rewrite:

@app.get("/api/notes/<int:id>")
@require_login
def get_note(id):
    with sqlite3.connect("notes.db") as conn:
        row = conn.execute(
            "SELECT id, title, body FROM notes WHERE id = ? AND user_id = ?",
            (id, request.user_id),
        ).fetchone()
    if not row:
        abort(404)
    return jsonify(id=row[0], title=row[1], body=row[2])

The ? placeholder passes id as a bound parameter rather than as literal SQL text, so it can never change the query's structure. The added user_id = ? also fixes the missing object-level authorisation, and explicit column selection avoids leaking unintended fields.

Marking criteria: 1 mark for correctly naming SQL injection, 1 mark for explaining how the f-string enables it, 1 mark for a correct parameterised rewrite, 1 mark for also addressing object-level authorisation and column exposure.

Of the 500 requests: 20 fail validation, so 20 responses should be 400 Bad Request. 15 fail authentication, so 15 responses should be 401 Unauthorized. 5 cause an unhandled server exception, so 5 responses should be 500 Internal Server Error.

The remaining $500 - 20 - 15 - 5 = 460$ requests succeed and receive a 2xx response (200 or 201 depending on the operation).

The body of the 500 responses must never contain the raw stack trace, internal file paths, database connection strings, or any other implementation detail; the server should log these details internally for the developers and return only a generic error message (e.g. "Internal server error") to the client.

Marking criteria: 1 mark for the correct count of 400 responses (20), 1 mark for 401 responses (15), 1 mark for 500 responses (5), 1 mark for correctly computing the successful count (460), 1 mark for correctly stating that stack traces/internal details must not be exposed in the 500 body.

Issues to evaluate.

1. No authentication or authorisation. Anyone can call this endpoint and move money out of any account into any other account; there is no check that the caller owns the from account.
2. SQL injection. Both amount, from and to are interpolated directly into SQL strings via f-strings, letting an attacker inject arbitrary SQL.
3. No input validation. There is no check that amount is a positive number, or that from and to exist, or that from has sufficient balance.
4. Not atomic. The two UPDATE statements are not wrapped in a single transaction; if the process crashes between them, money can be deducted from one account without being credited to the other.
5. Unhelpful response. Returning the bare string "ok" gives no status code and no way for the client to confirm what happened.

Rewritten endpoint.

@app.post("/api/transfer")
@require_login
def transfer():
    data = request.get_json(silent=True) or {}
    amount = data.get("amount")
    to_id = data.get("to")
    if not isinstance(amount, (int, float)) or amount <= 0:
        abort(400)
    if not isinstance(to_id, int):
        abort(400)
    from_id = request.user_id
    with db() as conn:
        row = conn.execute(
            "SELECT balance FROM accounts WHERE id = ?", (from_id,)
        ).fetchone()
        if not row or row[0] < amount:
            abort(400)
        conn.execute("BEGIN")
        conn.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (amount, from_id),
        )
        conn.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (amount, to_id),
        )
        conn.execute("COMMIT")
    return jsonify(status="transferred"), 200

Why each change fixes an issue. @require_login and using request.user_id for from_id stop an attacker moving funds out of an account they do not own. Parameterised ? placeholders remove the SQL injection vector entirely. Explicit type and value checks on amount and to reject malformed or negative transfers before touching the database. Wrapping both UPDATE statements in a single BEGIN/COMMIT transaction makes the transfer atomic, so a crash between the two statements leaves the database unchanged rather than half-applied. Returning a JSON body with an explicit 200 status gives the client a machine-readable confirmation.

Marking criteria: 1 mark for identifying the missing authentication/authorisation, 1 mark for identifying SQL injection, 1 mark for identifying missing input validation, 1 mark for identifying the non-atomic transaction risk, 1 mark for a correct parameterised and validated rewrite, 1 mark for correctly wrapping the two updates in a transaction, 1 mark for a correct, informative response.