FlawOpen

flawopen.com/sql-injection/Swift

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in Swift (SQLite3, GRDB)

Swift string interpolation such as "... = \(id)" inside sqlite3_prepare_v2(), GRDB fetch or execute calls lets untrusted input rewrite your SQL and return every row (CWE-89). The bound form for the SQLite C API and GRDB, why the same \() syntax is safe in a GRDB SQL literal, and how to sort by a chosen column.

💡 Plain English Explainer (ELI5)

Think of a tailor’s paper pattern: measurements belong in marked spaces, but a customer cannot add a new cutting instruction by writing into one. Swift string interpolation creates a finished SQL sentence before SQLite parses it, so text can become SQL instructions. sqlite3_prepare_v2() followed by sqlite3_bind_*() keeps the statement and values apart; GRDB fetchAll(sql:arguments:) and execute(sql:arguments:) do too. GRDB’s typed SQL literal is a special pattern that binds interpolated values, unlike an ordinary String. Since column names cannot be bound, choose ORDER BY from a switch of constant statements.

Core Concepts & Subsystem Terms

\(value) interpolation in SQL
"... WHERE id = \(userId)" builds an ordinary String before any database API sees it. Swift's type system cannot tell SQL text from data once they are one String, so the compiler gives no warning.
sqlite3_prepare_v2 + sqlite3_bind_*
sqlite3_prepare_v2 compiles only the first statement and leaves ?1 slots; sqlite3_bind_int64 or sqlite3_bind_text fills them. When binding a Swift String with sqlite3_bind_text, pass SQLITE_TRANSIENT (defined as unsafeBitCast(-1, to: sqlite3_destructor_type.self)) so SQLite copies the bytes.
GRDB arguments:
GRDB's fetchAll(db, sql:, arguments:) and execute(sql:, arguments:) bind StatementArguments to ? or :name markers. execute(sql:) also runs every statement in the string, so an interpolated value can append a second statement. SQLite.swift's prepare(sql, bindings...) works the same way.
GRDB SQL interpolation
GRDB can build requests from string literals typed as SQLRequest or SQL: let request: SQLRequest<Row> = "... WHERE status = \(status)" turns each interpolated value into a bound argument. The same \(status) in a plain String passed to sql: is pasted in. The syntax is identical; the declared type decides the safety.
switch over sort keys
Identifiers cannot be bound. A switch that returns one of a few complete, constant statements guarantees that only strings written in the source code reach SQLite.

Framework mappings

Step-by-Step Attack Flow

Step 1

Untrusted input reaches the data layer

A universal link, a push-notification payload or a JSON field from your API supplies userId, which is passed to findEmail() as a String.

Step 2

Interpolation builds the SQL

"... WHERE id = \(userId)" evaluates first. With the benign test value 0 OR 1=1, the statement reads WHERE id = 0 OR 1=1.

Step 3

SQLite compiles the attacker's text

sqlite3_prepare_v2 receives a finished statement with no slots to bind, so SQLite parses OR 1=1 as part of the WHERE clause.

Step 4

Every row, or an extra statement, runs

The lookup returns rows it should not. Through GRDB's execute(sql:) it is worse: a ; in the value starts a second statement, which also runs.

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
// UserStore.swift: each query below interpolates input into the SQL text
import GRDB
import SQLite3

// SQLite C API: \() builds the finished statement before prepare
func findEmail(db: OpaquePointer?, userId: String) -> String? {
    var stmt: OpaquePointer?
    let sql = "SELECT email FROM users WHERE id = \(userId)"
    guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else { return nil }
    defer { sqlite3_finalize(stmt) }
    guard sqlite3_step(stmt) == SQLITE_ROW, let text = sqlite3_column_text(stmt, 0) else { return nil }
    return String(cString: text)
}

// GRDB: a plain String passed to sql: is sent as written
func findOrders(_ db: Database, status: String) throws -> [Row] {
    try Row.fetchAll(db, sql: "SELECT id FROM orders WHERE status = '\(status)'")
}

// execute(sql:) runs every statement in the string
func revokeSessions(_ db: Database, userId: String) throws {
    try db.execute(sql: "UPDATE sessions SET revoked = 1 WHERE user_id = '\(userId)'")
}

// ORDER BY column taken straight from the caller
func listUsers(_ db: Database, sortColumn: String) throws -> [Row] {
    try Row.fetchAll(db, sql: "SELECT id, email FROM users ORDER BY \(sortColumn)")
}
HARDENED SECURE PATCH
// UserStore.swift: values are bound, statements are constants
import GRDB
import SQLite3

// SQLite C API: ?1 compiled first, value bound as a 64-bit integer
func findEmail(db: OpaquePointer?, userId: Int64) -> String? {
    var stmt: OpaquePointer?
    guard sqlite3_prepare_v2(db, "SELECT email FROM users WHERE id = ?1", -1, &stmt, nil) == SQLITE_OK else { return nil }
    defer { sqlite3_finalize(stmt) }
    sqlite3_bind_int64(stmt, 1, userId)
    guard sqlite3_step(stmt) == SQLITE_ROW, let text = sqlite3_column_text(stmt, 0) else { return nil }
    return String(cString: text)
}

// GRDB: ? marker, value passed in arguments
func findOrders(_ db: Database, status: String) throws -> [Row] {
    try Row.fetchAll(db, sql: "SELECT id FROM orders WHERE status = ?", arguments: [status])
}

// execute with arguments binds the value to ?
func revokeSessions(_ db: Database, userId: String) throws {
    try db.execute(sql: "UPDATE sessions SET revoked = 1 WHERE user_id = ?", arguments: [userId])
}

// switch picks one of three constant statements
func listUsers(_ db: Database, sortKey: String) throws -> [Row] {
    let sql: String
    switch sortKey {
    case "email": sql = "SELECT id, email FROM users ORDER BY email"
    case "created": sql = "SELECT id, email FROM users ORDER BY created_at"
    default: sql = "SELECT id, email FROM users ORDER BY id"
    }
    return try Row.fetchAll(db, sql: sql)
}

Engineering & System Hardening Checklist

Sources