Security of sqlite3_key() vs PRAGMA sqlite3_key?

[Tito Ciuro]

Hello,

Which is the preferred way to initialize sqlite3_key?:

1) Via function:

const char* key = "BIGsecret";
sqlite3_key(db, key, strlen(key));

-or-

2) Via PRAGMA:

sqlite3_exec(db, "PRAGMA key = 'BIGsecret', NULL, NULL, NULL);

Once the key has been set, is the value encrypted or obfuscated somehow in RAM? How easy would it be to determine the key by inspecting the memory space?

Thanks!

-- Tito

[Stephen Lombardo]

Hi Tito,

The two methods of initialization serve slightly different purposes. 

Using sqlite3_key is the preferred way of initiating the key if you have the ability to make the application changes necessary to call the function.

However, in many cases it's not possible to call sqlite3_key, for example:

1. running sqlcipher through an SQL command interface

2. replacing a .dll/.so with sqlcipher w/o making any application changes

3. when you don't have full control over the low-level SQLite interface due to the use of a wrapping library

The actual code path is quite similar, as the pragma just calls sqlite3_key anyway after the statement is parsed. 

With regard to your second question, SQLCipher does page by page encryption over the lifetime of an open database connection. Since SQLCipher doesn't ever fully decrypt or encrypt your database the key is needed, and stored in memory, as long as the database is open. When the database database handle is closed that memory is wiped.

Cheers,

Stephen

[Tito]

Hi Stephen,

So, while the database is open is the key encrypted while in memory?
If not, how difficult would it be for someone to determine what the
key is by inspecting the memory?

Thanks for your help,

-- Tito

[Stephen Lombardo]

Hi Tito,

The key is not encrypted while it is in memory. The reasoning for this follows; if we encrypted the key in memory, then we'd still need to have that new encryption-key stored in memory to get the database key to decrypt the data. Also, the database key would still need to be available any time the pager system was in use. Thus, the extra layers would not afford much additional security because keys are still in memory and must be available at the point data is read / written. 

In general, if an application is in a situation where rogue software can read it's running process memory then it it will be very difficult to secure using any software, including SQLCipher. 

If the goal is mainly reducing the exposure of a potentially long running process one option would be to open the database and then close the handle as soon as you're done with it. You could avoid most of the performance impacts by tuning down the key derivation iterations or using a raw key to disable derivation altogether. 

Is there a specific scenario that you are concerned with? If so, some additional details about the specifics might help address those concerns.

Thanks!

Cheers,

Stephen

[Tito]

Hi Stephen,

I'm very interested in SQLCipher and would like to know the pros and
cons, limitations, etc. The reason is that I was thinking about adding
support for SQLCipher in NanoStore (https://github.com/tciuro/
NanoStore)

Given that some developers will want to protect their data, knowing
how vulnerable SQLCipher could be is important. Depending on the app,
crackers out there would love to figure out the key to decrypt the
database. If the key sits in memory for as long as the database
connection is open, that would give a cracker ample time to debug the
memory. I understand that no methodology is bulletproof, but some
steps could narrow the window of opportunity. For example, would it be
possible to set the key before accessing the database and setting it
back to nil once finished? In other words, clear key and memset().
Then, set the key again before accessing the database and so on.

On iOS, I could even use the Keychain to store it permanently and read
it back automagically. The question is, can I set and clear the key
(i.e. sqlite3_key(db, key, strlen(key)) and sqlite3_key(db, nil, 0)
respectively) *while* the database connection is open? I'm assuming
you're a contributor to SQLCipher... so if this feature is not
implemented, would you consider adding it?

Regards,

-- Tito

[Tito]

Stephen,

I realize that I may have seemed a bit harsh with my SQLCipher
"vulnerability" comment. All software is vulnerable one way or
another. I'm not being negative about SLCipher any more than I would
be with my own software. I was referring to getting to know the
"places" where an attacker could take advantage and bypass the
security mechanism. If I didn't think that SQLCipher could fit the
bill, I wouldn't be spending time evaluating it, right? ;-)

Thanks again,

-- Tito

[Stephen Lombardo]

Hi Tito,

On Fri, Dec 23, 2011 at 2:48 PM, Tito <tci...@gmail.com> wrote:

All software is vulnerable one way or
another. I'm not being negative about SLCipher any more than I would
be with my own software.

I understand, there's certainly nothing wrong with scrutinizing software before you use it!

Key management is a deep and interesting topic, and subject to many security tradeoffs. The important thing to realize with SQLCipher is that it encrypts / decrypts the database in chunks as they are accessed. SQLCipher doesn't know ahead of time whether it's going to process 1 page for an index lookup in a small database or 200,000 for a full table scan. While operating at the page level provides excellent performance and scalability, it does require the key data to be around for the duration the database is in use.

In this light, I've tried to address your points below. 

 

> For example, would it be
> possible to set the key before accessing the database and setting it
> back to nil once finished? In other words, clear key and memset().
> Then, set the key again before accessing the database and so on.

This is possible - it's basically where I was going with the suggestion earlier to open the database, use it, and then close it when you are done. This is the safest way to do it because closing the database will ensure that everything is properly deallocated and wiped by SQLCipher. 

I would recommend against calling sqlite3_key to overwrite the key in memory. The reason is that if you changed the key, and then accidentally touched the database while an invalid key were set, bad things could happen. In the best case the database handle would become unusable, in a slightly worse case you could get invalid data back out to the application, or worst case even database corruption.

That said, the main concern I have about this approach in general is that it just shifts the responsibility onto the application. In other words, what does the application do with the key when SQLCipher isn't using it? 

Even if the SQLCipher doesn't have the key in memory, the application must if it will set the key before the database is used. In other words, unless an application will prompt the user to enter a password or key on every single operation, then as long as a database exists in a readable and writeable state, the key must exist in memory somewhere. We can move the key around, but it's sort of a shell game.

Further, to make it perform acceptably to set / unset the key on any sort of rapid schedule you would need to disable key derivation (provide a raw hex key to sqlcipher) or tune down key derivation (PRAGMA kdf_iter = x). The drawback is that while this would make setting the key faster, it could result in less security for the data at rest (i.e. the system could be more susceptible to brute force or dictionary attacks). 

> On iOS, I could even use the Keychain to store it permanently and read
> it back automagically.

If you assume that the Keychain is secure, you could wire the application to pull the key out of it dynamically. But that key is still going to be in memory at that point. If another application is able to read the process memory it could be compromised just as easily. 

Also, as an aside, I think there still some debate about how secure keychain is on iOS (http://sit.sit.fraunhofer.de/studies/en/sc-iphone-passwords-faq.pdf / https://github.com/ptoomey3/Keychain-Dumper), at least depending on how an application and individual phone is setup.

 

The question is, can I set and clear the key
> (i.e. sqlite3_key(db, key, strlen(key)) and sqlite3_key(db, nil, 0)
> respectively) *while* the database connection is open? I'm assuming
> you're a contributor to SQLCipher... so if this feature is not
> implemented, would you consider adding it?

In summary the preferred way of doing this with SQLCipher today would be to open/close the database as necessary. Using sqlite3_key to overwrite the key in a live connection is possible (though not with a nil value), but should be considered dangerous. 

In the longer term, perhaps a good compromise solution would be to allow a application to register a callback that would provide the key when needed.  This would allow an application to implement it's own measures for key protection and/or obfuscation, while minimizing potentially incompatible changes in the SQLCipher core. We'd still need to be careful though, because this could have some potentially significant impacts on performance, and we'd need to make sure the system could fail safe if a key was unavailable. We'll investigate this further as a potential enhancement in the new year.

Thanks, and Happy Holidays!

Cheers,

Stephen

[Tito Ciuro]

Hello Stephen,

Thanks for taking the time to write such a detailed explanation. My comments are inline:

On Dec 23, 2011, at 11:26 PM, Stephen Lombardo wrote:

In this light, I've tried to address your points below. 

 

> For example, would it be
> possible to set the key before accessing the database and setting it
> back to nil once finished? In other words, clear key and memset().
> Then, set the key again before accessing the database and so on.

This is possible - it's basically where I was going with the suggestion earlier to open the database, use it, and then close it when you are done. This is the safest way to do it because closing the database will ensure that everything is properly deallocated and wiped by SQLCipher. 

The problem I see with this approach is that it might be overkill for apps performing a large number of requests. NanoStore provides a high-level interface to SQLite, and closing/reopening the database would be an expensive operation. Even apps using pure SQLite might take a toll if many requests are performed during the life of the app.

I would recommend against calling sqlite3_key to overwrite the key in memory. The reason is that if you changed the key, and then accidentally touched the database while an invalid key were set, bad things could happen. In the best case the database handle would become unusable, in a slightly worse case you could get invalid data back out to the application, or worst case even database corruption.

Understood. The good news is that all interfaces performing SQL requests go through the same function. This brings down the number of places where I would have to set/unset the key to one.

That said, the main concern I have about this approach in general is that it just shifts the responsibility onto the application. In other words, what does the application do with the key when SQLCipher isn't using it? 

The beauty is that it's not the application responsibility. It's the framework, NanoStore. The app interfaces with NanoStore to manipulate the database. Therefore, I can shift the responsibility to NanoStore to store the key, retrieve it, set it and clear it from memory. All of this without any knowledge from the app's perspective.

Even if the SQLCipher doesn't have the key in memory, the application must if it will set the key before the database is used. In other words, unless an application will prompt the user to enter a password or key on every single operation, then as long as a database exists in a readable and writeable state, the key must exist in memory somewhere. We can move the key around, but it's sort of a shell game.

Not really. The NanoStore would store the key in the Keychain, not the user. Therefore, it's up to NanoStore to retrieve it as needed.

Further, to make it perform acceptably to set / unset the key on any sort of rapid schedule you would need to disable key derivation (provide a raw hex key to sqlcipher) or tune down key derivation (PRAGMA kdf_iter = x). The drawback is that while this would make setting the key faster, it could result in less security for the data at rest (i.e. the system could be more susceptible to brute force or dictionary attacks). 

> On iOS, I could even use the Keychain to store it permanently and read
> it back automagically.

If you assume that the Keychain is secure, you could wire the application to pull the key out of it dynamically. But that key is still going to be in memory at that point. If another application is able to read the process memory it could be compromised just as easily. 

The Keychain *is*secure. It all depends what mechanism the developer used to store and access the key. From the Fraunhofer paper:

If the developer chose to store the data in the keychain and instructed the system to make the stored credentials available even when the device is locked by setting the accessibility to kSecAttrAccessibleAlways (cp. [App10a]), then the app is affected. Otherwise the credentials of the app are not affected by the attack method.

In other words, only kSecAttrAccessibleAlways items are potentially affected. Also, please note that the person trying to obtain the keys from the Keychain would have to be in possession of the device.

Since NanoStore is a configurable framework, I would let the developer choose which method NanoStore would use to store the key:

kSecAttrAccessibleWhenUnlocked
kSecAttrAccessibleAfterFirstUnlock
kSecAttrAccessibleAlways
kSecAttrAccessibleWhenUnlockedThisDeviceOnly
kSecAttrAccessibleAfterFirstUnlockThisDeviceOnly
kSecAttrAccessibleAlwaysThisDeviceOnly

Regardless of the mechanism used, NanoStore will handle the key.

As for leaving the key in memory, it would be read and set right before performing the SQLite operation and cleared from memory right after entering SQLite's callback. At this point, I will clear and nilify the pointer. So the key will be in memory for as long as the SQLite op requires. Once finished, it'll be cleared from memory and the "original" stored safely in the Keychain.

Also, as an aside, I think there still some debate about how secure keychain is on iOS (http://sit.sit.fraunhofer.de/studies/en/sc-iphone-passwords-faq.pdf / https://github.com/ptoomey3/Keychain-Dumper), at least depending on how an application and individual phone is setup.

After reading the latest version of the report, it seems to really depend on two things:

1) The cracker must be in possession of the device

2) The key must have been stored using the kSecAttrAccessibleAlways method

The question is, can I set and clear the key
> (i.e. sqlite3_key(db, key, strlen(key)) and sqlite3_key(db, nil, 0)
> respectively) *while* the database connection is open? I'm assuming
> you're a contributor to SQLCipher... so if this feature is not
> implemented, would you consider adding it?

In summary the preferred way of doing this with SQLCipher today would be to open/close the database as necessary. Using sqlite3_key to overwrite the key in a live connection is possible (though not with a nil value), but should be considered dangerous. 

I'll set it with an empty string then. See how it goes.

In the longer term, perhaps a good compromise solution would be to allow a application to register a callback that would provide the key when needed.  This would allow an application to implement it's own measures for key protection and/or obfuscation, while minimizing potentially incompatible changes in the SQLCipher core. We'd still need to be careful though, because this could have some potentially significant impacts on performance, and we'd need to make sure the system could fail safe if a key was unavailable. We'll investigate this further as a potential enhancement in the new year.

Thanks, and Happy Holidays!

Cheers,

Stephen

I'll give it a try and see how well SQLCipher fits in NanoStore using the Keychain and this read/set/clear mechanism. I'll report back once I know more about it.

Thanks again and happy holidays!

Best regards,

-- Tito

[Stephen Lombardo]

Hi Tito,

I've made a few more final comments on this thread below. The first set is related to the security of the keychain, the second provides details about the fact that calling sqlite3_key will not clear the encryption key the way you need it to.

On Sun, Dec 25, 2011 at 6:31 PM, Tito Ciuro <tci...@gmail.com> wrote:

The Keychain *is*secure. It all depends what mechanism the developer used to store and access the key.

After reading the latest version of the report, it seems to really depend on two things:

1) The cracker must be in possession of the device

2) The key must have been stored using the kSecAttrAccessibleAlways method

There are a few other important points from the same report. The paper concludes that the iOS keychain should be considered secure ONLY if both of the following are true:

• Items are stored with a protection class that makes them only available when the device is unlocked. (see Section 2.14)
• A strong passcode of 6 alphanumeric digits is enforced (reduces the risk of brute-force attacks)

Relevant to the second point for device owners using a standard 4 digit pin:

The standard simple code of 4 numeric digits would be brute-forced in less than
9 minutes. Based on the assumption that the counter for wrong tries in the iOS can be
bypassed, as it is not hardware-based. [BS11b] provides a tool for passcode bruteforce
attack.

Relevant to the second point for device owners that don't use a pin:

2.18. What are the effects when no passcode is set? While being very convenient, this would eliminate the security provided by the iOS keychain. All entries, regardless of protection class, will be accessible with our attack.

As I understand it, based on the specific configuration of application and the device, the practical security of the keychain could vary from acceptable, to weak. Anecdotally, while I know of several people who don't have any passcode on their iPhone, I've never seen anyone use a 6+ digit alphanumeric code.

The practicality of these attacks seems well demonstrated. There are open source programs and also commercially available forensic tools that can brute force the standard iPhone pins (e.g. http://www.elcomsoft.com/).

We get a lot of questions on this list about key management, embedding keys, vaulting keys externally, etc. The general recommendation is that applications / libraries should not store the key on the device or in the application code. 

That said, like most things, there are tradeoffs involved. It seems like using the keychain to store the encryption key could negate some of the security of the data at rest, especially as level of security would be out of the hands of the application developer and could vary on a per-device basis. On the other hand, using the keychain could add a significant level of convenience by not requiring a secondary passphrase, and would minimize the amount of time the key is stored in memory. There is no perfect solution, so the right approach depends solely on the goals and the required security profile of the application. 

The question is, can I set and clear the key
> (i.e. sqlite3_key(db, key, strlen(key)) and sqlite3_key(db, nil, 0)
> respectively) *while* the database connection is open? I'm assuming
> you're a contributor to SQLCipher... so if this feature is not
> implemented, would you consider adding it?

In summary the preferred way of doing this with SQLCipher today would be to open/close the database as necessary. Using sqlite3_key to overwrite the key in a live connection is possible (though not with a nil value), but should be considered dangerous. 

I'll set it with an empty string then. See how it goes.

I did a bit of code review. Calling sqlite3_key with an empty string will *not* immediately overwrite and clear the key in memory. This is because the key derivation doesn't occur at the time the key is set. Instead, it occurs on the first database operation after the key is set. This facility allows a user to change other settings that might affect derivation, like the number of KDF iterations, but it means that you'd actually need to try a database operation with a bad key to clear the correct key.

In short, at this time, calling sqlite3_key will not have the effect you are looking for, and the only current way to do it without further code changes would be to close / open the database.

In the longer term, perhaps a good compromise solution would be to allow a application to register a callback that would provide the key when needed.  

I still think this is the most feasible approach in the long run, and will continue to investigate in light of the previously mentioned considerations. Another alternative might be to dig into the v2 sqlcipher code to change the way the key data is extracted.

Thanks!

Cheers,

Stephen

[Tito Ciuro]

Hi Stephen,

On Dec 28, 2011, at 7:23 PM, Stephen Lombardo wrote:

We get a lot of questions on this list about key management, embedding keys, vaulting keys externally, etc. The general recommendation is that applications / libraries should not store the key on the device or in the application code. 

I guess it all depends on what the developer/user is willing to trade: security vs. flexibility. If the general recommendation is to store the key away from the device, that means that the device must be connected to the Internet when the app launches or resumes its operation. That is a serious problem, as many of us who travel across countries don't have an international data plan or WiFi hotspots available everywhere. On the other hand, having the key on the device allows the app to continue working regardless of network connectivity.

That said, like most things, there are tradeoffs involved. It seems like using the keychain to store the encryption key could negate some of the security of the data at rest, especially as level of security would be out of the hands of the application developer and could vary on a per-device basis. On the other hand, using the keychain could add a significant level of convenience by not requiring a secondary passphrase, and would minimize the amount of time the key is stored in memory. There is no perfect solution, so the right approach depends solely on the goals and the required security profile of the application. 

Precisely.

I did a bit of code review. Calling sqlite3_key with an empty string will *not* immediately overwrite and clear the key in memory. This is because the key derivation doesn't occur at the time the key is set. Instead, it occurs on the first database operation after the key is set. This facility allows a user to change other settings that might affect derivation, like the number of KDF iterations, but it means that you'd actually need to try a database operation with a bad key to clear the correct key.

In short, at this time, calling sqlite3_key will not have the effect you are looking for, and the only current way to do it without further code changes would be to close / open the database.

In the longer term, perhaps a good compromise solution would be to allow a application to register a callback that would provide the key when needed.  

I still think this is the most feasible approach in the long run, and will continue to investigate in light of the previously mentioned considerations. Another alternative might be to dig into the v2 sqlcipher code to change the way the key data is extracted.

What if the function setting the key also performed a standard database call such as "select * from sqlite_master"? In other words:

1) set the desired key (either a dummy or valid value)

2) execute "select * from sqlite_master"

This function would ignore the results, allowing SQLCipher to perform the key derivation and set the specified key. If an empty string is not valid, another dummy value could be used, such as "0x0000FFFF", "0x0F0F0F0F" or anything else. Would that work?

Thank you very much for your insightful comments. If we don't chat before the end of the year, have a safe evening and a wonderful 2012!

Regards,

-- Tito

[Stephen Lombardo]

Hi Tito,

On Sat, Dec 31, 2011 at 10:04 AM, Tito Ciuro <tci...@gmail.com> wrote:

What if the function setting the key also performed a standard database call such as "select * from sqlite_master"? In other words:

1) set the desired key (either a dummy or valid value)

2) execute "select * from sqlite_master"

This function would ignore the results, allowing SQLCipher to perform the key derivation and set the specified key. If an empty string is not valid, another dummy value could be used, such as "0x0000FFFF", "0x0F0F0F0F" or anything else. Would that work?

 

This is a good and clever idea, but there are some potential issues due to page caching. First, you can't really guarantee whether SQLite will read a page and call SQLCipher for a given operation if page cache is in use. Additionally, you can't easily control the flushing of the cache either (at least that I'm aware). 

Consider a scenario where you change the key to a dummy value, then call select * from sqlite_master:

If the pages containing schema are already in cache, then they will be return from cache directly without invoking an operation through the sqlcipher codec functions. Thus key derivation won't occur, and the key won't be overwritten.

On the other hand, if the pages containing the schema are not in cache, then the key would be overwritten. You'd get an error, but then bad data would be stuck in the page cache. Even if you later set the key to the correct value, the bad data would be served out of page cache, and you'd continue to get an error for reads on that page.

That said, one way to make this work would be to disable page cache, i.e. by setting PRAGMA cache_size = 0; after setting the key the first time. This should ensure that no pages are cached and that every read goes through SQLCipher's codec. There are some additional caveats, though:

1. Disabling page cache could have a really negative performance impact on your database.
2. I've added some light tests for this scenario (multiple-key-calls-safe-1, 2, and 3) in the version 2 release candidate, but if doing this in a production implementation would warrant plenty of additional testing to make sure it is safe under load, with multithreaded access, shared page cache, etc.
3. While I don't anticipate it changing, we can't guarantee that the behavior of SQLCipher's key derivation won't change in a future release and impact the way this would work.
4. It is theoretically possible for the page caching behavior to change in the upstream SQLite sources at some point in the future. While the probability of this is low, SQLite has been going through some pretty major changes recently.

In summary, while this is a good idea and technically feasible, it probably still falls into the camp of techniques that are possible but not recommended.

Cheers,

Stephen

[Tito Ciuro]

Hi Stephen,

I see now why closing/reopening the database might be the only way to clear the key from memory. I've been researching how I could possibly flush the cache temporarily (i.e. clear cache, then set it back again to its former value), but it seems that there's no possible way to do that.

I'm afraid that closing/reopening the database might too prohibitive in terms of performance. It might not be a big deal after all to leave the key in memory, especially if we take into consideration the potential Keychain issues we've talked about.

Thanks for the info, help and comments.

Best,

-- Tito