Transactions and Preventing SQL Injection

Two topics separate toy database code from production code. Transactions make a group of statements succeed or fail together — money must never leave one account without arriving in the other. SQL injection is one of the most common and damaging security bugs: when user input is pasted into SQL, an attacker can change what the query does.

This lesson shows both problems happening, then fixes them, and covers the database exception hierarchy you use to handle errors.

How SQL Injection Works

If a login query is built as f"... WHERE username = '{name}'" and someone types nobody' OR '1'='1, the quote ends the string early and the rest becomes SQL — the condition is now always true. Attackers can use the same trick to read other tables or delete data. The fix is simple and absolute: always pass values as parameters. The driver then treats the input purely as data, whatever characters it contains.

Transactions and ACID

A transaction groups statements into one unit that is Atomic (all or nothing), Consistent (constraints hold), Isolated (others do not see half-finished work) and Durable (committed data survives crashes). In Python DB-API drivers, a transaction starts automatically with the first data-changing statement and ends with commit() or rollback(). with conn: does this for you: commit if the block succeeds, rollback if it raises.

Database Exceptions

DB-API drivers share an exception hierarchy under Error → DatabaseError: IntegrityError (constraint violations — duplicate keys, NULLs, CHECK, foreign keys), OperationalError (bad table names, locked database, lost connection), ProgrammingError (wrong number of parameters, closed cursor) and DataError. Catch the specific ones you can handle, roll back, and let the rest propagate.

Examples

SQL injection, and the parameterised fix

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (username TEXT, password_hash TEXT, is_admin INTEGER)")
conn.executemany("INSERT INTO users VALUES (?, ?, ?)", [("asha", "h1", 1), ("ravi", "h2", 0)])

user_input = "nobody' OR '1'='1"

unsafe = f"SELECT username FROM users WHERE username = '{user_input}'"
print("query:", unsafe)
print("unsafe result:", conn.execute(unsafe).fetchall())      # every user leaks

safe = conn.execute("SELECT username FROM users WHERE username = ?", (user_input,)).fetchall()
print("safe result:", safe)                                    # treated as a plain name

Output

query: SELECT username FROM users WHERE username = 'nobody' OR '1'='1'
unsafe result: [('asha',), ('ravi',)]
safe result: []

An all-or-nothing money transfer

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
conn.executemany("INSERT INTO accounts VALUES (?, ?)", [("asha", 1000), ("ravi", 200)])
conn.commit()

def transfer(sender, receiver, amount):
    try:
        with conn:                          # one transaction: both updates or neither
            conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, receiver))
            conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, sender))
        print(f"transferred {amount} from {sender} to {receiver}")
    except sqlite3.IntegrityError as e:
        print(f"transfer of {amount} failed and was rolled back ({e})")

def balances():
    return dict(conn.execute("SELECT name, balance FROM accounts ORDER BY name"))

transfer("asha", "ravi", 300)
print(balances())
transfer("ravi", "asha", 900)               # ravi has only 500: the credit to asha is undone
print(balances())

Output

transferred 300 from asha to ravi
{'asha': 700, 'ravi': 500}
transfer of 900 failed and was rolled back (CHECK constraint failed: balance >= 0)
{'asha': 700, 'ravi': 500}

Handling IntegrityError and OperationalError

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL)")
conn.execute("INSERT INTO orders VALUES (1, 'book')")
conn.commit()

attempts = [
    ("missing value", "INSERT INTO orders (item) VALUES (?)", (None,)),
    ("duplicate key", "INSERT INTO orders (id, item) VALUES (?, ?)", (1, "pen")),
    ("typo in table", "INSERT INTO ordrs (item) VALUES (?)", ("pen",)),
    ("valid insert", "INSERT INTO orders (item) VALUES (?)", ("lamp",)),
]
for label, sql, params in attempts:
    try:
        conn.execute(sql, params)
        conn.commit()
        print(f"{label:<14} saved")
    except sqlite3.IntegrityError as e:
        conn.rollback()
        print(f"{label:<14} IntegrityError: {e}")
    except sqlite3.OperationalError as e:
        conn.rollback()
        print(f"{label:<14} OperationalError: {e}")

print(conn.execute("SELECT id, item FROM orders").fetchall())
print(issubclass(sqlite3.IntegrityError, sqlite3.DatabaseError), issubclass(sqlite3.DatabaseError, sqlite3.Error))

Output

missing value  IntegrityError: NOT NULL constraint failed: orders.item
duplicate key  IntegrityError: UNIQUE constraint failed: orders.id
typo in table  OperationalError: no such table: ordrs
valid insert   saved
[(1, 'book'), (2, 'lamp')]
True True

Common Mistakes

Escaping quotes by hand instead of using parameters — escaping is easy to get wrong.
Committing after each statement of a multi-step operation, so a failure leaves half-applied changes.
Catching every exception and continuing without rolling back.
Relying on the application to enforce rules the database could enforce with constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY).
Keeping transactions open while waiting for user input or network calls, which holds locks.

Key Points to Remember

Never put user input into SQL text; pass it as parameters.
A transaction is all-or-nothing; with conn: commits on success and rolls back on error.
Database constraints plus transactions keep data consistent even when code fails.
Catch IntegrityError / OperationalError specifically and roll back.
Keep transactions short.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Try SQL injection, and the parameterised fix in Webnest Codelab Try An all-or-nothing money transfer in Webnest Codelab Try Handling IntegrityError and OperationalError in Webnest Codelab