By WebNest Studio Engineering Team
Python Tutorial
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.
Why it matters
SQL injection has been among the most damaging web vulnerabilities for decades, and it is prevented by one habit. Transactions matter just as much: a money transfer that takes from one account and then fails before adding to the other must leave no trace. Both topics are asked about in almost every back-end interview.
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
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
query: SELECT username FROM users WHERE username = 'nobody' OR '1'='1'
unsafe result: [('asha',), ('ravi',)]
safe result: []
An all-or-nothing money transfer
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())
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
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))
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.
Exercise
Complete transfer so that both updates happen inside one transaction. If the source account does not have enough money, raise ValueError, and the update already made to the target account must be undone. The program attempts a transfer that is too large and then prints the balances, which must be unchanged.
Expected output: transfer failed then [('A', 100), ('B', 50)]
import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)")
connection.executemany("INSERT INTO accounts VALUES (?, ?)", [("A", 100), ("B", 50)])
connection.commit()
def transfer(source, target, amount):
# TODO: inside "with connection:", add the amount to the target account,
# then read the source balance and raise ValueError if it is too small,
# then subtract the amount from the source account
pass
try:
transfer("A", "B", 500)
except ValueError:
print("transfer failed")
print(connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
Show hints
- Used as a context manager, a connection commits when the block ends normally and rolls back when the block raises an exception.
- Because the exception is raised inside the block, the earlier update to the target account is rolled back with it.
Show solution
import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)")
connection.executemany("INSERT INTO accounts VALUES (?, ?)", [("A", 100), ("B", 50)])
connection.commit()
def transfer(source, target, amount):
with connection:
connection.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, target))
balance = connection.execute("SELECT balance FROM accounts WHERE name = ?", (source,)).fetchone()[0]
if balance < amount:
raise ValueError("insufficient funds")
connection.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, source))
try:
transfer("A", "B", 500)
except ValueError:
print("transfer failed")
print(connection.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
Quick quiz
1. What makes a query vulnerable to SQL injection?
- A. Using SELECT *
- B. Not closing the connection
- C. Using placeholders
- D. Building the SQL text by inserting user input into the string
Show answer
D. The input can then end the intended statement and add SQL of its own.
2. What does the A in ACID stand for, and what does it mean?
- A. Atomicity: all the changes in a transaction happen, or none do
- B. Availability: the database is always online
- C. Accuracy: values are rounded correctly
- D. Authorisation: users must log in
Show answer
A. The other letters are consistency, isolation and durability.
3. What does with connection: do in sqlite3?
- A. Closes the connection at the end
- B. Commits if the block succeeds and rolls back if it raises; the connection stays open
- C. Opens a new database
- D. Locks the file permanently
Show answer
B. The connection must still be closed separately.
Interview questions
What is SQL injection and how do you prevent it?
Show answer
It is an attack in which input supplied by a user is interpreted as part of an SQL statement, because the program built the statement by joining strings. An attacker can then read or change data they should not reach, for example by entering ' OR '1'='1 as a password. The prevention is to use parameterised queries, where values are passed separately from the SQL, and never to format user input into a query.
What is a transaction?
Show answer
A transaction is a group of statements treated as a single unit: either all of them take effect, on commit, or none do, on rollback. It keeps the data consistent when an operation involves several changes that belong together, such as debiting one account and crediting another, and it isolates those changes from other users until they are committed.
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 in the playground
Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.
Run the examples
Change an input, predict the result, then compare it with the output. Explain why the result changes.
Written by
WebNest Studio Engineering Team
Practical lessons from developers working with Java, Python, React, databases and production systems.
Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering