Encryption - is this possible?

[cuuupid]

Normal SQLite has extensions/Pager support for adding encryption to the entire DB file i.e. AES ciphers. I was wondering if this project had similar capabilities.

Looking at how the DB is read/parsed, this may be as easy as encrypting/decryption on read/write of chunks.

[kukagg]

@pshah123 Have you managed to set it up?

[michaelpeterlee]

I recommend applying encryption at the application-level, storing data as blob/string and exposing indexes, as required. Encryption at lower-levels of the stack are a bonus and whilst they add layers around the onion, your application won't be dependent on them.

[imannms]

Someone has done this https://github.com/wireapp/websql
He uses sql.js with encryption enabled.

I've tried this and it worked.

It would be nice if this was also implemented in absurd-sql.

[hoangqwe159]

@imannms the repo link is expired. Can you give me some guidance about this problem, please?

[hoangqwe159]

https://github.com/hoangqwe159/better-absurd-sql

It is my solution for Encryption. Encryption for SharedArrayBuffer is not implemented. To make absurd-sql work across tabs, I use SharedWorker instead. The usage here:

import initSqlJs from "@kikko-land/sql.js";
import { SQLiteFS } from "@tashelix/better-absurd-sql";
import IndexedDBBackend from "@tashelix/better-absurd-sql/dist/indexeddb-backend";
import { expose } from "comlink";

/// <reference types="@types/sharedworker" />

// This is an any, as that is the return type from `initSqlJs`
const dbsMap = new Map<string, any>();

const passwordMap = new Map<string, string>();

const dbPath = (name: string): string => `${name}.sqlite`;

let SQL: any;
let isSqlInitialized = false;

async function initializeSql() {
    if (isSqlInitialized) return;

    isSqlInitialized = true;

    SQL = await initSqlJs({ locateFile: (file: any) => file });

    const sqlFS = new SQLiteFS(SQL.FS, new IndexedDBBackend(undefined, passwordMap));

    SQL.register_for_idb(sqlFS);

    SQL.FS.mkdir("/sql");
    SQL.FS.mount(sqlFS, {}, "/sql");
}

async function openDb(dbName: string, password?: string) {
    await initializeSql();

    if (password) passwordMap.set(dbPath(dbName), password);
    if (dbsMap.has(dbName)) return dbsMap.get(dbName);

    const path = `/sql/${dbName}.sqlite`;
    if (typeof SharedArrayBuffer === "undefined") {
        const stream = SQL.FS.open(path, "a+");
        await stream.node.contents.readIfFallback();
        SQL.FS.close(stream);
    }

    const db = new SQL.Database(path, { filename: true });

    db.exec(`PRAGMA cache_size=5000; PRAGMA page_size=8192; PRAGMA journal_mode=MEMORY;`);

    dbsMap.set(dbName, db);

    return db;
}

async function closeDb(dbName: string) {
    const db = dbsMap.get(dbName);
    if (!db) return;

    db.close();
    dbsMap.delete(dbName);
    passwordMap.delete(dbPath(dbName));
}

async function deleteDb(dbName: string) {
    if (dbsMap.has(dbName)) await closeDb(dbName);

    const path = `/sql/${dbName}.sqlite`;

    try {
        (globalThis as any).indexedDB?.deleteDatabase?.(`${dbName}.sqlite`);

        // console.log(SQL);
        // console.log(SQL.FS);

        let exists = true;
        try {
            SQL.FS.stat(path);
        } catch (e) {
            exists = false;
        }

        if (exists) {
            SQL.FS.unlink(path);
        }
    } catch (err) {
        console.error(`Failed to unlink db 'sql/${dbName}.sqlite', reason:`, err);
    }
}

async function dbRun(dbName: string, func: string, ...args: unknown[]) {
    return (await openDb(dbName))[func](...args);
}

async function dbExec(dbName: string, sql: string, params: unknown[] | Record<string, unknown>, password?: string) {
    return await (await openDb(dbName, password)).exec(sql, params, {});
}

// TODO: There is also a db.prepare function, look into using it: https://github.com/jlongster/absurd-example-project/blob/master/src/index.worker.js

// * Exposing the API

const api = { openDb, closeDb, deleteDb, dbRun, dbExec };
export type Api = typeof api;

expose(api);

// biome-ignore lint/suspicious/noGlobalAssign: This is how you initialize shared workers
onconnect = event => {
    const port = event.ports[0];
    expose(api, port);
};