SQLite: Calling a C Library Directly from Objective-C
Use the SQLite C API directly from Objective-C to create tables, run SQL, and manage a database, and understand prepared statements, transactions, indexes, migrations, locking, parameter binding, and — most importantly — how parameter binding prevents SQL injection.
Way back in this course's earliest modules, you learned that Objective-C is a strict superset of C — every valid C program is valid Objective-C, and Objective-C simply layers an object-messaging system on top of that C foundation. SQLite is where that foundation pays off directly and concretely: SQLite is not an Objective-C library at all. It's a small, embedded, C library — a single .c/.h pair (or a system library on Apple platforms) exposing a set of plain C functions like sqlite3_open, sqlite3_exec, and sqlite3_prepare_v2. There is no bridging layer, no wrapper required to use it — you call these C functions straight out of an .m file, mixing raw C pointers and C strings alongside your Objective-C objects and messages in the very same method body. This lesson works through the whole SQLite C API used from Objective-C: creating tables and running SQL, compiling prepared statements for reuse, wrapping related writes in transactions, adding indexes, evolving your schema over time with migrations, understanding how SQLite's file-level locking affects concurrent access, binding parameters safely into prepared statements, and — because this is serious enough to deserve its own section — using that parameter binding to eliminate SQL injection entirely.
SQLite as a C Library, Called from Objective-C — Tables and SQL
Every persistent app needs somewhere to keep structured data between launches, and SQLite is the most common answer on Apple platforms below the level of Core Data (which, as you'll see in the next two lessons, actually uses SQLite as its default storage engine underneath). SQLite implements an entire relational database — tables, SQL parsing, a query engine, transactions, indexes — inside a single embedded library with no separate server process; the 'database' is just a file on disk that your process opens directly. On iOS and macOS, that library ships as part of the system and you link against libsqlite3.tbd and #import <sqlite3.h> to get its declarations.
Because SQLite's public interface is plain C, every function you call returns a plain C integer result code, and every 'object' it hands you is an opaque C pointer, not an Objective-C object. A database connection is represented by sqlite3 *, and a compiled, ready-to-run statement is represented by sqlite3_stmt *. You open a connection with sqlite3_open(path, &db), and you must eventually close it with sqlite3_close(db). There is no automatic reference counting here — db and any sqlite3_stmt * you create are C resources with manual lifetimes, exactly the discipline the earliest C modules in this course established for malloc/free: every resource you acquire, you are responsible for releasing.
Almost every SQLite function returns an integer status code, and SQLITE_OK (which is 0) means success — anything else means something went wrong, and you should check it. This is a very different error-handling style from Objective-C's NSError ** out-parameter convention you've used with Foundation APIs: SQLite predates that convention entirely, and mixing the two styles in the same method — checking a plain C integer return code right next to handling an NSError * from a Foundation call — is exactly the kind of low-level/high-level blend this module is about. Getting a human-readable description of the last error on a connection uses sqlite3_errmsg(db), which returns a const char *, a raw C string, that you'll typically wrap in an NSString with stringWithUTF8String: to log or display it.
Before you can store anything, you define a table with CREATE TABLE, a SQL statement describing the table's name, its columns, and each column's type and constraints. A typical users table might be CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER): id is an autoincrementing primary key SQLite manages for you, name is required text, and age is an optional integer. The IF NOT EXISTS clause makes the statement safe to run every time your app launches — it's a no-op if the table already exists, rather than an error.
For a one-shot statement with no parameters and no results you care about row-by-row, sqlite3_exec(db, sql, callback, context, &errmsg) is the simplest way to run SQL: it takes the connection, the SQL text as a C string, an optional callback function invoked once per result row, an optional context pointer passed through to that callback, and an out-parameter for an error message. For CREATE TABLE, INSERT, UPDATE, and DELETE statements that don't need to return rows to you programmatically, sqlite3_exec with a NULL callback is often the most direct option.
The four basic CRUD operations map directly onto SQL statements: INSERT INTO users (name, age) VALUES (...) adds a row, SELECT id, name, age FROM users WHERE age > 18 reads rows matching a condition, UPDATE users SET age = ... WHERE id = ... modifies existing rows, and DELETE FROM users WHERE id = ... removes them. Reading results back — as opposed to just running a statement — is where sqlite3_exec's callback style gets awkward for real apps, which is exactly why the next section introduces prepared statements: a more structured, safer, and reusable way to run SQL, especially SQL that reads results or takes parameters.
Prepared Statements: Compile Once, Run Many Times
A prepared statement is SQL text that SQLite has already parsed and compiled into an internal, ready-to-execute form, represented by a sqlite3_stmt * handle. You create one with sqlite3_prepare_v2(db, sql, -1, &stmt, NULL), passing the connection, the SQL text (with ? placeholders anywhere a value will be supplied later), -1 to let SQLite read the SQL string up to its null terminator, an out-parameter that receives the compiled statement handle, and an unused tail pointer (typically NULL for a single statement). Once compiled, you execute it with sqlite3_step(stmt), which advances execution and returns SQLITE_ROW if a result row is available, SQLITE_DONE when execution has finished with no more rows, or an error code.
The real payoff of preparing a statement is reuse: parsing and query-planning SQL text has real cost, and if you're going to run essentially the same statement repeatedly — the same INSERT INTO users (name, age) VALUES (?, ?) for a hundred different rows, say — compiling it once with sqlite3_prepare_v2 and then calling sqlite3_reset(stmt) plus rebinding new parameter values between each execution is dramatically cheaper than calling sqlite3_exec a hundred times with a hundred freshly-built SQL strings. sqlite3_reset puts an already-executed statement back to its initial state so it can be stepped through again (typically after rebinding new parameter values), while sqlite3_finalize(stmt) permanently destroys the compiled statement and must be called exactly once when you're done with it, or you leak the resources SQLite allocated for it.
For SELECT queries, you step through result rows one at a time: each call to sqlite3_step that returns SQLITE_ROW means one row is available, and you read its columns with functions like sqlite3_column_int(stmt, columnIndex) or sqlite3_column_text(stmt, columnIndex), using a zero-based column index matching the order of columns in your SELECT list. You keep calling sqlite3_step in a loop until it returns SQLITE_DONE, at which point there are no more rows and the loop ends.
Transactions: Batching Writes for Atomicity and Speed
A transaction groups multiple SQL statements into a single all-or-nothing unit: you start one with BEGIN (or BEGIN TRANSACTION), run any number of statements, and then either COMMIT to make every change in the transaction permanent at once, or ROLLBACK to discard every change in the transaction as if none of it had happened. This matters for correctness whenever several statements need to succeed or fail together — transferring a value between two rows, for instance, should never leave the database with the debit applied but not the credit, and wrapping both statements in a transaction guarantees that either both happen or neither does.
Transactions also matter enormously for raw performance, and this is easy to underestimate. By default, SQLite runs in 'auto-commit' mode: every single statement you execute outside an explicit transaction is its own implicit transaction, and each one forces SQLite to flush its changes to disk (via fsync or equivalent) before returning, to guarantee durability. That per-statement disk sync is relatively expensive. If you insert a thousand rows as a thousand separate auto-committed INSERT statements, you pay that disk-sync cost a thousand times over. Wrap the same thousand inserts in a single BEGIN ... COMMIT pair, and SQLite only has to sync once, at the commit — the difference is routinely an order of magnitude or more in elapsed time for bulk writes.
The practical pattern is straightforward: call sqlite3_exec(db, "BEGIN", NULL, NULL, NULL) before a batch of related writes, run all the prepared-statement executions and resets for that batch, and finish with sqlite3_exec(db, "COMMIT", NULL, NULL, NULL) — or, if any step fails partway through, sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL) to back out everything done so far in that transaction rather than leaving the database in a half-updated state.
Indexes: Trading Storage and Write Speed for Read Speed
Without an index, finding rows matching a condition — SELECT * FROM users WHERE name = 'Alice' — requires SQLite to scan every single row in the table, checking each one against the condition; this is called a full table scan, and its cost grows linearly with the table's size. An index, created with CREATE INDEX idx_users_name ON users(name), builds a separate, sorted lookup structure (a B-tree, in SQLite's implementation) mapping column values to the rows that contain them, so a lookup by that column becomes roughly logarithmic in cost instead of linear — dramatically faster on any table with more than a handful of rows.
That speedup is not free. Every index SQLite maintains has to be updated on every INSERT, UPDATE, or DELETE that touches the indexed column, so more indexes mean slower writes, and each index also consumes additional storage on disk. The practical guideline is to index columns you actually filter or sort on frequently — a WHERE clause or ORDER BY on that column in a hot code path — and not to reflexively index every column 'just in case', since an unused index is pure overhead: write cost and storage spent for no corresponding read benefit.
A composite index, covering more than one column (CREATE INDEX idx_users_name_age ON users(name, age)), can speed up queries that filter on both columns together, or that filter on the leading column alone, but it generally does not help a query that filters only on the trailing column without the leading one. Choosing which columns to index, and in what order, is a real design decision that should follow from the actual query patterns your app runs, not be guessed at up front.
Migrations: Evolving a Schema Across App Versions
An app's database schema is rarely static — a later version adds a column, a new table, or an index that an earlier version's database file doesn't have. A migration is the process of transforming an existing database file, in place, from an older schema shape to a newer one, without losing the user's existing data. This is unavoidable the moment your app has shipped once and needs to change its schema for a subsequent release; you cannot simply drop and recreate the database, because that would discard everything the user has already stored.
SQLite's ALTER TABLE supports a useful but limited set of direct schema changes — most commonly adding a new column with ALTER TABLE users ADD COLUMN email TEXT, which is fast and safe because SQLite doesn't have to rewrite existing rows. It does not support directly dropping a column, renaming a column in every SQLite version, or changing a column's type — for those, the standard workaround is to create a new table with the desired schema, copy the data across with an INSERT INTO ... SELECT ..., drop the old table, and rename the new one into its place, all inside a transaction so the process is atomic.
The common, robust pattern for managing this over an app's lifetime is a versioned migration script list: you keep a PRAGMA user_version integer stored in the database file itself (SQLite provides this pragma specifically for application use), and at launch you read the current stored version, compare it against the schema version your current app code expects, and run each migration step in order — version 1-to-2, then 2-to-3, and so on — until the database is caught up, updating PRAGMA user_version as you go. This lets a user who skips several app versions at once still migrate correctly, one incremental step at a time, rather than requiring every possible upgrade path to be handled as a special case.
Database Locking: SQLite's Concurrency Model
SQLite is a file-based, embedded database with no separate server process arbitrating access, so it enforces safe concurrent access itself, at the file level, using locks. In its default rollback-journal mode, SQLite uses a hierarchy of lock states on the database file — SHARED (multiple readers may hold this simultaneously), RESERVED, PENDING, and EXCLUSIVE (only one writer may hold this, and it blocks all readers while held) — meaning that, by default, only one write can be in progress against the database file at any given moment, and a write in progress can block readers.
This has direct, practical consequences for an app that touches the same database file from multiple threads (or, less commonly, multiple processes): a write happening on a background thread can cause a SELECT running concurrently on another thread to fail with SQLITE_BUSY, meaning the database was locked and the operation couldn't proceed immediately. Your code has to be prepared to handle that — either by retrying after a short delay, or, more commonly in an Objective-C app, by funneling all access to a given sqlite3 * connection through a single serial dispatch queue, so that only one thread ever touches that connection at a time and lock contention within your own app simply cannot occur.
SQLite also offers Write-Ahead Logging (WAL) mode, enabled with PRAGMA journal_mode=WAL, which changes the locking model so that readers generally do not block writers and a writer does not block readers, at the cost of an extra -wal file alongside your main database file and some additional bookkeeping (periodic 'checkpointing' back into the main file). WAL mode is the common choice for apps that need better concurrent read/write behavior than the default rollback-journal mode provides, and it's worth knowing it exists even though the default mode is a reasonable starting point for a simple app with light concurrent access.
Parameter Binding: Filling in a Prepared Statement's Placeholders
When you prepare a statement containing ? placeholders — INSERT INTO users (name, age) VALUES (?, ?) — you supply the actual values afterward with the sqlite3_bind_* family of functions, one call per placeholder, addressed by its 1-based position in the statement. sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT) binds a C string into the first ?; sqlite3_bind_int(stmt, 2, age) binds an integer into the second. There are corresponding functions for other types — sqlite3_bind_double for floating-point values, sqlite3_bind_int64 for 64-bit integers, sqlite3_bind_null for an explicit NULL, and sqlite3_bind_blob for raw binary data.
That SQLITE_TRANSIENT argument in the text-binding call matters and is easy to get wrong: it tells SQLite to make its own private copy of the string data immediately, because SQLite cannot know how long the C string you passed in will remain valid — if it came from a temporary buffer, an autoreleased NSString's UTF-8 representation, or a value that will be freed before sqlite3_step actually runs, SQLite needs its own copy to be safe. The alternative, SQLITE_STATIC, tells SQLite the pointer is guaranteed to remain valid for as long as the statement needs it (a string literal, for instance) and it need not copy it — using SQLITE_STATIC for a pointer that isn't actually guaranteed to outlive the call is a genuine, if subtle, source of memory bugs.
Once every placeholder is bound, you call sqlite3_step(stmt) to actually execute the statement with those bound values, then sqlite3_reset(stmt) to prepare the same compiled statement for another round of binding and execution — this reset-and-rebind cycle is exactly the reuse pattern described in the prepared statements section, and it's what makes binding, rather than rebuilding a fresh SQL string every time, the standard and efficient way to run the same statement shape repeatedly with different data.
Injection Protection: Why You Always Bind, Never Concatenate
SQL injection is what happens when untrusted input — text typed by a user, or data pulled from any source you don't fully control — is spliced directly into a SQL string that then gets executed. If your query is built by string concatenation, an attacker who controls part of that input can inject their own SQL fragments that change the statement's meaning entirely: adding a OR '1'='1' to bypass a WHERE clause meant to restrict results, or appending an entirely separate destructive statement. This is not a theoretical concern — it is one of the most common and most damaging vulnerability classes in software that touches a database, and it applies just as much to a local SQLite database inside a mobile app (protecting against a compromised or malicious input source, or defense in depth) as it does to a server-side database exposed to the open internet.
The rule is absolute and has no legitimate exceptions in application code: never build a SQL statement by concatenating or interpolating untrusted values directly into the SQL text. Every value that comes from outside your own hard-coded SQL — user input, data from a file, data from a network response — must be passed in through sqlite3_bind_* parameter binding on a prepared statement, using ? placeholders, never string formatting like stringWithFormat:. When a value is bound as a parameter, SQLite treats it strictly as data, never as SQL syntax to be parsed — there is no way for a bound string, however malicious its contents, to change the structure of the statement it's bound into, because the statement's structure was already fixed at sqlite3_prepare_v2 time, before any value was ever supplied.
The two examples in this lesson's code below show the same operation — looking up a user by a name value that originated outside your code — done the vulnerable way and the safe way, side by side. The vulnerable version builds its WHERE clause with stringWithFormat:, splicing the raw name string into the SQL text; the safe version prepares a statement with a ? placeholder and binds the same name as a parameter. Both compile and both work correctly for ordinary input — the difference only becomes visible, and catastrophic, the moment the input itself contains SQL syntax, which is exactly why relying on 'it works with the input I tested' is not a defense.
Core SQLite C API Calls Used from Objective-C
Every sqlite3_* call returns a C integer result code — check it against SQLITE_OK/SQLITE_ROW/SQLITE_DONE. sqlite3_prepare_v2 compiles SQL text with ? placeholders once; sqlite3_bind_* fills them in before each sqlite3_step. Always sqlite3_finalize a statement and sqlite3_close a connection you opened — these are manually managed C resources.
sqlite3 *db;
sqlite3_open("path/to/db.sqlite", &db); // open (or create) a database file
sqlite3_exec(db, "CREATE TABLE ...", NULL, NULL, &err); // run SQL with no bound params
sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, "INSERT INTO t VALUES (?, ?)", -1, &stmt, NULL); // compile once
sqlite3_bind_text(stmt, 1, cString, -1, SQLITE_TRANSIENT); // bind param 1
sqlite3_bind_int(stmt, 2, someInt); // bind param 2
sqlite3_step(stmt); // execute; returns SQLITE_ROW, SQLITE_DONE, or an error code
sqlite3_reset(stmt); // reuse the compiled statement with new bound values
sqlite3_finalize(stmt); // destroy the compiled statement when fully done with it
sqlite3_close(db); // close the connectionCode Examples
This method opens (or creates) a SQLite file at path with sqlite3_open, checking the returned status code against SQLITE_OK before doing anything else. It then runs a CREATE TABLE IF NOT EXISTS statement with sqlite3_exec, using a NULL row callback since this statement produces no result rows. Note the raw C string handling throughout: [path UTF8String] converts the Objective-C NSString into a C string SQLite can accept, and sqlite3_errmsg/errMsg come back as raw const char */char * that must be freed with sqlite3_free when SQLite allocated them.
if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { This is the prepared-statement-plus-transaction pattern from this lesson combined into one method: sqlite3_prepare_v2 compiles the INSERT statement exactly once, outside the loop, and the loop then binds fresh values and calls sqlite3_step/sqlite3_reset for each user, reusing the same compiled statement rather than re-preparing SQL text on every iteration. The whole batch is wrapped in BEGIN/COMMIT so all the inserts are flushed to disk together in a single sync, not one at a time — for a hundred or a thousand users, this is dramatically faster than auto-committing every individual INSERT. If any insert in the loop fails, a production version would typically call ROLLBACK instead of COMMIT at the end, so the batch remains all-or-nothing rather than partially applied.
if (sqlite3_step(stmt) != SQLITE_DONE) { The vulnerable version builds the entire WHERE clause with stringWithFormat:, treating the untrusted name as raw text that becomes part of the SQL grammar itself — an attacker-supplied value containing SQL syntax (a stray quote and an OR '1'='1') changes what the statement matches. The safe version prepares a statement with a ? placeholder, fixing the statement's structure before any value is supplied, then binds untrustedName as data with sqlite3_bind_text. Because binding never re-parses the value as SQL, the exact same malicious string is harmless in the safe version — it's compared as a literal name that happens not to exist, nothing more.
Common mistake: Using stringWithFormat:/string concatenation to build SQL containing any value that isn't a hard-coded literal is the single most common and most dangerous SQLite mistake — always use a ? placeholder and sqlite3_bind_* instead.
#import <sqlite3.h>
- (sqlite3 *)openDatabaseAtPath:(NSString *)path {
sqlite3 *db = NULL;
if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {
NSLog(@"Failed to open database: %s", sqlite3_errmsg(db));
return NULL;
}
const char *createSQL =
"CREATE TABLE IF NOT EXISTS users ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name TEXT NOT NULL, "
"age INTEGER)";
char *errMsg = NULL;
if (sqlite3_exec(db, createSQL, NULL, NULL, &errMsg) != SQLITE_OK) {
NSLog(@"Failed to create table: %s", errMsg);
sqlite3_free(errMsg);
}
return db;
}- (void)insertUsers:(NSArray<NSDictionary *> *)users intoDatabase:(sqlite3 *)db {
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
sqlite3_stmt *stmt;
const char *insertSQL = "INSERT INTO users (name, age) VALUES (?, ?)";
if (sqlite3_prepare_v2(db, insertSQL, -1, &stmt, NULL) != SQLITE_OK) {
NSLog(@"Prepare failed: %s", sqlite3_errmsg(db));
sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
return;
}
for (NSDictionary *user in users) {
NSString *name = user[@"name"];
NSInteger age = [user[@"age"] integerValue];
sqlite3_bind_text(stmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(stmt, 2, (int)age);
if (sqlite3_step(stmt) != SQLITE_DONE) {
NSLog(@"Insert failed: %s", sqlite3_errmsg(db));
}
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}// VULNERABLE — never do this: the raw, untrusted name is spliced directly into SQL text.
- (void)findUserVulnerable:(NSString *)untrustedName inDatabase:(sqlite3 *)db {
NSString *sql = [NSString stringWithFormat:
@"SELECT id, name FROM users WHERE name = '%@'", untrustedName];
// If untrustedName is: Alice' OR '1'='1
// the executed SQL becomes:
// SELECT id, name FROM users WHERE name = 'Alice' OR '1'='1'
// which matches every row in the table, not just "Alice".
sqlite3_exec(db, [sql UTF8String], someCallback, NULL, NULL);
}
// SAFE — the same lookup using a prepared statement and bound parameter.
- (void)findUserSafe:(NSString *)untrustedName inDatabase:(sqlite3 *)db {
sqlite3_stmt *stmt;
const char *sql = "SELECT id, name FROM users WHERE name = ?";
if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [untrustedName UTF8String], -1, SQLITE_TRANSIENT);
while (sqlite3_step(stmt) == SQLITE_ROW) {
// untrustedName is always treated as a literal string value here,
// never as SQL syntax — "Alice' OR '1'='1" simply fails to match any row.
}
s
…SQLite in Production Apps
SQLite is one of the most widely deployed pieces of software in existence — it ships inside every iOS and Android device, every major web browser, and countless embedded systems, and it is the storage engine Core Data itself uses by default under the hood, as you'll see in the next two lessons. Apps that need fine-grained control over their SQL, or that are porting an existing schema from another platform, often talk to SQLite directly through this C API (frequently wrapped in a thin Objective-C convenience layer they write once and reuse) rather than adopting Core Data's higher-level object-graph model — trading Core Data's conveniences (faulting, change tracking, undo) for direct control over exactly what SQL runs and when.
NSString *sql = [NSString stringWithFormat:@"DELETE FROM users WHERE id = %@", userSuppliedId];
sqlite3_prepare_v2(db, "DELETE FROM users WHERE id = ?", -1, &stmt, NULL); sqlite3_bind_int(stmt, 1, userSuppliedId.intValue);
Concatenating any non-hard-coded value into SQL text opens the door to SQL injection. Always compile the statement with a ? placeholder and bind the value — the statement's structure is then fixed before the value is ever supplied.
Calling sqlite3_prepare_v2 fresh inside a loop that runs the same INSERT a thousand times, one prepare per iteration.
Call sqlite3_prepare_v2 once, before the loop, then call sqlite3_bind_*, sqlite3_step, and sqlite3_reset for each iteration, reusing the same compiled sqlite3_stmt *.
Re-preparing identical SQL on every iteration re-parses and re-plans the same statement over and over for no benefit; preparing once and resetting/rebinding is the entire point of prepared statements.
Running a thousand individual INSERT statements outside any explicit transaction and expecting it to be fast.
Wrap the batch in sqlite3_exec(db, "BEGIN", ...) ... sqlite3_exec(db, "COMMIT", ...) so SQLite only performs one disk sync for the whole batch instead of one per statement.
In SQLite's default auto-commit mode, each statement outside an explicit transaction is its own transaction with its own disk sync — batching writes inside one transaction is often an order of magnitude faster.
Forgetting to call sqlite3_finalize on a sqlite3_stmt *, or sqlite3_close on a sqlite3 * connection, once you're done with it.
Always finalize every prepared statement and close every connection you open, exactly once, even along error-handling paths.
sqlite3_stmt * and sqlite3 * are manually managed C resources with no automatic reference counting — leaving them unfinalized/unclosed leaks resources and, for a connection, can leave the database file locked.
What kind of library is SQLite, from Objective-C's perspective? A. A pure Objective-C framework with only NSObject-based APIs B. A plain C library exposing C functions like sqlite3_open and sqlite3_exec directly C. A Swift-only package with no C interoperability D. A remote database server accessed over a network socket
B. A plain C library exposing C functions like sqlite3_open and sqlite3_exec directly — SQLite's public interface is plain C — functions, C structs, and opaque C pointer handles — called directly from Objective-C source without any bridging layer, exactly the way any C function can be called from an Objective-C superset file.
Why does batching many INSERT statements inside a single BEGIN/COMMIT transaction dramatically improve performance? A. It compresses the SQL text before sending it to SQLite B. It skips SQLite's SQL parser entirely for statements inside a transaction C. It reduces the number of disk syncs from one per auto-committed statement to one for the whole batch D. It disables SQLite's locking so no waiting occurs
C. It reduces the number of disk syncs from one per auto-committed statement to one for the whole batch — In auto-commit mode, each statement outside an explicit transaction is its own transaction and forces its own disk sync; wrapping a batch in BEGIN/COMMIT reduces that to a single sync for the entire batch.
What does sqlite3_prepare_v2 do? A. Executes a SQL statement immediately and returns its result rows B. Compiles SQL text (with ? placeholders) into a reusable sqlite3_stmt * without running it yet C. Opens a new database connection D. Permanently destroys a previously compiled statement
B. Compiles SQL text (with ? placeholders) into a reusable sqlite3_stmt * without running it yet — sqlite3_prepare_v2 parses and compiles SQL text into an internal, ready-to-execute form referenced by a sqlite3_stmt * handle; sqlite3_step is what actually executes it.
Why is building SQL with stringWithFormat: to insert an untrusted value dangerous? A. stringWithFormat: is slower than sqlite3_bind_text B. A value containing SQL syntax can change the meaning of the executed statement — SQL injection C. stringWithFormat: cannot produce valid UTF-8 strings D. It causes a compiler error in Objective-C
B. A value containing SQL syntax can change the meaning of the executed statement — SQL injection — Concatenating untrusted input directly into SQL text lets that input's content alter the statement's structure (for example, injecting OR '1'='1'); parameter binding avoids this because bound values are always treated strictly as data, never as SQL syntax.
What does SQLite's default locking model mean for concurrent access from multiple threads? A. Any number of threads can write to the database simultaneously with no restriction B. Only one write can be in progress at a time, and a write in progress can block concurrent reads C. SQLite automatically spawns a separate process per thread to avoid contention D. Locking only applies when using WAL mode, never by default
B. Only one write can be in progress at a time, and a write in progress can block concurrent reads — By default, SQLite's file-level lock hierarchy allows only one writer at a time, and while a write holds an exclusive lock, concurrent readers can be blocked and receive SQLITE_BUSY.
What is the standard versioned-migration pattern for evolving a SQLite schema across app releases? A. Delete the old database file and recreate it fresh on every app update B. Store a schema version (e.g. via PRAGMA user_version), and run each incremental migration script in order until the stored version matches the app's expected version C. Rely on sqlite3_open to automatically detect and apply schema changes D. Avoid changing the schema after the first release entirely
B. Store a schema version (e.g. via PRAGMA user_version), and run each incremental migration script in order until the stored version matches the app's expected version — Tracking the current schema version (commonly with PRAGMA user_version) and applying each migration step in sequence lets a database catch up correctly even if a user skips several app versions at once, without discarding existing data.
Write an Objective-C method that takes an untrusted NSString search term and an open sqlite3 * connection, and returns an NSArray of matching user names from a users table, using a prepared statement with a bound parameter (not string concatenation) for a case-insensitive partial match with SQL's LIKE operator.
sqlite3_stmt *stmt; sqlite3_prepare_v2(db, "SELECT name FROM users WHERE name LIKE ?", -1, &stmt, NULL); NSString *pattern = [NSString stringWithFormat:@"%%%@%%", searchTerm]; sqlite3_bind_text(stmt, 1, [pattern UTF8String], -1, SQLITE_TRANSIENT); NSMutableArray *results = [NSMutableArray array]; while (sqlite3_step(stmt) == SQLITE_ROW) { const char *name = (const char *)sqlite3_column_text(stmt, 0); [results addObject:[NSString stringWithUTF8String:name]]; } sqlite3_finalize(stmt); return results;
Prepare SELECT name FROM users WHERE name LIKE ?, and build the wildcard pattern (e.g. by wrapping the term in %...%) into the bound parameter's value itself, not into the SQL text — the % wildcards belong in the data you bind, not in string-formatted SQL.