sqlcipher_export returning error

97 views
Skip to first unread message

Vinay Hegde

unread,
Jan 23, 2015, 11:20:22 AM1/23/15
to sqlc...@googlegroups.com
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

unread,
Jan 23, 2015, 2:39:21 PM1/23/15
to sqlc...@googlegroups.com
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:


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
signature.asc

Vinay Hegde

unread,
Jan 27, 2015, 2:06:55 AM1/27/15
to sqlc...@googlegroups.com
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

unread,
Jan 27, 2015, 4:35:24 AM1/27/15
to sqlc...@googlegroups.com
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

unread,
Jan 27, 2015, 9:29:36 AM1/27/15
to sqlc...@googlegroups.com
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

signature.asc

Vinay Hegde

unread,
Jan 27, 2015, 11:23:20 PM1/27/15
to sqlc...@googlegroups.com
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

unread,
Jan 28, 2015, 7:07:27 AM1/28/15
to sqlc...@googlegroups.com
u can see my folder structure like below:

Nick Parker

unread,
Jan 28, 2015, 10:47:02 AM1/28/15
to sqlc...@googlegroups.com
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
signature.asc
Reply all
Reply to author
Forward
0 new messages