Simple instructions for creating and reading encrypted DB

SQLCipher
1

Hello

I’m having an issue that I’m sure is related to how I am using the API and I thought that this might be a good way for anyone who is using the community edition can get a headstart on a simple howto.

I downloaded the git code on to my Linux box and it compiles properly (including the extra options during configure as instructed) and so I’ve decided to test it.

I’ve got a piece of code that creates an encrypted database like so:

//open database
rc = sqlite3_open(outfile,&component_db);
if (rc != SQLITE_OK)
{
printf(“Could not open the sqlite database\n”);
exit(4);
}
sqlite3_key(component_db, “testkey”, strlen(“testkey”));
//printf(“creating version table\n”);
rc = sqlite3_exec(component_db, “CREATE TABLE ‘version’ (‘version_id’ INTEGER PRIMARY KEY)”, NULL, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
fprintf(stderr, “SQL error: %s\n”, zErrMsg);
sqlite3_free(zErrMsg);
}

and another piece of code that opens and reads it like so:

*rc = sqlite3_open(component_database_file_from_keyfile,&(component_db));*
if (rc != SQLITE_OK) 
*{*
  *g_print("failed to open in component database: %s\n", *sqlite3_errmsg(component_db));*
  *sqlite3_close(component_db);*
 *}*
 *else*
 *{*
   *g_print("Component database let's try to make it faster\n");*
   *sqlite3_key(component_db, "test", strlen("test"));*
   *rc = sqlite3_exec((component_db), "PRAGMA synchronous = OFF", 0, 0, &zErrMsg);*
   *rc = sqlite3_exec((component_db), "PRAGMA journal_mode = MEMORY", 0, 0, &zErrMsg);*
   *if (sqlite3_exec(component_db, "SELECT count(\*) FROM version", NULL, NULL, NULL) == SQLITE_OK)*
   *{*
     *g_print("key is correct\n");*
   *}*
   else 
   *{*
     *g_print("key is wrong\n");*
   *}*

}

Problem is that I always get a fail on the read. It seems to create a database that is encrypted but I cannot seem to open it with the sqlcipher command line application either. Obviously I’m creating the DB wrong but I cannot see how. It seems pretty straightforward.

Can anyone see anything I’m forgetting or misunderstanding.

Mark

2

Hi @markcia

I am not certain why you are receiving the failure, could you try to isolate the issue with a smaller piece of code? There may be an issue with the code formatting as it is currently displayed. As an example, I put together this simple C program that replicates most of your example, however it does not fail to read the database. Could you give this a try in your environment?

#include <stdio.h>
#include <string.h>
#include "sqlite3.h"

int main(){
  int rc;
  sqlite3 *db;
  sqlite3_stmt *stmt;
  char *password = "demo";

  rc = sqlite3_open("demo.db", &db);
  if(rc != SQLITE_OK){
    printf("failed to open database\n");
  }
  rc = sqlite3_key(db, password, strlen(password));
  if(rc != SQLITE_OK){
    printf("failed to key database\n");
  }
  rc = sqlite3_exec(db, "create table if not exists version(version_id integer primary key);", 0, 0, NULL);
  if(rc != SQLITE_OK){
    printf("failed to create version table\n");
  }
  rc = sqlite3_exec(db, "insert into version(version_id) values(5);", 0, 0, NULL);
  if(rc != SQLITE_OK){
    printf("failed to insert data into version table\n");
  }
  if(db != NULL){
    sqlite3_close(db);
  }
  rc = sqlite3_open("demo.db", &db);
  if(rc != SQLITE_OK){
    printf("failed to open database\n");
  }
  rc = sqlite3_key(db, password, strlen(password));
  if(rc != SQLITE_OK){
    printf("failed to key database\n");
  }
  rc = sqlite3_exec(db, "PRAGMA synchronous = OFF;", 0, 0, NULL);
  if(rc != SQLITE_OK){
    printf("failed to set syncrhronous to OFF\n");
  }
  rc = sqlite3_exec(db, "PRAGMA journal_mode = MEMORY;", 0, 0, NULL);
  if(rc != SQLITE_OK){
    printf("failed to set journal mode to MEMORY\n");
  }
  rc = sqlite3_prepare(db, "select count(*) from version;", -1, &stmt, NULL);
  rc = sqlite3_step(stmt);
  if(rc == SQLITE_ROW){
    printf("Current version:%s\n", sqlite3_column_text(stmt, 0));
  }
  sqlite3_finalize(stmt);
  if(db != NULL){
    sqlite3_close(db);
  }
  return 0;
}
3

OK. I’m feeling sheepish…

I had a function in the database create code that determined if the database existed already or not (I didn’t want to overwrite the database by mistake)

If it existed is would not create a new database and therefore not put the key in.

Doh!

Now then what would be the default key if one is not entered? Is there a default string there?

Mark

4

Hi @markcia

If you omit the sqlite3_key call, you will simply generate a plain text SQLite database, nothing would be encrypted. It is generally best to minimize the number of times you need to open/key your database instance as it is a time consuming behavior by default. Preferably opening and keying the database once during the application life-cycle if possible.

I’m unsure of what you are trying to do with the version table above, however if you are using that as a means to track your application schema migrations you have applied to a database, you might consider managing that with PRAGMA user_version instead.