Problem in exporting the plain text database to encrypted in iphone

[A sayyad]

Hi All,

I have one plain text database . I am trying to export it to encrypted database using following code ---

NSString* de_query = [NSString stringWithFormat:@"ATTACH DATABASE '%@' AS encrypted KEY '1234';",enc_databasePath];

  const char* sqlstmt = [de_query cStringUsingEncoding:NSUTF8StringEncoding];

 res = sqlite3_exec(unencrypted_DB, sqlstmt, NULL, NULL, NULL);

NSString* de_export = [NSString stringWithFormat:@"SELECT sqlcipher_export('encrypted');"];

        const char* sqlstmt1 = [de_export cStringUsingEncoding:NSUTF8StringEncoding];

        

    // export database

res = sqlite3_exec(unencrypted_DB,sqlstmt1, NULL, NULL, NULL);

  // Detach encrypted database

        res = sqlite3_exec(unencrypted_DB, "DETACH DATABASE encrypted;", NULL, NULL, NULL);

My problem is that the exported database is empty.

[Stephen Lombardo]

Hi Ashiq,

What result code are you getting back from the ATTACH, i.e. have you verified that the attach command worked? Make user you aren't trying to attach in a transaction.

If the attach is returning SQLITE_OK, what result code do you getback from the sqlcipher_export call?

Finally, have you tried the export on the command line with a copy of your database?

Cheers,

Stephen

[ashiq sayyad]

I am getting following response codes --

For Attach query  ----- SQLITE_OK

 For Export query  ---- SQLITE_ERROR

For Deattach query ----- SQLITE_OK

[Stephen Lombardo]

Hi Ashiq,

I would first suggest running the command line SQLCipher against your database to perform the export function, and see if you also get an error there. The test suite for SQLCipher verifies export using a wide variety of constructs (e.g. tables, indexes, triggers, virtual tables, etc).  If you also get an error on the command line we may need to look a the specific database you are using. 

If it works on the command line, something may be awry with your application. Here are some further suggestions:

• Try to create a brand new SQLCipher database from scratch in the same app using sqlite3_key() to set the key. If you get a compile or link error on sqlite3_key, or an encrypted database is not created, then you probably have the project setup incorrectly and SQLCipher isn't actually being linked into the app
• Verify that you are using the latest version of SQLCipher on the master branch from the git repository (sqlcipher_export() was not present in versions earlier that 2.0)
• Try calling sqlite3_errmsg to see if there is a more descriptive error message available
• Ensure that enc_databasePath is a writeable path

If none of these suggestions set you on the right path, we'd need you to provide a small reference application that demonstrates the problem to troubleshoot further.

Thanks!

Cheers,

Stephen

[sudhakar panda]

[sudhakar panda]

Hi Stephen,
                    I am also facing same prob,can't able to encrypt the plaintext file.In  sqlite_export case,i am getting SQLITE_ERROR msg as response.After that i tried through command line,but here also getting error like "there is no sqlite_export method".In addition ,i have taken into consider whatever you suggested in your last post.That's also not favored to me.Any suggestion would be most welcome.

Regards,
sudhakar

[ashiq sayyad]

Is there any utility tests for iphone as there is for android...

[Stephen Lombardo]

Hi Sudhakar,

Are you calling sqlite_export? The proper function is sqlcipher_export().

If you are actually calling sqlcipher_export() and encryption is not working it is likely a path issue.

First, if you are calling sqlite3 on the command line without providing a full path to the executable, then you are probably using the built in SQLite on Mac (or another sqlite3 that may be available in your PATH environment variable) which does not support encryption (http://en.wikipedia.org/wiki/Path_(computing)#Unix_style)

Assuming you have compiled the command line tool, try to run the command from inside the SQLCipher source directory where you built the tool, like so.

./sqlite3 /path/to/database.db

Once you've opened a database, make sure you run the PRAGMA key operation first, then create a table. See if the resulting database is encrypted to make sure you are using the right version, then if so, try your tests again. 

On the other hand, if encryption is working, but you are unable to call sqlcipher_export() they you may be using an outdated version of SQLCipher. Try to get the latest version of SQLCipher from the official GitHub repository on the master branch. 

If you are running into trouble only under iOS then you probably haven't linked SQLCipher correctly into the application. The suggestions I provided to Ashiq about adding a call to sqlite3_key would be relevant. Alternately you could review the integration instructions again (http://sqlcipher.net/ios-tutorial/). 

If you are absolutely sure you've built SQLCipher in correctly it would be helpful to have access to the source to a representative application that demonstrates the problem.

Cheers,

Stephen

[Stephen Lombardo]

Hi Ashiq,

SQLCipherSpeed is a reference application for iOS

https://github.com/sqlcipher/SQLCipherSpeed

Cheers,

Stephen

[Emil Indradjaja]

Hi all:

Is there anybody using XCode 4.4 and Mountain Lion with SQLcipher already? 

Thanks. 

--
Emil Indradjaja

[Steven Parkes]

On Aug 2, 2012, at 10:31 AM, Emil Indradjaja <emil.in...@gmail.com> wrote:

> Is there anybody using XCode 4.4 and Mountain Lion with SQLcipher already?

Yes, though I use my own custom project files.

[sudhakar panda]

Hi Stephen,
               Thanks a lot.It's really so much helpful.As such,here i am answering all of your queries.
1.I am using correct sqliteciher_export() as per instruction;
2.Encryption working properly by using sqlite_key method.
3.I am giving complete db path like in below given
       NSString *enc_databasePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]          stringByAppendingPathComponent: @"encrypted"];
        NSString* de_export = [NSString stringWithFormat:@"SELECT sqlcipher_export('%@');",enc_databasePath];

        const char* sqlstmt1 = [de_export cStringUsingEncoding:NSUTF8StringEncoding];
        // export database
        res = sqlite3_exec(unencrypted_DB,sqlstmt1, NULL, NULL, NULL);

4.In command line,1st m going that path where plaintext.db is residing .
       ..dbpath./Documents>sqlite3
        sqlite>here i am running all the sqlite commands whatever in fourm and in sqlcipher documents.
      -->here only i am umable to run that sqlcipher_export command..

5.what you suggested to use sqlcipherspped app for ashiq,thats i have taken for testing ..but nowhere i find any usage of sqlcipher_export method.what i noticed from there that's measuring performance of the sqlicpher.

But ,i am trying by putting different combination  with looking your suggestion .If any testapp which using sqlite_export ,then that would be make away from confusion.

Thanks & Regards,
Sudhakar

[sudhakar panda]

y  Hi,
        Please find the sample app in  my attachment which has 2 buttons(create datebase & export database)
1.Create database---It will create a plaintext db file(contacts.db) having 3 records.
2.Export Database--It will export a 'contacts.db' to 'encrypted'.
      But it created encrypted db file with no records.Kindly let me know if i missed something while exporting.

Thanks,
Sudhakar

[Sife]

If you still have issues to encrypt your database, send it to me and I will encrypt it for you.

[Stephen Lombardo]

Sudahkar,

There are numerous problems in this example project.

1. It is not even setup to integrate SQLCipher according to the supported community edition instructions. These instructions were carefully prepared to ensure that architecture specific linking happens properly. Direct linking to specific .a files in the xcode build directories is a bad idea; please don't deviate from the published instructions, as it can lead to unintended consequences. The instructions are here http://sqlcipher.net/ios-tutorial/
2. After applying the steps to properly integrate SQLCipher, the example project won't build, and is missing numerous project references.

After removing broken references and fixing the linking of SQLCipher we must dive into the deeper code issues. 

1. In createDB the code is calling sqlite3_key, which would cause the testcontacts.db to be created encrypted, not plaintext. That call must be commented out if you are testing plaintext to encrypted conversion.
2. In decryptDB the program opening a database file with a different path, contacts.db, instead of the testcontacts.db file created in the previous step. That needs to be changed to testcontacts.db, or the database to be exported is completely empty anyway.
3. Before attaching the database this program calls sqlite3_open on target database path: "if (sqlite3_open([enc_databasePath UTF8String], &unencrypted_DB) == SQLITE_OK) {". That call effectively overwrites the original database handle for the unencrypted database, thus loosing the context for the source database. It also opens and locks the encrypted database, such that the encrypted database becomes attached to itself, and is then locked. That needs to be removed entirely.
4. The call to "SELECT sqlcipher_export('%@');" is passing the full path to the database into sqlcipher_export. That is incorrect, only the name of the attached database should be passed.

After making all of these changes the code works as expected, and exports an encrypted databased.

With regard to your issue on the command line:

On Friday, August 3, 2012 1:54:26 PM UTC+5:30, sudhakar panda wrote:

Hi Stephen,

4.In command line,1st m going that path where plaintext.db is residing .
       ..dbpath./Documents>sqlite3
        sqlite>here i am running all the sqlite commands whatever in fourm and in sqlcipher documents.
      -->here only i am umable to run that sqlcipher_export command..

As I posted previously in the thread that will now work. Calling a naked "sqlite3" will just use the SQLite that ships with Mac OSX and is already in your path, not the SQLCipher variant. .You must use the full path to the compiled SQLCipher binary. i.e.

 ..dbpath./Documents> /User/name/Documents/code/sqlcipher/sqlite3  database.db

I think that this clearly and conclusively demonstrates that sqlcipher_export() is working as expected. Thus, this thread will now be considered closed. 

Cheers,

Stephen