diff --git a/web/database/utils/db-utils.js b/web/database/utils/db-utils.js index 3d23eccd4..8a6008e77 100644 --- a/web/database/utils/db-utils.js +++ b/web/database/utils/db-utils.js @@ -1,68 +1,89 @@ // @flow import { detect as detectBrowser } from 'detect-browser'; import type { QueryExecResult } from 'sql.js'; import { isStaff } from 'lib/shared/staff-utils.js'; import { isDev } from 'lib/utils/dev-utils.js'; import { DB_SUPPORTED_BROWSERS, DB_SUPPORTED_OS } from './constants.js'; -import { type EmscriptenModule } from '../types/module.js'; +import type { EmscriptenModule } from '../types/module.js'; import { type SQLiteQueryExecutor } from '../types/sqlite-query-executor.js'; const browser = detectBrowser(); function clearSensitiveData( dbModule: EmscriptenModule, path: string, sqliteQueryExecutor: SQLiteQueryExecutor, ) { sqliteQueryExecutor.delete(); dbModule.FS.unlink(path); } function parseSQLiteQueryResult(result: QueryExecResult): T[] { const { columns, values } = result; return values.map(rowResult => { const row: any = Object.fromEntries( columns.map((key, index) => [key, rowResult[index]]), ); return row; }); } // NOTE: sql.js has behavior that when there are multiple statements in query // e.g. "statement1; statement2; statement3;" // and statement2 will not return anything, the result will be: // [result1, result3], not [result1, undefined, result3] function parseMultiStatementSQLiteResult( rawResult: $ReadOnlyArray, ): T[][] { return rawResult.map((queryResult: QueryExecResult) => parseSQLiteQueryResult(queryResult), ); } +function importDatabaseContent( + content: Uint8Array, + dbModule: EmscriptenModule, + path: string, +) { + const stream = dbModule.FS.open(path, 'w+'); + dbModule.FS.write(stream, content, 0, content.length, 0); + dbModule.FS.close(stream); +} + +function exportDatabaseContent( + dbModule: EmscriptenModule, + path: string, +): Uint8Array { + return dbModule.FS.readFile(path, { + encoding: 'binary', + }); +} + function isSQLiteSupported(currentLoggedInUserID: ?string): boolean { if (!currentLoggedInUserID) { return false; } if (!isDev && (!currentLoggedInUserID || !isStaff(currentLoggedInUserID))) { return false; } return ( DB_SUPPORTED_OS.includes(browser.os) && DB_SUPPORTED_BROWSERS.includes(browser.name) ); } const isDesktopSafari: boolean = browser && browser.name === 'safari' && browser.os === 'Mac OS'; export { parseMultiStatementSQLiteResult, isSQLiteSupported, isDesktopSafari, + importDatabaseContent, + exportDatabaseContent, clearSensitiveData, }; diff --git a/web/database/utils/worker-crypto-utlis.test.js b/web/database/utils/worker-crypto-utlis.test.js index 288d029bd..3515b4caf 100644 --- a/web/database/utils/worker-crypto-utlis.test.js +++ b/web/database/utils/worker-crypto-utlis.test.js @@ -1,107 +1,102 @@ // @flow -import initSqlJs, { type SqliteDatabase } from 'sql.js'; - +import { exportDatabaseContent, importDatabaseContent } from './db-utils.js'; import { decryptDatabaseFile, encryptDatabaseFile, exportKeyToJWK, generateDatabaseCryptoKey, importJWKKey, } from './worker-crypto-utils.js'; -import { getSQLiteDBVersion } from '../queries/db-queries.js'; +import { getDatabaseModule } from '../db-module.js'; -const TEST_DB_VERSION = 5; const TAG_LENGTH = 16; const IV_LENGTH = 12; -// calling export on empty schema will return empty buffer -function setUpMockDb(database: SqliteDatabase) { - database.exec(` - CREATE TABLE test_table ( - key TEXT UNIQUE PRIMARY KEY NOT NULL, - value TEXT NOT NULL - ); - `); - database.exec(` - INSERT INTO test_table VALUES ("key_1", "value 1"); - `); - database.exec(`PRAGMA user_version=${TEST_DB_VERSION};`); -} +const FILE_PATH = 'test.sqlite'; +const TEST_KEY = 'key'; +const TEST_VAL = 'val'; describe('database encryption utils', () => { - let database; + let sqliteQueryExecutor; + let dbModule; let cryptoKey; beforeAll(async () => { - const SQL = await initSqlJs(); - database = new SQL.Database(); - setUpMockDb(database); + dbModule = getDatabaseModule(); + sqliteQueryExecutor = new dbModule.SQLiteQueryExecutor('test.sqlite'); + sqliteQueryExecutor.setMetadata(TEST_KEY, TEST_VAL); cryptoKey = await generateDatabaseCryptoKey({ extractable: false }); }); it('should encrypt database content', async () => { - const dbContent: Uint8Array = database.export(); + const dbContent: Uint8Array = exportDatabaseContent(dbModule, FILE_PATH); const { ciphertext, iv } = await encryptDatabaseFile(dbContent, cryptoKey); expect(iv.byteLength).toBe(IV_LENGTH); expect(ciphertext.length).toBe(dbContent.length + TAG_LENGTH); }); it('is decryptable', async () => { - const dbContent: Uint8Array = database.export(); + const dbContent: Uint8Array = exportDatabaseContent(dbModule, FILE_PATH); const encryptedData = await encryptDatabaseFile(dbContent, cryptoKey); const decrypted = await decryptDatabaseFile(encryptedData, cryptoKey); expect(decrypted).toEqual(dbContent); }); it('should fail with wrong key', async () => { - const dbContent: Uint8Array = database.export(); + const dbContent: Uint8Array = exportDatabaseContent(dbModule, FILE_PATH); const encryptedData = await encryptDatabaseFile(dbContent, cryptoKey); const newCryptoKey = await generateDatabaseCryptoKey({ extractable: false, }); expect(decryptDatabaseFile(encryptedData, newCryptoKey)).rejects.toThrow(); }); it('should fail with wrong content', async () => { - const dbContent: Uint8Array = database.export(); + const dbContent: Uint8Array = exportDatabaseContent(dbModule, FILE_PATH); const encryptedData = await encryptDatabaseFile(dbContent, cryptoKey); const randomizedEncryptedData = { ...encryptedData, ciphertext: encryptedData.ciphertext.map(uint => uint ^ 1), }; expect( decryptDatabaseFile(randomizedEncryptedData, cryptoKey), ).rejects.toThrow(); }); it('should create database with decrypted content', async () => { - const dbContent: Uint8Array = database.export(); + const dbContent: Uint8Array = exportDatabaseContent(dbModule, FILE_PATH); const encryptedData = await encryptDatabaseFile(dbContent, cryptoKey); const decrypted = await decryptDatabaseFile(encryptedData, cryptoKey); - const SQL = await initSqlJs(); - const newDatabase = new SQL.Database(decrypted); - expect(getSQLiteDBVersion(newDatabase)).toBe(TEST_DB_VERSION); + importDatabaseContent(decrypted, dbModule, 'new-file.sqlite'); + + const executor = new dbModule.SQLiteQueryExecutor('new-file.sqlite'); + + expect(executor.getMetadata(TEST_KEY)).toBe(TEST_VAL); }); it('should export and import key in JWK format', async () => { // creating new key const key = await generateDatabaseCryptoKey({ extractable: true }); - const dbContent: Uint8Array = database.export(); + const dbContent: Uint8Array = dbModule.FS.readFile(FILE_PATH, { + encoding: 'binary', + }); const encryptedData = await encryptDatabaseFile(dbContent, key); // exporting and importing key const exportedKey = await exportKeyToJWK(key); const importedKey = await importJWKKey(exportedKey); // decrypt using re-created on import key const decrypted = await decryptDatabaseFile(encryptedData, importedKey); - const SQL = await initSqlJs(); - const newDatabase = new SQL.Database(decrypted); - expect(getSQLiteDBVersion(newDatabase)).toBe(TEST_DB_VERSION); + importDatabaseContent(decrypted, dbModule, 'new-file.sqlite'); + + const executor = new dbModule.SQLiteQueryExecutor('new-file.sqlite'); + + expect(executor.getMetadata(TEST_KEY)).toBe(TEST_VAL); }); });