FMDB + SQLCipher in iOS

[Raj S]

Hi All,
I have successfully integrated SQLCipher in iOS application without build issue and tried to encrypt the existing sqlite file, but unable to encrypt it.

I referred following links.
http://sqlcipher.net/ios-tutorial/
http://sqlcipher.net/sqlcipher-api/#sqlcipher_export

Configure this one also in command line

./configure -enable-tempstore=yes --prefix=/path/to/install/target/sqlcipher CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"

make

and tried use following command,
./sqlite3 /pat/to/database
Got error : -bash: ./sqlite3: No such file or directory

I dont know abt this issue. Can you please give solution for this issue?

[Raj]

sqlite3 /Path/retail_db.sqlite
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>  ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
sqlite> SELECT sqlcipher_export('encrypted');
Error: no such function: sqlcipher_export
sqlite>
Now, i got "no such function: sqlcipher_export" error.

Thanks,
Raj

[Nick Parker]

Hi Raj,

It looks like you are using a version of the SQLite command shell that
does not have SQLCipher included. Please take a look at these
instructions [1] for building the command line shell. Also, note that
the shell name was renamed to sqlcipher, it is not called sqlite3 anymore.

1. http://sqlcipher.net/introduction/

On 8/26/13 6:07 AM, Raj wrote:
> sqlite3 /Path/retail_db.sqlite
> SQLite version 3.7.12 2012-04-03 19:43:07
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
> sqlite> SELECT sqlcipher_export('encrypted');
> Error: no such function: sqlcipher_export
> sqlite>
> Now, i got "no such function: sqlcipher_export" error.
>
> Thanks,
> Raj
>
> On Monday, 26 August 2013 16:21:23 UTC+5:30, Raj S wrote:
>
> Hi All,
> I have successfully integrated SQLCipher in iOS application without
> build issue and tried to encrypt the existing sqlite file, but
> unable to encrypt it.
>
> I referred following links.

> http://sqlcipher.net/ios-tutorial/ <http://sqlcipher.net/ios-tutorial/>
> http://sqlcipher.net/sqlcipher-api/#sqlcipher_export

> <http://sqlcipher.net/sqlcipher-api/#sqlcipher_export>
>
> Configure this one also in command line
> ./configure -enable-tempstore=yes
> --prefix=/path/to/install/target/sqlcipher
> CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"
> make
>
>
> and tried use following command,
> ./sqlite3 /pat/to/database
> Got error : -bash: ./sqlite3: No such file or directory
>
> I dont know abt this issue. Can you please give solution for this issue?
>
>
>

> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "SQLCipher Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlcipher+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

--
Nick Parker

[Raj]

Hi Nick,

Thanks for your reply. I have existing sqlite file with many tables and integrated FMDB for DB operations. Now, i want to include sqlcipher for encryption.

I referred this url http://sqlcipher.net/ios-tutorial/ to integrate the sqlcipher without no issues.

configure this one
openssl-1.0.1e]$ ./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="/PATH/openssl-1.0.1e/libcrypto.a"
Usage: Configure [no-<cipher> ...] [enable-<cipher> ...] [experimental-<cipher> ...] [-Dxxx] [-lxxx] [-Lxxx] [-fxxx] [-Kxxx] [no-hw-xxx|no-hw] [[no-]threads] [[no-]shared] [[no-]zlib|zlib-dynamic] [no-asm] [no-dso] [no-krb5] [sctp] [386] [--prefix=DIR] [--openssldir=OPENSSLDIR] [--with-xxx[=vvv]] [--test-sanity] os/compiler[:flags]
[Raj@ openssl-1.0.1e]$ make

Used following commands to encrypt
./sqlcipher /path/to/database/retail_db.sqlite
SQLCipher version 3.7.17 2013-05-20 00:56:22

Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> ATTACH DATABASE 'encrypted.sqlite' AS encrypted KEY 'test';

sqlite> SELECT sqlcipher_export('encrypted');

sqlite> DETACH DATABASE encrypted;
sqlite>

Questions :
1.Above commands should be use in code or command line?
2.Can i replace this encrypted sqllite(encrypted.sqlite) to original one(retail_db.sqlite)?
3.Should be decrypt the database when i perform the db operations such as insert, update function or sqlite open?
4..Is this the right way to encrypt? if not, please explain the correct steps?

Can you please reply this?

[Nick Parker]

Hi Raj,

I have replied to your questions inline:

On 8/27/13 2:38 AM, Raj wrote:
> Hi Nick,
>
> Thanks for your reply. I have existing sqlite file with many tables and
> integrated FMDB for DB operations. Now, i want to include sqlcipher for
> encryption.
>
> I referred this url http://sqlcipher.net/ios-tutorial/
> <http://sqlcipher.net/ios-tutorial/> to integrate the sqlcipher without
> no issues.
>

> *configure this one*

> openssl-1.0.1e]$ ./configure --enable-tempstore=yes
> CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="/PATH/openssl-1.0.1e/libcrypto.a"
> Usage: Configure [no-<cipher> ...] [enable-<cipher> ...]
> [experimental-<cipher> ...] [-Dxxx] [-lxxx] [-Lxxx] [-fxxx] [-Kxxx]
> [no-hw-xxx|no-hw] [[no-]threads] [[no-]shared] [[no-]zlib|zlib-dynamic]
> [no-asm] [no-dso] [no-krb5] [sctp] [386] [--prefix=DIR]
> [--openssldir=OPENSSLDIR] [--with-xxx[=vvv]] [--test-sanity]
> os/compiler[:flags]
> [Raj@ openssl-1.0.1e]$ make
>

> *Used following commands to encrypt*

> ./sqlcipher /path/to/database/retail_db.sqlite
> SQLCipher version 3.7.17 2013-05-20 00:56:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> ATTACH DATABASE 'encrypted.sqlite' AS encrypted KEY 'test';
> sqlite> SELECT sqlcipher_export('encrypted');
> sqlite> DETACH DATABASE encrypted;
> sqlite>
>
> Questions :
> 1.Above commands should be use in code or command line?

The above commands appear to have been run via the command line shell,
however you could also execute them within your iOS application using
the C API exposed from integrating SQLCipher with your iOS application.

> 2.Can i replace this encrypted sqllite(encrypted.sqlite) to original
> one(retail_db.sqlite)?

You can use the encrypted.sqlite file within your iOS application, note
that you will need to make sure your application is bundling SQLCipher,
not just creating the SQLCipher shell as above.

> 3.Should be decrypt the database when i perform the db operations such
> as insert, update function or sqlite open?

When you have SQLCipher integrated within your iOS application, using
the API, SQLCipher will handle decrypting the content as needed from the
database. More information on the SQLCipher design can be found here [1].

> 4..Is this the right way to encrypt? if not, please explain the correct
> steps?
>

The example you gave above uses the command line shell, this is fine but
your application requirements will dictate exactly how things need to
operate. For example, you may need to encrypt content directly on your
iOS application so integration of SQLCipher within your application
would be important. The difference being that you performed the
encryption above on using the shell on your computer.

1. http://sqlcipher.net/design

> Can you please reply this?
>
> On Monday, 26 August 2013 16:21:23 UTC+5:30, Raj wrote:
>
> Hi All,
> I have successfully integrated SQLCipher in iOS application without
> build issue and tried to encrypt the existing sqlite file, but
> unable to encrypt it.
>
> I referred following links.

> http://sqlcipher.net/ios-tutorial/ <http://sqlcipher.net/ios-tutorial/>
> http://sqlcipher.net/sqlcipher-api/#sqlcipher_export

> <http://sqlcipher.net/sqlcipher-api/#sqlcipher_export>
>
> Configure this one also in command line
> ./configure -enable-tempstore=yes
> --prefix=/path/to/install/target/sqlcipher
> CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"
> make
>
>
> and tried use following command,
> ./sqlite3 /pat/to/database
> Got error : -bash: ./sqlite3: No such file or directory
>
> I dont know abt this issue. Can you please give solution for this issue?
>
>
>

[Raj]

Hi Nick,
Thanks for your reply. I tried to insert the data using FMDB API and getting following error.
Error Code 26: file is encrypted or is not a database
Please give me the solution.

On Monday, 26 August 2013 16:21:23 UTC+5:30, Raj wrote:

[Nick Parker]

Hi Raj,

Please post a code sample that show what you are trying to accomplish,
along with the location where your error occurs. This will help us
better understand what is going on.

On 8/30/13 8:17 AM, Raj wrote:
> Hi Nick,
> Thanks for your reply. I tried to insert the data using FMDB API and
> getting following error.
> Error Code 26: file is encrypted or is not a database
> Please give me the solution.
>
> On Monday, 26 August 2013 16:21:23 UTC+5:30, Raj wrote:
>
> Hi All,
> I have successfully integrated SQLCipher in iOS application without
> build issue and tried to encrypt the existing sqlite file, but
> unable to encrypt it.
>
> I referred following links.

> http://sqlcipher.net/ios-tutorial/ <http://sqlcipher.net/ios-tutorial/>
> http://sqlcipher.net/sqlcipher-api/#sqlcipher_export

> <http://sqlcipher.net/sqlcipher-api/#sqlcipher_export>
>
> Configure this one also in command line
> ./configure -enable-tempstore=yes
> --prefix=/path/to/install/target/sqlcipher
> CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"
> make
>
>
> and tried use following command,
> ./sqlite3 /pat/to/database
> Got error : -bash: ./sqlite3: No such file or directory
>
> I dont know abt this issue. Can you please give solution for this issue?
>
>
>

[Raj]

Hi Nick,
Here is my code.

    database = [FMDatabase databaseWithPath:path];

- (BOOL)openDB{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *databasePath = [docsPath stringByAppendingPathComponent:kDBName];

    sqlite3 *db;
    if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK) {
        const char* key = [@"testkey" UTF8String];
        sqlite3_key(db, key, strlen(key));
        if (sqlite3_exec(db, (const char*) "SELECT count(*) FROM sqlite_master;", NULL, NULL, NULL) == SQLITE_OK) {
            // password is correct, or, database has been initialized
            NSLog(@"password is correct, or, database has been initialized");

            [database open];
            return YES;
        } else {
            // incorrect password!
            NSLog(@"incorrect password!");
        }
       
        sqlite3_close(db);
    }
    return NO;
}


-(void)dumpShops:(NSMutableArray *) shopsArray{
    if([self openDB]){
        for (Shop *shop in shopsArray) {
            BOOL result = [database executeUpdate:@"insert or replace into shops(id,name,street_add,phone,area_id) values(?,?,?,?,?)",
                           shop.shopId,
                           shop.shopName,
                           shop.address,
                           shop.phone,
                           shop.areaId];
            NSLog(@"result = %d",result);
            if ([database hadError]) {
                NSLog(@"Error Code : %d: %@", [database lastErrorCode], [database lastErrorMessage]);
            }
            else{
                NSLog(@"No errors from the database");
            }
        }
        [database close];
    }
}

I have successfully opened the db using openDB function and got "password is correct, or, database has been initialized" message. I tried to insert data using dumpShops functions. But unable to insert the data in encrypted sqlite.
Got an error : Error Code 26: file is encrypted or is not a database

[Nick Parker]

Hi Raj,

The issue here is that the function openDB is entirely self-contained
and operates on a function local variable sqlite3 pointer. So once you
open the database and return, the sqlite3 pointer goes out of scope.
Finally, the database reference you receive from the call to [FMDatabase
databaseWithPath:path] does not operate on the sqlite3 pointer that you
previously opened.

You would need to make sure that FMDB is properly integrated with
SQLCipher. This may require modifying the FMDB source to allow you to
provide a password when acquiring the database reference and internally
calling sqlite3_key within the FMDB source.

> > an email to sqlcipher+...@googlegroups.com <javascript:>.

> > For more options, visit https://groups.google.com/groups/opt_out

> <https://groups.google.com/groups/opt_out>.
>
> --
> Nick Parker

[Stephen Lombardo]

Hi Raj,

FMDB exposes a setKey method when compiled with SQLITE_HAS_CODEC that you can use to pass in the key for the database. Please give those a try.

Cheers,
Stephen

[Raj]

Hi Stephen,

I have tried with setKey function  But still not working. Please see my code as follow.

- (BOOL)openDB{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *databasePath = [docsPath stringByAppendingPathComponent:kDBName];

    if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK) {
        const char* key = [@"testkey" UTF8String];
        sqlite3_key(db, key, strlen(key));
        if (sqlite3_exec(db, (const char*) "SELECT count(*) FROM sqlite_master;", NULL, NULL, NULL) == SQLITE_OK) {
            // password is correct, or, database has been initialized
            NSLog(@"password is correct, or, database has been initialized");

            [database setKey:@"testkey"];

            [database open];
            return YES;
        } else {
            // incorrect password!
            NSLog(@"incorrect password!");
        }
       
        sqlite3_close(db);
    }
    return NO;
}

[Raj]

Hi Nick,

I removed sqlite3 local variable and added as a public variable. But still not working. The FMDB was working fine before integrated with sqlcipher. So, i can say no issues with FMDB.

On Monday, 26 August 2013 16:21:23 UTC+5:30, Raj wrote:

[Nick Parker]

Hi Raj,

I think there may be some confusion on the behavior you are seeing and I
wanted to try to explain what might be occurring. If you have followed
the SQLCipher for iOS integration tutorial, and also included FMDB
within your application you end up with two different interfaces to talk
to the database, (i.e., SQLCipher and FMDB). The problem is they aren't
working together so much as they are working side-by-side.

In order for this to work properly you need FMDB to use SQLCipher, not
the standard SQLite that it typically does. For example, there is a
FMDatabase class which takes a path to a database and can be opened and
keyed. FMDB is actually calling various SQLite API's directly to
perform these operations. You need to make sure that FMDB is linking to
SQLCipher instead of the standard SQLite.

We provide free public support for SQLCipher on this mailing list,
however this is focused around integrating a third-party client library
that wraps the SQLite API. We offer commercial support agreements [1]
if you are interested in help with this.

1. http://sqlcipher.net/support

On 9/4/13 4:13 AM, Raj wrote:
> Hi Nick,
>
> I removed sqlite3 local variable and added as a public variable. But
> still not working. The FMDB was working fine before integrated with
> sqlcipher. So, i can say no issues with FMDB.
>
> On Monday, 26 August 2013 16:21:23 UTC+5:30, Raj wrote:
>
> Hi All,
> I have successfully integrated SQLCipher in iOS application without
> build issue and tried to encrypt the existing sqlite file, but
> unable to encrypt it.
>
> I referred following links.

> http://sqlcipher.net/ios-tutorial/ <http://sqlcipher.net/ios-tutorial/>
> http://sqlcipher.net/sqlcipher-api/#sqlcipher_export

> <http://sqlcipher.net/sqlcipher-api/#sqlcipher_export>
>
> Configure this one also in command line
> ./configure -enable-tempstore=yes
> --prefix=/path/to/install/target/sqlcipher
> CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto"
> make
>
>
> and tried use following command,
> ./sqlite3 /pat/to/database
> Got error : -bash: ./sqlite3: No such file or directory
>
> I dont know abt this issue. Can you please give solution for this issue?
>
>
>