15. Security
- Understand SQL injection and the three defenses the server uses:
?parameters, an allow-list, an authorizer. - Read a validation
422and know why every field is checked before touching the database. - Understand CORS: what it is, why it exists, how to see it with curl, why the test server opens it.
- Treat tokens as secrets: where to keep them, where never to put them.
- Know what the reverse proxy adds (HTTPS, security headers) and what the rate limit protects.
Theory
Section titled “Theory”SQL injection
Section titled “SQL injection”The oldest attack, and still one of the most frequent: a value supplied by the user ends up inside the text of the query. If the server built "SELECT * FROM books WHERE title = '" + titolo + "'" and you sent x' OR 1=1 --, the query would become … WHERE title = 'x' OR 1=1 --': every row. With '; DROP TABLE books; --, worse.
The defenses, all present in the test server:
- Parameters (
?+ aparamslist): the value travels separately from the SQL text and the engine never interprets it as code. You saw it inmeta.sqlin chapter 11:WHERE price < ?withparams: [12]. - An allow-list for what cannot be a parameter — table and column names:
/queryonly acceptsbooks, authors, customers, orders, reviewsand, for each one, a closed list of fields.?sort=priceon/booksgoes through the same list. - An authorizer + a read-only connection for
/sql: even if aDELETEarrived, SQLite rejects it before running it (chapter 12).
Input validation
Section titled “Input validation”Every body goes through a model (Pydantic, on the server side) that declares types, limits and shape: price >= 0, rating between 1 and 5, a five-digit zip, non-empty items, at most 20 lines. A value outside the rules produces a 422 with the list of problems, before the database is involved. The database still has its own constraints (chapter 13): two layers are better than one.
A browser running JavaScript on https://yoursite.com cannot, by default, read the response of a request to https://corso-api-json.giuseppebosi.com: this is the same-origin policy, which prevents one site from reading another’s data using the user’s credentials (cookies). CORS (Cross-Origin Resource Sharing) is the mechanism by which a server tells the browser “this other site may read me”: it answers with an Access-Control-Allow-Origin header. For “non-simple” requests (methods other than GET/POST, custom headers such as Authorization) the browser first sends an OPTIONS preflight request and goes ahead only if the server approves it.
Important: CORS applies only to browsers. curl, Python and Node do not enforce it. It is not a protection for the server: it is a protection for the browser’s user.
The test server answers Access-Control-Allow-Origin: * because it is public, uses no cookies and wants the “Try it” widgets and your experiments from any page to work.
Secrets
Section titled “Secrets”The sandbox token is a secret (a small one, but a secret): whoever has it can write to your copy. Rules that apply to any API key:
- never in the source code or in a repository: environment variables or a
.envfile ignored by git; - never in the URL (
?token=…ends up in proxy logs and in the history): in theAuthorizationheader; - never inside the JavaScript of a public site: anyone can read it. If needed, a backend acts as an intermediary.
HTTPS, security headers, rate limiting
Section titled “HTTPS, security headers, rate limiting”HTTPS encrypts everything (URL, headers, token, body) and guarantees that you are talking to the real server. It is terminated by Caddy, the reverse proxy in front of the test server, which also adds headers such as Strict-Transport-Security (always use HTTPS), X-Content-Type-Options: nosniff (do not guess types), X-Frame-Options: DENY (no iframes). The rate limit (chapter 14) closes the circle: it limits the damage from automated abuse or brute-force attempts.
curl, explained option by option
Section titled “curl, explained option by option”An injection that does not work
Section titled “An injection that does not work”curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/query -H 'Content-Type: application/json' \-d '{"from": "books", "select": ["id", "title"], "where": {"field": "title", "op": "eq", "value": "x'"'"' OR 1=1 --"}}'- "value": "x' OR 1=1 --"
- the classic payload: closes the string, adds an always-true condition, comments out the rest
- '"'"'
- the trick to put a single quote inside a shell single-quoted string: close ('), open double ("), write the quote ('), close double ("), reopen (')
{"items":[],"meta":{"sql":"SELECT id, title FROM books WHERE title = ? LIMIT 20 OFFSET 0","params":["x' OR 1=1 --"],"count":0}}Zero results: the server looked for a book whose title is literally x' OR 1=1 --. The payload stayed a value.
And if the injection targets the field name, where parameters cannot help?
curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/query -H 'Content-Type: application/json' \ -d '{"from": "books", "where": {"field": "title = 1 OR 1", "op": "eq", "value": 1}}'{"error":{"code":"unknown_field","message":"Campo 'title = 1 OR 1' non ammesso per books","details":{"allowed":["id","title",…]}}}The name is not in the list: 400. And on /sql, even a SELECT that tries to read SQLite’s internal catalog is stopped by the authorizer: SELECT * FROM sqlite_master → access to sqlite_master.type is prohibited.
Validation: a body full of errors
Section titled “Validation: a body full of errors”curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/books \-H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \-d '{"title": "", "author_id": "uno", "category_id": 1, "price": -3}' | python3 -m json.tool{ "error": { "code": "validation_error", "message": "Richiesta non valida: controlla i campi in details", "details": [ { "loc": ["body", "title"], "msg": "String should have at least 1 character", "type": "string_too_short" }, { "loc": ["body", "author_id"], "msg": "Input should be a valid integer, unable to parse string as an integer", "type": "int_parsing" }, { "loc": ["body", "price"], "msg": "Input should be greater than or equal to 0", "type": "greater_than_equal" } ] }}Three problems, reported all together with the path (loc) of each: the client can highlight the right fields. No query was run.
CORS seen from curl
Section titled “CORS seen from curl”curl -s -i https://corso-api-json.giuseppebosi.com/api/v1/health -H 'Origin: https://esempio.com' | grep -i '^access-control\|^HTTP'- -H 'Origin: https://esempio.com'
- the header the browser adds automatically to say which site the request comes from. With curl we simulate it by hand
- | grep -i '^access-control\|^HTTP'
- shows only the status line and the CORS headers (
-iignores case;\|is OR in grep)
HTTP/1.1 200 OKaccess-control-allow-origin: *access-control-expose-headers: ETag, Location, Retry-After, X-Request-Id, X-RateLimit-Limit, X-RateLimit-Remainingallow-origin: * = any site can read. expose-headers lists the headers the browser’s JavaScript can see (by default it sees very few): without it, the “Try it” widget could not show you ETag or Location.
The preflight the browser sends before a POST with Authorization:
curl -s -i -X OPTIONS https://corso-api-json.giuseppebosi.com/api/v1/books \-H 'Origin: https://esempio.com' \-H 'Access-Control-Request-Method: POST' \-H 'Access-Control-Request-Headers: authorization,content-type' | grep -i '^access-control\|^HTTP'- -X OPTIONS
- the preflight method: "may I make this request?"
- Access-Control-Request-Method
- the method the browser would like to use afterwards
- Access-Control-Request-Headers
- the non-standard headers it would like to send
HTTP/1.1 200 OKaccess-control-allow-origin: *access-control-allow-methods: DELETE, GET, HEAD, OPTIONS, PATCH, POST, PUTaccess-control-max-age: 600access-control-allow-headers: authorization,content-typeApproved, and the browser may remember the answer for 600 seconds. If an API answers the preflight without allow-headers: authorization, your fetch from the browser fails with a CORS error even though everything works with curl: now you know where to look.
The token in the header, not in the URL
Section titled “The token in the header, not in the URL”# male: il token finisce nei log e nella cronologiacurl -s 'https://corso-api-json.giuseppebosi.com/api/v1/sandbox/me?token=sb_…'# benecurl -s https://corso-api-json.giuseppebosi.com/api/v1/sandbox/me -H "Authorization: Bearer $TOKEN"(The test server also accepts ?token= for teaching convenience: do not do it with real APIs.) And a malformed header is rejected with a clear message: -H 'Authorization: Basic abc' → 401 bad_authorization, Usa l'intestazione: Authorization: Bearer TOKEN (use the header: Authorization: Bearer TOKEN).
The reverse proxy’s security headers
Section titled “The reverse proxy’s security headers”curl -s -I https://corso-api-json.giuseppebosi.com/ | grep -iE 'strict-transport|x-content-type|x-frame|referrer'You will see strict-transport-security, x-content-type-options: nosniff, x-frame-options: DENY, referrer-policy: Caddy adds them to every response, site and API. Locally (uvicorn alone) they are not there.
Python and TypeScript
Section titled “Python and TypeScript”A client that keeps the secret out of the code, never puts it in the URL and shows the user the validation errors field by field:
# CORSO_TOKEN=sb_… python3 sicuro.py (il token arriva dall'ambiente, non dal codice)import osimport sys
import httpx
BASE = "https://corso-api-json.giuseppebosi.com/api/v1"token = os.environ.get("CORSO_TOKEN")if not token: sys.exit("imposta CORSO_TOKEN (POST /sandbox per ottenerlo)")
client = httpx.Client(base_url=BASE, headers={"Authorization": f"Bearer {token}"}, timeout=10)
def crea_libro(dati: dict) -> dict | None: r = client.post("/books", json=dati) # json= → Content-Type e serializzazione corretti if r.status_code == 422: for problema in r.json()["error"]["details"]: campo = ".".join(str(p) for p in problema["loc"][1:]) # salta "body" print(f" campo {campo}: {problema['msg']}") return None r.raise_for_status() return r.json()
print("tentativo con dati sbagliati:")crea_libro({"title": "", "author_id": "uno", "category_id": 1, "price": -3})print("tentativo corretto:")libro = crea_libro({"title": "Manuale di difesa", "author_id": 1, "category_id": 9, "price": 24.9})print(" ", libro["id"], libro["title"])
# un valore ostile resta un valorer = client.post("/query", json={"from": "books", "where": {"field": "title", "op": "like", "value": "%' OR 1=1 --"}})print("iniezione:", r.json()["meta"]["count"], "risultati")What you should see:
tentativo con dati sbagliati: campo title: String should have at least 1 character campo author_id: Input should be a valid integer, unable to parse string as an integer campo price: Input should be greater than or equal to 0tentativo corretto: 81 Manuale di difesainiezione: 0 risultati// CORSO_TOKEN=sb_… node --experimental-strip-types sicuro.ts (il token arriva dall'ambiente)const BASE = "https://corso-api-json.giuseppebosi.com/api/v1";const token = process.env.CORSO_TOKEN;if (!token) { console.error("imposta CORSO_TOKEN (POST /sandbox per ottenerlo)"); process.exit(1); }const headers = { Authorization: `Bearer ${token}`, "Content-Type": "application/json" };
type Problema = { loc: (string | number)[]; msg: string; type: string };
async function creaLibro(dati: object) { const r = await fetch(`${BASE}/books`, { method: "POST", headers, body: JSON.stringify(dati) }); if (r.status === 422) { const { error } = await r.json(); for (const p of error.details as Problema[]) console.log(` campo ${p.loc.slice(1).join(".")}: ${p.msg}`); return null; } if (!r.ok) throw new Error(`HTTP ${r.status}`); return r.json();}
console.log("tentativo con dati sbagliati:");await creaLibro({ title: "", author_id: "uno", category_id: 1, price: -3 });console.log("tentativo corretto:");const libro = await creaLibro({ title: "Manuale di difesa", author_id: 1, category_id: 9, price: 24.9 });console.log(" ", libro.id, libro.title);
// un valore ostile resta un valoreconst r = await fetch(`${BASE}/query`, { method: "POST", headers, body: JSON.stringify({ from: "books", where: { field: "title", op: "like", value: "%' OR 1=1 --" } }) });console.log("iniezione:", (await r.json()).meta.count, "risultati");tentativo con dati sbagliati: campo title: String should have at least 1 character campo author_id: Input should be a valid integer, unable to parse string as an integer campo price: Input should be greater than or equal to 0tentativo corretto: 81 Manuale di difesainiezione: 0 risultatiTry it now
Section titled “Try it now”Response headers
Response headers
Response headers
Response headers
Exercise
Section titled “Exercise”Try it online, nothing to install:
Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.
- With
/sql, trySELECT * FROM books WHERE title = 'x' OR 1=1— does it work? Why is this not an injection? - Create a customer with an invalid email (
"mario") and one with a one-lettername: read the two422s. - Your site at
https://blog.esempio.itcalls a vendor’s API and the browser says “blocked by CORS policy”, but curl works. Who has to change something, and what?
Solution
- It works and returns up to 200 books: you are the author of the query, and
/sqlis read-only by design. An injection is when a user’s value becomes code without you wanting it. value is not a valid email addressfor the email;String should have at least 2 charactersfor the name.- The vendor’s server must answer with an
Access-Control-Allow-Originthat includes your origin (and approve the preflight if you useAuthorization). You cannot “turn off” CORS from the client; at most you can go through a backend of your own.
Common mistakes
Section titled “Common mistakes”- Building SQL or filters by concatenating strings because “the values come from me anyway”. They come from you today.
- Validating only in the frontend: anyone can call the API without the frontend.
- Believing CORS protects the server: it protects the browser’s user; the server protects itself with authentication and rate limiting.
- Tokens in URLs, logs, repositories. If it happens: revoke it (for the sandbox,
DELETE /sandbox) and generate a new one. - Plain HTTP “just to try”: the token travels in clear text. The test server does not even answer on HTTP.
Summary
Section titled “Summary”Parameters, allow-lists and an authorizer against injections; layered validation; CORS as a browser rule, not a server one; tokens in headers and out of the code; HTTPS and security headers from the reverse proxy; rate limiting against abuse. In the next chapter you put it all into a robust client: timeouts, retries, types and logs.