How do you use SQLite safely and quickly: bound parameters, indexes, and schema migrations?

13.2.2 · L5 · Interview

Predict first

A hostile string is bound as a parameter. What does the table hold afterwards?

import Foundation
import SQLite3

func openDB(_ path: String) -> OpaquePointer { var db: OpaquePointer?; precondition(sqlite3_open(path, &db) == SQLITE_OK); return db! }
func run(_ db: OpaquePointer, _ sql: String) -> Int32 { sqlite3_exec(db, sql, nil, nil, nil) }
func scalar(_ db: OpaquePointer, _ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, sql, -1, &st, nil)
    defer { sqlite3_finalize(st) }
    guard sqlite3_step(st) == SQLITE_ROW, let c = sqlite3_column_text(st, 0) else { return "nil" }
    return String(cString: c)
}
let db = openDB(NSTemporaryDirectory() + "t13d-\(getpid()).sqlite")
_ = run(db, "CREATE TABLE a(id INTEGER PRIMARY KEY, n TEXT)")
var st: OpaquePointer?
sqlite3_prepare_v2(db, "INSERT INTO a(n) VALUES(?)", -1, &st, nil)
sqlite3_bind_text(st, 1, "x'); DROP TABLE a;--", -1, unsafeBitCast(-1, to: sqlite3_destructor_type.self))
sqlite3_step(st); sqlite3_finalize(st)
print(scalar(db, "SELECT count(*) FROM a"), scalar(db, "SELECT n FROM a"))
sqlite3_close(db)

The table is dropped
1 x'); DROP TABLE a;--
0 nil

Predict first

What does the query planner say for an indexed equality lookup and for a leading-wildcard LIKE?

import Foundation
import SQLite3

func openDB(_ path: String) -> OpaquePointer { var db: OpaquePointer?; precondition(sqlite3_open(path, &db) == SQLITE_OK); return db! }
func run(_ db: OpaquePointer, _ sql: String) -> Int32 { sqlite3_exec(db, sql, nil, nil, nil) }
func scalar(_ db: OpaquePointer, _ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, sql, -1, &st, nil)
    defer { sqlite3_finalize(st) }
    guard sqlite3_step(st) == SQLITE_ROW, let c = sqlite3_column_text(st, 0) else { return "nil" }
    return String(cString: c)
}
let db = openDB(NSTemporaryDirectory() + "t13e-\(getpid()).sqlite")
_ = run(db, "CREATE TABLE a(id INTEGER PRIMARY KEY, n TEXT)")
_ = run(db, "CREATE INDEX ia ON a(n)")
func plan(_ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, "EXPLAIN QUERY PLAN " + sql, -1, &st, nil)
    var out = ""
    while sqlite3_step(st) == SQLITE_ROW { out += String(cString: sqlite3_column_text(st, 3)!) }
    sqlite3_finalize(st)
    return out
}
print(plan("SELECT * FROM a WHERE n = 'x'"))
print(plan("SELECT * FROM a WHERE n LIKE '%x'"))
sqlite3_close(db)

SEARCH a USING COVERING INDEX ia (n=?), then SCAN a
SCAN a, then SCAN a
SEARCH a USING INDEX ia, then SEARCH a USING INDEX ia

Predict first

The schema version is kept in user_version:

import Foundation
import SQLite3

func openDB(_ path: String) -> OpaquePointer { var db: OpaquePointer?; precondition(sqlite3_open(path, &db) == SQLITE_OK); return db! }
func run(_ db: OpaquePointer, _ sql: String) -> Int32 { sqlite3_exec(db, sql, nil, nil, nil) }
func scalar(_ db: OpaquePointer, _ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, sql, -1, &st, nil)
    defer { sqlite3_finalize(st) }
    guard sqlite3_step(st) == SQLITE_ROW, let c = sqlite3_column_text(st, 0) else { return "nil" }
    return String(cString: c)
}
let db = openDB(NSTemporaryDirectory() + "t13f-\(getpid()).sqlite")
print(scalar(db, "PRAGMA user_version"))
_ = run(db, "PRAGMA user_version = 2")
print(scalar(db, "PRAGMA user_version"), scalar(db, "PRAGMA integrity_check"))
sqlite3_close(db)

0, then 2 ok
1, then 2 ok
0, then 0 ok

Mental model. Bound parameters are filling in a form with the answers in separate boxes instead of rewriting the question each time. An index is the alphabetical tab on a phone book: fast if you search by name, useless if you search by the second letter of the street.

Bind parameters, never concatenate. Build statements with ? placeholders (sqlite3_prepare_v2, then sqlite3_bind_text, sqlite3_step, sqlite3_finalize), so values are sent separately from the SQL text. That prevents SQL injection (a name like x'); DROP TABLE a;-- is stored as plain text and the table survives) and lets SQLite reuse the compiled statement. Prepare once and rebind for loops. Wrapper libraries (GRDB, SQLite.swift) do this for you and add type-safe records, which is the main reason to use them rather than the C API directly. Use transactions around batches, keep a single serialised access path for writes (one connection or a queue), and use separate read connections in WAL mode.

Indexes make lookups fast but cost write time and space. Check what a query does with EXPLAIN QUERY PLAN: SEARCH a USING COVERING INDEX ia (n=?) means the index is used, while SCAN a means a full table scan. A pattern that starts with a wildcard (LIKE '%x') cannot use an ordinary index and scans every row, and applying a function to a column in the WHERE clause has the same effect; for text search use FTS5. Index the columns in WHERE, JOIN and ORDER BY of the hot queries, not every column. Migrations: store the schema version in PRAGMA user_version, and on launch run, inside one transaction, every migration step between the stored and the current version (if version < 2 { ALTER TABLE … }) and then set the new version. ALTER TABLE can add a column and rename things, but changing or dropping a column in older SQLite needs the copy dance (create new table, copy rows, drop old, rename). Make each step idempotent, test with a database from every shipped version, back up before a risky migration, and never edit an already-shipped migration; add a new one. Keep long migrations off the launch path, or show progress, because a migration that takes too long gets the app killed by the launch watchdog.

The parameter is data, not SQL, so the string is stored verbatim and nothing is dropped.

The equality uses the index. The leading wildcard cannot, so SQLite scans the whole table.

A new database has version 0. The value is a free header integer you set yourself, and the integrity check reports ok.

Common traps

Building SQL by string interpolation. It allows injection and defeats statement reuse; bind values.
Editing an old migration after it has shipped. Users on the old version skip it; add a new step instead.

They might follow up with

How do you see whether an index is used? EXPLAIN QUERY PLAN: SEARCH … USING INDEX versus SCAN.
Why does LIKE '%x' scan the table? A leading wildcard means no ordered prefix to look up.

How do you use SQLite safely and quickly: bound parameters, indexes, and schema migrations?

Predict first

Predict first

Predict first

Common traps

They might follow up with

Related questions