04.3 Parameterised queries and database permissions

01 | WHEN INPUT ENTERS A QUERY

A user might enter a member name, book ID or return date. Treating that input as part of SQL syntax can cause broken queries and SQL injection: input changes the meaning of the statement.

A parameterised query keeps SQL structure separate from supplied values. The database interface binds the data into placeholders rather than parsing it as instructions.

This guide uses Python’s sqlite3 interface. Its positional placeholder is ?. Other database drivers may use different notation.

02 | SEE A COMPLETE SAFE LOOKUP

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE Members (member_id INTEGER PRIMARY KEY, name TEXT)")
connection.executemany("INSERT INTO Members VALUES (?, ?)",
                       [(1, 'Maya'), (2, "O'Connor")])

wanted_name = input("Member name: ")
rows = connection.execute(
    "SELECT member_id, name FROM Members WHERE name = ?",
    (wanted_name,),
).fetchall()
print(rows)
connection.close()

Run this standalone file in VS Code. Enter O'Connor: the apostrophe stays inside the data value and the row is found. Input is not pasted into the SQL string.

The comma in (wanted_name,) creates a one-item tuple. Passing a plain string where a parameter sequence is expected is a common beginner error.

03 | DO NOT QUOTE THE PLACEHOLDER

rows = connection.execute(
    "SELECT member_id, name FROM Members WHERE name = ?",
    (wanted_name,),
).fetchall()

Correct: name = ?. Incorrect: name = '?'. A quoted question mark is an actual text literal, not a parameter position.

The number of supplied values must match the placeholders, in order. Bind integers as integers where appropriate, and use Python None for SQL NULL.

04 | WRITES NEED PARAMETERS TOO

# Snippets for an open connection using the Section 04 library schema.
new_title = "A Programmer's Guide"
barcode = 'LIB-020'
connection.execute(
    "INSERT INTO Books (barcode, title) VALUES (?, ?)",
    (barcode, new_title),
)

new_due_date = '2026-10-30'
loan_id = 100
connection.execute(
    "UPDATE Loans SET due_on = ? WHERE loan_id = ?",
    (new_due_date, loan_id),
)

These are snippets, not standalone programs. Use the library connection or project. Transaction rules still apply: safe parameter binding does not decide when work should commit.

Do not substitute an f-string, concatenation or manual quote replacement for bound input values. Executescript is for our fixed setup scripts, not user-composed commands.

05 | PARAMETERS DO NOT STAND FOR IDENTIFIERS

A placeholder binds a value; it does not choose a table, column or SQL keyword. SELECT ? FROM Books selects a supplied value for each row, not the column named by that value.

If users choose sorting, map choices to a fixed allow-list of complete query strings you wrote:

sort_queries = {
    'title': "SELECT book_id, title FROM Books ORDER BY title, book_id",
    'barcode': "SELECT book_id, title FROM Books ORDER BY barcode, book_id",
}
choice = input("Sort by title or barcode: ").strip()
if choice not in sort_queries:
    raise ValueError("Choose a listed sort option.")
rows = connection.execute(sort_queries[choice]).fetchall()

The user selects a known option; their text is never inserted as SQL syntax.

06 | VALIDATION IS A DIFFERENT JOB

Parameter binding prevents values from rewriting SQL structure. It does not prove that a return date is sensible, a name is genuine or an ID refers to an active loan.

Validate formats and ranges, then apply current database rules. A user can safely supply a negative ID as data, but your application may still reject it as invalid.

When searching with LIKE, binding protects SQL syntax, but % and _ in the supplied pattern still have wildcard meaning. Decide whether you are offering pattern search or literal text search.

07 | AUTHORISATION DECIDES WHO MAY ACT

Authentication establishes who a user is. Authorisation decides what they may do. A well-formed parameterised DELETE must still be refused if the user is not allowed to delete that record.

Enforce access in the trusted application or database layer for every relevant operation. Hiding a button is not sufficient. A person can try a request through another interface.

The library project is a local single-user learning program. It has no real login or role system and should not be presented as a multi-user service.

08 | APPLY LEAST PRIVILEGE

Least privilege means granting only the access needed for the task. Many server databases provide users, roles and GRANT/REVOKE controls; exact syntax depends on the product.

Ordinary SQLite does not provide the same built-in per-user GRANT/REVOKE model. It is an embedded database, so application controls and operating-system file access are important.

09 | SQLITE FILE ACCESS IS A REAL BOUNDARY

from pathlib import Path
import sqlite3

# Run only after the project has created library.db in this folder.
path = Path(__file__).with_name("library.db").resolve()
uri = path.as_uri() + "?mode=ro"
connection = sqlite3.connect(uri, uri=True)
try:
    print(connection.execute("SELECT book_id, title FROM Books").fetchall())
finally:
    connection.close()

This opens an existing project database read-only through that connection. A missing file is not silently created. It is useful for a reporting tool.

It does not stop a person who has filesystem write access from opening another writable connection. Protect database files and backups with suitable OS access rules; do not place a live database in a publicly downloadable website folder.

10 | PRACTISE THE DISTINCTIONS

In a copy of the safe lookup program, test an apostrophe, an unknown name and a string containing SQL-looking punctuation. Confirm it is treated as a value and does not broaden the search.

Next, build the complete local library program and explain which responsibilities it implements and which remain outside its scope.

KEEP LEARNING

Previous page 04.4 Project: a library database

References: Python SQLite parameters and transactions · SQLite transactions · Partial indexes