SQLite Forum

Verifying schema for application file format
Login
I'm interested in using SQLite as the data store for a desktop application. When sharing application files, in this case SQLite DBs, how can I verify the schema of an untrusted DB matches what I expect from the schema version?

Placing constraints on columns is great both to catch application bugs and document the specifics of the format, but I can't rely on them, if an untrusted DB could exclude them from its schema. Even if I implement all the same constraints in application code, other operations could surely fail if the schema is not as expected. It'd be great to be able to fail with "invalid file" when the DB is first loaded if the schema is wrong.

The most obvious option would seem to be the sqlite_schema table - is this the definitive source of the schema, or could it be subverted by a malicious DB? Would subversion be caught by an integrity_check? Alternatively, is there some way I could verify the schemas of two DBs - an untrusted one and a temporary :memory: DB created as a template - are identical?

I hope my reasoning makes sense, thanks in advance for any assistance.