Is there any way to obtain a current key?

[little-brother]

I develop an attachment feature in my app. I found that to attach a new database, I have to do the next steps:

1. Obtain a current key. That is a current/primary db key or empty.
2. Reset key to ''
3. (optional) Setup a new key and cipher
4. Attach a database
5. Recover the cipher key (perhaps, cipher + opts too) from step 1.

I suppose that pragma key was removed due security reason. But maybe an C-API way is exist?

[utelle]

I develop an attachment feature in my app. I found that to attach a new database,

What exactly do you mean by attaching a new database? Typically, already existing database files are attached, but in principle, it is also possible to attach a database file that doesn't already exist.

I have to do the next steps:

1. Obtain a current key. That is a current/primary db key or empty.

Are you talking of the passphrase of the main database? You don't have to retrieve it, if you want to use the same cipher scheme and passphrase for the attached database.

2. Reset key to ''

Why? If you issue a PRAGMA KEY command for the main database you will no longer be able to access the main database.

3. (optional) Setup a new key and cipher

If the database you intend to attach should use a different passphrase and/or cipher scheme than the main database, then you need to specify these parameters, of course.

4. Attach a database

The SQL ATTACH command has the form

ATTACH [DATABASE] <db-file-expression> AS <schema-name> [KEY <passphrase>]

1. If you want to use the cipher scheme and passphrase of the main database, do not specify a cipher scheme (via URI parameter or via PRAGMA) and do not use the KEY kewyword - the cipher scheme and the passphrase of the main database will then be used automatically.
2. If you want to attach a plain unencrypted database, specify an empty passphrase.
3. If you want to use a different cipher scheme and passphrase, specify both - the cipher scheme via URI or PRAGMA and the passphrase via the KEY keyword (or via URI parameter).

5. Recover the cipher key from step 1.

Why? As long as the connection to the main database is not closed, you don't need the cipher scheme or passphrase again.

I suppose that pragma key was removed due security reason. But maybe an C-API way is exist?

No. The "problem" is that the key/passphrase needs to be known on execution of the ATTACH command - issuing a PRAGMA KEY statement afterwards would be too late. Therefore you have to specify the passphrase via the KEY keyword (or via URI parameter).

[utelle]

An alternative method to attach a database would be

1. Execute the ATTACH command with an explicit empty key after the KEY keyword (to avoid that the passphrase of the main database will be used),
2. (Optionally) execute PRAGMA commands to set the cipher scheme to be used for the attached database,
3. Execute PRAGMA <schema-name-of-attached-database>.KEY='passphrase'; to establish the encryption key.

[little-brother]

What exactly do you mean by attaching a new database?

Thank you for the explanations. I should have given an example to eliminate unnecessary questions.

I develop SQLite editor and I want to allow to users attach any databases in any time (open encrypted database and attach unencrypted or open encrypted and attach another encrypted or any else). Let suppose, one opened an unencrypted database bs.sqlite and later he attachs encrypted database crypt_a. It's how SQLite-cli code that works can be

(1) .open bs.sqlite --> open 
(2) select count(*) from sqlite_master; --> test connection = 15
(3) pragma cipher = 'aes128cbc'; --> set cipher for a new connection to `crypt_a`
(4) pragma legacy = 0;
(5) pragma key = 'a'; --> set passphrase
(6) .open crypt_a.sqlite --> open
(7) pragma key = ''; --> restore passphrase for bs.sqlite
(8) pragma database_list; --> check that both bs.sqlite and crypt_a.sqlite are connected
(9) select * from crypt_a.t; --> test crypt_a.sqlite

If I skip pragma key = ''; (7) or instead of pragma key = 'a'; (5) + .open crypt_a.sqlite (6) do .open crypt_a.sqlite as crypt_a key '' + pragma crypt_a.key = 'a'; then crypt_a is not attached.

This is my test code.

P.S. Maybe .open crypt_a.sqlite as crypt_a key 'a' is a solution. I need to check it in my code.

[utelle]

I develop SQLite editor and I want to allow to users attach any databases in any time (open encrypted database and attach unencrypted or open encrypted and attach another encrypted or any else).

That really shouldn't be a big deal.

Let suppose, one opened an unencrypted database bs.sqlite and later he attachs encrypted database crypt_a. It's how SQLite-cli code that works can be

(1) .open bs.sqlite --> open 
(2) select count(*) from sqlite_master; --> test connection = 15
(3) pragma cipher = 'aes128cbc'; --> set cipher for a new connection to `crypt_a`
(4) pragma legacy = 0;
(5) pragma key = 'a'; --> set passphrase
(6) .open crypt_a.sqlite --> open
(7) pragma key = ''; --> restore passphrase for bs.sqlite
(8) pragma database_list; --> check that both bs.sqlite and crypt_a.sqlite are connected
(9) select * from crypt_a.t; --> test crypt_a.sqlite

Your CLI code above doesn't attach a database. Line (6) opens a new database connection for crypt_a.sqlite, but what's more important: it closes implicitly the first database connection opened in line (1). That is, line (8) will always show a single line for the "main" database. Finally, line (5) sets the key for bs.sqlite (!!!) ... and since bs.sqlite is not encrypted you will get an error message on the next access to bs.sqlite.

If I skip pragma key = ''; (7) or instead of pragma key = 'a'; (5) + .open crypt_a.sqlite (6) do .open crypt_a.sqlite as crypt_a key '' + pragma crypt_a.key = 'a'; then crypt_a is not attached.

Again, this can't work, because .open does not attach a database (and it doesn't support the syntax of the ATTACH command either.

Please find below CLI code that should do what you intend:

(1)  .open bs.sqlite --> open
(2)  SELECT count(*) FROM sqlite_master; --> test connection = 15

     -- Variant A (for attaching an existing non-empty database)
(3A) PRAGMA cipher = 'aes128cbc'; --> set cipher for the next connection
(4A) PRAGMA legacy = 0; -- not necessary, because it is the default
(5A) ATTACH 'crypt_a.sqlite' AS crypto KEY 'a';

     -- Variant B (for attaching an existing non-empty database)
(3B) -- use URI parameters instead of PRAGMAs
(4B)
(5B) ATTACH 'file:crypt_a.sqlite?cipher=aes128cbc&legacy=0' AS crypt_a KEY 'a';

     -- Variant C (for attaching an existing non-empty database)
(3C) -- use URI parameters instead of PRAGMAs
(4C) -- specify key as URI parameter
(5C) ATTACH 'file:crypt_a.sqlite?cipher=aes128cbc&legacy=0&key=a' AS crypt_a;

(6)  PRAGMA database_list; --> check that both bs.sqlite and crypt_a.sqlite are connected
(7)  SELECT * FROM crypt_a.t; --> test crypt_a.sqlite

     -- Variant D (for attaching a NEW and EMPTY database ONLY (!!!))
(8D) ATTACH 'crypt_new.sqlite' AS crypt_new KEY '';
(9D) PRAGMA cipher = 'aes128cbc'; --> set cipher for the attached database
(10D) PRAGMA crypt_new.key='new'; --> set key for new attached database, schema name crypt_new MUST be used here

Variant D really works only for attaching a not yet existing empty database. For existing non-empty database files the key/passphrase MUST be specified in the ATTACH command.

[little-brother]

Your CLI code above doesn't attach a database. Line (6)

Oh, sorry, I'm a little distracted today. (6) .open crypt_a.sqlite --> open means (6) .attach crypt_a.sqlite --> attach, of course.

Thanks. Variant C looks like the best solution for me (since it doesn't use pragmas for main-scheme). I'll try it.

[utelle]

Oh, sorry, I'm a little distracted today. (6) .open crypt_a.sqlite --> open means (6) .attach crypt_a.sqlite --> attach, of course.

AFAIK .attach is not a known SQLite shell command. You have to execute the SQL command ATTACH.

Variant C looks like the best solution for me (since it doesn't use pragmas for main-scheme).

1. From a security standpoint of view you should prefer variant B. URI parameters will stay in memory as long as the corresponding connection is open (where a potential attacker could find them), while your application can clear the memory holding the ATTACH command immediately after the command has been executed.
2. The PRAGMAs for configuring a cipher scheme are not exactly linked to the main database scheme, but to a SQLite connection object. Actually, the schema name if given in a PRAGMA command will be ignored for those cipher schema configuring PRAGMAs (with a few exceptions like the PRAGMAs key or rekey which affect exactly one database schema within the current connection).

[little-brother]

.attach is not a known SQLite shell command.

Yep, I used it for code simplicity. I have to use It's how pseudo SQLite-cli ... instead of It's how SQLite-cli ....

Finally, I implemented B, because in this case any special chars in the key can be masked by SQLite quote-function. In case C I need to mask it by URI-rules e.g. replace spaces by %20.

Thanks a lot for your comments. They are really usefull for me :)