SQLite Forum

Can I get the file name of a temporary on-disk database?

Can I get the file name of a temporary on-disk database?

(1) By Tim Fors (tim44s) on 2020-12-03 19:23:23 [link] [source]

The docs for sqlite3_open state this:

If the filename is an empty string, then a private, temporary on-disk database will be created. This private database will be automatically deleted as soon as the database connection is closed.

Is there any way to get the name of the file that is created? I need it for a subsequent ATTACH DATABASE statement, but I can't see any way of getting it.

Creating my own temporary file so that I know the name of it (via mkstemp) doesn't work because a) Even though it's empty, it won't be an SQLite database file, so can't be passed to sqlite3_open, and b) To address a), I could delete the file that mkstemp creates and then pass the name of it to sqlite3_open to create a database with that file name, but of course that creates a gap between when msktemp generated the file name, and when SQLite actually creates a database with that name, and that eliminates the guarantee that the file name generated by mkstemp is uniquely available for the process to use. The risk of this might be small, but is something to be avoided if possible.

Any help on this would be greatly appreciated.



(2) By Richard Hipp (drh) on 2020-12-03 19:31:53 in reply to 1 [link] [source]

Is there any way to get the name of the file that is created?

No. On unix, the file is inaccessible, because it is unlinked as soon as it is opened. On Windows, the file is created for exclusive access with the delete-on-close and hidden flags set, so ever if you knew the name, you wouldn't be able to do anything with it.

(3) By Tim Fors (tim44s) on 2020-12-03 19:41:31 in reply to 2 [link] [source]

Thanks Richard. I was afraid that might be the answer.

So, there's really no way to accomplish this in a 100% reliable manner?

  1. Create a temporary database
  2. Use it in an ATTACH DATABASE statement (or any other context where the name might be needed)

It seems like I have no choice but to delete the file created by mkstemp and pass the name to sqlite3_open, which is far from ideal.

How do I submit a request that the SQLite team provide a solution to this in a future version?

(4) By Richard Hipp (drh) on 2020-12-03 20:05:07 in reply to 3 [source]

You can ask SQLite to give you the name suitable for use as a temporary database file using the sqlite3_file_control(SQLITE_FCTRL_TEMP_FILENAME) interface. Or you can create your own unique temporary filename using an SQL statement like this:

SELECT hex(randomblob(32));

Then simply run ATTACH with that filename. The new database will be created automatically.

(5) By Tim Fors (tim44s) on 2020-12-03 21:39:43 in reply to 4 [link] [source]

Yes, there are other ways I could generate a name for the temp database file. I was using tmpnam which is not considered safe, so started looking at mkstemp instead. It does give me the temp file name I need, however...

No matter how I end up with a temp file name, the issue remains: The creation of that file name, and the creation of the file with that name won't be "atomic", meaning that there's a slight chance of the same name being snapped up by some other process in between the time the name is generated and the file is actually created.

My understanding is that mkstemp provides that kind of atomicity (whereas tmpnam does not), but if I have to close and delete the file before passing the name into sqlite3_open to let it create a temp database with that name, the atomicity of msktemp is not helpful in this case. And nothing else is going to give me that atomicity either.

I assume that letting SQLite create the temp database itself (by passing an empty string to sqlite3_open) is also "atomic" in this sense, but again, that's of no use to me since I need to know what name it used so I can do an ATTACH DATABASE with it.

(6) By Richard Hipp (drh) on 2020-12-03 23:56:05 in reply to 5 [link] [source]

there's a slight chance of the same name being snapped up by some other process

With 256 bits of randomness in the name, the chance is so slight as to be meaningless. For all practical purposes, it is impossible.

(7) By Tim Fors (tim44s) on 2020-12-04 01:18:14 in reply to 6 [link] [source]

Yes, it is only a remote possibility. However, my understanding is that this is the very reason that "tmpnam" has been declared "dangerous" - there's no guarantee that between the time you get the file name and actually create the file and open it, some other process hasn't snuck in and already created it. gcc warns about its use and mkstemp is recommended instead because it creates and opens the file "atomically". But that doesn't help because SQLite can't use the file created by msktemp.

Since using tmpnam is considered bad practice, I was hoping to find some other solution that doesn't suffer from this same race condition, and as stated it does not seem possible.

Anyway, thanks for your replies. I think it would be wise for SQLite to provide some foolproof solution to this in the future - if "tmpnam" is dangerous due to the race condition it creates, I would think SQLite would also be interested in helping developers avoid the same kind of problem when it comes to temporary databases. Providing the name of a temp database created by SQLite would solve the problem, but there may be other ways too. For example, AFAIK, the only way to accomplish the ATTACH DATABASE capability is to use that statement with the name of the database file to attach, but perhaps an API could be created which allows the equivalent thing, but using the database connection handle instead of the file name. Just thinking out loud.

(8) By Tim Fors (tim44s) on 2020-12-04 01:24:23 in reply to 6 [link] [source]

Allowing a file with size 0 to be passed in on sqlite3_open would be another possible solution, with the semantics being the same as if the file didn't exist already i.e. go ahead and turn this file into an SQLite database file.

(9) By Richard Hipp (drh) on 2020-12-04 01:31:17 in reply to 7 [link] [source]

"tmpnam()" does not provide 256 bits of randomness in its name. It was invented long, long ago when AT&T unix had a filename length limit of 14 characters, which limited the amount of randomness that could be applied. And things like /dev/random had not yet been invented, which means that you couldn't easily get good randomness to use to invent the name - an attacker could guess your PRNG seed. Hence, tmpnam() is considered insecure.

But these days, all filesystems have much longer length limits, and so it is no problem to create a filename with an absurd amount of randomness taken from a high-quality source, such as /dev/random. The resulting filename is perfectly safe.

(10) By RandomCoder on 2020-12-04 01:34:23 in reply to 7 [link] [source]

While I feel that this is out of band for SQLite, that's up to the SQLite devs to make that call.

Could you not open a temp file using mktemp, write in an empty SQLite file you provide as part of your applications assets (and perhaps even have that file prepopulated with your basic schema if that makes sense), then use it as a normal db file using SQLite's normal interface?

(11) By Richard Hipp (drh) on 2020-12-04 01:55:04 in reply to 10 [link] [source]

Y'all are making this way harder than it needs to be:

  1. SQLite will happily open an empty file as if it were a database. There is no need to prefill it with valid content.

  2. If you really need a temporary file (for any reason, not just for use by SQLite) then grab a bunch of high-quality randomness (like from /dev/random), take the hex value of that randomness (or some other encoding, like base-32) and use that for the filename, and you will be perfectly safe. Just make sure that you use plenty of quality randomness - the output of rand() function from libc is not quality randomness and will not get you a secure filename - but the randomblob() function in SQL is and will work fine.

(12) By Tim Fors (tim44s) on 2020-12-04 02:01:23 in reply to 9 [link] [source]

Thanks for that info Richard. I am curious, though, why tmpnam has not been made more robust as the improvements you mentioned came along, so that the file name it produces benefits from the increased randomness. Would there have been any issues with backwards compatibility of code that somehow came to rely on its old and insufficient behaviour? Hard to imagine why that would be the case.

Instead (or rather, in addition to that increased randomness), the recommended solution is a function like mkstemp, which guarantees no "holes" between file name creation and creation of a file with that name. There doesn't seem to be a way to just get the file name like tmpnam did, but which has the improved randomness in it to make the race condition a non-issue.

I'm no expert on this topic so perhaps there's a good explanation that's not evident to me.


(13) By Tim Fors (tim44s) on 2020-12-04 02:06:22 in reply to 11 [link] [source]

SQLite will happily open an empty file as if it were a database. There is no need to prefill it with valid content.

Ah okay, good to know. I thought I encountered this problem earlier today, where I tried passing it the file created by mkstemp, and it didn't work. If I'm able to reproduce that I'll follow up, but if not then this is the perfect solution. Thanks.