System.Data.SQLite

Hello,

Is it possible to port the database encryption feature into UNIX and access them portably?

The reason I'm asking is this:

I have a system that already works with System.Data.SQLite and Oracle. A Winforms application calls PL/SQL packages in a Oracle database in a UNIX box, performing data preparation and filtering. Filtered records are retrieved/downloaded to several different encrypted SQLite databases in a Windows box.  Database file can reach up to 800M, some even reach 2G. Each SQLite database is then distributed to different organization.  Organizations use a Winforms application to read the file.

Now the client requests a big change on the architecture, ie: move all processing in a 64bit machine (Sun Solaris 10).This means that the preparation of the SQLite database needs to be done on the UNIX server. 

We can create non-encrypted SQLite db files and encrypt them on a Windows box later. But it will take a lot of time and defeats the purpose of moving to server side processing.

My initial thought is to modify Python's PySQLite to generate encrypted database in UNIX, but still allow System.Data.SQLite to access it.

I appreciate any pointers on how to do this.

Thank you,

IIRC The System.Data.SQLite encryption code is pretty windows specific in that it uses the windows crypto api.  However the method it uses to do the encryption ( sqlite's os abstraction) is standard stuff.  I dont see any reason that you couldnt write your own file system abstraction which implemented encryption in a crossplatform way.  This is however a lot of work and would involve a custom System.Data.SQLite build on windows & unix.

I think you'd have better luck either encrypting the database after it's been built then un-encrypting it before reading in windows or just encrypting the data in the application layer.

Jeff, Thanks for your input.

jeffreyabecker:

I think you'd have better luck either encrypting the database after it's been built then un-encrypting it before reading in windows or just encrypting the data in the application layer.

The application is already in production. If we add encryption layer in the application side, previous databases will not be readable. The work to make the front end layer backward compatible will be much more involved.

I may try a different route, ie:

1. rebuild a custom sqlite linked with openssl for RC4 and SHA1 in the UNIX box following information provided by sraillard in hist post in thread http://sqlite.phxsoftware.com/forums/p/473/2013.aspx#2013.  If I can pull this off, the frontend code will not need be changed.
2. adjustment of pysqlite with key/rekey methods linked with the custom sqlite lib
3. cross fingers
   

However, I'm not too sure wether I can duplicate the functionality in a UNIX environment.  Not to mentioned endianess and 32 vs 64 issues.

I'll give it a try on my 32bit linux box this weekend and will provide update OR more questions next monday :)

Thanks

Have you looked into Mono?  They have a sqlite provider originally based on the System.Data.SQLite code.  That might be easier to to modify.

jeffreyabecker:

Have you looked into Mono?  They have a sqlite provider originally based on the System.Data.SQLite code.  That might be easier to to modify.

Good idea.

I happen to have a subversion working copy of mono tree.  So updated to latest revision and skimmed through the code.

Unfortunately, mono does not support the database encryption feature.  The encryption support is implemented on the sqlite3 software (not on the System.Data.SQLite layer).  If they want to support the same encryption mechanism, it will mean that they will have to provide their own sqlite3 build.

Well,

After several days struggling along with CryptoAPI and openSSL, I finally managed to encrypt in Windows using the same method used in crypt.c and successfully decrypt in Linux.

I have no idea wether what I did was correct or not, but it works.

Now, I think it's just a matter of writing it properly.

Hi,

Just want to update:

I have successfully generate an encrypted database in Solaris (using modified SQLite 3.5.8 and pysqlite), and access it in Windows.  So, yes it possible, and it works.

Also, it turns out that we don't have to use OpenSSL to perform the encrypt/decrypt function. Instead, we can use any SHA1 and RC4 implementation.

Thank you,

congratulations!

Any chance you'll push some of these changes back to Robert? I'm working on similar changes also due to the fact that the crypto is windows specific and it seems like reducing any of the platform dependent issues would help everyone out.

Chris

I think Robert has purposely chose CryptoAPI for reasons decribed here and . Besides, my changes are actually on the sqlite3 source (not on System.Data.SQLite).

However, I'll prepare some documentation on how to do it.

Rgds,

rchavik,

Any updates on the documentation of how you did it?  It would be really great since others (like me) were looking into this.  Thanks.