LESSON 86 · DEEPER PYTHON32 min

Query SQLite Safely with Parameters

Use SQLite placeholders for values and understand when a transaction commits or rolls back.

Before you start: Dictionaries · Functions · Exceptions

Example check: Worked and guided code samples are checked against CPython 3.13. Practice uses Pyodide 0.29.5 (Python 3.13) in your browser. How we check lessons

WATCH

Video lesson: Query SQLite Safely with Parameters

The preview is stored on this site. YouTube loads only when you press Play. Watch on YouTube

Jump to a chapter

Read the video transcript

Select a timestamp to watch that moment on YouTube.

00:00
Imagine a search box connected to a SQLite table. A normal name should either find one person or return no rows. Today we will use an in-memory database, so no files or outside services are involved. First, we will put a user's search text directly into an SQL string. The result demonstrates why combining code and data is dangerous, even in a tiny tutorial. Then we will send the SQL statement and the values separately using a question- mark placeholder. SQLite will treat the value as data, so quotation marks inside that value cannot change the query. Finally, we will add a transaction: two related changes should either both succeed
00:41
or both be undone. Watch the actual terminal result at each stage, then run the matching examples on the lesson page. The key ideas are not tied to a particular web framework. A parameter protects values, while a transaction protects the consistency of a sequence of writes. Those are two different jobs, and we will test both. The table contains three names, but there is no person called our search string. The printed statement shows what went wrong. The quote in the value closed the quoted string, and the rest of the text became part of the SQL expression. The expression one equals one is true for every row.
01:23
The two dashes turn the trailing quote into a comment, allowing the statement to run. This is a small, controlled example of SQL injection. Quoting or escaping text by hand is fragile, because the application starts mixing two languages: Python builds text that SQLite then interprets as SQL. Even if the input comes from a configuration file rather than a user, direct interpolation makes a query harder to reason about. The table setup used placeholders for its inserts, but the search did not; that difference is deliberate. We need the search value to stay a value all the way into SQLite. A string containing quotes should be searched literally, and it should simply
02:06
return no matching records. The SQL text now contains a question mark where a value belongs. The second argument is a sequence of bound parameters. `(name,)` is a one-element tuple; without the comma, parentheses around a string do not create a tuple. SQLite handles the binding, so the unusual search text stays a single literal value. Its quotation mark, equals sign, and comment marker are no longer parsed as SQL structure. The first query returns no rows, which is exactly what a search for an unknown name should do. The second query proves that the ordinary search still works.
02:46
Placeholders are for values, such as names and numbers. They are not a way to inject a table name or sort direction into the structure of a statement. For those structural choices, choose from a small allowlist of known SQL fragments. Also notice that we are using `fetchall` only because this example has a few rows. For a large result, iterate over a cursor or use a limit rather than loading every row just to display them. The two updates are parts of one transfer. If only the first update is kept, five credits vanish. A `with db` block commits the transaction when the block finishes normally and rolls it back if an exception leaves the block.
03:29
Both statements still use placeholders: the amount and the name are data, not handwritten fragments of SQL. The query after the block displays fifteen credits for each account. In a real application, we would also check that the source account exists and has enough credits before allowing the transfer. This small lesson focuses on the two boundaries that are easy to confuse: parameter binding decides how values are interpreted, and a transaction decides whether a group of changes is saved together. Neither replaces the other. A transaction around an unsafe interpolated query would still allow its meaning to change. Conversely, perfectly parameterized updates without a transaction could still
04:12
leave a partial operation after an error. Run the two updates together, inspect the result, and then test the failure path. The first statement would lower Ada's balance to fifteen. The second attempts to insert another row with the primary key Grace, so SQLite raises an integrity error. Because that exception leaves the transaction block, the earlier update is rolled back. We catch the error outside the block and inspect the table. Both balances remain at their starting values. The explicit commit after setup matters here: it establishes the baseline before the transaction we intend to test.
04:53
Without that boundary, earlier setup writes might be part of the same pending transaction and the demonstration would be misleading. This test also shows why checking only the error message is insufficient. We need to verify the data after failure. Try changing the duplicate name to a new person and predict what the final rows will be. Then try the search from the beginning with a parameterized query and confirm it remains an ordinary no-match. When you write real database code, make these two checks routine: values are bound, and partial writes do not survive a failed operation. The interactive lesson includes a practice task and quiz to make both decisions
05:34
explicit.

THE IDEA

Understand the concept

SQLite gives a small Python program a relational database without a separate database server. sqlite3.connect(':memory:') creates a database for one connection; it disappears when that connection closes. The browser exercise uses that temporary database, so no visitor data is stored on the website. A local application can instead connect to a named file, but it must manage that file and its backups deliberately.

Never paste a user-supplied value into a SQL string with an f-string. A quote in a category can make a query invalid, and carefully crafted text can change its meaning. The question-mark placeholder is reserved for a value: connection.execute('... WHERE category = ?', (category,)). The one-element tuple needs its trailing comma. Database identifiers such as table names cannot be supplied through a placeholder.

A SELECT returns rows, normally tuples. Decide whether callers need just one column, full records, or named rows. Add ORDER BY when deterministic order matters; databases do not promise insertion order in a query without it. The lesson's find_items returns sorted names and treats an unmatched or malicious-looking category as data, producing an empty list rather than a broader result.

For a group of writes, use a connection as a context manager. A successful block commits; an exception rolls back pending changes. The connection context does not close the connection, so close it separately when finished. In the task, focus on the safe SELECT; the transaction example shows how to keep a failed update from leaving half a change behind.

SQLite can use an in-memory database
? placeholders bind values separately from SQL
ORDER BY makes result order explicit
A failed connection block rolls back pending writes

WORKED EXAMPLES

See it step by step

Read the code, predict the output, then compare it with the result.

01. See why concatenation breaks quoted values

PYTHON

category = "kid's"
print(f"SELECT name FROM items WHERE category = '{category}'")

EXPECTED OUTPUT

SELECT name FROM items WHERE category = 'kid's'

The inner quote ends the SQL string early. Escaping by hand is brittle; bind the value instead.

02. Bind a value rather than assembling SQL

PYTHON

import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE items (name TEXT, category TEXT)')
db.execute('INSERT INTO items VALUES (?, ?)', ('Puzzle', "kid's"))
print(db.execute('SELECT name FROM items WHERE category = ?', ("kid's",)).fetchall())

EXPECTED OUTPUT

[('Puzzle',)]

The driver handles the quote as content and the query returns the intended row.

03. Roll back an incomplete write

PYTHON

import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE points (value INTEGER)')
try:
    with db:
        db.execute('INSERT INTO points VALUES (5)')
        raise ValueError('cancel')
except ValueError:
    pass
print(db.execute('SELECT COUNT(*) FROM points').fetchone()[0])

EXPECTED OUTPUT

The exception makes the connection context roll back the pending INSERT.

GO DEEPER

A closer look

Follow the reasoning, inspect each result, then try the suggested changes in the console below.

01 / 03

Break a query with an ordinary apostrophe

The first string is a legitimate category, kid's, yet inserting it between SQL quotes creates malformed syntax. Print the generated statement and find the inner apostrophe. Even without an attacker, text assembly is brittle. With untrusted input, the same pattern can change a query's meaning instead of merely causing an error.

Keep the SQL statement fixed and pass the category separately as a parameter. A placeholder can represent a data value, not a table name or arbitrary SQL fragment. The one-value parameter tuple needs its comma: (category,). A database driver can then handle quotes and other characters as content rather than syntax.

PYTHON

category = "kid's"
print(f"SELECT name FROM items WHERE category = '{category}'")

EXPECTED OUTPUT

SELECT name FROM items WHERE category = 'kid's'

Follow the reasoning

Inspect the broken SQL string before executing it.
Separate statement text from user values.
Use a one-element tuple for one question-mark placeholder.

02 / 03

Query an in-memory database with a bound value

The next example creates a tiny SQLite database in memory and inserts one record. The category still contains an apostrophe, but both INSERT and SELECT use question-mark placeholders. The returned row is Puzzle, exactly the record requested. Nothing is written to the visitor's computer; this database lasts only for its connection.

A SELECT without ORDER BY does not promise a stable display order, so the checked function should sort names explicitly in SQL when multiple rows match. It should also return an empty list for an unmatched category instead of broadening the query. Test a malicious-looking string as data: it should not become a condition that selects every row.

PYTHON

import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE items (name TEXT, category TEXT)')
db.execute('INSERT INTO items VALUES (?, ?)', ('Puzzle', "kid's"))
print(db.execute('SELECT name FROM items WHERE category = ?', ("kid's",)).fetchall())

EXPECTED OUTPUT

[('Puzzle',)]

Follow the reasoning

Create a known table and row in memory.
Bind the category value in both insert and lookup.
Add a deterministic ordering rule for multirow results.

03 / 03

Keep an incomplete write out of the database

The final run performs an INSERT inside a connection context and then deliberately raises ValueError. After the exception, the row count is zero. The context rolls back pending changes so the database does not retain half of an intended unit of work. This is separate from placeholder safety but equally important for reliable data updates.

A successful context block commits changes; neither outcome closes the connection automatically. When building a local application, decide how long the connection should live and close it when finished. The task focuses on a safe SELECT, but this example provides a concrete failure case to remember before turning the query into a larger workflow with writes.

PYTHON

import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE points (value INTEGER)')
try:
    with db:
        db.execute('INSERT INTO points VALUES (5)')
        raise ValueError('cancel')
except ValueError:
    pass
print(db.execute('SELECT COUNT(*) FROM points').fetchone()[0])

EXPECTED OUTPUT

Follow the reasoning

Insert a row inside a connection context.
Raise an exception before leaving the block.
Confirm the row count is zero afterward.

HANDS-ON

Try it in Python

Edit the example and run it. Python starts in your browser the first time you click Run.

Python console

Ready to run

import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE items (name TEXT, category TEXT)')
db.executemany('INSERT INTO items VALUES (?, ?)', [('Hammer', 'tools'), ('Saw', 'tools'), ('Mug', 'home')])

def find_items(connection, category):
    query = f"SELECT name FROM items WHERE category = '{category}' ORDER BY name"
    return [row[0] for row in connection.execute(query)]

print(find_items(db, 'tools'))

Need input()? Add one value per line

Input lines

Ctrl / ⌘ + Enter to run

OUTPUT

Your output appears here.

Your turnReplace the interpolated SQL in find_items with a ? parameter. Return names sorted alphabetically. The prepared call prints ['Hammer', 'Saw']; a category containing a quote or SQL syntax must be treated only as data.

Need a hint?

Use connection.execute('SELECT name FROM items WHERE category = ? ORDER BY name', (category,)). Extract row[0] from each result row.

Complete the task and select Check task to verify your code.

DEBUG BETTER

Typical mistakes

Everyone meets these errors. See what causes them and how to fix them.

Interpolating an untrusted value

COMMON MISTAKE

query = f"SELECT name FROM items WHERE category = '{category}'"

FIX

connection.execute('SELECT name FROM items WHERE category = ?', (category,))

What happens: Quotes break the query and SQL-looking values can alter its meaning.
Bind values; do not manufacture SQL syntax from external text.

Assuming rows arrive in insertion order

COMMON MISTAKE

SELECT name FROM items WHERE category = ?

FIX

SELECT name FROM items WHERE category = ? ORDER BY name

What happens: A later query plan can return a different order.
Specify the order required by the interface.

Browse this lesson’s problem fixes

VERIFY THE STORY

Sources and further reading