Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 33 ยท Using SQLite from Code

Parameters and SQL Injection

Learn how SQL injection works, why string building is dangerous, and how ? and :name placeholders keep SQLite queries safe, plus quoting identifiers.

Every program that talks to a database eventually meets user input: a search box, a login form, a URL parameter. The tempting way to use that input is to paste it into the SQL text. That one habit is behind SQL injection, one of the oldest and most damaging security bugs on the web. SQLite is embedded and has no network login, but your application still sends it text that someone else may have written, so it is just as exposed as any server database.

This lesson shows exactly how an injection works, then the fix: placeholders (? and :name) that send values separately from the SQL. You already met binding in SQLite from Python, JavaScript and Other Languages; here you will see why it matters and where it stops helping, such as table and column names.

Data is not code

String building Input becomes part of the SQL.
Binding Input stays a plain value.

The database never parses a bound value as SQL.

1 / 2

How an injection works

Imagine a login check that builds its query by gluing the typed name into the text:

TEXT
"SELECT id FROM users WHERE name = '" + name + "' AND pin = '" + pin + "'"

When someone types ana and 1234, the finished text is a perfectly normal query. When someone types ' OR 1=1 -- as the name, the quote in the input closes the string early and the rest becomes SQL: OR 1=1 is always true and -- comments out the pin check. The attacker never needed a password.

You can watch this happen without any application at all, because SQL itself can build strings with the || concatenation operator. The next example builds the query text the way a careless program would and prints it, for a normal input and a hostile one.

SQL Watch the query text change shape

Press Run (or Ctrl + Enter) to see the result here.

For the hostile row the built text is SELECT id FROM users WHERE name = '' OR 1=1 --' AND active = 1. The first quote ends the string, OR 1=1 makes the test always true, and -- turns the rest of the line, including the active = 1 check, into a comment. The intended query asked for one named, active user; the built one asks for everyone.

Seeing the damage on real data

SQLite cannot execute a string as SQL from inside SQL, so the next example runs the two resulting queries by hand. The first is what the program meant. The second is what the hostile input produced.

SQL The intended query and the injected query

Press Run (or Ctrl + Enter) to see the result here.

The first query returns one row. The second returns all four users, including the administrator and the inactive account. In a real attack the output of such a query is often what gets rendered back on screen or used to decide who is logged in.

The real thing, with Python

Python runs in your browser here with a genuine SQLite, so you can try both styles for real. The unsafe function glues the name into an f-string; the safe one uses a ? placeholder.

Python Unsafe string building versus a placeholder

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The unsafe function returns all four users for the attack string, even the inactive one, because the comment removed the active = 1 test, while the safe one returns an empty list: it searched for a user literally named ' OR 1=1 --, found none, and that is the correct answer. The Python module also refuses to run two statements through execute, so the DROP TABLE attempt fails with an error. That is a useful guard rail, but not a defense: other drivers allow multiple statements per call, and the first attack needed only one.

Placeholders: ? and :name

A placeholder is a hole in the SQL text that is filled later with a value. SQLite supports several styles, and drivers expose them in different ways:

Style Example Notes
? WHERE id = ? Positional, filled in order.
?NNN WHERE id = ?1 Numbered, can be reused.
:name WHERE id = :id Named, easiest to read.
@name, $name WHERE id = @id Alternative named forms.

The key point is how it works: SQLite parses and plans the statement first, with the holes still empty, and only then receives the values. A bound value is stored as a text, number or blob and is never parsed again, so no quote or semicolon in it can change the statement's structure.

You can try named parameters in a runnable form in Python. This also shows that values can be passed as a dictionary, so the order of the placeholders does not matter.

Python Named parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The apostrophe in O'Brien's mug needed no escaping at all. That is the other benefit of binding: names with quotes, emoji or newlines simply work.

You can only bind values: things that could be a literal, such as a number in a WHERE or a string in an INSERT. Table names, column names, ORDER BY direction and SQL keywords cannot be placeholders. The next sections cover those.

Escaping is not a safe replacement

A common myth says "I will just double the single quotes and then string building is fine." Doubling quotes (' becomes '') is exactly what SQL's own string literal syntax needs, and it does stop the simplest attack. But it is fragile:

  • It only protects values inside single quotes. A number placed into the text without quotes, such as WHERE id = followed by user input, is still injectable with no quote at all (1 OR 1=1).
  • Every place that builds SQL has to remember to escape. One forgotten place is a vulnerability.
  • Text from other encodings or layers (a backslash handling step, a framework that unescapes) can undo the escaping.
  • It does nothing for the structure of the query, such as identifiers.

Binding replaces all of this with one rule that is easy to audit: no user data is ever concatenated into SQL text. SQLite does offer quote() and format('%Q') to produce a correctly quoted literal, and they are useful for generating SQL scripts or debug output, but not as a substitute for binding in application code.

SQL quote() and %Q produce valid literals

Press Run (or Ctrl + Enter) to see the result here.

quote() returns text that is a valid SQL literal for its input: strings get single quotes with inner quotes doubled, numbers stay bare and NULL becomes the word NULL. %Q does the same inside format() and also adds the surrounding quotes, while %q only doubles the quotes and leaves you to add them.

Identifiers cannot be bound

Sometimes the table or column really is dynamic: a report where the user picks the sort column, or a tool that works on any table. A placeholder will not work here, because ORDER BY ? with the text 'price' sorts by the constant string, not by the column. The safe approach is an allow-list: a fixed set of known good names in your code, and the user's choice only picks from it.

Python
SORTABLE = {"name", "price", "created_at"}
column = request_value if request_value in SORTABLE else "name"
sql = f"SELECT name, price FROM products ORDER BY {column}"

Because column can only ever be one of three constants, nothing the user types reaches the SQL. If you must accept arbitrary identifiers, quote them with double quotes and double any double quote inside (" becomes ""), and still validate against the real schema (pragma_table_info lists the columns). The runnable example below shows the quoting rule and why it is still a last resort.

SQL Quoting an identifier

Press Run (or Ctrl + Enter) to see the result here.

Other defenses worth adding

Binding is the first line of defense, not the only one:

  • Least privilege. Open the database read-only (for example file:app.db?mode=ro with URI filenames) in code paths that only read. An injection into a read-only connection cannot delete anything.
  • Validate the shape of input. If a field must be a number, convert it to a number before using it. If it must be one of a few words, check it against a list.
  • Limit what you echo. Do not show raw database error messages to end users; log them instead.
  • Keep one statement per call. Prefer driver functions that run a single statement. Reserve the "run a whole script" function for your own migration files.
  • Do not trust stored data either. A value saved safely can still be dangerous if a later query concatenates it. This is called second-order injection, and binding fixes it the same way.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a bound parameter protect against SQL injection?

    The SQL structure is fixed before the value arrives, so quotes, semicolons or keywords inside the value can never change the statement.
  2. 2. Which part of a query can NOT be replaced by a placeholder?

    Placeholders stand for values only. Identifiers such as table and column names must be checked against an allow-list and then written into the SQL text.
  3. 3. A program builds WHERE id = + user_input with no quotes. Does doubling single quotes in the input make it safe?

    Quote escaping only protects text inside quotes. An unquoted number position needs binding or strict conversion to an integer.
  4. 4. In the hostile name ' OR 1=1 --, what does -- do?

    Two dashes start a line comment in SQL, so attackers use them to cut off the remainder of the original query, such as an extra condition.

Summary

  • SQL injection happens when user input is concatenated into SQL text so that it can change the query's structure.
  • Placeholders (?, ?NNN, :name, @name, $name) send values separately, so values never act as SQL. This also handles quotes in names for free.
  • Escaping quotes by hand is fragile; use binding for every value.
  • Identifiers and keywords cannot be bound: choose them from a fixed allow-list, or quote them correctly and validate against the schema.
  • Add depth with read-only connections, input validation, single-statement calls and no raw error messages for users.

Next: Backup, VACUUM and Maintenance, keeping your database file healthy and safely copied.