SQL for backend developers: SELECT, JOIN and avoiding SQL injection

Tables, rows and a question

Byte: A database is a spreadsheet that answers questions.

Your server keeps its data in a database: tables with columns, one row per thing. The vault has an items table (id, name, vault, price) and a vaults table (name, floor, keeper).

You don't loop over rows yourself. You ask the database a question in SQL and it hands back only the rows you asked for. That's faster and much less code than filtering in Python.

items row	means
(1, 'Crown', 'North', 950)	item 1 is a crown in the North vault, price 950
(7, 'Rope', 'Deep', 8)	item 7 is rope in the Deep vault, price 8

Quick check: Where should filtering thousands of items happen?

Fetch every row, then filter in a Python loop
Ask the database for just the rows you need

Right. Let the database filter; send back only what the request needs.

SELECT, WHERE, ORDER BY

Byte: SQL reads like a sentence.

SELECT names the columns, FROM the table, WHERE keeps matching rows and ORDER BY sorts them (add DESC for biggest first).

In Python, db.execute(sql) runs the query. .fetchall() returns a list of tuples, .fetchone() one tuple, or None if nothing matched.

shelf.py

rows = db.execute(
    "SELECT name, price FROM items WHERE price <= 50 ORDER BY price"
).fetchall()
# [('Rope', 8), ('Map', 12), ('Key', 25), ('Lantern', 40)]
 
row = db.execute("SELECT name FROM items WHERE id = 99").fetchone()
# None: no item 99

Quick check: Which query lists North's items, priciest first?

SELECT name FROM items WHERE vault = 'North' ORDER BY price DESC
SELECT name FROM items ORDER BY vault = 'North'
SELECT DESC name FROM items WHERE 'North'

WHERE picks the rows, ORDER BY price DESC puts the priciest first.

Never paste input into SQL

Byte: A quote mark in the wrong place opens the whole vault.

If you build SQL with an f-string, the visitor's text becomes part of the query. A vault name like North' OR '1'='1 turns your WHERE into always-true and leaks every row. That is SQL injection, one of the oldest attacks on the web.

The fix: put a ? where each value goes and pass the values separately, as a tuple. The database treats them as data, never as SQL.

ledger.py

# Dangerous: the input becomes SQL
db.execute(f"SELECT name FROM items WHERE vault = '{vault}'")
 
# Safe: the input is only ever a value
db.execute("SELECT name FROM items WHERE vault = ?", (vault,))

Quick check: Why the comma in (vault,)?

It makes a one-item tuple, which execute() expects
It's a typo that Python ignores

Right. (vault) is just vault in brackets; (vault,) is a tuple.

JOIN: two tables, one answer

Byte: The floor is in another table. JOIN brings it over.

items knows each item's vault; vaults knows each vault's floor. JOIN ... ON lines rows up where a column matches, so one query can answer with both.

Short names after the table (items i, vaults v) keep it readable. When a query finds nothing, answer 404 with HTTPException, like a missing route.

floors.py

row = db.execute(
    "SELECT i.name, v.floor FROM items i "
    "JOIN vaults v ON v.name = i.vault WHERE i.id = ?",
    (item_id,),
).fetchone()
if row is None:
    raise HTTPException(status_code=404)

Quick check: What does JOIN vaults v ON v.name = i.vault do?

Pairs each item with the vault row whose name matches its vault
Copies the vaults table into items

Each item row gets its vault's columns alongside it, for this query only.