Use parameter binding for a quoted search value

By Batu · English technical notes

Also published in our primary archive.

A quoted search value belongs in query parameters, not in the SQL source text. The input here is the ordinary Python string O'Reilly Media, which contains an apostrophe. The SQL statement remains fixed: its ? is a qmark placeholder, and the one-item tuple supplies the value. SQLite receives the value separately from the statement syntax, so no manual quote escaping is needed for this equality comparison.

After inserting three synthetic titles, the query returns only the matching title. The assertion checks that the quote was stored and matched as data rather than breaking the SQL string. The ordered query makes the output deterministic even if the test data later gains duplicate matching values. This is not a complete authorization or input-validation strategy, and parameter binding cannot be used in place of SQL syntax where an application needs a table name, column name, or sort direction.

The sqlite3 module is in Python's standard library, and this example requires Python 3.6 or later because it uses an f-string; it relies on no newer sqlite3 API. Python's documentation specifically recommends placeholders rather than string formatting for values. SQLite documents the parameter forms accepted by SQL expressions.

AI-assistance disclosure: this article was drafted with AI assistance and should be adapted to an application's error-handling requirements.

Sources: Python sqlite3 placeholders guide, SQLite SQL parameters, and Python formatted string literals.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE book(title TEXT NOT NULL)")
con.executemany(
    "INSERT INTO book(title) VALUES (?)",
    [("O'Reilly Media",), ("Open Source Guide",), ("Other Press",)],
)

search_title = "O'Reilly Media"
rows = con.execute(
    "SELECT title FROM book WHERE title = ? ORDER BY title",
    (search_title,),
).fetchall()
assert rows == [("O'Reilly Media",)]
print(f"matches: {rows}")

con.close()

matches: [("O'Reilly Media",)]