--- key: "sql-injection-python" title: "Fix SQL injection in Python sqlite3" url: "https://softwaresecurity.com/domains/appsec/secure-coding/sql-injection-python-sqlite3/" api: "https://softwaresecurity.com/api/v1/articles/sql-injection-python.json" domain: "appsec" topic: "Application security" type: "Recipe" identifiers: ["CWE-89","A05:2025","V1.2.4","CAPEC-66","WSTG-INPV-05"] tags: ["Injection","Python"] stages: ["build"] roles: ["developer","appsec-engineer"] license: "CC-BY-4.0" license_url: "https://creativecommons.org/licenses/by/4.0/" code_license: "MIT" --- # Fix SQL injection in Python sqlite3 Bind query values separately from SQL syntax, then prove the fix preserves legitimate behavior and blocks an injection probe. Canonical URL: https://softwaresecurity.com/domains/appsec/secure-coding/sql-injection-python-sqlite3/ ## In short Never build SQL text from input. Keep the statement constant and pass values separately: `con.execute("SELECT id FROM accounts WHERE owner = ?", (owner,))`. The driver sends the value as data, so a quote or `OR 1=1` cannot change the query. Binding covers values only: map sort columns and other identifiers through an allowlist, and still check that the caller may read the rows the query selects. - Use `?` or `:name` placeholders with a separate parameter sequence; `(owner,)` needs its trailing comma. - Do not quote the placeholder or pre-escape quotes: `O'Connor` is valid data that binding preserves. - Identifiers and keywords cannot be bound: map a sort choice such as `newest` to a fixed fragment such as `id DESC`. - For an `IN` list, generate one placeholder per element, bind every element and cap the list length. - Binding stops injection, not unauthorized reads: keep the owner or tenant condition in the query. ## Verify it 1. Run the program and confirm it prints `PASS: injection blocked; legitimate lookup preserved`. 2. Assert that the probe `' OR 1=1 --` returns `[]` from the fixed lookup while `alice` returns `[(1,)]`. 3. Insert an `O'Connor` row and assert the fixed lookup returns it unchanged. 4. Search for `execute(` calls whose SQL is built with `+`, f-strings, `%` or `.format()` and replace each with placeholders. 5. Check that every user-selected column, direction or table name passes through an allowlist map before it reaches SQL. ## What you will learn - Explain how concatenation changes SQL grammar. - Use sqlite3 binding without corrupting valid values. - Distinguish value binding, identifier selection and object authorization. Audience: Python developers fixing a SQL injection finding, and reviewers checking that the fix keeps legitimate lookups working. Before you start: - Read a Python function, a SELECT query, and a bound parameter tuple. Time: About 27 minutes: 7 to read, 3 on the code and 17 for the exercise ## Understand why a value becomes SQL syntax 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. ## Run the vulnerable and repaired lookup 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. ```python 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() ``` ## Use the same binding in other drivers 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. ```java 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 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 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); } } ``` | Stack | Placeholder | Bound call | | --- | --- | --- | | Python sqlite3 | `?` or `:owner` | `con.execute(sql, (owner,))` | | Python psycopg (PostgreSQL) | `%s` or `%(owner)s` | `cur.execute(sql, (owner,))` | | SQLAlchemy | `:owner` | `conn.execute(text(sql), {"owner": owner})` | | Java JDBC | `?` | `ps.setString(1, owner)` on a `PreparedStatement` | | .NET SqlClient | `@owner` | `cmd.Parameters.Add("@owner", SqlDbType.NVarChar, 100).Value = owner` | | Node.js pg | `$1` | `client.query(sql, [owner])` | | Go database/sql | `$1` (PostgreSQL) or `?` (MySQL, SQLite) | `db.QueryContext(ctx, sql, owner)` | | PHP PDO | `:owner` | `$stmt->execute(['owner' => $owner])` | | Ruby on Rails | `?` or a hash condition | `Account.where(owner: owner)` | ## Interpret each result and strengthen the regression 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. | Input in the original fixture | Vulnerable result | Fixed result | | --- | --- | --- | | `alice` | `[(1,)]` | `[(1,)]` | | `' OR 1=1 --` | `[(1,), (2,)]` | `[]` | | `O'Connor` | SQL syntax error | `[]` | | `nobody` | `[]` | `[]` | ## Handle identifiers, authorization and later reuse 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. ## A real-world failure 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. ## Extend the binding rule to variable-length filters 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. ## Guided practice Run the complete sqlite3 program above, then reason about one extension. ### Supplied material ```text Existing rows: (1, alice), (2, bob). Additional row for step 3: (3, O'Connor). Authenticated caller: alice. Requested sort choices (three separate inputs): S1=newest S2=owner S3=owner; DROP TABLE accounts Allowed map: newest => id DESC; owner => owner ASC. ``` ### 1. Predict the SQL grammar Expand the vulnerable query for the supplied probe before running the file. Mark the string close, new predicate and comment. Worked answer: The condition becomes `owner = '' OR 1=1`, with the final quote commented out. Both ids qualify. The issue does not require a second SQL statement. ### 2. Execute the regression Save the complete code block as `sql_binding_demo.py` and run `python3 sql_binding_demo.py`. Compare the printed result with the table. Worked answer: The program prints `PASS: injection blocked; legitimate lookup preserved`. A failing assertion identifies a behavior change; a syntax error indicates the pasted program or local interpreter needs correction. ### 3. Prove quotes survive Before the print line, add `con.execute("INSERT INTO accounts VALUES (?, ?)", (3, "O'Connor"))` and assert `fixed("O'Connor") == [(3,)]`. Run the file again. Worked answer: The same PASS message appears. A quote is preserved as data in both `INSERT` and `SELECT`; removing or doubling it in application input would corrupt this behavior. ### 4. Separate two remaining decisions Evaluate the three supplied sort strings against the map, then decide whether Alice may request `fixed("bob")`. Worked answer: `newest` selects `id DESC` and `owner` selects `owner ASC`. The entire third string is unknown and must be rejected. `fixed("bob")` correctly returns `[(2,)]` at the database level but the application must deny Alice if the rule permits only her own account. Deliverable: A passing local program and four explicit explanations: vulnerable grammar, binding, legitimate quote handling, and the separate authorization rule. Scope: Requires Python 3 with its standard-library sqlite3 module. The program creates only an in-memory database and two fictional accounts. ## Executable practice - [SQL injection lab: Separate SQL structure from data with sqlite3](https://softwaresecurity.com/labs/sql-injection-sqlite/) Articles and teaching material: CC BY 4.0. Original executable code: MIT. Attribution: Software Security. ## Sources - [Python sqlite3: parameter substitution](https://docs.python.org/3/library/sqlite3.html). Python 3.15. - [OWASP SQL Injection Prevention](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html) - [MITRE CWE-89: SQL injection](https://cwe.mitre.org/data/definitions/89.html). CWE 4.20. - [OWASP Query Parameterization Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html) - [Java SE 21: java.sql.PreparedStatement](https://docs.oracle.com/en/java/javase/21/docs/api/java.sql/java/sql/PreparedStatement.html). Java SE 21. - [psycopg 3: passing parameters to SQL queries](https://www.psycopg.org/psycopg3/docs/basic/params.html) - [NVD: CVE-2023-34362, SQL injection in MOVEit Transfer](https://nvd.nist.gov/vuln/detail/cve-2023-34362)