How to generate *.db on server side and send to Android client?

SQLCipher
1

Sup gentlemen, I’m new to the club.

I’ve tried the SQLCipher for Android and it worked out perfectly, nice stuff. But it turned out that I need to generate my app specific databases on server side for these reasons:

  1. Parsing and database creation from Standard SQLite to SQLCipher SQLite increased by ~250% in time, this will result on client getting mad. :stuck_out_tongue:
  2. There’s a special need to generate those databases on server-side just because they’re the same for every client, this would reduce the massive request, client processing power and such. Saves my server and client for too much processing (imagine hundreds of clients doing the app data json requests and building the SQLCipher DB at the same time?)

What do I need?

My Android App is being currently served by Java (Spring) REST Services on a Windows Enviroment, so… Is there a Desktop Java SQLCipher API avaiable? Is there a Windows PHP SQLCipher’ed PHP module avaiable?

These questions are for Windows 7 x64 env, but I plan to go to a clustered Linux Production server in the future as well.

How can I solve this problem?

Thank you very much!

Renato C

2

Not at the moment, while we have done some initial work on a JDBC connector for SQLCipher, it is not currently available.

We do not offer that as a commercial product, however you might consider using the documentation for building SQLCipher for PHP as a reference to building it on Windows.

We offer various commercial libraries here depending on your specific needs, if you have a specific question around those offerings we would be glad to help!

3

Hey,

Thanks for your reply, it is amazing to know that you’re working on that JDBC Driver. And I might even consider paying for one of your projects, I just need to get into production phase though.

I managed to compile your Windows version of SQLCipher for testing and I am having problems.

I am creating a SQLite Database using Java and as soon as I hit it with these commands:

R:\client\sqlite_database>sqlcipher plain.db
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> ATTACH DATABASE 'test.db' AS test KEY 'akey';
sqlite> SELECT sqlcipher_export('test');
sqlite> DETACH DATABASE test;

And afterwards issue this:

R:\client\sqlite_database>sqlcipher test.db
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA key = 'akey';
sqlite> select * from pa;
1|Andorra|AD|AND|20
2|Emiratos Árabes Unidos|AE|ARE|784
3|Afeganistão|AF|AFG|4

I am having encoding problems…

This is the dependency I’m using to generate the database:

<dependency>
	<groupId>org.xerial</groupId>
	<artifactId>sqlite-jdbc</artifactId>
	<version>3.7.2</version>
</dependency>

EDIT: I’ve tried using the generated encrypted database from sqlcipher_export() from the procedure above and sent it to my Android app, using the same password. This is the result:

net.sqlcipher.database.SQLiteException: file is encrypted or is not a database

EDIT 2: Seems that I get the same output from a simple “SELECT * FROM pa;” on the generated sqlite database from Java. I’ve updated the dependency to 3.8.6 and re-generated the database to match the SQLCipher SQLite version (3.8.6) but no go…

R:\client\sqlite_database>sqlcipher plain.db
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM pa;
1|Andorra|AD|AND|20
2|Emiratos Árabes Unidos|AE|ARE|784
3|Afeganistão|AF|AFG|4

LAST EDIT:

I am major dumb. Reasons:

  1. Windows cmd doesnt output UTF-8, only ASCII (forgot my first days of Computer Science… :frowning:), that’s why the output is weirdo.
  2. Android and Java SQLite + SQLCipher DOES match, I got it working. The reason it was not working before was that I was moving the database inside my app’s /database folder and user/group was root, the app cannot read files outside its user/group.

Admin can mark this thread as RESOLVED.

Note: Maybe, the dev could say net.sqlcipher.database.SQLiteException: file is encrypted or is not a database (have you also check your file user/group/permissions?)

Thank you all!

4

Hi @renatoaraujoc

I’m glad you were able to resolve the issue you were seeing.

5

Hi,
I am also working on the same issue. I am using a scheduler to create db file . How can I use these commands in java for encrypting the db. Iam using cordova-sqlcipher-adapter. Is there any way using SQLCipher for encryption while creating db

sqlite> ATTACH DATABASE ‘test.db’ AS test KEY ‘akey’;
sqlite> SELECT sqlcipher_export(‘test’);
sqlite> DETACH DATABASE test