How can I confirm the in-memory database was encrypted

[anhdowastaken]

I would like to confirm this kind of information, is there any method?

[frankbraun]

I never used go-sqlcipher to create an in-memory database, although that should be possible with the latest release (with the functionality from mattn/go-sqlite3). How did you create the in-memory database?

[anhdowastaken]

@frankbraun Thanks for your response!

Basically this is how I create the database:

inMemoryConnectionString  = "file::memory:?cache=shared&_pragma_key=x'%s'"
...
// Create random key
var encryptingKey [32]byte
var encryptingKeyString string
_, err = io.ReadFull(rand.Reader, encryptingKey[:])
if err != nil {
    log.Errorf("%+v", err)
    encryptingKeyString = "2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99"
} else {
    encryptingKeyString = hex.EncodeToString(encryptingKey[:])
}
conn, err = sql.Open("sqlite3", fmt.Sprintf(inMemoryConnectionString, encryptingKeyString))
if err != nil {
    log.Errorf("%+v", err)
    return
}

I understand that it's possible for me to read first 16 bytes the local db file to confirm (and I can confirm if local db file). Where should I start reading if in-memory db?

[frankbraun]

I see, thanks for the explanation. You want to make sure that your in-memory database is encrypted in case the machine is swapping? Of course it is better to have it encrypted, but the decryption key is still in memory. You just reduce the attack surface.

Anyway, it seems like a complicated problem... When you close the database handle your in-memory database is gone. When you access the database with the Go functions you are working on the decrypted level. So what you want is access to the memory pointer directly so you can check it. In sqlite3 there is this function: https://www.sqlite.org/c3ref/serialize.html

In order to use that you need the *C.sqlite3 pointer, but mattn/go-sqlite3 doesn't expose that. So you would have to add the serialize function to mattn/go-sqlite3, use it to get the memory of the database (avoiding a copy by passing SQLITE_SERIALIZE_NOCOPY to sqlite3_serialize), and then check the first 16 bytes of the memory pointer, just like for local db files.

That's the only way I see to do that.

[anhdowastaken]

Thanks, I will try at my end if possible.

[frankbraun]

Good luck! Please update here if you find out something. Now I'm curious if this can be achieved ;)

[anhdowastaken]

@frankbraun I don't have a chance to try your solution yet but I found another method and it seems legit. Latest version of mattn/go-sqlite3 already supports Backup. That means I can backup my in-memory database to a local file. According to sqlite document, the backup process is only done if both source and destination are plain or encrypted data so if I can confirm the backup is encrypted, that means the source (in-memory database) is encrypted too. At first, this method is acceptable to me.

[anhdowastaken]

@frankbraun Today I have a chance to try your solution (with default configuration), and I have some notes below:

• If I pass flag=0 to sqlite3_serialize(), I understand that address of memory location where the database is copied to is returned. However, though I encrypt or do not encrypt my in-memory database, the first 16 bytes of it are always "SQLite format 3\000".
• If I pass flag=SQLITE_SERIALIZE_NOCOPY, a NULL pointer is always returned at my local. But size of the database is good. Therefore, I think about these sentences:

  However, if the F argument contains the SQLITE_SERIALIZE_NOCOPY bit, then no memory allocations are made, and the 
  sqlite3_serialize() function will return a pointer to the contiguous memory representation of the database that SQLite is 
  currently using for that database, or NULL if the no such contiguous memory representation of the database exists. A 
  contiguous memory representation of the database will usually only exist if there has been a prior call to 
  sqlite3_deserialize(D,S,...) with the same values of D and S.

  In my situation, the memory might be non-contiguous.

I don't want to dive into sqlite3_deserialize() so with me, using Backup is enough this time.