Fix SQL injection in Python sqlite3

The failure starts before SQLite looks up an account. The vulnerable function builds one string containing both the developer's query and the supplied owner. For alice, the resulting condition is owner = 'alice'. For the supplied probe, the first quote closes the string literal, OR introduces another condition, 1=1 makes it true for every row, and the two hyphens comment out the trailing quote. The database faithfully executes the resulting program. It cannot infer which characters the programmer intended as data.

This exercise uses Python's standard-library sqlite3 module, an in-memory database and two fictional accounts. No server, credentials or third-party package is required. Its narrow security property is that a supplied owner cannot change the query's grammar. It does not establish that the caller is entitled to select that owner. Alice supplying the ordinary text bob is an authorization problem even after SQL injection is fixed. Keep those two assertions separate when reviewing a patch.

Copy the entire block into a file named sql_binding_demo.py and run python3 sql_binding_demo.py. The vulnerable helper is a local demonstration only. The repaired helper sends fixed SQL text and a separate sequence of values through the database driver's binding interface. SQLite treats the probe as one text value, so the OR and comment characters have no syntactic role. There is no account whose literal owner equals that complete string, so the answer is an empty list. The lab at /labs/sql-injection-sqlite/ has you make the same repair, plus a literal LIKE search, in the browser.

The trailing comma in (owner,) forms a one-element tuple. A parenthesized string is still a string and may be interpreted as a sequence of parameters. Do not quote the placeholder: WHERE owner = '?' compares against the literal question mark rather than binding the supplied owner. Do not pre-escape quotes either; that changes legitimate stored values and recreates a second, inconsistent encoding scheme.

import sqlite3

con = sqlite3.connect(":memory:")
try:
    con.execute("CREATE TABLE accounts (id INTEGER, owner TEXT)")
    con.executemany("INSERT INTO accounts VALUES (?, ?)",
                    [(1, "alice"), (2, "bob")])

    def vulnerable(owner):  # Demonstration only; do not deploy.
        sql = "SELECT id FROM accounts WHERE owner = '" + owner + "'"
        return con.execute(sql).fetchall()

    def fixed(owner):
        return con.execute(
            "SELECT id FROM accounts WHERE owner = ?", (owner,)
        ).fetchall()

    probe = "' OR 1=1 --"
    assert len(vulnerable(probe)) == 2
    assert fixed(probe) == []
    assert fixed("alice") == [(1,)]
    assert fixed("nobody") == []
    assert fixed("O'Connor") == []
    print("PASS: injection blocked; legitimate lookup preserved")
finally:
    con.close()

Every mainstream database API has a prepared-statement or parameter interface; only the placeholder syntax changes. The rule is identical in each: the SQL text is a constant written by the developer, and each value is passed through the driver as a separate argument. Never format the values into the string yourself, even with a driver that uses %s: psycopg replaces %s safely only when the values go in the second argument of execute. /fix/sql-injection/ shows the same repair for each common framework.

The Java program below is the JDBC version of the same lookup, run against an in-memory SQLite database through the org.xerial:sqlite-jdbc driver. PreparedStatement sends the statement and the owner separately, so the probe matches nothing and O'Connor keeps its quote. ORM escape hatches need the same care: Django raw() and RawSQL, SQLAlchemy text() and Hibernate query strings are safe with bound parameters and injectable when built by concatenation. A stored procedure that assembles dynamic SQL with string concatenation inside the database reintroduces the flaw even when the call itself is parameterized.

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AccountLookup {
    // The SQL text is a constant; the owner travels separately as a bound value.
    static List<Integer> idsForOwner(Connection con, String owner) throws SQLException {
        String sql = "SELECT id FROM accounts WHERE owner = ? ORDER BY id";
        try (PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, owner);
            try (ResultSet rs = ps.executeQuery()) {
                List<Integer> ids = new ArrayList<>();
                while (rs.next()) ids.add(rs.getInt("id"));
                return ids;
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        // Needs a JDBC driver on the classpath, for example org.xerial:sqlite-jdbc.
        try (Connection con = DriverManager.getConnection("jdbc:sqlite::memory:");
             Statement st = con.createStatement()) {
            st.execute("CREATE TABLE accounts (id INTEGER, owner TEXT)");
            st.execute("INSERT INTO accounts VALUES (1, 'alice'), (2, 'bob'), (3, 'O''Connor')");
            check(idsForOwner(con, "' OR 1=1 --").isEmpty(), "probe is data");
            check(idsForOwner(con, "alice").equals(List.of(1)), "alice");
            check(idsForOwner(con, "O'Connor").equals(List.of(3)), "quote preserved");
            System.out.println("PASS: PreparedStatement binds the owner as data");
        }
    }

    static void check(boolean ok, String what) {
        if (!ok) throw new AssertionError(what);
    }
}

The first assertion demonstrates impact: the probe obtains two rows from a lookup that should match an owner. The fixed assertions then test the unwanted behavior and ordinary behavior independently. Seeing no exception is insufficient; a filter that rejects every request would also stop this probe. Alice must still produce [(1,)], while nobody and the literal probe produce []. O'Connor is a useful boundary because a quote is valid data, not evidence that the person is attacking the application.

To distinguish quote handling from a missing-record result, insert an O'Connor account and assert that the fixed lookup retrieves it. Also test the empty string deliberately: an application may reject it as invalid input, while SQL binding itself correctly compares it as data. If a test fails, inspect the statement shape and parameter count before adding a character blacklist. In a real service, record a request identifier and database error category without logging passwords, full query values or an unrestricted stack trace.

Binding applies to values, not identifiers or SQL keywords. A user-selected sort field needs a mapping such as newest → created_at DESC and owner → owner ASC, with only developer-authored fragments inserted into the statement. Binding ORDER BY ? would sort by a constant value rather than select a column. Reject an unknown sort choice before executing the query. For an IN list, generate the required number of placeholders and bind every element; define empty-list behavior explicitly rather than constructing invalid SQL.

Stored text stays untrusted when reused. A safely inserted display name can become a second-order injection if an export later concatenates it into a new query. Follow the value through each execution call. Finally, derive tenant and caller constraints from authenticated server context and include them in the lookup. Use a database identity with only necessary permissions; this limits damage if another route is vulnerable but cannot repair a broken lookup. The runnable example proves its local binding property; other routes and queries need their own tests.

In May and June 2023 the Cl0p group mass-exploited CVE-2023-34362, an unauthenticated SQL injection in Progress MOVEit Transfer, to steal data from thousands of organizations. The missing control was exactly what this recipe enforces: passing values as parameters instead of building SQL from strings.

A common follow-up is selecting several ids. If the approved id list is [1, 2], the statement shape can be WHERE id IN (?, ?) with parameters (1, 2). The program constructs only the count of placeholders, never the submitted values. If the list is empty, return an empty result or deliberately skip the query according to the API contract; do not interpolate an empty string and hope every database interprets IN () identically. Cap the list length as a separate resource rule. Binding ten thousand values may preserve grammar while still consuming unacceptable work.

LIKE introduces another distinction. Binding the value % is safe from SQL injection, but % retains its documented wildcard meaning inside a LIKE expression. If the feature promises a literal substring search, the application must apply the database's supported wildcard-escaping convention and specify the matching ESCAPE behavior. If wildcard search is intentional, retain it and limit result count. A bound value's inability to rewrite SQL does not mean that every value has the same business meaning.

Finally, distinguish SELECT permissions from write permissions. An UPDATE accounts SET owner=? WHERE id=? is bound correctly but can still transfer ownership if id is caller-controlled and the actor is unauthorized. Require the permitted owner or tenant constraint, then inspect the affected-row result. A regression for grammar safety and a regression for ownership safety should fail for different reasons so later maintainers can see which property a change breaks.

Repair the code

SQL injection lab: Separate SQL structure from data with sqlite3

Sources & further reading

The standards, specifications and documentation behind the guidance above. Each is the evidence for a claim on this page; consult it for detail beyond the defaults and versions listed here.

1. Python sqlite3: parameter substitution. Python 3.15.
2. OWASP SQL Injection Prevention
3. MITRE CWE-89: SQL injection. CWE 4.20.
4. OWASP Query Parameterization Cheat Sheet
5. Java SE 21: java.sql.PreparedStatement. Java SE 21.
6. psycopg 3: passing parameters to SQL queries
7. NVD: CVE-2023-34362, SQL injection in MOVEit Transfer

Reference information

Tags

InjectionPython

SDLC phase

Development

Role

DevelopersAppSec engineers

ASVS 5.0.0 requirements

v5.0.0-1.2.4

CAPEC attack patterns

• CAPEC-66 at MITRE

OWASP WSTG v4.2 tests

WSTG-INPV-05

Download this guide as Markdown

Continue exploring