Use placeholders for SQLite queue inputs

By Batu · English technical notes

Also published in our Blogger archive.

Bind queue values rather than formatting SQL

The question mark is a placeholder and (key,) is a one-item parameter tuple. The apostrophe and semicolon in the request key round-trip unchanged because Connection.execute binds data instead of inserting it into SQL syntax. The comma in that tuple is significant.

Placeholders do not replace table names or ORDER BY terms. Choose variable SQL structure from a fixed allowlist, and validate a request key’s own format separately.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(request_key TEXT)")
    key = "owner's-request; still data"
    connection.execute("INSERT INTO job VALUES (?)", (key,))
    stored = connection.execute("SELECT request_key FROM job").fetchone()[0]
    assert stored == key
    print("stored=quote-containing-key")

Expected stdout:

stored=quote-containing-key

Sources

- sqlite3 placeholders

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.