Preventing SQL Injection in SQLite: Parameterized Queries

SQL Injection Is a String-Building Bug

SQL injection happens when user input ends up as part of the SQL text your database parses. Once that line blurs — once a value the user typed becomes syntax the database executes — the user can do anything you can do.

Here's the classic anti-pattern, in pseudocode any language can produce:

-- DON'T DO THIS
query = "SELECT * FROM users WHERE name = '" + user_input + "'"

If user_input is Ada, you get a normal lookup. If user_input is ' OR 1=1 --, you get:

SELECT * FROM users WHERE name = '' OR 1=1 --'

The -- comments out the trailing quote, OR 1=1 matches every row, and the attacker just dumped your user table. Worse versions chain ; and a second statement to drop tables, exfiltrate data, or insert a new admin account.

The vulnerability isn't in SQLite. It's in the code that built that string.

Parameterized Queries: the Actual Fix

A parameterized query separates the SQL text from the values. The SQL has placeholders — ? or :name — and you pass the values alongside. SQLite parses and compiles the SQL once, then binds your values into the compiled plan. The values cannot become SQL.

Run a vulnerable-looking lookup the safe way:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT);
INSERT INTO users (name, role) VALUES ('Ada', 'admin'), ('Boris', 'user'), ('Rosa', 'user');

-- Imagine the value after = came from a user. Bound, it's just text.
SELECT * FROM users WHERE name = 'Ada';

In the SQLite shell you literally type the value, but in your application code the equivalent looks like this (Python's sqlite3 driver):

# Python — parameterized, safe
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

Pass the SQL and the tuple of values as two separate arguments. The driver sends them to SQLite separately. Even if user_input is ' OR 1=1 --, SQLite looks for a user literally named ' OR 1=1 -- and finds none.

What "Safe" Actually Means Here

The safety isn't pattern-matching or escaping. It's structural. SQLite compiles the statement into an internal form before it ever sees your value:

-- The compiled statement has a slot, not a string.
SELECT * FROM users WHERE name = ?
                                 ^
                                 placeholder slot

When you bind a value, it goes into that slot as a typed datum — TEXT, INTEGER, BLOB, whatever. SQLite never re-parses it as SQL. There is no syntax for the attacker to inject because the parser already finished its job.

That's why parameterized queries are reliable in a way escaping never is. Escaping tries to scrub dangerous characters out of a string. Binding never builds the dangerous string in the first place.

Don't Reach for String Formatting

Every language has a tempting shortcut — f-strings in Python, template literals in JavaScript, String.format in Java — and every one of them is a foot-gun for SQL.

# DON'T — f-string interpolates the value into the SQL text
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# DON'T — same problem, % formatting
cursor.execute("SELECT * FROM users WHERE name = '%s'" % user_input)

# DO — placeholder + values argument
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

The first two splice user input into the SQL string before the driver ever sees it. By the time SQLite gets the query, the damage is done. The third keeps the SQL and the value in different lanes.

This rule is mechanical: if you ever feel yourself building a SQL string with +, f-strings, format, or template literals where a value goes — stop and use a placeholder instead.

Multiple Parameters and Named Placeholders

Real queries usually have more than one value. SQLite supports both positional ? and named :name placeholders:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total REAL, status TEXT);
INSERT INTO orders (customer, total, status) VALUES
  ('Ada', 120.0, 'paid'),
  ('Boris', 45.0, 'pending'),
  ('Rosa', 89.5, 'paid');

-- Positional: order matters
SELECT * FROM orders WHERE customer = 'Ada' AND status = 'paid';

-- Named: order doesn't matter
SELECT * FROM orders WHERE total > 50 AND status = 'paid';

In application code those translate to:

# Positional
cursor.execute(
    "SELECT * FROM orders WHERE customer = ? AND status = ?",
    ("Ada", "paid"),
)

# Named — clearer when there are several parameters
cursor.execute(
    "SELECT * FROM orders WHERE total > :min_total AND status = :status",
    {"min_total": 50, "status": "paid"},
)

Named parameters scale better. Once you're past three or four values, ?, ?, ?, ? becomes a guessing game; :customer, :total, :status, :created_at is self-documenting.

Identifiers Need a Different Approach

Bound parameters only work for values — the things on the right side of =, inside IN (...), in VALUES (...). They do not work for table names, column names, or SQL keywords like ASC/DESC.

-- This does NOT work. The placeholder can't stand in for a column name.
SELECT * FROM users ORDER BY ? ASC

If you need a dynamic identifier — say, letting a user choose which column to sort by — validate against an allowlist before building the SQL:

# Allowlist approach
ALLOWED_SORT_COLUMNS = {"name", "created_at", "role"}

if sort_column not in ALLOWED_SORT_COLUMNS:
    raise ValueError(f"Invalid sort column: {sort_column}")

query = f"SELECT * FROM users ORDER BY {sort_column} ASC"
cursor.execute(query)

The user-supplied string is checked against a fixed set of known-safe values before it gets near the SQL. The f-string is only acceptable here because sort_column can no longer be anything except one of three hardcoded names.

A Concrete Injection Attempt, Defused

Let's see both versions side by side with a hostile input. Set up a small users table:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
  ('Ada', 'ada@example.com'),
  ('Boris', 'boris@example.com'),
  ('Rosa', 'rosa@example.com');

-- The attacker submits this as their "name":
--   ' OR 1=1 --
-- A vulnerable app would build:
SELECT * FROM users WHERE name = '' OR 1=1 --';

-- A parameterized app would send:
SELECT * FROM users WHERE name = ''' OR 1=1 --';

The vulnerable form returns every user. The parameterized form looks for a user literally named ' OR 1=1 -- and returns nothing. Same input, totally different outcome — because in the second case the value never became SQL.

A Short Checklist

• Use ? or :name placeholders for every value that comes from outside your code — user input, request bodies, environment variables, anything you didn't write inline.
• Never build SQL with +, f-strings, or format where a value goes.
• For dynamic table or column names, validate against a fixed allowlist before splicing into the query.
• Trust the driver. Don't write your own quote-escaping function. The bound-parameter machinery is older, more battle-tested, and right.
• Code review your team's queries with one question: is any user input being concatenated into SQL text? If yes, fix it.

Get this habit into your fingers and SQL injection stops being a class of bug you have to think about.

Next: Connecting From Applications

You've seen the safe shape of a query — placeholder in the SQL, value passed alongside. The next page walks through actually wiring up SQLite from real application code in Python, Node.js, and a few others, including connection management and where parameterized queries fit into a typical request flow.