SQLite3 opens a file that is not a real database - why?

Question

Using SQLite3 v3.41.0.

Rename any random file that is NOT a SQLite3 database as C:\Tmp\NoDatabase.db. In the sqlite3.exe command-line program run:

.open C:\\Tmp\\NoDatabase.db

No error occurs. Then run:

.tables

And "Error: file is not a database" is displayed. Why no error when the (fake) database is opened?

Similarly using the SQLite v3.37.0 System.Data.SQLite.dll .NET wrapper. Open a connection with the following connection string:

"Data Source=C:\\Tmp\\NoDatabase.db;Version=3;FailIfMissing=True;"

The IDbConnection object actually reports the database connection is open! An exception is thrown when anything is attempted, such as extracting schema information like table information. This is consistent with the behaviour of the sqlite3.exe command-line tool operated.

• Why is no error being reported when opening a (fake) SQLite3 database?
• What is best programming practice to catch bad input such as the file is not a genuine SQLite3 database?

Answer 1

Thanks Shawn! By reading the file header, this can be quickly checked. Here is some C# code to do that.

public static bool IsRealSQLiteDB(string dbName)
{
    // Determine if the file is a genuine database (by reading the database header).
    // Fails if the file header does not match the expected format.
    bool isRealDB = false;

    // According to the SQLite documentation:
    //      https://sqlite.org/fileformat2.html#magic_header_string
    // every valid SQLite3 database file begins with the following 16 bytes (in hex):
    //      53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00

    // Corresponds to the UTF-8 string "SQLite format 3" including a NULL terminator character.
    if (!string.IsNullOrEmpty(dbName))
    {
        try
        {
            // Read the file header as stream of characters
            // Note: StreamReader opens files in blocking mode by default.
            // Use a non-blocking FileStream to prevent "access denied".
            char [] headerAsChar = new char[16];
            using (FileStream fs = new FileStream(dbDetails.dbName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                using (StreamReader sr = new StreamReader(fs)) {
                    sr.ReadBlock(headerAsChar, 0, 16);
                }
            }

            // Convert to a string (without a terminating NULL) and check the value
            string headerAsString = new string(headerAsChar, 0, 15);
            isRealDB = (string.Compare(headerAsString, "SQLite format 3") == 0);
        }
        catch { isRealDB = false; }
    }

    return isRealDB;
}

The reference is from the official SQLite documentation.