Database applications and security

Most people never type SQL. They use applications, web sites, phone apps and office systems, that send SQL to a database on their behalf. This lesson covers the programmer's side of that arrangement: connecting to a database from Python with the standard DB-API, passing parameters safely, managing transactions in code, and the trade-offs of object-relational mappers. It then covers security, from the most common database attack, SQL injection, to access control with users, roles and privileges, protecting data at rest and in transit, and the privacy laws that govern what personal data a database may hold and for how long.

Connecting from a program: the DB-API

Python's DB-API, specified in PEP 249, is a common interface that database drivers implement: sqlite3 in the standard library, psycopg for PostgreSQL, mysqlclient and PyMySQL for MySQL and MariaDB, and others. The pattern is always the same. Connect to obtain a connection object; obtain a cursor, or call execute on the connection directly; execute statements with parameters; fetch results one row at a time, in batches, or all at once; commit or roll back; close. Other languages have equivalents with the same shape: JDBC in Java, ADO.NET in C#, and PDO in PHP, which this course site uses to talk to its MariaDB database.

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row                         # rows behave like dictionaries
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
    CREATE TABLE member (card_no INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
    CREATE TABLE loan (loan_id INTEGER PRIMARY KEY,
                       card_no INTEGER NOT NULL REFERENCES member(card_no),
                       isbn TEXT NOT NULL, due_date TEXT NOT NULL, returned TEXT);
""")
members = [(5001, "Noor", "noor@example.ca"), (5002, "Owen", None), (5003, "Priya", "priya@example.ca")]
con.executemany("INSERT INTO member VALUES (?, ?, ?)", members)          # parameters, not string pasting
con.executemany("INSERT INTO loan (card_no, isbn, due_date) VALUES (?, ?, ?)",
                [(5001, "978-1", "2026-10-22"), (5001, "978-2", "2026-11-02"), (5003, "978-3", "2026-10-30")])
con.commit()

cur = con.execute("""SELECT m.name, COUNT(l.loan_id) AS open_loans
                     FROM member m LEFT JOIN loan l ON l.card_no = m.card_no AND l.returned IS NULL
                     GROUP BY m.card_no ORDER BY m.name""")
for row in cur:                                         # iterate without loading everything
    print(f"{row['name']:6s} {row['open_loans']}")
print("one row:", dict(con.execute("SELECT * FROM member WHERE card_no = ?", (5002,)).fetchone()))

Noor   2
Owen   0
Priya  1
one row: {'card_no': 5002, 'name': 'Owen', 'email': None}

Two details deserve attention. Parameters are passed separately from the SQL text, using placeholders: sqlite3 accepts ? and :name, while psycopg uses %s; the driver sends values to the DBMS as data, never as part of the statement. And Python's sqlite3 module, by default, opens a transaction implicitly before data-modifying statements and requires an explicit commit; if a program exits without committing, its changes are discarded. Using the connection as a context manager, with con:, commits on success and rolls back on an exception, which is a convenient way to get atomicity right.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE account (id TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
con.execute("INSERT INTO account VALUES ('A', 100), ('B', 0)")
con.commit()

def transfer(src, dst, amount):
    try:
        with con:                                        # commit on success, roll back on error
            con.execute("UPDATE account SET balance = balance + ? WHERE id = ?", (amount, dst))
            con.execute("UPDATE account SET balance = balance - ? WHERE id = ?", (amount, src))
        return "ok"
    except sqlite3.IntegrityError:
        return "refused"

print(transfer("A", "B", 60), dict(con.execute("SELECT * FROM account")))
print(transfer("A", "B", 60), dict(con.execute("SELECT * FROM account")))

ok {'A': 40, 'B': 60}
refused {'A': 40, 'B': 60}

SQL injection

SQL injection is the classic database attack and still among the most damaging web vulnerabilities; the OWASP Top 10 list of web application risks has included injection in every edition. It happens when a program builds SQL by pasting user input into the statement text. The input can then contain SQL syntax that changes the meaning of the statement. The example below is a deliberately vulnerable login check, run against a throwaway in-memory database, to show the mechanism so that you can recognize and prevent it.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (username TEXT PRIMARY KEY, pw_hash TEXT)")
con.execute("INSERT INTO users VALUES ('admin', 'x9f2...'), ('noor', 'b71c...')")

def login_vulnerable(username, pw_hash):
    sql = f"SELECT username FROM users WHERE username = '{username}' AND pw_hash = '{pw_hash}'"
    return sql, con.execute(sql).fetchall()

def login_safe(username, pw_hash):
    sql = "SELECT username FROM users WHERE username = ? AND pw_hash = ?"
    return sql, con.execute(sql, (username, pw_hash)).fetchall()

attack = "admin' --"                                  # input crafted to end the string and comment out the rest
for f in (login_vulnerable, login_safe):
    sql, rows = f(attack, "wrong")
    print(f.__name__)
    print("  SQL :", sql)
    print("  rows:", rows)

login_vulnerable
  SQL : SELECT username FROM users WHERE username = 'admin' --' AND pw_hash = 'wrong'
  rows: [('admin',)]
login_safe
  SQL : SELECT username FROM users WHERE username = ? AND pw_hash = ?
  rows: []

With string formatting, the input admin' -- closes the quoted username and turns the password check into a comment, so the query returns the admin row with no password at all. With parameters, the same characters are just an unusual username that matches no one. Injection can do far worse than bypassing a login: depending on the system and privileges, attackers have used it to read entire tables of customer data, modify records and, where multiple statements are allowed, drop tables.

Escaping quotes by hand is not an adequate defence: character sets, edge cases and different quoting rules across systems have defeated many home-made escaping functions. Parameterized queries are simple, supported by every driver, and often faster, because the DBMS can reuse the plan of a prepared statement.

Object-relational mappers

An object-relational mapper (ORM), such as SQLAlchemy and the Django ORM in Python, Hibernate in Java or Entity Framework in .NET, maps classes to tables and objects to rows, so that programmers work with objects and the ORM generates SQL. ORMs reduce repetitive code, handle parameters automatically, and make simple create, read, update and delete operations quick to write. Their pitfalls are well known: generated queries can be inefficient, the 'N + 1 queries' problem (loading a list of objects, then issuing one more query per object to fetch related data) can multiply database round trips, and complex reporting queries are often clearer in SQL. A balanced approach uses an ORM for routine operations, writes SQL for complex queries, and watches the SQL the ORM actually generates. The joined, single-table and table-per-class inheritance options in ORMs correspond to the hierarchy mappings of lesson 7.

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE author (author_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE book (isbn TEXT PRIMARY KEY, title TEXT, author_id INTEGER REFERENCES author);
INSERT INTO author VALUES (1, 'Okoye'), (2, 'Gauthier'), (3, 'Patel');
INSERT INTO book VALUES ('1', 'Atlas', 1), ('2', 'Bread and Salt', 3), ('3', 'Small Engines', 3), ('4', 'Tides', 2);
""")
queries = []
con.set_trace_callback(queries.append)                # record every statement sent

# N + 1 pattern: one query for the list, then one per row
for isbn, title, aid in con.execute("SELECT isbn, title, author_id FROM book").fetchall():
    con.execute("SELECT name FROM author WHERE author_id = ?", (aid,)).fetchone()
print("N+1 approach:", len(queries), "queries")

queries.clear()
rows = con.execute("SELECT b.title, a.name FROM book b JOIN author a USING (author_id)").fetchall()
print("join approach:", len(queries), "query,", len(rows), "rows")

N+1 approach: 5 queries
join approach: 1 query, 4 rows

Access control

A DBMS controls who can do what through accounts (users), roles and privileges. Privileges are granted on objects: SELECT, INSERT, UPDATE and DELETE on tables or views, sometimes on individual columns; EXECUTE on procedures; and administrative rights such as creating tables. GRANT gives privileges and REVOKE removes them. A role is a named bundle of privileges, granted to users, so that permissions follow job functions rather than individuals. Views combine with privileges to give row- and column-level control: grant SELECT on a view that shows only some columns, and nothing on the base table. PostgreSQL also offers row-level security policies. SQLite, being an embedded library, has no users or GRANT at all; access is controlled by file permissions and by the application.

-- Server systems such as PostgreSQL or MariaDB (not SQLite)
CREATE ROLE registrar_clerk;
GRANT SELECT, INSERT, UPDATE ON enrollment TO registrar_clerk;
GRANT SELECT (student_id, name, email) ON student TO registrar_clerk;  -- column-level

CREATE VIEW my_section_grades AS ... ;                                  -- rows for the current instructor
GRANT SELECT ON my_section_grades TO instructor_role;

CREATE USER web_app WITH PASSWORD '...';                                 -- PostgreSQL syntax
GRANT registrar_clerk TO web_app;
REVOKE INSERT ON enrollment FROM registrar_clerk;

The principle of least privilege applies throughout: each application connects with an account that has only the privileges it needs, never as the database owner or administrator. A public web site that only reads the catalogue should connect as a read-only user; then even a successful injection attack cannot change data. Credentials belong in configuration outside the source code, such as environment variables or a secrets manager, never in a public repository. This site, for example, keeps its database settings in a local configuration file that is excluded from every published copy of the code.

Protecting data at rest and in transit

Encryption in transit, using TLS between the application and the database server, prevents eavesdropping on the network; managed cloud databases usually require it. Encryption at rest protects data files, backups and logs if storage is stolen or discarded; servers offer transparent data encryption, and operating systems and cloud providers encrypt whole volumes. Passwords should never be stored at all, only salted hashes computed with a slow algorithm designed for the purpose, such as Argon2, bcrypt or PBKDF2, as CISSP-114 discusses. Auditing records who accessed or changed sensitive data, and tested backups, kept separately and encrypted, protect against both failure and ransomware. Classification of data by sensitivity, covered in CISSP-111, decides which of these measures each table needs.

Privacy law and the database designer

Personal information in Canadian databases is governed by privacy law. The Personal Information Protection and Electronic Documents Act (PIPEDA) applies to private-sector organizations in most provinces; Alberta, British Columbia and Quebec have their own substantially similar private-sector laws, and public bodies fall under federal and provincial public-sector acts. A bill to replace PIPEDA with a new federal privacy act was introduced in Parliament in June 2026 and had not passed as of October 2026, so PIPEDA remains in force; check the current status. Quebec's Law 25 is the strictest in the country, with privacy impact assessments, mandatory breach notification, a right to data portability in force since September 2024, and penalties of up to $25 million or 4 per cent of worldwide turnover. Under PIPEDA's principles, organizations may collect only what is needed for identified purposes, must keep it accurate and secure, must give individuals access to their information, and must not keep it longer than necessary.

These obligations become design requirements. Data minimization means not adding columns 'just in case': if the library does not need birth dates, it should not collect them. Retention limits mean a schedule for deleting or anonymizing old records, such as loan history older than a set period, implemented with scheduled jobs. Deletion requests must reach every copy of the data, including derived tables, caches, logs and, eventually, backups, which is much easier if personal data is concentrated in a few tables keyed by a person identifier, exactly what normalization encourages. Access requests require being able to find everything about a person, again easier with a clean design. Pseudonymization, replacing names with identifiers in analytics tables, reduces risk when data is used for reporting.