SQL injection begins when data is promoted into query syntax. A title containing an apostrophe is not inherently invalid. The problem appears when an application constructs a statement by concatenating that title into SQL. For data values, the boundary belongs at the database API: keep the SQL template fixed and pass values through parameters.
This lab executes actual queries against fresh in-memory SQLite databases. It is not a Python simulation of SQL and it contacts no database server. Fixtures contain ordinary titles, duplicate titles in different tenants and a string that resembles a SQL condition. Each input must remain a literal title to match.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE notes (tenant_id INTEGER, title TEXT)")
con.execute("INSERT INTO notes VALUES (?, ?)", (7, "Jo's notes"))
rows = con.execute("SELECT title FROM notes WHERE tenant_id = ? AND title = ?", (7, "Jo's notes")).fetchall()
print(rows)
con.close()The placeholders are unquoted question marks. The tuple supplies two values; (title,) would be a one-element tuple. Do not use an f-string to insert a value before calling execute: a later call to the API cannot undo the earlier mixing of syntax and data. This rule still applies when the value came from a database or another internal service.
A placeholder represents a value, not arbitrary SQL structure. ORDER BY ? binds a constant expression; it does not choose a column by name. The build accepts only "id" and "title" as sort choices, mapping them to application-owned clauses. Unknown choices raise ValueError. This is a small, explicit language for the structural option, rather than an attempt to remove suspicious punctuation from arbitrary SQL.
Binding also does not decide which rows a caller may see. A perfectly parameterized query that searches all tenants can return another tenant's matching title. Here the trusted caller supplies the already authenticated tenant ID, and the query must contain both the tenant predicate and the title predicate. A client-submitted tenant field cannot become that trusted argument merely because it is an integer. The next lesson supplies the object-level policy missing from a tenant-only search.
The task returns (id, title) tuples in a deterministic order and makes no writes. Sorting by title needs an ID tie-breaker because this equality search deliberately allows several notes with the same title. Tests check the result and inspect the database afterward; they do not assert a particular implementation string. A lookup of a syntax-looking title should return exactly the row with that title, even if its punctuation resembles a condition.
Before coding, trace one title containing an apostrophe and one shared across tenants. Write the expected rows first. Then explain why binding both values handles grammar separation while retaining the tenant filter handles a separate access scope. Database permissions, schema constraints, transaction design and authorization remain additional responsibilities in a deployed service.