Error in opening the database file

[radhar]

@sjlombardo

Hi......Hope you are doing well.

Thanks for the this tutorial "tutorial-iphone-sqlite-encryption-with-sqlcipher".

I am trying to encrypt my database by following your tutorial. While in the process i am able to build it with out any errors. And after that i created a database outside the xcode project (i.e stored in desktop location). And now opened my terminal and done the following process as you mention in one of the issue:

1)went to the SQLCipher source directory and
2)then ./configure CFLAGS="-DSQLITE_HAS_CODEC -lcrypto
3)/sqlite3 /path/to/your/database

When it loads then run some commands:

4)sqlite> pragma key = '43ec78dec5e8805d143bb1a1452d274ff49cb';
5)sqlite> select * from sqlite_master;

After this i copied the sqlite database file into xcode project and done with the following code:

-(void) checkAndCreateDatabase{// in this method i am copying the TerminalDb.sqlite file from resource folder to document folder of the app.

// Check if the SQL database has already been saved to the users phone, if not then copy it over
BOOL success;

// Create a FileManager object, we will use this to check the status
// of the database and to copy it over if required
NSFileManager *fileManager = [NSFileManager defaultManager];

databaseName = @"TerminalDb.sqlite";

// Get the path to the documents directory and append the databaseName
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *documentsDir = [documentPaths objectAtIndex:0];
databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

// Check if the database has already been created in the users filesystem
success = [fileManager fileExistsAtPath:databasePath];

// If the database already exists then return without doing anything
if(success) return;

// If not then proceed to copy the database from the application to the users filesystem

// Get the path to the database in the application package
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

// Copy the database from the package to the users filesystem
[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
[fileManager release];

}

-(void) check{
NSString *databasePath1 = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]
stringByAppendingPathComponent: @"TerminalDb.sqlite"];
sqlite3 *db;

if (sqlite3_open([databasePath1 UTF8String], &db) == SQLITE_OK) {

    sqlite3_exec(db, "pragma key = '43ec78dec5e8805d143bb1a1452d274ff49cb'", NULL, NULL, NULL);

             if (sqlite3_exec(db, (const char*) "SELECT count(*) FROM sqlite_master;", NULL, NULL, NULL) == SQLITE_OK) {
                     NSLog(@"Entered the if condition");
                 // password is correct, or, database has been initialized
                 } else {
                     NSLog(@"Entered the else condition");
                 // incorrect password!
                 }
                 }

//ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret'; //-- create a new encrypted database

// CREATE TABLE encrypted.t1(a,b); //-- recreate the schema in the new database (you can inspect all objects using SELECT * FROM sqlite_master)
// INSERT INTO encrypted.t1 SELECT * FROM t1; ///-- copy data from the existing tables to the new tables in the encrypted database
// DETACH DATABASE encrypted;

}
-(void)createPatientTempTable // here i am creating a table in TerminalDb.sqlite.
{

NSArray *arrDocPath1 =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
// b create the actual destination path

NSString *strDestPath1 = [NSString stringWithFormat:@"%@/TerminalDb.sqlite",[arrDocPath1 objectAtIndex:0]];

if (sqlite3_open([strDestPath1 UTF8String], &database) != SQLITE_OK)
{
    sqlite3_close(database); // in case partially opened
    database = nil; // signal open error
}
sqlite3_exec(database, "pragma key = '43ec78dec5e8805d143bb1a1452d274ff49cb'", NULL, NULL, NULL);

BOOL ret;
int rc;
// SQL to create new database
const char *createItemsSQL = 
"CREATE TABLE 'temp_patient_table' ('id' INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 'firstname' TEXT, 'secondname' TEXT, 'temppatient_id' TEXT)";

sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(database, createItemsSQL, -1, &stmt, NULL);

ret = (rc == SQLITE_OK);
if (ret)
{ // statement built, execute
    rc = sqlite3_step(stmt);
    ret = (rc == SQLITE_DONE);
}

sqlite3_finalize(stmt); // free statement

}
-(void)copyPatientDetailsToTempTable{ // here inserting data into the table

NSArray *arrDocPath1 = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//    // Create the actual destination path
NSString *strDestPath1 = [NSString stringWithFormat:@"%@/TerminalDb.sqlite",[arrDocPath1 objectAtIndex:0]];

if(sqlite3_open([strDestPath1 UTF8String], &database) == SQLITE_OK)
{
    sqlite3_exec(database, "pragma key = '43ec78dec5e8805d143bb1a1452d274ff49cb'", NULL, NULL, NULL);



        NSString *insertQuery = @"INSERT INTO temp_patient_table(firstname,secondname,temppatient_id) VALUES('chakra','gemb','8')";


        void *v;
        char *err_msg;

        if(sqlite3_exec(database, [insertQuery UTF8String] , 0 , v , &err_msg) == SQLITE_OK)
        {
            NSLog(@"inserted successfully ");



        }
        else {

            NSLog(@"insertion has some problem %s",sqlite3_errmsg(database));

        }

    }



}

-(void)getAllPhysicianDetails{ // here for getting the data inside the table

NSArray *arrDocPath1 = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//    // Create the actual destination path
NSString *strDestPath1 = [NSString stringWithFormat:@"%@/TerminalDb.sqlite",[arrDocPath1 objectAtIndex:0]];


if(sqlite3_open([strDestPath1 UTF8String], &database) == SQLITE_OK) {

    sqlite3_exec(database, "pragma key = '43ec78dec5e8805d143bb1a1452d274ff49cb'", NULL, NULL, NULL);
    NSString *query;

    query = @"select firstname,secondname,temppatient_id from temp_patient_table";




    const char *sql = [query UTF8String];

    sqlite3_stmt *statement = nil;

    if(sqlite3_prepare_v2(database,sql, -1, &statement, NULL)!= SQLITE_OK){

        NSLog(@"error");

    }
    //NSAssert1(0,@”error preparing statement”,sqlite3_errmsg(database));

    else

    {
        while(sqlite3_step(statement)==SQLITE_ROW){

            NSString *strTempPhysicianId = [NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement, 0)];
            NSLog(@"%@",strTempPhysicianId);

            NSString *first = [NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement, 1)];

            NSLog(@"%@",first);

            NSString *last=[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(statement, 2)];

            NSLog(@"%@",last);


        }


    }

    sqlite3_finalize(statement);
}

}

All the four methods work fine with out any error....and i am able insert and retrieve the data back from the table.

But while opening the sqlite file in the application's document folder from firefox it is showing the following error:

"SQLiteManager: Error in opening file TerminalDb.sqlite - either the file is encrypted or corrupt
Exception Name: NS_ERROR_FILE_CORRUPTED
Exception Message: Component returned failure code: 0x8052000b (NS_ERROR_FILE_CORRUPTED) [mozIStorageService.openUnsharedDatabase]"

Can i know the reason behind the error and solution to see database (TerminalDb.sqlite) file in encrypted formatting if everything done up to now is correct. if any thing went wrong please give detailed solution for it.

Thanks for reading everything which i mentioned above.

Hope you reply me soon.

Cheers
Radhar.

[sjlombardo]

Firefox's SQLite Manager doesn't suppor SQLCipher. You can't use it to open an encrypted database. If you want to open it on your computer, you should use the SQLCipher-enabled sqlite3 command shell you built in step 1.