Parameterized Queries & Transactions
SQL Injection (SQLi) has remained among the top security threats in web applications for over two decades. Understanding the compiler-level mechanics of why SQL injection happens is essential for every software engineer.
How String Concatenation Corrupts SQL Bytecode:
When you construct a query using Python f-strings or string concatenation:
# DANGEROUS VULNERABLE CODE:
username = input("Enter username: ")
password = input("Enter password: ")
query = f"SELECT * FROM users WHERE user = '{username}' AND pass = '{password}'"If an attacker inputs admin' -- for the username, the resulting string sent to the database parser becomes:
SELECT * FROM users WHERE user = 'admin' --' AND pass = '...'In SQL syntax, -- indicates a comment. The SQL parser completely ignores everything following the comment, executing only WHERE user = 'admin'. The attacker logs into the admin account without knowing the password!
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password_hash TEXT, role TEXT)")
cur.executemany("INSERT INTO users VALUES (?, ?, ?, ?)", [
(1, "admin", "hash_secret_9988", "SuperAdmin"),
(2, "ravi", "hash_pass_1234", "User"),
(3, "balaji", "hash_pass_5678", "Developer")
])
conn.commit()
# Malicious user inputs designed to bypass authentication:
malicious_payload = "admin' OR '1'='1"
print("--- 🛡️ Testing Secure Parameterized Query Defense ---")
# Parameterized query with '?' placeholder:
cur.execute("SELECT * FROM users WHERE username = ?", (malicious_payload,))
matched_users = cur.fetchall()
print(f"Query matched {len(matched_users)} user accounts.")
if len(matched_users) == 0:
print("✅ Attack neutralized! Database safely treated input as a literal string.")
conn.close()Because the SQL syntax tree is compiled before parameters are bound, no amount of quotes, semicolons (; DROP TABLE), or SQL keywords inside malicious_payload can modify the query's AST (Abstract Syntax Tree).
In database engineering, a Transaction is a sequence of one or more SQL operations executed as an indivisible unit. Transactions are governed by the ACID properties:
| Pillar | Full Name | Core Principle |
|---|---|---|
| A | Atomicity | All or Nothing: If any single SQL statement fails or crashes, 100% of the entire transaction is cancelled and undone via rollback(). |
| C | Consistency | Transactions take the database from one valid state to another, maintaining all schema constraints (uniqueness, foreign keys). |
| I | Isolation | Concurrent operations execute independently without seeing incomplete, uncommitted intermediate states of other transactions. |
| D | Durability | Once commit() returns successfully, data is permanently persisted to disk and survives system power outages. |
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("CREATE TABLE wallets (wallet_id TEXT PRIMARY KEY, owner TEXT, balance REAL CHECK(balance >= 0))")
cur.executemany("INSERT INTO wallets VALUES (?, ?, ?)", [
("W101", "Balaji", 15000.0),
("W102", "Alex", 2000.0)
])
conn.commit()
def execute_money_transfer(sender_id, receiver_id, amount):
"""Executes a financial transfer with guaranteed ACID atomicity."""
print(f"\n--- 💸 Initiating Transfer: ₹{amount:,.2f} from {sender_id} -> {receiver_id} ---")
try:
# 1. Deduct from sender:
cur.execute("UPDATE wallets SET balance = balance - ? WHERE wallet_id = ?", (amount, sender_id))
# 2. Simulate intermediate validation:
if amount > 50000:
raise ValueError("Transfer exceeds anti-fraud transaction ceiling limit (₹50,000)!")
# 3. Credit to receiver:
cur.execute("UPDATE wallets SET balance = balance + ? WHERE wallet_id = ?", (amount, receiver_id))
# 4. Commit all statements together:
conn.commit()
print("✅ Transaction COMMITTED: Both accounts updated in sync!")
except Exception as err:
# 5. Rollback: Undo every modification in this transaction:
conn.rollback()
print(f"🛑 Transaction ABORTED & ROLLED BACK: {err}")
# Test 1: Valid Transfer
execute_money_transfer("W101", "W102", 5000.0)
# Test 2: Invalid Transfer (Attempting to transfer more than balance or limit)
execute_money_transfer("W101", "W102", 90000.0)
# Inspect final balances:
cur.execute("SELECT * FROM wallets")
print("\n--- Current Wallet Balances ---")
for w in cur.fetchall():
print(f"• {w['owner']} ({w['wallet_id']}): ₹{w['balance']:,.2f}")
conn.close()The database table definition includes CHECK(balance >= 0). If an UPDATE statement tries to reduce a balance below zero, SQLite throws an IntegrityError and aborts the statement immediately!
Writing code that manually tries to "undo" changes by executing inverse UPDATE queries if an error occurs is dangerous and buggy. Always use the built-in conn.rollback() method provided by the database engine.
Use Python context manager with conn: to wrap two INSERT statements. If the second insert raises an error, verify that the first insert is automatically rolled back.
import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE logs (id INTEGER PRIMARY KEY, msg TEXT UNIQUE)")
try:
with conn: # Python context manager auto-commits or auto-rolls back!
cur.execute("INSERT INTO logs VALUES (1, 'Log entry A')")
cur.execute("INSERT INTO logs VALUES (2, 'Log entry A')") # UNIQUE violation!
except sqlite3.IntegrityError:
print("Caught IntegrityError! Entire block was rolled back automatically.")
cur.execute("SELECT COUNT(*) FROM logs")
print("Total rows in logs table:", cur.fetchone()[0]) # 0 rows! Perfect rollback!
conn.close()Q Why should I use "with conn:" instead of manual commit/rollback calls?
When using "with conn:", Python automatically calls conn.commit() if the block finishes without exception, and automatically executes conn.rollback() if any exception is raised, eliminating boilerplate try-except-finally blocks.
Q What is the difference between table-level locking and row-level locking?
Table-level locking (used by default in SQLite) locks the entire table during writes. Row-level locking (used by PostgreSQL and MySQL InnoDB) locks only the specific rows being updated, allowing other transactions to modify different rows simultaneously.
Q Can SQL Injection occur in ORMs like SQLAlchemy?
Standard ORM methods (like session.query(User).filter_by(name=user_input)) use parameterized queries automatically and are completely immune to SQLi. However, if you execute raw SQL strings via text(f"..."), SQLi vulnerabilities can still occur.