SQLite Binding Parameters: ?, :name, and Safe Values

Binding Is How Values Get Into a Prepared Statement

A prepared statement is SQL with holes in it. Binding is the act of filling those holes with values — safely, one by one, through the driver's API rather than by mashing strings together.

The shape always looks the same: write the SQL with placeholders, then hand the values over separately.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
);

-- The SQL the database sees:
INSERT INTO users (name, email) VALUES (?, ?);

In the CLI you can't really demonstrate binding (the shell has no app code attached), but the SQL above is exactly what your application sends. The ? marks are placeholders. Your driver — sqlite3 in Python, better-sqlite3 in Node, rusqlite in Rust — fills them in via a separate bind call.

The mental model: the SQL is the recipe, the bound values are the ingredients. They never touch.

Positional Placeholders: ?

The simplest placeholder is ?. Each one matches the next value you bind, in order.

INSERT INTO users (name, email) VALUES (?, ?);

In Python that's:

cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Rosa", "rosa@example.com"),
)

The first ? gets "Rosa", the second gets "rosa@example.com". Pass too few or too many values and the driver raises an error before the statement runs.

You can also number them explicitly with ?1, ?2, ?3 — useful when the same value appears more than once:

SELECT ?1 AS greeting, ?1 AS still_the_same;

?1 reuses the first bound value. Without numbering, you'd have to bind the same value twice.

Named Placeholders: :name

Once a statement has more than two or three holes, positional binding turns into a guessing game. Named parameters fix that:

INSERT INTO users (name, email)
VALUES (:name, :email);

In Python:

cursor.execute(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    {"name": "Boris", "email": "boris@example.com"},
)

The order of keys in the dictionary doesn't matter — only the names do. SQLite also accepts @name and $name as alternative prefixes; they all behave the same way. :name is by far the most common.

Named parameters pay off the moment you have an UPDATE with five columns, or a query that uses the same value in WHERE and RETURNING.

Binding NULL

The right way to insert NULL is to pass your language's null value through the binding API. The driver handles the translation:

INSERT INTO users (name, email) VALUES (?, ?);
-- Bind: ("Cyrus", None)   in Python
-- Bind: ["Cyrus", null]   in Node

SELECT id, name, email FROM users;

None, null, nil, whatever your language calls it — the driver turns it into a real SQL NULL. Don't bind the string "NULL"; that stores the four-character text "NULL". And don't interpolate the word NULL into the SQL text — that defeats binding entirely.

The same rule covers numbers, blobs, dates: pass the native value, let the driver bind it.

Reusing a Statement With Different Values

Binding pairs naturally with prepared statements. Prepare once, bind-and-execute many times. The parser does its work a single time and the database reuses the compiled plan for every bound set of values.

INSERT INTO users (name, email) VALUES (?, ?);
-- Bind ("Ada",   "ada@example.com")    -> execute
-- Bind ("Boris", "boris@example.com")  -> execute
-- Bind ("Cyrus", NULL)                 -> execute

SELECT id, name, email FROM users ORDER BY id;

Most drivers wrap this in an executemany (Python) or a .run() loop (Node). Either way, what you save is the parsing cost — which is small per statement but real when you're inserting thousands of rows.

Don't Mix Styles in One Statement

SQLite technically allows positional and named placeholders in the same statement. Resist it.

-- Legal but a footgun:
INSERT INTO users (name, email) VALUES (?, :email);

A reader has to mentally track two binding APIs at once, and most drivers don't support the mixed form cleanly. Pick one style per statement: ? for one or two values, :name for everything else.

A Common Pitfall: Binding Is Not String Formatting

The whole point of binding is that values don't go through SQL parsing. Compare these two Python lines:

# Wrong — string formatting:
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# Right — parameter binding:
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

The first line builds SQL by concatenation. If name is "'; DROP TABLE users; --", the database happily parses and runs the injected statement. The second line sends the SQL and the value through different channels — the value is bound as a string, full stop, no matter what characters it contains. That's why every guide tells you to bind: it's not about style, it's about what the parser sees.

We'll dig into the injection side of this on the next page.

Another Pitfall: You Can't Bind Identifiers

Placeholders work for values — strings, numbers, blobs, NULLs. They don't work for table names, column names, or SQL keywords:

-- This does NOT do what you want:
SELECT * FROM ? WHERE id = ?;
-- The first ? binds as a string literal, not a table name.

If you genuinely need a dynamic table or column name (rare in application code), validate it against an allow-list and concatenate it into the SQL yourself — never directly from user input. For everything else, bind.

A Worked Example

Putting the pieces together — a small users table written and read entirely through bindings:

CREATE TABLE users (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    email   TEXT,
    active  INTEGER NOT NULL DEFAULT 1
);

-- Application would bind these values; shown here as literals:
INSERT INTO users (name, email)        VALUES ('Ada',   'ada@example.com');
INSERT INTO users (name, email)        VALUES ('Boris', 'boris@example.com');
INSERT INTO users (name, email, active) VALUES ('Cyrus', NULL, 0);

-- Real query a driver would run, with :name and :active bound:
SELECT id, name, email
FROM users
WHERE active = :active AND name LIKE :name
ORDER BY id;

In real code the INSERTs and the SELECT would all use placeholders. The CLI just doesn't have an app to bind from, so the literals stand in for what binding produces.

Next: Preventing SQL Injection

Binding parameters is the mechanism. Why it stops SQL injection — and the handful of places where binding alone isn't enough — is what the next page is about.