Unable to decrypt sqlite

[iDev]

Hi everyone,

I have been trying to encrypt/ decrypt a sqlite database in my iPhone
project. I am able to encrypt the database by using the rekey method.
But I am unable to decrypt it.
I have kept my sqlite.db file in a folder. I am currently trying this
on a simulator.

Below are the code snippets:
.....

[[SQLiteDB sharedSQLiteDB] open:<path to sqlite> withKey:@""];
[[SQLiteDB sharedSQLiteDB] reKey:@"abc"]; // database is encrypted at
this step
[[SQLiteDB sharedSQLiteDB] close];
[[SQLiteDB sharedSQLiteDB] open:<path to sqlite> withKey:@"abc"];
[[SQLiteDB sharedSQLiteDB] reKey:@""]; // dabase should be decrypted
at this step but it isn't.

........

- (BOOL)open:(NSString *)path withKey:(NSString *)masterKey {

if (sqlite3_open([path fileSystemRepresentation], &_db) !=
SQLITE_OK) {
NSLog(@"SQLite Opening Error: %s", sqlite3_errmsg(_db));
return NO;
}

if(masterKey)
sqlite3_exec(_db, [[NSString stringWithFormat:@"PRAGMA key =
'%@'", masterKey] UTF8String], NULL, NULL, NULL);

if (sqlite3_exec(_db, (const char*) "SELECT count(*) FROM
sqlite_master", NULL, NULL, NULL) != SQLITE_OK)
{
[self close];
NSLog(@"SQLite Key Error!");
return NO;
}

filePath = [path retain];
return YES;
}


- (void)reKey:(NSString *)masterKey
{
sqlite3_exec(_db, [[NSString stringWithFormat:@"PRAGMA rekey =
'%@'", masterKey] UTF8String], NULL, NULL, NULL);

}


I have read various posts on this topic in sqlcipher google groups,
but I am unable to decrypt it. Sorry about my ignorance, but any help
would be highly appreciated.

[Stephen Lombardo]

Hi iDev,

I'm a bit confused about what you are trying to do here. 

If you are trying to take an existing non-encrypted database, encrypt it, and then decrypt it back, our recommended approach is not to use rekey, but instead to use ATTACHed databases to copy data between a standard and an sqlcipher database. There is more information and a concrete example here:

http://www.zetetic.net/blog/2009/12/29/how-to-encrypt-a-plaintext-sqlite-database-to-use-sqlcipher/

If instead, you are just trying to use sqlcipher to encrypt data in general (i.e. with no conversion from a pre-existing database), then you just need to use sqlite3_key. You basically just open the database, provide the key and then make sql calls. There are no separate encryption / decryption steps - all of that is handled on the fly by the sqlcipher code. In the code you posted previously, you'd never call rekey at all. Each time you open the database you call PRAGMA key, and then run a quick check to ensure that sqlite_master is readable. 

Let me know if either of these tips resolves the problem.

Cheers,

Stephen

[anubha]

Hi, everyone

I am also struggling with the same problem in my iphone app.

I also tried with the concept of attaching database.

I am able to attach a database.But the structure of the new database
is not the same as the original one.

There is no exact sample code which I can follow. There are only
abstract tutorials.

If anyone has tried with attaching & copying between databases, & can
share the piece of code, it will be of great help for me!!

On Sep 13, 8:28 am, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hi iDev,
>
> I'm a bit confused about what you are trying to do here.
>
> If you are trying to take an existing non-encrypted database, encrypt it,
> and then decrypt it back, our recommended approach is not to use rekey, but
> instead to use ATTACHed databases to copy data between a standard and an
> sqlcipher database. There is more information and a concrete example here:
>

> http://www.zetetic.net/blog/2009/12/29/how-to-encrypt-a-plaintext-sql...

>
> If instead, you are just trying to use sqlcipher to encrypt data in general
> (i.e. with no conversion from a pre-existing database), then you just need
> to use sqlite3_key. You basically just open the database, provide the key
> and then make sql calls. There are no separate encryption / decryption steps
> - all of that is handled on the fly by the sqlcipher code. In the code you
> posted previously, you'd never call rekey at all. Each time you open the
> database you call PRAGMA key, and then run a quick check to ensure that
> sqlite_master is readable.
>
> Let me know if either of these tips resolves the problem.
>
> Cheers,
> Stephen
>

[Rus Selin]

Good afternoon to all!
Correct me if what not so:
==================================================================
/* test.h: */

#include "sqlite3.h"

extern "C" const char* sqlite3_libversion(void);
extern "C" int sqlite3_open(const char *filename, sqlite3 **ppDb);
extern "C" int sqlite3_exec(sqlite3*, const char *sql, int (*callback)
(void*,int,char**,char**), void *, char **errmsg);
extern "C" int sqlite3_close(sqlite3 *);
extern "C" int sqlite3_key(sqlite3 *db, const void *pKey, int nKey);
extern "C" int sqlite3_rekey(sqlite3 *db, const void *pKey, int nKey);

/* test.c: */

#include <stdio.h>
#include "test.h"

sqlite3* hDB = 0;
int res = 0;

const char* pSQL = "CREATE TABLE IF NOT EXISTS [test1] ( "
" [id] INTEGER PRIMARY KEY NULL, "
" [message] VARCHAR(40) NULL "
");";

int main(int argc, char**argv) {

printf("\nSQLite: %s\n", sqlite3_libversion());

printf("\n------------------------------------------\n");

res = sqlite3_open("test.db", &hDB);
res = sqlite3_exec(hDB, pSQL, NULL, NULL, NULL);
printf("pSQL result: %ld\n", res);
res = sqlite3_close(hDB);

printf("\n------------------------------------------\n");

res = sqlite3_open("test.db", &hDB);
res = sqlite3_rekey(hDB, "hello", 5);
printf("\nresult sqlite3_rekey: %ld\n\n", res);
res = sqlite3_close(hDB);

printf("\n------------------------------------------\n");

res = sqlite3_open("test.db", &hDB);
res = sqlite3_key(hDB, "hello", 5);
res = sqlite3_exec(hDB, "SELECT count(*) FROM sqlite_master;",
NULL, NULL, NULL);
printf("\nresult sqlite3_exec: %ld\n\n", res);
res = sqlite3_close(hDB);

return 0;
}

==================================================================
cmd: (i use mingw)

rm -f test.db
g++ -c -o test.o test.c
g++ -o test.exe -d sqlite3.dll test.o
./test.exe

==================================================================
output: (./test.exe)


ruslansk@PC-006 /home/user/projects/sqlite/sqlite3_test

SQLite: 3.7.2

------------------------------------------
pSQL result: 0

------------------------------------------
sqlite3_rekey: entered db=9776496 pKey=hello, nKey=5
sqlite3_rekey: database pDb=9776928
sqlite3_rekey: no codec attached to db, attaching now
sqlite3CodecAttach: entered nDb=0 zKey=hello, nKey=5
codec_set_cipher_name: entered db=9776496 nDb=0 cipher_name=aes-256-
cbc for_ctx=
0
codec_set_kdf_iter: entered db=9776496 nDb=0 kdf_iter=4000 for_ctx=0
codec_set_pass_key: entered db=9776496 nDb=0 cipher_name=hello nKey=5
for_ctx=0
cipher_ctx_copy: entered target=9799896, source=9799800
sqlite3_rekey: updating page size for iv_sz change from 0 to 16
sqlite3CodecGetKey: entered db=9776496, nDb=0
sqlite3CodecAttach: entered nDb=2 zKey=hello, nKey=0
sqlite3Codec: entered pgno=1, mode=3, ctx->mode_rekey=0, pg_sz=1024
codec_key_derive: entered c_ctx->pass=hello, c_ctx->pass_sz=0 ctx-
>kdf_salt=9885
256 ctx->kdf_salt_sz=16 c_ctx->kdf_iter=4000 c_ctx->key_sz=0
cipher_ctx_cmp: entered c1=9799896 c2=9799800
codec_key_derive: entered c_ctx->pass=hello, c_ctx->pass_sz=5 ctx-
>kdf_salt=9885
256 ctx->kdf_salt_sz=16 c_ctx->kdf_iter=4000 c_ctx->key_sz=32
codec_key_derive: deriving key using PBKDF2
sqlite3Codec: switch mode=3 offset=16
codec_cipher:entered pgno=1, mode=0, size=1008
sqlite3CodecGetKey: entered db=9776496, nDb=0
sqlite3Codec: entered pgno=2, mode=3, ctx->mode_rekey=0, pg_sz=1024
sqlite3Codec: switch mode=3 offset=0
codec_cipher:entered pgno=2, mode=0, size=1024
sqlite3Codec: entered pgno=1, mode=7, ctx->mode_rekey=0, pg_sz=1024
sqlite3Codec: switch mode=7 offset=16
codec_cipher:entered pgno=1, mode=1, size=1008
sqlite3Codec: entered pgno=2, mode=7, ctx->mode_rekey=0, pg_sz=1024
sqlite3Codec: switch mode=7 offset=0
codec_cipher:entered pgno=2, mode=1, size=1024
sqlite3Codec: entered pgno=1, mode=6, ctx->mode_rekey=0, pg_sz=1024
sqlite3Codec: switch mode=6 offset=16
codec_cipher:entered pgno=1, mode=1, size=1008
sqlite3Codec: entered pgno=2, mode=6, ctx->mode_rekey=0, pg_sz=1024
sqlite3Codec: switch mode=6 offset=0
codec_cipher:entered pgno=2, mode=1, size=1024
codec_set_pass_key: entered db=9776496 nDb=0 cipher_name=hello nKey=5
for_ctx=1
sqlite3Codec: entered pgno=1, mode=7, ctx->mode_rekey=1, pg_sz=1024
cipher_ctx_cmp: entered c1=9799896 c2=9799800
codec_key_derive: entered c_ctx->pass=hello, c_ctx->pass_sz=5 ctx-
>kdf_salt=9885
256 ctx->kdf_salt_sz=16 c_ctx->kdf_iter=4000 c_ctx->key_sz=32
codec_key_derive: deriving key using PBKDF2
sqlite3Codec: switch mode=7 offset=16
codec_cipher:entered pgno=1, mode=1, size=1008
sqlite3Codec: entered pgno=2, mode=7, ctx->mode_rekey=1, pg_sz=1024
sqlite3Codec: switch mode=7 offset=0
codec_cipher:entered pgno=2, mode=1, size=1024
sqlite3_rekey: committing
sqlite3Codec: entered pgno=1, mode=6, ctx->mode_rekey=1, pg_sz=1024
sqlite3Codec: switch mode=6 offset=16
codec_cipher:entered pgno=1, mode=1, size=1008
sqlite3Codec: entered pgno=2, mode=6, ctx->mode_rekey=1, pg_sz=1024
sqlite3Codec: switch mode=6 offset=0
codec_cipher:entered pgno=2, mode=1, size=1024
cipher_ctx_copy: entered target=9799800, source=9799896

result sqlite3_rekey: 0

codec_ctx_free: entered iCtx=2293356
cipher_ctx_free: entered iCtx=9885516
cipher_ctx_free: entered iCtx=9885520

------------------------------------------
sqlite3_key: entered db=9776496 pKey=hello nKey=5
sqlite3CodecAttach: entered nDb=0 zKey=hello, nKey=5
codec_set_cipher_name: entered db=9776496 nDb=0 cipher_name=aes-256-
cbc for_ctx=
0
codec_set_kdf_iter: entered db=9776496 nDb=0 kdf_iter=4000 for_ctx=0
codec_set_pass_key: entered db=9776496 nDb=0 cipher_name=hello nKey=5
for_ctx=0
cipher_ctx_copy: entered target=9776416, source=9799848
sqlite3Codec: entered pgno=1, mode=3, ctx->mode_rekey=0, pg_sz=1024
codec_key_derive: entered c_ctx->pass=hello, c_ctx->pass_sz=5 ctx-
>kdf_salt=9808
640 ctx->kdf_salt_sz=16 c_ctx->kdf_iter=4000 c_ctx->key_sz=32
codec_key_derive: deriving key using PBKDF2
cipher_ctx_cmp: entered c1=9776416 c2=9799848
cipher_ctx_copy: entered target=9776416, source=9799848
sqlite3Codec: switch mode=3 offset=16
codec_cipher:entered pgno=1, mode=0, size=1008

result sqlite3_exec: 11

codec_ctx_free: entered iCtx=2293356
cipher_ctx_free: entered iCtx=9799820
cipher_ctx_free: entered iCtx=9799824

==================================================================
Particularly next lines interest:

pSQL result: 0 ---- table added success

result sqlite3_rekey: 0 ---- database success encrypt

result sqlite3_exec: 11 ---- after open, input key...
sqlite3_exec(hDB, "SELECT count(*) FROM sqlite_master;".....
return 11; /// SQLite
error ----> #define SQLITE_CORRUPT 11 /* The database disk
image is malformed */

Like all it is true. Has killed on meditations and tests about one
week :) judge please!

[Stephen Lombardo]

Hi Rus,

As mentioned earlier in this thread and in several other posts/discussions, you shouldn't be using sqlite3_rekey for this. If you are trying to take an existing non-encrypted database and encrypt it then our recommended approach is to use ATTACHed databases to copy data between a standard and an sqlcipher database. There is more information and a concrete example here:

http://www.zetetic.net/blog/2009/12/29/how-to-encrypt-a-plaintext-sqlite-database-to-use-sqlcipher/

Again, please don't use sqlite3_rekey to try and convert a plan sqlite database into a sqlcipher database, it just won't work. 

Thanks!

Cheers,

Stephen

[Rus Selin]

I thank, Stephen. I continue to watch new versions))