skip to content

Limits, Extensions & Corruption

SQLite ships inside every phone with real limits: size caps, extensions such as FTS5 and JSON, no user model, and corruption when a file is copied or written unsafely.

on this pageshow

explore

questions

6

Why does SQLite have no CREATE USER, GRANT or REVOKE, and what decides who can read a SQLite database file?

level: juniorimportance: should knowfreq 45%

answer

  1. a library, not a server
  2. nothing to log in to
  3. an ordinary file on disk
  4. file permissions are the gate
  5. the authorizer limits SQL, not people

basics

~20 s

SQLite runs inside the application and reads an ordinary file, so there is no server login and no GRANT or REVOKE. Anyone the operating system lets open the file can read every table in it.

solid answer

~50 s

SQLite is a library linked into the application, reading and writing one ordinary file, so there is no server to authenticate against and no catalogue of users or roles. Its documentation lists `GRANT` and `REVOKE` as deliberately omitted: the only permissions that apply are the operating system's file permissions. Whoever can open the file can read every table, and with write access change any row. On a phone the app's private storage is the boundary, so anything that copies the file out, such as a device backup or a rooted device, exposes everything. Inside the app you can narrow what one connection may do with `sqlite3_set_authorizer()`, a read-only open or `PRAGMA query_only`, but those restrain your own code or untrusted SQL, not someone holding the file. Keeping data secret from that person needs file-level encryption, which the public-domain library does not include.

go deeper

for a junior

Recall that SQLite is a library reading one file: no server, no logins, no GRANT. Say plainly that file permissions decide access and that a copy of the file exposes every table.

for a middle

Explain what the in-library controls do: the authorizer callback, read-only opens, query_only and sqlite3_limit, and that each restrains SQL on one connection rather than identifying a caller.

for a senior

Show the threat model: backups, exported files and rooted devices carry the whole database, deleted rows may linger in free pages, and only file-level encryption protects against someone holding a copy.

for a principal

Frame the boundary decision: when per-user rights are a real requirement, the answer is a service in front of the file or a client-server database, not access control bolted onto an embedded library.

## Why there is nothing to log in to A client-server database runs as its own process. Clients connect over a socket, present a name and a password, and the server decides from its own catalogue what each **login** may touch. That catalogue is what `CREATE USER`, `GRANT` and `REVOKE` edit. **SQLite** has no such process. It is a library compiled into the application, and a query is a function call that reads and writes the pages of one ordinary file. There is no second party to authenticate to, so nobody exists to enforce a privilege. SQLite's own list of omitted SQL features names `GRANT` and `REVOKE`, explaining that the only access permissions that can be applied are the normal file permissions of the operating system, and that the commands would be meaningless for an embedded engine. They are not accepted and ignored: the parser rejects `GRANT SELECT ON message TO reporting` with `near "GRANT": syntax error`, and `CREATE USER` fails the same way. ## What actually decides who can read the file - **File permissions.** The operating system decides which processes may open the file for reading or writing. A process that can read it can read every table, index and schema entry; no row, column or table is hidden from it. - **Where the file lives.** On a phone, the app's private storage keeps other apps out. For most apps that directory is the whole security boundary. - **Every copy of the file.** A device backup, an exported debug bundle or a file pulled off a rooted device carries the complete database, and anyone can open an unencrypted copy with the `sqlite3` command-line tool. - **Deleted content.** With `PRAGMA secure_delete` off, which is the normal default, deleted rows can survive in free pages until they are reused. Turning it on makes SQLite overwrite deleted content with zeros, at some I/O cost. ## What the library can restrict, and what it cannot SQLite does offer controls, but every one of them acts on SQL running through a particular connection. None of them identifies a caller. | Mechanism | What it limits | What it does not stop | |---|---|---| | `sqlite3_set_authorizer()` callback | which actions SQL prepared on that connection may perform, such as `ATTACH`, `DROP TABLE` or reading a column; it answers `SQLITE_OK`, `SQLITE_IGNORE` or `SQLITE_DENY` | another process, or a person, opening the file directly | | Read-only open (`SQLITE_OPEN_READONLY`, or `mode=ro` in a URI filename) | writes through that connection | writes through any other connection | | `PRAGMA query_only=ON` | `CREATE`, `DELETE`, `DROP`, `INSERT` and `UPDATE` on that connection, which fail with `SQLITE_READONLY` | checkpoints and commits, which still run, and other connections | | `sqlite3_limit()` | how long or complex accepted SQL may be | reading the file at all | These are useful when the app runs SQL it did not write, for instance a power-user query box, or when you want a reporting connection that cannot change data even if a bug tries. They are defences against your own code and untrusted statements. They are not identity: SQLite never asks who is calling. ## Keeping the contents secret If the threat is someone who obtains a copy of the file, only the file's bytes matter: 1. Keep the database in the app's private storage, never in a shared or world-readable location. 2. Decide deliberately whether the file should be included in device backups; a backup is a copy. 3. Encrypt the file if its contents must stay secret from someone holding it. The public-domain library does not encrypt. The SQLite developers sell a drop-in encrypting version, the SQLite Encryption Extension, and other encrypting builds exist. 4. Keep the key outside the database file and outside the app's plain resources, or encryption only moves the problem. ## Where server-style access control belongs Per-user rights are a real requirement in some systems, and an embedded file cannot provide them. Two honest shapes exist: - **A service in front of the file.** One process owns the SQLite file, authenticates its clients and decides what each may do. The file is reachable only by that process. - **A client-server database.** When many people or services need different rights to the same data, a server with real logins and privileges is the right tool; how `GRANT`, roles and revocation work there is covered with the general material on relational access control. ## Common confusions - Believing `GRANT` is silently ignored. It is a syntax error, which at least fails loudly. - Treating the authorizer callback as authentication. It filters statements on one connection and knows nothing about users. - Assuming a read-only connection protects the data. It protects against writes through that handle only. - Assuming private storage means private forever. Every backup or exported copy carries the whole database, deleted content possibly included.

  • If a SQLite app lets power users type their own queries, how do you stop those queries from attaching other files or dropping tables?
    Register an authorizer with `sqlite3_set_authorizer()` on that connection and return `SQLITE_DENY` for `SQLITE_ATTACH`, `SQLITE_DROP_TABLE` and any other action the feature does not need; the statement is then rejected with an error when it is prepared. Lower limits such as `SQLITE_LIMIT_ATTACHED` with `sqlite3_limit()`, and run the feature on a read-only connection or with `PRAGMA query_only=ON`.
  • Does deleting a row in SQLite remove its text from the database file?
    Not necessarily. With `PRAGMA secure_delete` off, the normal default, freed content can remain in free pages until SQLite reuses them, so someone with a copy of the file may recover deleted messages with a page-level tool. Turning `secure_delete` on overwrites deleted content with zeros at some I/O cost, and `VACUUM` rebuilds the file without its free pages.

A SQLite file is like a paper notebook rather than a library with a desk clerk: nobody checks a card at the door, so whoever holds the notebook can read every page, and protection comes only from where you keep it or from writing it in cipher.

  • SQLite users are created with CREATE USER, as on a database server.
  • GRANT statements in SQLite are accepted and silently ignored.
  • Only the app that created a SQLite file is able to open it.
  • SQLite encrypts every database file by default.
  • sqlite3_set_authorizer() authenticates who is connecting to the database.
  • PRAGMA query_only stops other processes from writing the file.
open as a page

Which SQLite size limits can a phone app storing chat history actually hit, and what error does each one raise?

level: middleimportance: should knowfreq 35%

basics

~20 s

SQLite caps a string, a BLOB and a whole row at 1,000,000,000 bytes (SQLITE_TOOBIG), a table at 2000 columns, a statement at 32766 host parameters since 3.32.0, and the file at page size times max_page_count (SQLITE_FULL).

open as a page

In SQLite, how do you store and query a JSON settings value, and what changes when you store it as JSONB instead of text?

level: middleimportance: should knowfreq 40%

basics

~20 s

SQLite has no JSON type: it stores JSON as text and queries it with built-in functions and the -> and ->> operators. Since 3.45.0 it can also store JSONB, a binary BLOB those functions read without re-parsing.

open as a page

A phone app's crash reports show SQLite returning 'database disk image is malformed' for some users; what corrupts a SQLite file, and how do you confirm and recover it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

SQLite files corrupt when something outside SQLite breaks its rules: a file copied or restored without its -wal or journal, broken locks, two library copies in one process, or disabled syncs. Confirm with PRAGMA integrity_check; salvage with the CLI's .recover.

open as a page

Should a mobile app rely on the platform's bundled SQLite or ship its own SQLite build, and how would you decide?

level: principalimportance: should knowfreq 25%

basics

~20 s

Use the bundled SQLite when the oldest supported device's version and build options cover your features; ship your own when you need newer features or uniform behaviour, accepting the app size, the patching duty and the one-copy-per-process rule.

open as a page