How to check pragma key is correct or incorrect?

[ayyyushhhhh]

I initially thought I could run a query to check if the entered key is correct. However, I was surprised to learn that the database only attempts to open once. If the key is incorrect, it throws an error and saves that state, preventing any further attempts—even if the correct key is entered afterward.

Is there any way to check whether the key is correct or incorrect multiple times without this limitation?

[simolus3]

If an error happens while opening the database, we can't generally know what state the database is in and what still needs to happen to make it ready (e.g. with migrations). Also, we want to avoid hiding errors (if you catch the first error or push it somewhere or if it happens in a stream where errors might not be handled properly, the error should still be visible).

As long as the open operation doesn't throw, you should be fine. So you could try to apply pragma key = and then run pragma user_version in a try/catch block afterward. If it throws, chances are the key is wrong.

[ayyyushhhhh]

Is there a simpler way to handle the passcode entry process? Currently, once the passcode is entered, there's no way to retry unless the app is restarted. Could you explain this process in a bit more detail?

What I'm aiming to do is set up a backup system using this passcode. Here's the flow:

1- The user enters the database passcode.
2 -The file is then uploaded to the server.
3- The user logs in again, and we retrieve the file from the server.

Now, to access this file, we need the passcode again. This is where I'm encountering the need for a check—essentially, a way to verify the passcode without requiring a restart of the app each time. How can we streamline this process?

[simolus3]

I guess you're opening the app database after step 3, right? You could briefly open the database with package:sqlite3 instead of using your drift database class to check the key before using drift?

You can also always close a drift database and open it again to retry the key entry.

[ayyyushhhhh]

create this function

static void openDatabase(String key) async {
    final path = await getApplicationDocumentsDirectory();
    Database db = sqlite3.open(File('${path.path}/mentcura.db.enc').path);
    NativeDatabase.opened(
      db,
      setup: (database) {
        database.execute("PRAGMA key = '$key';");
        db.execute('select count(*) from sqlite_master');
      },
    );
    db.dispose();
  }

But in return got this error.

[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: SqliteException(26): while executing, file is not a database, file is not a database (code 26)

This error is still there even if I delete the old file and creates a new file.

[simolus3]

Your snippet didn't use the temporary database, did you do something like this / did that find the wrong key?

  static void openDatabase(String key) async {
    final path = await getApplicationDocumentsDirectory();
    Database db = sqlite3.open(File('${path.path}/mentcura.db.enc').path);
    try {
      db.execute('pragma key = ?', [key]);
      db.execute('select count(*) from sqlite_master');
    } on SqliteException catch (e) {
      print('Likely a wrong key: $e');
    } finally {
      db.dispose();
    }

    NativeDatabase.opened(
      db,
      setup: (database) {
        database.execute("PRAGMA key = '$key';");
        db.execute('select count(*) from sqlite_master');
      },
    );
  }

[ayyyushhhhh]

Thanks Man you are a life saver! Used this code

 static Future<bool> checkDbpassword(String key) async {
    final path = await getApplicationDocumentsDirectory();
    Database db = sqlite3.open(File('${path.path}/mentcura.db.enc').path);
    try {
      db.execute('pragma key = "$key"');
      db.execute('select count(*) from sqlite_master');
      db.dispose();
      return true;
    } on SqliteException catch (e) {
      db.dispose();
      return false;
    }
  }

I have encountered a secondary problem. I’ve provided an option allowing them to reset the database if they don't know the key. When they choose this option, the old file is deleted and replaced with a new one, where they can set a new passcode. So, if I hot restart the app everything is working fine. But if I hot reload the app. I'm getting this error.

flutter Unhandled Exception: SqliteException(1032): while executing statement, attempt to write a readonly database, attempt to write a readonly database

[simolus3]

Does this exception only happen when the new database was created due to the forgotten password feature? Or how is that + hot reload related?

[ayyyushhhhh]

Yes this exception is only there when we are creating a new database. If I close the app and run it again, then there is no exception even if I create a new database. But if I don't restart the app after deleting the file, I encounters this issue.

[simolus3]

Are you only deleting the database file or also the -journal file (you should delete both)?

Also, can you make sure that no existing database connection is active during the delete? If you first open a drift database on the old file and then delete + re-create the file, the opened database connection might stop working.