How can i encrypt an existed plain db with data already in it?

[MrHxxx]

Hi, I try to open an existed plain db with data, but when i connect, there is error "NOTADB"

I wonder if i need to encrypt the db first, so i tried many api from sqlite-jdbc-crypt but failed

there this some ways to encrypt an existed db with original sqlcipher phase i know:

$ ./sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
sqlite> SELECT sqlcipher_export('encrypted');
sqlite> DETACH DATABASE encrypted;

i wonder whether i can do the similar thing with [sqlite-jdbc-crypt] sdk?

what i want to do is :

1. encrypt an existed plain db
2. reserve the data from plain db
3. open db and connect db with cipher crypt

Expect your reply, thank you very much!

[Willena]

Hello,

Opening a plain (not encrypted) database should not be an issue. sqlite-jdbc-crypt is mainly tested with plain databases.

I see you are using sqlcipher, all options for that cipher are documented here. You can use the SQLiteMCSqlCipherConfig class to help you configure the connection to the (encrypted or not) database in your java source code.

The general key encryption manipulations are documented here : https://github.com/Willena/sqlite-jdbc-crypt/blob/master/USAGE.md#encryption-key-manipulations; If you are configuring the connection using helper configuration classes, like SQLiteMCChacha20Config, SQLiteMCConfig, SQLiteMCRC4Config, SQLiteMCSqlCipherConfig, SQLiteMCWxAES128Config or SQLiteMCWxAES256Config step 2 of the previous instructions is automatically done.

The README files on this repository are a little bit messed up, make sure you are using this dependency to use encryption capabilities.

<dependency>
  <groupId>io.github.willena</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>3.44.1.0</version>
</dependency>

Example connection using SQLiteMCSqlCipherConfig and SqlCipher v3 default configuration

        try(Connection con =
                new SQLiteMCSqlCipherConfig.getV3Defaults().build().createConnection("jdbc:sqlite:file:/home/toto/myfile.db"))
        {
        // Do someting with the database
        // con.createStatement().execute("SELECT * FROM table")
        }

Note: I would back-up (copy) the original file before doing this kind of operation.

[MrHxxx]

Thanks very much for your kindly reply first. I used the previous version, here is my pom:

io.github.willena sqlite-jdbc 3.40.1.0

And I have tried your way before: try { String url = "jdbc:sqlite:C:/sqlite/test.db"; SQLiteMCSqlCipherConfig config = SQLiteMCSqlCipherConfig.getV3Defaults().build(); Connection connection = config.createConnection(url); } In this way I can connect my plain db (test.db), but when I tried to encrypt it with key, it occurs connection error: SQLiteMCSqlCipherConfig config = SQLiteMCSqlCipherConfig.getV3Defaults().withKey("111111").build(); the error is "org.sqlite.SQLiteException": [SQLITE_NOTADB] File opened that is not a database file (file is not a database) Is it for this reason for wrong pom version or my coding way?

What my confusion is, whether I can just use sqlite-jdbc-crypt and java code to achieve my goal:

1. I have a test.db (exist plain db, with data already in)
2. Connect the db
3. Encrypt the db by sqlite-jdbc-crypt and java code ,automatically, using my custom key (maybe generate a new encrypted db? I saw somewhere , it says sqlcipher can't encrypt a plain db directly, only by generating a new db and transfer data from the old db to it)
4. Use as usual
5. Keep the original data of plaint db (test.db) during the whole process

Currently I have achieved the goal by using original sqlcipher phase, but I want to change to coding way. Hoping that I have expressed myself clearly, thanks again!

[Willena]

Hello,

but when I tried to encrypt it with key, it occurs connection error: SQLiteMCSqlCipherConfig config = SQLiteMCSqlCipherConfig.getV3Defaults().withKey("111111").build(); the error is "org.sqlite.SQLiteException": [SQLITE_NOTADB] File opened that is not a database file (file is not a database) Is it for this reason for wrong pom version or my coding way?

The code is indeed not correct; To encrypt an existing plain database with data:

1. You must open the database with the cipher configuration without setting the key. Connection connection = SQLiteMCSqlCipherConfig.getV3Defaults().build().createConnection(url);
2. Manually execute the PRAGMA rekey statement (as described here https://github.com/Willena/sqlite-jdbc-crypt/blob/master/USAGE.md#encrypt-a-plain-database) : connection.createStatement().execute(String.format("PRAGMA rekey=%s", yourKey));

After the connection to the DB is closed, you can use a configuration that sets the key and use the database as usual: SQLiteMCSqlCipherConfig.getV3Defaults().withKey("111111").build()

it says sqlcipher can't encrypt a plain db directly, only by generating a new db and transfer data from the old db to it

The implementation in this library is not based on sqlcipher source code, I cannot guaranty having the same behavior. The library only provides the cipher primitives and function to be compatible with sqlcipher encryption and sqlcipher produced databases.

Keep the original data of plaint db (test.db) during the whole process

Just copy the file before encrypting it, it would be easier. After the rekey operation, the original file will be altered !

[MrHxxx]

ok,i'll try,thanks!