SQLite Encryption performance

Can anyone state, in general terms, if there is much of a performance hit if you encrypt an SQLite database, versus using it unencrypted? Is the data decrypted by SQLite on every query or does it get decrypted in memory when it is first loaded and from that point on it is more less working with unencrypted data? There doesn’t seem to be much of performance hit from what I can tell, our database is not that big, around 2.5 MB in total size). Performance is half the issue. The other half is if it is encrypted, and I get a customer problem in house, it has to be either decrypted before we can load it in our standard DB editor we use (Navicat) or loaded into another one that will read DB’s encrypted by Xojo (there is another thread a couple of weeks ago about why they are different).

Thanks for any help.

I doubt you’d even notice it as far as performance goes
http://www.hwaci.com/sw/sqlite/see.html
Where ever it resides its always encrypted
Its read & written in encrypted form as far as I know

This is a few years old but is probably still relevant today. Note that Richard Hipp, the architect and primary author of SQLite, replied regarding the performance impact of using encryption.

http://sqlite.1065341.n5.nabble.com/SQLite-Encryption-Extension-Performance-td62865.html

This may also be useful, especially the How SEE Works section at the bottom of the page.

https://www.sqlite.org/see/doc/trunk/www/readme.wiki

we always use encryption. And if you use pragma for big cache, you will probably never notice:

db.SQLExecute "PRAGMA cache_size = 20000"

what do i need to do the database to include encryption. I use SQLiteManager to maintain my data file. I do i need to do in my project to read/write the encrypted data file?

Read the documentation and focus on the Encrypted Databases section.

Perhaps someone else can reply regarding SQLiteManager because I don’t use it.

seem like i need to create a new encrypted database. Is it possible to convert an existing database to encrypted database?

Yes… You encrypt and decrypt whenever you want. No need to create a new DB

i think i must have mistaken when reading the documentation. so all i need to do is supply a “db.EncryptionKey” on my data file.

[code]Encrypted Databases
AES-128 encryption is used. For more information about the encryption, refer to the SQLite Encryption documentation.
To create an encrypted database, specify a value for the EncryptionKey property. The EncryptionKey property must be set before calling either Connect or CreateDatabaseFile. In other words, write something like this to create a new database:
Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem(“db.sqlite”)
db.EncryptionKey = “howdy+doody”
If Not db.CreateDatabaseFile Then
//handle error here
End If

When you open an encrypted database file, you need to supply the key:
Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem(“db.sqlite”)
db.EncryptionKey = “howdy+doody”
If Not db.Connect Then
//handle error here
End If[/code]

Thanks Christian, I will try that out.

i try the second code with my database that was not encrypted and i get error when opening the sqlite file “library routine called out of sequence”

Don’t you have to call DB.Encrypt(“myEncryptionKey”) right AFTER DB.Connect( ) to encrypt it initially?

Then after it is encrypted, when you try to actually access it, you use DB.EncryptionKey = “myEncryptionKey” right BEFORE DB.Connect( ).

let me try Merv…

it work!!! on sqlite… anyone know what command to use for CubeSQL??

the db.EncryptionKey = “myEncryptionKey” and DB.Encrypt(“myEncryptionKey”) does not work for CubeSQL