Parameterized Queries
Prevent SQL injection.
Parameterized Queries is a free Python Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Danger of String Building
Building SQL by concatenating strings is risky. If user input contains SQL, it can change your query. This is called SQL injection.
Never do this with untrusted data.
name = 'Alice'
# Unsafe pattern - do NOT do this
query = "SELECT * FROM users WHERE name = '" + name + "'"
print(query)What Injection Looks Like
Imagine a malicious value. Concatenating it changes the query meaning entirely.
name = "x' OR '1'='1"
query = "SELECT * FROM users WHERE name = '" + name + "'"
print(query)
print('The OR clause makes every row match!')Placeholders to the Rescue
The safe way is parameterized queries. Use ? as a placeholder and pass values as a tuple. The driver escapes them safely.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()Multiple Placeholders
Use one ? per value. Pass them in a tuple in the same order they appear.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()Single Value Tuples
A one-element tuple needs a trailing comma: ('Alice',). Without it, Python treats the parentheses as just grouping.
good = ('Alice',)
bad = ('Alice')
print(type(good), type(bad))Injection Attempt Blocked
With placeholders, a malicious value is treated as plain data, not SQL. It simply matches nothing.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
evil = "x' OR '1'='1"
cur.execute('SELECT * FROM users WHERE name = ?', (evil,))
print('Rows matched:', cur.fetchall())
conn.close()Named Placeholders
SQLite also supports named placeholders with :name syntax. Pass a dictionary of values.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute('INSERT INTO users (name, age) VALUES (:n, :a)', {'n': 'Carol', 'a': 40})
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()Parameters in WHERE
Placeholders work in any clause that takes a value, including WHERE.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))
cur.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
cur.execute('SELECT name FROM users WHERE age > ?', (28,))
print(cur.fetchall())
conn.close()executemany
To insert many rows, use executemany() with a list of tuples. It is faster and still safe.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
rows = [('Alice',), ('Bob',), ('Carol',)]
cur.executemany('INSERT INTO users (name) VALUES (?)', rows)
cur.execute('SELECT COUNT(*) FROM users')
print(cur.fetchone())
conn.close()Placeholders Are Not for Identifiers
Placeholders only work for values, not for table or column names. Those must come from a trusted whitelist in your own code.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
allowed = {'name', 'id'}
col = 'name'
if col in allowed:
cur.execute('SELECT ' + col + ' FROM users')
print('Safe column query ran')
conn.close()A Reusable Insert Function
Wrapping parameterized inserts in a function keeps your code clean and consistently safe.
import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
def add_user(cursor, name):
cursor.execute('INSERT INTO users (name) VALUES (?)', (name,))
add_user(cur, 'Dave')
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()Quick Check
Test your knowledge of safe queries.
Recap
You learned to write injection-safe queries.
- Never concatenate untrusted input into SQL
- Use
?placeholders with a tuple of values - Named
:nameplaceholders take a dict executemany()inserts many rows safely- Placeholders are for values, not identifiers
Frequently asked questions
Is the “Parameterized Queries” lesson free?
Yes — the full text of “Parameterized Queries” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.
What will I learn in “Parameterized Queries”?
Prevent SQL injection. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Python Academy?
No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Parameterized Queries” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Python Academy lesson?
Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.