Encrypting temporary files

[ncruces]

Hi. I'm trying to develop my own encrypting VFS (n the context of developing a Go driver). I was looking into your code for inspiration, and have a few comments/concerns.

Have you tested this branch?

SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

Lines 435 to 441 in 80722b0

	else if (flags & SQLITE_OPEN_SUBJOURNAL)
	{
	const char* dbFileName = sqlite3_filename_database(zName);
	mcFile->pMainDb = mcFindDbMainFileName(mcFile->pVfsMC, dbFileName);
	mcFile->zFileName = zName;
	SQLITE3MC_DEBUG_LOG("mcVfsOpen SUB Journal: mcFile=%p fileName=%s dbFileName=%s\n", mcFile, mcFile->zFileName, dbFileName);
	}

I… don't think you can do this: sqlite3_filename_database is only safe to call with a main database, journal, or WAL name? None of the other kinds of files are safe to call this, and can corrupt memory…

[ncruces]

That said, encrypting these files is important.

SQLITE_OPEN_SUBJOURNAL is used for SAVEPOINTs, and statement journals.

SQLITE_OPEN_TRANSIENT_DB are (I think) used for materialization of queries, transient indices and transient databases for VACUUM.

SQLITE_OPEN_TEMP_JOURNAL are used for journals of TEMP databases, but are also used by the VDBE sorter (in a format called PMA that's nothing like a journal).

So all of these potentially store database data, though not necessarily in the form of pages (and with reserved bytes at the end).

[utelle]

Have you tested this branch?

SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

Lines 435 to 441 in 80722b0

	else if (flags & SQLITE_OPEN_SUBJOURNAL)
	{
	const char* dbFileName = sqlite3_filename_database(zName);
	mcFile->pMainDb = mcFindDbMainFileName(mcFile->pVfsMC, dbFileName);
	mcFile->zFileName = zName;
	SQLITE3MC_DEBUG_LOG("mcVfsOpen SUB Journal: mcFile=%p fileName=%s dbFileName=%s\n", mcFile, mcFile->zFileName, dbFileName);
	}

I… don't think you can do this: sqlite3_filename_database is only safe to call with a main database, journal, or WAL name? None of the other kinds of files are safe to call this, and can corrupt memory…

Up to now I haven't experienced any memory corruption, and I haven't received any bug reports either. However, it can indeed happen that zName is a NULL pointer. I have to further investigate how to handle that situation.

That said, encrypting these files is important.

SQLITE_OPEN_SUBJOURNAL is used for SNAPSHOTs, and statement journals.

SQLITE_OPEN_TRANSIENT_DB are (I think) used for materialization of queries, transient indices and transient databases for VACUUM.

SQLITE_OPEN_TEMP_JOURNAL are used for journals of TEMP databases, but are also used by the VBDE sorter (in a format called PMA that's nothing like a journal).

So all of these potentially store database data, though not necessarily in the form of pages (and with reserved bytes at the end).

Currently SQLite3 Multiple Ciphers has the same limitations as the official SQLite Encryption Extension: temporary databases and in-memory databases are not encrypted.

I'll look into the issue and check if and when there are relevant effects.

[ncruces]

You should look at the implementation of these functions:
https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/main.c#L4845

They literally just walk back the entire memory until they find 4 consecutive 0 bytes:
https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/main.c#L4710

The only reason this works for database, journal, and WAL file names is that the pager allocates memory this way for compatibility purposes. But it's a huge hack.
https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/pager.c#L4797

[utelle]

You should look at the implementation of these functions. [...]

I'm aware of these functions. And I verified that the function sqlite3_filename_database is always useable for the filename passed to the VFS Open method. However, there is the case of a NULL pointer passed in as the filename. Function sqlite3_filename_database returns NULL in that case. As a consequence it is impossible to locate the codec structure in that case, and any data will be written to the file unencrypted. This may impose a problem.

The only reason this works for database, journal, and WAL file names is that the pager allocates memory this way for compatibility purposes. But it's a huge hack. https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/pager.c#L4797

AFAICT this shouldn't impose a problem, because my code doesn't rely on this specific memory layout. Instead the function sqlite3_filename_database is used.

As said only the case that the filename is a NULL pointer may be a problem. I will check whether we can do anything about this use case.

[ncruces]

So the reason this might be safe is that SQLITE_OPEN_SUBJOURNAL is always called with a NULL database filename:
https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/pager.c#L4496

Then sqlite3_filename_database always returns NULL:
https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/main.c#L4845

And you always get the key for the main database of the connection:

SQLite3MultipleCiphers/src/sqlite3mc_vfs.c

Lines 221 to 228 in 80722b0

	static sqlite3mc_file* mcFindDbMainFileName(sqlite3mc_vfs* mcVfs, const char* zFileName)
	{
	sqlite3mc_file* pDb;
	sqlite3_mutex_enter(mcVfs->mutex);
	for (pDb = mcVfs->pMain; pDb && pDb->zFileName != zFileName; pDb = pDb->pMainNext){}
	sqlite3_mutex_leave(mcVfs->mutex);
	return pDb;
	}

I don't know if that's the intended behaviour. But all it takes for a crash/corruption is for SQLITE_OPEN_SUBJOURNAL to be used with a named database.

Edit: you're assuming SQLite won't ever do this. That's a fair assumption. A VFS in a shim chain might, but it'd be violating some reasonable assumptions, so that's OK.

Currently SQLite3 Multiple Ciphers has the same limitations as the official SQLite Encryption Extension: temporary databases and in-memory databases are not encrypted.

Without looking at their source code, it's hard to know what this really means. But TEMP databases (and tables) use SQLITE_OPEN_TEMP_DB, which I didn't mention. SQLITE_OPEN_TRANSIENT_DB is used for “main” database data, that I have little doubt about. What exactly SQLITE_OPEN_TEMP_JOURNAL stores when used by the VDBE sorter I'm not sure; it's definitely not a journal, though.

My current thinking is to encrypt temporary files (no file name, and delete-on-close) with a random key. But I use whole file encryption, and don't depend on the file format (database, journal, wal) to identify page boundaries (and page reserved space).

Feel free to close though. I hope this was useful, and not a waste of your time!

[utelle]

So the reason this might be safe is that SQLITE_OPEN_SUBJOURNAL is always called with a NULL database filename: https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/pager.c#L4496

Right. But this means that page data will be written to disk unencrypted.

This should be avoided, of course. Actually, the intention is to use the encryption key of the main database for all related journal files, but without the filename of the main database file, it is not possible to determine the encryption key of the main database.

Then sqlite3_filename_database always returns NULL: https://github.com/sqlite/sqlite/blob/0526db32e2657b2b16fa3333e5b8c6e24754eaf2/src/main.c#L4845

And you always get the key for the main database of the connection:

No, unfortunately not. A NULL pointer will be returned.

I don't know if that's the intended behaviour. But all it takes for a crash/corruption is for SQLITE_OPEN_SUBJOURNAL to be used with a named database.

The SQLite core uses only proper filenames or NULL.

Edit: you're assuming SQLite won't ever do this. That's a fair assumption. A VFS in a shim chain might, but it'd be violating some reasonable assumptions, so that's OK.

Right. Function sqlite3_filename_database is also only guaranteed to work, if the filename was provided by the pager (or the SQLite core).

So, if a developer violates the rules, he/she will be to blame for any misbehaviour.

Currently SQLite3 Multiple Ciphers has the same limitations as the official SQLite Encryption Extension: temporary databases and in-memory databases are not encrypted.

Without looking at their source code, it's hard to know what this really means.

Of course, this is correct. Without access to the SEE source code it is hard to tell how encryption is actually handled. I suspect that it is still done in that way that it is not done via a VFS. The VFS API lacks an official way to determine the SQLite connection to which the file belongs.

But TEMP databases (and tables) use SQLITE_OPEN_TEMP_DB, which I didn't mention. SQLITE_OPEN_TRANSIENT_DB is used for “main” database data, that I have little doubt about. What exactly SQLITE_OPEN_TEMP_JOURNAL stores when used by the VDBE sorter I'm not sure; it's definitely not a journal, though.

If no page data are stored then we would be on the safe side.

My current thinking is to encrypt temporary files (no file name, and delete-on-close) with a random key.

This is certainly an option, if you store the key in the SQLite file descriptor.

But I use whole file encryption, and don't depend on the file format (database, journal, wal) to identify page boundaries (and page reserved space).

In the beginning I had thought about such an approach, too, but decided against it.

Feel free to close though. I hope this was useful, and not a waste of your time!

For now, I will keep the issue open, because the handling of NULL filenames is certainly suboptimal - to say the least.

Thanks for sharing your thoughts.

[ncruces]

If no page data are stored then we would be on the safe side.

It's not page data, but it's at a minimum:

1. data from the columns being sorted, and
2. either data for all output columns, or the primary key for each row.

This is certainly an option, if you store the key in the SQLite file descriptor.

Encrypting temporary files with a random key is what I settled on. But I benefit from not trying to be the default VFS: people are explicitly saying they want encryption for this connection. I also refuse to open the connection if there's no encryption key.

I guess if you're trying to be the default VFS, and both unencrypted and encrypted databases, the best thing you can do is advise users to set PRAGMA temp_store=memory. Which is also what SQLCipher advises.

Well, thanks for listening! And if you're curious, this is my attempt:
https://github.com/ncruces/go-sqlite3/tree/main/vfs/adiantum

[utelle]

If no page data are stored then we would be on the safe side.

It's not page data, but it's at a minimum:

1. data from the columns being sorted, and
2. either data for all output columns, or the primary key for each row.

Well, as soon as database content data are involved, it would certainly make sense to encrypt such data if they are written to disk. Therefore it is important to hold temporary data in memory using compile time option SQLITE_TEMP_STORE or PRAGMA temp_store=memory.

The build files coming with SQLite3 Multiple Ciphers use the above compile time option. However, the behaviour can be changed at the user's discretion with the PRAGMA command.

Looking at the following quote from the SEE documentation ...

The data is encrypted in both the main database and in the rollback journal or WAL file but is unencrypted when held in memory.

... I get the impression that the official SQLite encryption extension does not encrypt temporary data.

BTW, do you happen to have SQL command samples at hand to trigger the various temporary data types in the VFS Open method? I would like to further investigate which temporary data are potentially written to disk.

This is certainly an option, if you store the key in the SQLite file descriptor.

Encrypting temporary files with a random key is what I settled on. But I benefit from not trying to be the default VFS: people are explicitly saying they want encryption for this connection. I also refuse to open the connection if there's no encryption key.

Yes, SQLite3 Multiple Ciphers makes its VFS the default, but that can be changed.

I guess if you're trying to be the default VFS, and both unencrypted and encrypted databases,

Actually, the SQLite3 Multiple Ciphers VFS is a VFS shim which can be combined with any real VFS. So, the user can decide which VFS to use and whether encryption should be enabled or not.

the best thing you can do is advise users to set PRAGMA temp_store=memory. Which is also what SQLCipher advises.

Yes, temporary data should be kept in memory. However, this is not always possible in case of memory constraints.

Therefore it could be useful to allow encrypting temporary data, too.

Well, thanks for listening! And if you're curious, this is my attempt: https://github.com/ncruces/go-sqlite3/tree/main/vfs/adiantum

I'll certainly take a look. Thanks for the pointer.

[ncruces]

BTW, do you happen to have SQL command samples at hand to trigger the various temporary data types in the VFS Open method? I would like to further investigate which temporary data are potentially written to disk.

I can trigger most of these by running mptest and speedtest1 (which are part of the SQLite test suite), but I'll try to isolate the behaviours in question.

[utelle]

I can trigger most of these by running mptest and speedtest1 (which are part of the SQLite test suite),

I have used speedtest1 in the past for some experiments, but not for an in-depth analysis. I will take another look at those tests.

but I'll try to isolate the behaviours in question.

TIA.

[ncruces]

The example is in Go, but the relevant part is SQL, so hopefully it helps.

func Test_files(t *testing.T) {
	file := filepath.Join(t.TempDir(), "test.db")

	db, err := Open(file)
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	err = db.Exec(`
		-- Setup
		PRAGMA cache_size=10;
		PRAGMA cache_spill=0;
		PRAGMA temp_store=file;

		BEGIN;

		-- SQLITE_OPEN_MAIN_DB
		-- SQLITE_OPEN_MAIN_JOURNAL

		CREATE TABLE t1 (a, b);
		INSERT INTO t1 VALUES (0, zeroblob(1048576));
		INSERT INTO t1 VALUES (1, zeroblob(1048576));

		-- SQLITE_OPEN_TEMP_DB
		-- SQLITE_OPEN_TEMP_JOURNAL

		CREATE TABLE temp.t2 (c, d);
		INSERT INTO temp.t2 VALUES (0, zeroblob(1048576));
		INSERT INTO temp.t2 VALUES (1, zeroblob(1048576));

		COMMIT;
	`)
	if err != nil {
		t.Fatal(err)
	}

	err = db.Exec(`BEGIN`)
	if err != nil {
		t.Fatal(err)
	}

	for i := 0; i < 1000; i++ {
		err = db.Exec(`
			-- SQLITE_OPEN_SUBJOURNAL
			SAVEPOINT savept;

			INSERT INTO t1 VALUES (random(), zeroblob(1024));
			INSERT INTO temp.t2 VALUES (random(), zeroblob(1024));
		`)
		if err != nil {
			t.Fatal(err)
		}
	}
	err = db.Exec(`COMMIT`)
	if err != nil {
		t.Fatal(err)
	}

	err = db.Exec(`
		-- SQLITE_OPEN_TRANSIENT_DB
		SELECT * FROM t1, t2 WHERE a=c;
	`)
	if err != nil {
		t.Fatal(err)
	}

	err = db.Exec(`
		-- SQLITE_OPEN_TEMP_JOURNAL
		SELECT * FROM t1 ORDER BY a;
	`)
	if err != nil {
		t.Fatal(err)
	}
}

Except for SQLITE_OPEN_MAIN_DB and SQLITE_OPEN_MAIN_JOURNAL (and SQLITE_OPEN_MAIN_WAL/OPEN_SUPER_JOURNAL, which I didn't test here), all other files: have null name, and flags are create, exclusive, delete-on-close. So, basically, they're temporary files.

From reading the magic numbers that are written into offset zero of the files:

• SQLITE_OPEN_TEMP_DB is a database, like SQLITE_OPEN_MAIN_DB
• SQLITE_OPEN_TEMP_JOURNAL is a rollback journal, like SQLITE_OPEN_MAIN_JOURNAL when used as such
• SQLITE_OPEN_TRANSIENT_DB seems to be something else, but may be a database without a header
• SQLITE_OPEN_SUBJOURNAL seems to be something else, but may be a journal without a header
• SQLITE_OPEN_TEMP_JOURNAL is something else when used by the VDBE sorter

I've also confirmed that all of SQLITE_OPEN_TRANSIENT_DB, SQLITE_OPEN_SUBJOURNAL and SQLITE_OPEN_TEMP_JOURNAL can contain database data (by writing a specific string into a table and testing if it shows up in an xWrite call). SQLITE_OPEN_TEMP_DB and SQLITE_OPEN_TEMP_JOURNAL will also have whatever you write to the temp database.

[utelle]

The example is in Go, but the relevant part is SQL, so hopefully it helps.

Thanks for providing the example code.

Except for SQLITE_OPEN_MAIN_DB and SQLITE_OPEN_MAIN_JOURNAL (and SQLITE_OPEN_MAIN_WAL/OPEN_SUPER_JOURNAL, which I didn't test here), all other files: have null name, and flags are create, exclusive, delete-on-close. So, basically, they're temporary files.

The main database and journals are properly encrypted with SQLite3 Multiple Ciphers.

All files with a null filename will not be encrypted, because it is impossible to identify the codec linked to the main database file. Unfortunately, a VFS has no means to identify the database connection of the file, thus making it impossible to locate the codec of the database connection.

From reading the magic numbers that are written into offset zero of the files:

• SQLITE_OPEN_TEMP_DB is a database, like SQLITE_OPEN_MAIN_DB
• SQLITE_OPEN_TEMP_JOURNAL is a rollback journal, like SQLITE_OPEN_MAIN_JOURNAL when used as such

In accordance with the official SQLite Encryption Extension (SEE) temporary databases (and temporary journals related to them) will not be encrypted. Therefore it is strongly recommended to store temporary data in memory.

• SQLITE_OPEN_TRANSIENT_DB seems to be something else, but may be a database without a header

If PRAGMA temp_store=memory is used, I assume that the data are all stored in memory. However, I have to analyze under which circumstances a transient database is written to file.

• SQLITE_OPEN_SUBJOURNAL seems to be something else, but may be a journal without a header

In principal, subjournals will be encrypted. But only, if the codec could be located. This is a use case I have to further investigate.

• SQLITE_OPEN_TEMP_JOURNAL is something else when used by the VDBE sorter

I assume the this journal type will reside in memory. But I have to check in detail.

I've also confirmed that all of SQLITE_OPEN_TRANSIENT_DB, SQLITE_OPEN_SUBJOURNAL and SQLITE_OPEN_TEMP_JOURNAL can contain database data (by writing a specific string into a table and testing if it shows up in an xWrite call).

Yes, if temporary data are written to file this would be a problem with SQLite3 Multiple Ciphers, because no encryption occurs.

SQLITE_OPEN_TEMP_DB and SQLITE_OPEN_TEMP_JOURNAL will also have whatever you write to the temp database.

That is clear. However, SQLite3 Multiple Ciphers never claimed to encrypt temporary database objects.

IMHO the behaviour of SQLite3 Multiple Ciphers is ok, as long as temporary data are kept in memory.

If an attacker has access to the memory of a system, it actually doesn't matter whether temporary data are encrypted or not, because those data will be accessible in SQLite3's cache anyway.

[ncruces]

All these files have null filenames (except main database, journal, and WAL, as well as super journals which have no data).

So it is really important that they are kept in memory. And they will, by default, with SQLITE_TEMP_STORE. So SQLite3 Multiple Ciphers should be OK, I agree.

At most, I think you should tweak the website because I don't think the claim about subjournals holds. They're not encrypted… but they should also stay in memory with the default settings.

PS: to be clear: with PRAGMA temp_store=memory none of these temporary files are created so, since that's the default for this build, you're safe, IMO; it's just important that this is made clear.

[utelle]

At most, I think you should tweak the website because I don't think the claim about subjournals holds. They're not encrypted… but they should also stay in memory with the default settings.

Yes, subjournals impose a problem due to the null filename, unless they are kept in memory.

PS: to be clear: with PRAGMA temp_store=memory none of these temporary files are created so, since that's the default for this build, you're safe, IMO; it's just important that this is made clear.

I will try to improve the documentation in this respect.