Does sqlcipher windows supports encryption for memory table?
Hello @rajendradewani - you can create an in memory database by using the special filename “:memory:”. However, in the context of SQLCipher, it is somewhat unusual to want to do so. SQLCipher is designed to provide long term protection of data persisted in database files. If your data is transient in nature and stored only in memory, then there is a much different threat profile. If an attacker already has the administrative privileges necessary to directly access the system memory of a different process, then your application could no doubt be compromised in other ways, e.g. by reading plaintext information directly from controls, or extracting key material from memory and using that.
Hi,
Yes I am accessing it via “:memory:” and noticed performance issue when using SQLCipher.
So, my question here is, is SQLCipher encrypted memory to temp tables ?
If it is not, then the performance could be sqllite issue, if SQLCipher does encrypt the memory table it may be SQLCipher is having performance issue.
@rajendradewani - If you are providing a key (e.g. via sqlite3_key or PRAGMA key), then SQLCipher encryption will be active. If you have not provided a key after opening the database, then it will not. With respect to performance, I would suggest taking a look at this post on SQLCipher performance, which explains so of the practical impacts of SQLCipher and how to ensure optimal performance.
With respect to temporary storage, those are different than in memory databases. You can look at the “Database Encryption and Temporary files” section of the design documentation for more information about what is / is not encrypted.
Hi
I have provided key, my database is encrypted.
Journal file is on - i.e the default settings.
I am not able to find a straight answer , i am using “:memory:” after opening the encrypted database using key.
Then I use “:memory:” and use insert into from a physical table to a memory table.
Is memory table encrypted ?
is temp table encrypted ?
@rajendradewani The reason you are not finding a straight answer is that it is still completely unclear what your application is actually doing. There is no such thing as a “memory table” or “temp table” in this context. If you have an in-memory database, and you have provided a key for it after opening it, or attached it to an existing keyed connection, then SQLCipher encryption will be used. Temporary files are encrypted as described in the aforementioned document. However, what is a “temporary” table from your application perspective may not be a temporary file from SQLCipher’s perspective. It might be more helpful to answer if you could post the code you are using.
@sjlombardo,
Thank you for sharing the detail. sorry got confuse with memory and temp table.
Here are more details
We have created a physical database - Encrypted with SQLCipher.
We inserted 500k records in a table.
using SQLLite3_Open we used “:memory:” database.
Then we attached the physical database. During attaching, we did pass the encryption key
We created a table in the Memory table using create table as TableOfPhysicalDb
Detach the physical database.
We then read the table’s row, one by one which is in the memory database.
We noticed performance issue when reading the records one by one.
Earlier we were using sqlcrypt with the same procedure as above, the performance with 500k records when using with sqlcrypt is a lot faster. When using SQLCipher we noticed performance issue and it slower by approx 40%.
We tried SQLCipher Static library and dynamic library but there we did not find any difference on the performance.
Hello @rajendradewani
Please keep in mind that the key derivation process is slow by design. Is there a reason you are not querying directly from the encrypted database?
Hi,
key derivation process is slow but when compared with previous encryption tool we were using, sqlciper seems to be having performance loss approx 35-40%
I would like to know if the ":memory: database, which does not have key but gathers data using attach keyword from an Encrypted database, will sqlciper use encryption on the memory database?, if not why I am encountering slowness. Is there anything else we are missing.
We cannot query encryption table directly, because multiple application may use same database and our application collects data to memory database from varies tables and then presents data back to use. We have being using this for years and is not possible to change that core logic.
Hello @rajendradewani
When you have a connection to a :memory: database, that means that specific database is ephemeral, and will not be written to disk. You are attaching a SQLCipher encrypted database with a key to the :memory: database. In order to access any of your data on the encrypted database, it must first be decrypted. When you create the in memory table and select all the data from then encrypted database, all the physical data must be decrypted. The first step in this process is to turn your password into a key using PBKDF2 which is a slow process by design. We don’t know whether your former database product is using key derivation, but if not this could easily account for a significant performance difference (key derivation can take up to a couple seconds depending on the device, but is usually less). From that point accessing the data from the memory database would not involve any further encryption. However, if you are comparing start to finish times for the load and query process, SQLCipher would appear slower because of the key derivation step - that is to say that your process of loading data into memory does not make the process faster (all the data still needs to be decrypted).
We would still recommend tracking down any performance issues with our suggestions here - PRAGMA cipher_profile; might be particularly helpful to find exactly where the slowdown is occuring. If you’d like additional support with this it would really be neccessary to see the exact code or have a reproducilbe test case; for that we would strongly suggest commercial support. Thanks!
Hi
I am comparing the time after the data is inserted into a table in “;memory:” and not the transfer.
After the table is filled with the required data, we detach the encrypted database.
we then read the table from “;memory:”, each row, one by one,
select fieldnames from table where rowid=1
select fieldnames from table where rowid=2
select fieldnames from table where rowid=3
We are facing performance issue when reading records from “;memory:”,
Hello @rajendradewani. In that case, then SQLCipher encryption should not be involved in the queries.
It is possible that there are other factors at play. First, you should verify you are using the database name “:memory:”. In your most recent post, you twice mentioned “;memory:” (i.e with a leading semicolon). If you pass that string it will open a file called “;memory:”. You could also try to swap SQLCipher out for a standard build of SQLite, and try the same experiment for comparison.
I’m not sure what else we can do to help you here from a general standpoint. If you’re using SQLCipher in a commercial application and you’d like to have a more thorough review or discussion, feel free to reach out to us at support@zetetic.net about a commercial / enterprise support subscription.
(i.e with a leading semicolon)
Raj : sorry that is a typo.
Will try experimenting direct use of SQLLite and compare the performance.
On a different topic.
Is it possible to use SQLLite version from sqlite site and use with SQLCipher, instead of using the sqllite version provided with SQLCipher?
SQLCipher itself includes a full version of SQLite, where we integrate the direct source releases provided from SQLite into our source tree. Changing the version of SQLite used within SQLCipher is possible, however doing so is not something that we document publicly or recommend.
Will try experimenting direct use of SQLLite and compare the performance.
Raj: using direct sqllite latest version version 3.9.2.
I am comparing the time after the data is inserted into a table in “:memory:” and not the transfer.
After the table is filled with the required data, we detach the encrypted database.
we then read the table from “;memory:”, each row, one by one,
select fieldnames from table where rowid=1
select fieldnames from table where rowid=2
select fieldnames from table where rowid=3
We do not see perfomance issue.
Changing the version of SQLite used within SQLCipher is possible,
Raj: Would it be possible to share the documentation. I want to try latest sqllite version with SQLCipher.
or if you have a precompile or a version of SQLCiper with latest sqlite , I would like to try and troubleshoot the issue.
Or if 3.9.x is not available in SQLCipher any version higher then 3.8.11 would also work. I see 3.8.11 and latest version have performance fixes.
https://www.sqlite.org/releaselog/3_8_11.html
SQLite now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9.
Hello @rajendradewani
The the approach you mention of querying individual rows by row id is horribly inefficient, regardless of whether you are using an in memory database. You should really consider changing that to select all data, and then process the result set row by row (e.g. select fieldnames from table order by rowid). That alone could considerably improve performance, regardless of the version of SQLite you are using.
We are aware of the performance improvements in recent versions of SQLite (we follow the updates closely). That said, we do not currently have a version available based on a higher upstream SQLite right now. Each SQLCipher update has to be carefully tested and rebased on selected SQLite versions that have proven stable. You can read more about this here if you are interested, in the section entitled “Packaging”.
https://www.zetetic.net/sqlcipher/design/
It is worth noting that we are planning another update in the near future, so feel free to keep an eye out for announcements.
HI @sjlombardo,
Thank you for the valuable feedback and will try to incorporate it in our application.
As the application is already built & tested and is used by customer, for the time being I would stay with the current logic of rowid.
I will wait for the new version and see if that fixes the performance issue.
Once the performance is fixed, we will go to the next level of optimization (i.e the one you suggest) .
Performance comparison between SQLCipher, SQLCrypt and SQLite.
When using
select fieldnames from table where rowid=1
select fieldnames from table where rowid=2
( data already placed in the Memory database and the encrypted databse is Detached ).
SQLCipher:
Table Name Records Time
InvoiceLine 14728 45m 25s
SalesOrderLine 5342 14m 32s
SQLCrypt:
Table Name Records Time
InvoiceLine 14728 29m 10s
SalesOrderLine 5342 8m 55s
SQLite(v3.9.2):
Table Name Records Time
InvoiceLine 14728 12m 52s
SalesOrderLine 5342 4m 21s
Hello @rajendradewani. The timings are interesting. If you’d like to post a working performance test project that we can independently use to reproduce these results we’d be happy to look into it further.
hi @sjlombardo , We have created a sample application in VC++. The sample demonstrate the time it take to dump data from physical database (encrypted) to memory database and then querying the memory database tables.
Dump will have difference when comparing it with Encrypted database and non-Encrypted but Querying should not have much difference.
Please share which is the best way to share the sample application - with source code.
-Regards
Rajendra Dewani