How to use an encrypted SQLite database

Purpose. Protect sensitive data on disk with an encrypted database (via SQLite3MC). Encryption is opt-in: without a key the database is a plain SQLite, with no cipher cost. With a key, CrossFly applies the cipher and validates the key on open (fails early, not on the first query).

APIs used

• SqliteDb.open(String filename, byte[] key) — opens/creates encrypted with the default cipher Sqlite.Cipher.CHACHA20 (ChaCha20-Poly1305).
• SqliteDb.open(String filename, byte[] key, Sqlite.Cipher cipher) — opens with an explicit cipher. Options: CHACHA20, SQLCIPHER (AES-256-CBC + HMAC), AES128CBC, AES256CBC, RC4, ASCON128.
• SqliteDb.rekey(byte[] newKey) — changes the key via sqlite3_rekey. Passing null or an empty array (new byte[0]) removes encryption (“rekey to no key”).
• SqliteException.code — numeric error code. Compare it against constants from Sqlite. Wrong key / file is not a database ⇒ code == Sqlite.NOTADB (26). Other useful ones: Sqlite.BUSY (5), Sqlite.CONSTRAINT (19).

How the wrong key shows up. On open with a key, CrossFly selects the cipher, applies the key, and performs a forced read (SELECT count(*) FROM sqlite_master) to fail immediately with a wrong key, closing the connection. You catch a SqliteException with code == Sqlite.NOTADB.

Mind the cipher when reopening. To reopen an existing database, use the same cipher it was created with. CHACHA20 (default) only decrypts CHACHA20 databases; a SQLCIPHER database requires open(file, key, Sqlite.Cipher.SQLCIPHER).

Complete example (copy, paste and run)

package com.exemplo;

import java.nio.charset.StandardCharsets;

import dev.crossfly.ui.*;                     // Window
import dev.crossfly.ui.layout.*;             // VerticalLayout
import dev.crossfly.sqlite.*;                // SqliteDb, Sqlite, SqliteException

/** Runnable main screen. Point the launcher (classname) and run: gradlew :lwjgl3:run */
public class MainWindow extends Window
{
   @Override
   public void onShow() throws Exception
   {
      super.onShow();
      setLayout(new VerticalLayout());

      byte[] key = "minha-senha-forte".getBytes(StandardCharsets.UTF_8);

      // Open/create encrypted (default cipher CHACHA20)
      try (SqliteDb db = SqliteDb.open("secure.db", key))
      {
         db.exec("CREATE TABLE IF NOT EXISTS secret(id INTEGER PRIMARY KEY, v TEXT)");
         db.execute("INSERT INTO secret(v) VALUES(?)", "dado sensível");

         // Change the key
         byte[] novaKey = "senha-ainda-mais-forte".getBytes(StandardCharsets.UTF_8);
         db.rekey(novaKey);
         // db.rekey(new byte[0]);  // <- would remove the encryption
      }

      // Explicit cipher (compatible with SQLCipher)
      try (SqliteDb db = SqliteDb.open("sqlcipher.db", key, Sqlite.Cipher.SQLCIPHER))
      {
         db.exec("SELECT count(*) FROM sqlite_master");
      }

      // Detect wrong key
      try (SqliteDb db = SqliteDb.open("secure.db", "chave-errada".getBytes(StandardCharsets.UTF_8)))
      {
         // doesn't reach here: validation already failed at open
      }
      catch (SqliteException e)
      {
         if (e.code == Sqlite.NOTADB)          // 26 = wrong key / not a database
            System.out.println("Chave incorreta");
         else
            throw e;
      }
   }
}

The snippet below shows only the core. To run it, place it in the MainWindow skeleton above.

Minimal snippet

// verified in the source: open(file,key), open(file,key,cipher), rekey, SqliteException.code == Sqlite.NOTADB
import dev.crossfly.sqlite.*;
import java.nio.charset.StandardCharsets;

byte[] key = "minha-senha-forte".getBytes(StandardCharsets.UTF_8);

// Open/create encrypted (default cipher CHACHA20)
try (SqliteDb db = SqliteDb.open("secure.db", key))
{
   db.exec("CREATE TABLE IF NOT EXISTS secret(id INTEGER PRIMARY KEY, v TEXT)");
   db.execute("INSERT INTO secret(v) VALUES(?)", "dado sensível");

   // Change the key
   byte[] novaKey = "senha-ainda-mais-forte".getBytes(StandardCharsets.UTF_8);
   db.rekey(novaKey);
   // db.rekey(new byte[0]);  // <- would remove the encryption
}

// Explicit cipher (compatible with SQLCipher)
try (SqliteDb db = SqliteDb.open("sqlcipher.db", key, Sqlite.Cipher.SQLCIPHER))
{
   db.exec("SELECT count(*) FROM sqlite_master");
}

// Detect wrong key
try (SqliteDb db = SqliteDb.open("secure.db", "chave-errada".getBytes(StandardCharsets.UTF_8)))
{
   // doesn't reach here: validation already failed at open
}
catch (SqliteException e)
{
   if (e.code == Sqlite.NOTADB)          // 26 = wrong key / not a database
      System.out.println("Chave incorreta");
   else
      throw e;
}

Dig deeper

• Security best practices · SQLite storage guide
• Sibling recipes: Open/create a database · Parameterized CRUD