sqlcipher_export returning error

[Vinay Hegde]

Hi Sir,

I am using SQLCipher to encrypt and decrypt sqlite db.

Scenario is :

1) i have already have plain sqlite3 DB file with lots of data.

2) so i first attached to new sqlcipher encrypted DB file.

3) till here no issue.

4) later i tried to use sqlcipher_export command to copy the data.

5) but execution returned me error with code 1 i.e #define SQLITE_ERROR        1   /* SQL error or missing database */

below is my code snippet and please help to me to resolve it.

sqlite3 *unencrypted_DB;

        NSString *path_u = local_databasePath;

        

        if (sqlite3_open([path_u UTF8String], &unencrypted_DB) == SQLITE_OK) {

            NSLog(@"Database Opened");

            int res = -1;

            

            NSString *enc_databasePath = [libsDir stringByAppendingPathComponent:@"encrypted.sqlite"];

            if (sqlite3_open([enc_databasePath UTF8String], &unencrypted_DB) == SQLITE_OK) {

                

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

                NSLog(@"deq: %@",de_query);

                

                const char* sqlstmt = [de_query UTF8String];

                

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

            }

            NSString* de_export = 

[NSString stringWithFormat:@"SELECT sqlcipher_export('%@');",enc_databasePath];// SUSPECTING ISSUE HERE 

            const char* sqlstmt1 = [de_export UTF8String];

            

            // export database

            res = sqlite3_exec(unencrypted_DB,sqlstmt1, NULL, NULL, NULL); // ISSUE I AM FACING AS MENTIONED ABOVE

                        

            // Detach encrypted database

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

            

            NSLog (@"End database copying");

            sqlite3_close(unencrypted_DB);

[William Gray]

Hi Vinay,

Calling sqlite3_open() twice for two file locations using the same database pointer looks suspect to me, that may have something to do with your error. Have a look at the sqlcipher_export() documentation, it includes some good examples for doing what you want:

https://www.zetetic.net/sqlcipher/sqlcipher-api/#sqlcipher_export

The basic set of steps is:

1. Open your existing database

2. Using that connection, perform an ATTACH specifying a file location, name and key:

ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';

3. Copy over the data

SELECT sqlcipher_export('encrypted’);

4. Detach, close the connection to the plain text database

5. Open the newly created file and provide the key used in the ATTACH step

Hope that helps!

Billy Gray

[Vinay Hegde]

Hello Mr. Billy,

Thanks for your reply. i changed the code. no two open statements.

but now SQLCIPHER_EXPORT command is failing with error code 1 (missing DB).

i am using sqlcipher 2.1.1 version of ios library.

can you give me simple code which works?

Regards,

Vinay

[Vinay Hegde]

here is my code and below is the error.

 NSLog(@"Database Opened");

        int res = -1;

        

        NSString *enc_databasePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] stringByAppendingPathComponent: @"encrypted.sqlite"];

        NSMutableString* str =  [[NSMutableString alloc]initWithString:@"encrypted"];

        NSMutableString* queryStr = [[NSMutableString alloc]initWithString:@"ATTACH DATABASE '%@' AS "];

          [queryStr appendString:str];

        if (sqlite3_open([enc_databasePath UTF8String], &unencrypted_DB) == SQLITE_OK) {

        

            NSString* de_query = [NSString stringWithFormat:queryStr,path_u];

            NSLog(@"deq: %@",de_query);

            

            const char* sqlstmt = [de_query UTF8String];

            

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

        }

        

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

    

        const char* sqlstmt1 = [de_export UTF8String];

        

        // export database

        res = sqlite3_exec(unencrypted_DB,sqlstmt1, NULL, NULL, NULL); ====>>>SQLCIPHER_EXPORT command is failing with error code 1 (missing DB).

i think xcode is not recognizing SQLCIPHER_EXPORT  command

[Nick Parker]

Hello Vinay,

You mentioned below that you were using SQLCipher 2.1.1, could you
clarify the scenario in which you are trying to use the sqlcipher_export
function? Specifically, are you trying to encrypt a plain text
database, or something else?

Does your attach statement run, if so, what is the return code? If the
attach statement does not run, sqlcipher_export will not be able to
operate as that is a prerequisite.

On 1/27/15 3:35 AM, Vinay Hegde wrote:
> here is my code and below is the error.
>
> NSLog(@"Database Opened");
>
> int res = -1;
>
>
>

> NSString*enc_databasePath =

> [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
> NSUserDomainMask, YES) objectAtIndex:0] stringByAppendingPathComponent:
> @"encrypted.sqlite"];
>
> NSMutableString* str =

> [[NSMutableStringalloc]initWithString:@"encrypted"];
>
> NSMutableString* queryStr =
> [[NSMutableStringalloc]initWithString:@"ATTACH DATABASE '%@' AS "];

>
> [queryStr appendString:str];
>
> if (sqlite3_open([enc_databasePath UTF8String], &unencrypted_DB)
> == SQLITE_OK) {
>
>
>
> NSString* de_query = [NSString
> stringWithFormat:queryStr,path_u];
>
> NSLog(@"deq: %@",de_query);
>
>
>
> const char* sqlstmt = [de_query UTF8String];
>
>
>
> res = sqlite3_exec(unencrypted_DB, sqlstmt, NULL, NULL, NULL);
>
> }
>
>
>

> NSString* de_export = [NSStringstringWithFormat:@"SELECT

> sqlcipher_export ('%@')",str];
>
>
>
> const char* sqlstmt1 = [de_export UTF8String];
>
>
>
> // export database
>
> res = sqlite3_exec(unencrypted_DB,sqlstmt1, NULL, NULL, NULL);
> ====>>>SQLCIPHER_EXPORT command is failing with error code 1 (missing DB).
>
> i think xcode is not recognizing SQLCIPHER_EXPORT command
>
>
>
>
>
> On Tuesday, January 27, 2015 at 12:36:55 PM UTC+5:30, Vinay Hegde wrote:
>
> Hello Mr. Billy,
>
> Thanks for your reply. i changed the code. no two open statements.
> but now SQLCIPHER_EXPORT command is failing with error code 1
> (missing DB).
> i am using sqlcipher 2.1.1 version of ios library.
>
> can you give me simple code which works?
>
> Regards,
> Vinay
>
> On Saturday, January 24, 2015 at 1:09:21 AM UTC+5:30, William Gray
> wrote:
>
> Hi Vinay,
>
> Calling sqlite3_open() twice for two file locations using the
> same database pointer looks suspect to me, that may have
> something to do with your error. Have a look at the
> sqlcipher_export() documentation, it includes some good examples
> for doing what you want:
>

> https://www.zetetic.net/sqlcipher/sqlcipher-api/#sqlcipher_export <https://www.zetetic.net/sqlcipher/sqlcipher-api/#sqlcipher_export>

>
> The basic set of steps is:
>
> 1. Open your existing database
> 2. Using that connection, perform an ATTACH specifying a file
> location, name and key:
> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'testkey';
> 3. Copy over the data
> SELECT sqlcipher_export('encrypted’);
> 4. Detach, close the connection to the plain text database
> 5. Open the newly created file and provide the key used in the
> ATTACH step
>
> Hope that helps!
> Billy Gray
>

> --
>
> ---
> 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
> <mailto:sqlcipher+...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

--
Nick Parker

[Vinay Hegde]

Hello Nick,

Yes i am using 2.1.1 version.

Scenario: 

I have mobi app which stores some metadata to  plain sqlite db. Now there is a requirement to encrypt the DB both old DB and new DB from next release onwards.

So i am checking upgrade scenario of my app.  My attach statement executed fine with code 0 but 

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

            const char* sqlstmt1 = [de_export UTF8String];

            

            // export database

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

res returned me error code 1 ( /* SQL error or missing database */)

it menace my sqlcipher_export is not working .

can you please tell me why ?

Regards,

Vinay Hegde

[Vinay Hegde]

u can see my folder structure like below:

[Nick Parker]

Hello Vinay,

I have replied to this via your other thread here:

https://groups.google.com/d/msg/sqlcipher/MKLAS-j4ECo/gWLIku6BrQcJ

Please feel free to respond there. Thanks!

On 1/28/15 6:07 AM, Vinay Hegde wrote:
> u can see my folder structure like below:
>

> <https://lh5.googleusercontent.com/-UAl51ub7SjY/VMjQ8DzYAmI/AAAAAAAAEbM/vTlnDz40cT0/s1600/Screen%2BShot%2B2015-01-28%2Bat%2B5.36.43%2BPM.png>

>
>
>
> On Wednesday, January 28, 2015 at 9:53:20 AM UTC+5:30, Vinay Hegde wrote:
>
> Hello Nick,
>
> Yes i am using 2.1.1 version.
> Scenario:
> I have mobi app which stores some metadata to plain sqlite db. Now
> there is a requirement to encrypt the DB both old DB and new DB from
> next release onwards.
> So i am checking upgrade scenario of my app. My attach statement
> executed fine with code 0 but
>

> NSString* de_export = [NSStringstringWithFormat:@"SELECT

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