Does anyone know how this API integrates with a standard SQLite database?
There is like a $2000 licnense fee (which is way out of my budget), it is something that Xojo DOES include in their SQLite package, but something that Apple does not.
I could (if I could afford it), buy the API and recompile a new SQLITE module, but I thought if I could figure out what it actually did, I might be able to integreate similar functionality into my Swift SQLite wrapper. But I’m not sure how it alters the normal SQLite process flow
Unless you have the sqlite source you sent be able to do it as an “add on”
It actually NEVER decrypts that data in the database and the reading/writing of data is ALWAYS encrypted using one of several mechanisms
The code changes how the reading & writing of data to the database file is done
The only way to use/create an SEE database is with the SQLITE code that supports it
Does Swift have any encryption APIs itself? If it does, why not implement your own encryption/decryption mechanism client side within your wrapper to encrypt before saving to the DB and decrypting after retrieving from the DB?
8th supports encrypted SQLite databases with it’s built-in version of SQLite, so I thought it was a feature of SQLite! The actual encryption uses AES-256-GCM and the actual database is encrypted including all the metadata. Naturally encryption key is not stored in the database, and if you lose or cannot recreate it, you will not be able to access the data.
I could have asked the developer of 8th how he handles SQLite database encryption to give you some ideas. I am pretty sure he have not paid 2000 $ or use SEE SQLite add-on.
8th includes very extensive crypto support.There is a encryption tool written in 8th.
I was thinking that any query/update etc would need to have the relevant column data encrypted within the wrapper before being executed. Am I missing something that I didn’t think about?
I realize it’s a lot more work than just letting SEE do it all for you.
IF you use sqlite built with SEE you dont have to even think about it
The database appears absolutely normal in every respect and you use the same old sql as you would have with it unencrypted
Except everything stays encrypted
Other encryption add ons for sqlite dont work in the same way
And you’d have to do a lot more work
Simple queries with a simple where clause like select * from table where column = value would probably be able to be handled this way
But any relations between tables would need to be handled as well
Possibly involving having to decrypt data to perform the related query
Other query types might also be affected - LIKE for instance
Its one reason I truly appreciate that SEE makes it absolutely transparent
The main intent of this question was to see if anyone new HOW SEE worked. Where in the SQLite process it sunk its hooks, Exactly where/when a piece of data was encryptied/decrypted.
I “could” add code that would decrypt information as it is building the recordset to return
But to encrypt is more difficult, depending on the syntax of the query
SQLExecute("Insert into table values(?,?)",["A","B"])
is “easy” as the values are in an array that can be accessed prior to writing to the DB
but
SQLExecute("Insert into table values('A','B')")
not so much, as the values would need to be parsed out of the query and the query rebuilt
Each page is encrypted separately. The key to encryption is a combination of the page number, the random nonce (if any) and the database key. The data is encrypted in both the main database and in the rollback journal or WAL file but is unencrypted when held in memory. This means that if an adversary is able to view the memory used by your program, she will be able to see unencrypted data.
The nonce value is changed by a rollback.
The see-aes128-ccm.c variant uses AES in CCM mode with a 16-byte randomly choosen nonce on each page and and 16-byte message authentication code (MAC). Thus with crypto3ccm.c, 32 bytes of every database pages are taken up by encryption and authentication overhead. Consequently, database files created using crypto3ccm.c may be a little larger. Also, because the MAC is computed whenever a page is modified, and verified when a page is read, crypto3ccm.c will often be a little slower. Such is the cost of authentication.
You really don’t want to do it this way anyway. You would make your data unqueryable. Literally the only thing you could do is query entire rows by rowid. You couldn’t even use foreign key constraints or joins. You’d take away all the power of your database. It’d be easier to use a CSV instead, since you’d be throwing your database features out the window.
I get that, but you can’t. You need a version of SQLite with it built in. Look at the “how see works” info posted before me. How do you propose you access the page data?
In theory IF a person took the sqlite C source they could write their own IO routines to do encrypt pages like SEE does
Definitely NOT trivial but it is theoretically possible - like you or I building a rocket to the moon is theoretically possible but highly unlikely
AND a TON of work to get it right
Yes, it’s possible. The developer of 8th, Ron Aaron uses his own extension, full-page encryption so the entire database is encrypted, no meta-data visible.
