How can I do the same using sqlcipher on macOS which can open the database?

[StringKe]

I've tried using the key in several ways and every time I get SqliteError: file is not a database.

my code:

    const db = new Database(files.wxFileIndex, {
        readonly: true,
        verbose: function (...args: any[]) {
            console.log(`SQLITE => `, ...args);
        },
    });
    db.pragma(`key='${password}';`);
    db.pragma(`cipher_use_hmac = off;`);
    db.pragma(`kdf_iter = 4000;`);
    db.pragma(`cipher_page_size = 1024;`);
    db.pragma(`cipher_hmac_algorithm = HMAC_SHA1;`);
    db.pragma(`cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;`);

    // db.pragma(`key='${password}'`);

    // db.pragma(`cipher='sqlcipher'`);
    // db.pragma(`legacy=4`);
    // db.pragma(`key='${password}'`);

    console.log(db.prepare('SELECT * FROM sqlite_master;'));

logger:

SQLite version 3.46.1 2024-08-13 09:16:08 (SQLCipher 4.6.1 community)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open WxFileIndex.db
sqlite> PRAGMA key='49b2c71';
ok
sqlite> PRAGMA cipher_use_hmac = off;
sqlite> PRAGMA kdf_iter = 4000;
sqlite> PRAGMA cipher_page_size = 1024;
sqlite> PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
sqlite> PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
sqlite> SELECT * FROM sqlite_master;
table|WxFileIndex3|WxFileIndex3|2|CREATE TABLE WxFileIndex3 (  msgId LONG,  username TEXT,  msgType INTEGER,  msgSubType INTEGER,  path TEXT,  size LONG,  msgtime LONG,  hash BLOB,  diskSpace LONG,  linkUUID BLOB)
table|WxFileIndexDirty|WxFileIndexDirty|3|CREATE TABLE WxFileIndexDirty(msgId INTEGER PRIMARY KEY)
table|WxFileIndexRefresh|WxFileIndexRefresh|4|CREATE TABLE WxFileIndexRefresh(indexRowId INTEGER PRIMARY KEY)
table|WxFileIndexRegistry|WxFileIndexRegistry|5|CREATE TABLE WxFileIndexRegistry(id INTEGER PRIMARY KEY, value BLOB)
table|WxFileIndexLinkify|WxFileIndexLinkify|6|CREATE TABLE WxFileIndexLinkify(id INTEGER PRIMARY KEY, originalPath TEXT, targetPath TEXT, status INT)
table|WxFileIndexDownloadMigration|WxFileIndexDownloadMigration|7|CREATE TABLE WxFileIndexDownloadMigration(id INTEGER PRIMARY KEY, originalPath TEXT, targetPath TEXT, indexRowId INT, msgId INT, status INT)
index|msgid_username_index|WxFileIndex3|9|CREATE INDEX msgid_username_index ON WxFileIndex3 ( msgId,username,msgSubType )
index|username_type_index|WxFileIndex3|11|CREATE INDEX username_type_index ON WxFileIndex3 ( username,msgtime,msgSubType )
index|WxFileIndex_uuid|WxFileIndex3|12|CREATE INDEX WxFileIndex_uuid ON WxFileIndex3 (linkUUID)
sqlite>

[m4heshd]

Can you create a minimal reproducible repo for this?

Make sure to include a minimal copy of that encrypted database with the proper key in the repo.

[StringKe]

You have been invited to my private repository. https://github.com/StringKe/bsmc-report

[m4heshd]

Here you go, change your wxFileIndex() function to the following

index.ts:

async function wxFileIndex(path: string, password: string) {
    const db = new Database(path, {
        readonly: true,
        verbose: function (...args: any[]) {
            console.log(`SQLITE => `, ...args);
        },
    });
    db.pragma(`cipher='sqlcipher'`);
    db.pragma(`legacy = 1`);
    db.pragma(`key='${password}';`);
    db.pragma(`hmac_use = 1`);
    db.pragma(`kdf_iter = 4000`);
    db.pragma(`legacy_page_size = 1024`);
    db.pragma(`hmac_algorithm = 0`);
    db.pragma(`kdf_algorithm = 0`);

    const tables = db.prepare(`SELECT name FROM sqlite_master WHERE type='table'`).all();
    console.log(tables);
}

Output:

> npx esno src/index.ts

SQLITE =>  PRAGMA cipher='sqlcipher'
SQLITE =>  PRAGMA legacy = 1
SQLITE =>  PRAGMA key='49b2c71';
SQLITE =>  PRAGMA hmac_use = 1
SQLITE =>  PRAGMA kdf_iter = 4000
SQLITE =>  PRAGMA legacy_page_size = 1024
SQLITE =>  PRAGMA hmac_algorithm = 0
SQLITE =>  PRAGMA kdf_algorithm = 0
SQLITE =>  SELECT name FROM sqlite_master WHERE type='table'
[
  { name: 'WxFileIndex3' },
  { name: 'WxFileIndexDirty' },
  { name: 'WxFileIndexRefresh' },
  { name: 'WxFileIndexRegistry' },
  { name: 'WxFileIndexLinkify' },
  { name: 'WxFileIndexDownloadMigration' }
]

Your database was created with SQLCipher v1. That should be specified explicitly when using BS3MC. Next time, please make sure that you have gone through all the necessary documentation when you work with this library because it could get quite complicated. If you looked through it, you would've found this documentation on how to work with legacy SQLCipher DBs.