Verify SQLCipher encryption by AES decrypting it

[another.sqlcipher.user]

All,

After finally get SQLCipher works in my prototyp ios app, I feel
delighted. I thank you for your time and archive posts.

I want to verify the encryption is truely AES 256 by being able to
decrypted it using my own choice of a 3rd party tool. I will post my
findings back to this community so to benefit eliminating doubts from
potential users of this great tool.


The following is from SQLCiphers security features

- The default algorithm is 256-bit AES in CBC mode (cipher and mode
can be changed at run time via “PRAGMA cipher”).
- Each database page is 1024 bytes and is encrypted and decrypted
individually.
- Every page has it’s own initialization vector generated by OpenSSL’s
RAND_pseudo_bytes which is stored at the end of the page.
- When initialized with a passphrase SQLCipher derives the key data
using PBKDF2 (OpenSSL’s PKCS5_PBKDF2_HMAC_SHA1). Each database is
initialized with a unique pseduo-random salt in the first 16 bytes of
the file. This salt is used for key derivation and it ensures that
even if two databases are created using the same password will not
have the same encryption key. The default configuration uses 4000
iterations for key derivation (this can be changed at runtime using
“PRAGMA kdf_iter”.
- If use of a passphrase is undesirable an application can provide raw
binary key data
- When encrypted, the entire database file appears to contain random
data
- SQLCipher does not attempt to implement its own encryption. Instead
it uses the widely available and peer-reviewed OpenSSL libcrypto for
all cryptographic functions.


I need help in figuring out how to decrypt my almost empty database
(2048 bytes)

1. I have to figure out how to get the key data from my passphrase
(PBKDF2, does openssl have any tool to do this task? I checked
openssl.exe, only pkcs12, 7 and 8)

2. For my 2048 bytes database, I will just have to read the first 1024
bytes and then call OpenSSL to decrypt it aes-256-cbc, and do it again
for the 2nd 1024 bytes, correct?

Thank you,

[Stephen Lombardo]

Hello,

Here are a few resources you might find helpful:

https://github.com/sjlombardo/sqlcipher-tools/blob/master/decrypt.c

https://github.com/sjlombardo/sqlcipher/blob/v2beta/test/crypto.test

The first is a small proof-of-concept program that strips the encryption off of a database using solely OpenSSL, bypassing the SQLCipher entirely. It demonstrates exactly what you described in your email, using only standard OpenSSL functions.

The second is the crypto test script that we use for SQLCipher, it might be instructional for you to review to see what scenarions we already test.

Cheers,

Stephen

[another.sqlcipher.user]

Stephen,

Thank you so much for the help. This is precisely what i am looking
for, and I don't have to write any code.

Here's for anyone else how I did it.

1. Get https://github.com/sjlombardo/sqlcipher-tools/blob/master/decrypt.c
2. Make sure it's in a directory parallel to openssl, then issue this
command to build this console executable:
gcc decrypt.c -I../openssl-1.0.0e/include -l crypto -o decrypt -g
3. Get your encrypted db to current directory as sqlcipher.db
4. Run ./decrypt This decrypt the db and create a new db called
sqlite.db
5. cat sqlite.db (or hexdump -C) to see all clear text of sqlite.db,
or use the regular sqlite3 to open this sqlite.db to verify the
content is well.

[William Gray]

Hi there,

Have a look at the sqlcipher-tools repository, decrypt.c is an example of verifying decryption:

https://github.com/sqlcipher/sqlcipher-tools/blob/master/decrypt.c

If you just want to check the data is encrypted at rest, use hexdump the way we show in the sqlcipher page, see "an illustrative terminal listing:"

https://www.zetetic.net/sqlcipher/design

Regards,
Billy Gray

Sent from my iPhone