Chapter 29 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. In psycopg2, you execute SQL through a: - A) Connection only - B) Cursor (obtained from a connection) - C) Pool only - D) Repository

Q2. The correct placeholder for a value in a psycopg2 query is: - A) An f-string {value} - B) %s, with the value passed separately - C) ? always - D) + str(value)

Q3. SQL injection happens when: - A) You use %s placeholders - B) User input is concatenated/formatted into the SQL string and executed - C) You use a connection pool - D) You commit too often

Q4. Parameterized queries prevent injection because: - A) They escape quotes manually - B) The value is sent separately and never interpreted as SQL - C) They disable user input - D) They run faster

Q5. cur.execute("... WHERE id = %s", (user_id,)) — the %s is: - A) Python string formatting - B) A query placeholder filled safely by the driver - C) A comment - D) A wildcard

Q6. To make two inserts atomic, you: - A) Run them in separate connections - B) Wrap them in one transaction (commit both or rollback both) - C) Use two cursors - D) Add an index

Q7. RETURNING order_id in an INSERT lets you: - A) Roll back - B) Get the generated id without a second query - C) Parameterize the query - D) Pool connections

Q8. Opening a new connection per web request at scale: - A) Is best practice - B) Can exhaust the server's connections; use a pool - C) Prevents injection - D) Is required

Q9. A UniqueViolation exception means: - A) A bug in psycopg2 - B) A unique constraint did its job (e.g., duplicate email) — handle it - C) A connection failure - D) A deadlock

Q10. You should parameterize: - A) Only strings - B) Every value coming from outside hard-coded literals - C) Nothing - D) Only numbers

True/False

Q11. Using an f-string to insert a "safe-looking" value into SQL is fine. (True / False)

Q12. You can parameterize a table or column name with %s. (True / False)

Short answer

Q13. Explain why cur.execute("SELECT * FROM users WHERE name = '" + name + "'") is dangerous and how to fix it.

---

Answer key

Q1 — B. A cursor executes SQL and holds results.

Q2 — B. %s placeholder with values passed separately (not f-strings).

Q3 — B. Concatenating/formatting user input into executed SQL.

Q4 — B. The value travels separately and is never parsed as SQL.

Q5 — B. It's a driver placeholder, not Python % formatting.

Q6 — B. One transaction → both or neither (atomicity).

Q7 — B. Returns generated columns (e.g., the new id) inline.

Q8 — B. Exhausts connections; use a pool.

Q9 — B. A constraint working as intended; catch and handle it.

Q10 — B. Every external value (not just strings/numbers).

Q11 — False. f-strings/format into SQL = injection risk, "safe-looking" or not. Always parameterize.

Q12 — False. Parameters are for values. Identifiers (table/column names) need separate, careful handling (psycopg2.sql).

Q13. It builds SQL by concatenating name into the string, so a malicious name like '; DROP TABLE users; -- becomes executed SQL (SQL injection) — an attacker can read, modify, or destroy data. Fix it by parameterizing: cur.execute("SELECT * FROM users WHERE name = %s", (name,)), so the value is sent separately and can never be interpreted as SQL.

Scoring: 12–13 you integrate safely; 9–11 re-drill parameterization; below 9, redo Exercises B.