23

In the code below, pathToNonDatabase is the path to a simple text file, not a real sqlite database. I was hoping for sqlite3_open to detect that, but it doesn't (db is not NULL, and result is SQLITE_OK). So, how to detect that a file is not a valid sqlite database?

sqlite3 *db = NULL;
int result = sqlite3_open(pathToNonDatabase, &db);

if((NULL==db) || (result!=SQLITE_OK)) { 
   // invalid database
}
apalopohapa
  • 4,983
  • 5
  • 27
  • 29

4 Answers4

25

sqlite opens databases lazily. Just do something immediately after opening that requires it to be a database.

The best is probably pragma schema_version;.

  • This will report 0 if the database hasn't been created (for instance, an empty file). In this case, it's safe work with (and run CREATE TABLE, etc)
  • If the database has been created, it will return how many revisions the schema has gone through. This value might not be interesting, but that it's not zero is.
  • If the file exists and isn't a database (or empty), you'll get an error.

If you want a somewhat more thorough check, you can use pragma quick_check;. This is a lighter-weight integrity check, which skips checking that the contents of the tables line up with the indexes. It can still be very slow.

Avoid integrity_check. It not only checks every page, but then verifies the contents of the tables against the indexes. This is positively glacial on a large database.

Steven Fisher
  • 44,462
  • 20
  • 138
  • 192
  • 1
    "pragma schema_version;" throws "database is locked" error sometimes. I'll give the "pragma quick_check;" a try – Camilo Sanchez Mar 13 '14 at 02:13
  • 1
    If your database is locked, it's locked. Everything will fail. Try again when it isn't locked. :) – Steven Fisher Mar 13 '14 at 02:25
  • You're right. Everything fails when the database is locked. Even selects. In my case I wanted to determine if the file was a Sqlite3 database. If I get the "database is locked" error I think it is safe to assume the file is a Sqlite3 database. – Camilo Sanchez Mar 13 '14 at 02:38
  • This doesn't work for an encrypted database (e.g. using SQLCipher). – Iulian Onofrei Jan 16 '17 at 09:00
  • No, it won't, unless you unlock it with the same key. That's how SQLite was designed, and SQLCipher is a hack on that. – Steven Fisher Jan 16 '17 at 22:17
  • Using `pragma schema_version;` is a solution of the question. For an existing text file I get `sqlite3.DatabaseError: file is not a database` (python sqlite3). However, for a non existing file, `sqlite.connect()` creates a database for you, which may not be what you want. The check also comes back "OK" when the file is empty. For these cases, I ended up using OS file checks. – ynux Sep 17 '19 at 11:13
9

For anyone needing to do this in C# with System.Data.SQLite you can start a transaction, and then immediately roll it back as follows:-

    private bool DatabaseIsValid(string filename)
    {
        using (SQLiteConnection db = new SQLiteConnection(@"Data Source=" + filename + ";FailIfMissing=True;"))
        {
            try
            {
                db.Open();
                using (var transaction = db.BeginTransaction())
                {
                    transaction.Rollback();
                }
            }
            catch (Exception ex)
            {
                log.Debug(ex.Message, ex);
                return false;
            }
        }
        return true;
    }

If the file is not a valid database the following SQLiteException is thrown - file is encrypted or is not a database (System.Data.SQLite.SQLiteErrorCode.NotADb). If you aren't using encrypted databases then this solution should be sufficient. (Only the 'db.Open()' was required for version 1.0.81.0 of System.Data.SQLite but when I upgraded to version 1.0.91.0 I had to insert the inner using block to get it to work).

The Lonely Coder
  • 613
  • 9
  • 12
4

I think a pragma integrity_check; could do it.

adius
  • 13,685
  • 7
  • 45
  • 46
Benoit
  • 76,634
  • 23
  • 210
  • 236
1

If you want only to check if the file is a valid sqlite database then you can check with this function:

    private bool CheckIfValidSQLiteDatabase(string databaseFilePath)
    {
        byte[] bytes = new byte[16];
        using (FileStream fileStream = new FileStream(databaseFilePath, FileMode.Open, FileAccess.Read))
        {
            fileStream.Read(bytes, 0, 16);
        }
        string gg = System.Text.ASCIIEncoding.ASCII.GetString(bytes);
        return gg.Contains("SQLite format");
    }

as stated in the documentation: sqlite database header

IamJose
  • 81
  • 8
  • This will detect if the file is something else than a sqlite database file, but it will not detect a broken sqlite database file. – Prof. Falken Feb 22 '23 at 00:25