Basic structure program to cipher database with AES 128 to read and write

[Zapotecatl]

Hello,

I did my basic structure program in order to cipher my database with AES 128 to read and write:

#include <stdio.h>
#include <string.h>
#include <sqlite3mc.h>

int main(){
  int rc;
  sqlite3 *db;
  
  rc = sqlite3_open("demo.db", &db);
  
  if(rc != SQLITE_OK){
  
    rc = sqlite3mc_config(db, "default:cipher", sqlite3mc_cipher_index("aes128cbc"));
    if (!(rc < 0)) {
      printf("configure the cipher successful\n");
    }
    else {
      printf("failed to configure the cipher\n");
    }
    
    // Multiple cipher encrypt the database with AES 128
    const char *password = "demo";
    rc = sqlite3_key(db, password, strlen(password));
    if(rc != SQLITE_OK){
       printf("failed to key database\n");
    }
	
    // HERE //////////////////////
    //  My stuff
    //////////////////////////////////////////
  }
  else {
    printf("failed to open database\n");
  }
  
  if(db != NULL){
    sqlite3_close(db);
  }
}

I run my program and seems all works fine.

Thinking in this requirement: "Cipher your SQLite database with AES 128 for read and write".

My question is: this basic structure program accomplish the requirement?

I appreciate a lot your help, thank you!

[utelle]

I did my basic structure program in order to cipher my database with AES 128 to read and write:

#include <stdio.h>
#include <string.h>
#include <sqlite3mc.h>

int main(){
  int rc;
  sqlite3 *db;
  
  rc = sqlite3_open("demo.db", &db);
  
  if(rc != SQLITE_OK){
  
    rc = sqlite3mc_config(db, "default:cipher", sqlite3mc_cipher_index("aes128cbc"));

The cipher configuration step is only necessary, if aes128cbc is not already the default cipher.

    if (!(rc < 0)) {
      printf("configure the cipher successful\n");
    }
    else {
      printf("failed to configure the cipher\n");
    }
    
    // Multiple cipher encrypt the database with AES 128
    const char *password = "demo";
    rc = sqlite3_key(db, password, strlen(password));
    if(rc != SQLITE_OK){
       printf("failed to key database\n");
    }

For an already existing database (either unencrypted or encrypted) the return code of function sqlite3_key will not indicate whether the given key is correct. It just indicates that the encryption engine was successfully initialized with the given key.

Whether the given key is correct for an existing database will not be known, until the database is actually accessed (using some SQL statements). This follows the usual SQLite philosophy to only access the underlying database file when data are actually read or written.

However, for a new empty database the given key will be used to encrypt the database file.

	
    // HERE //////////////////////
    //  My stuff
    //////////////////////////////////////////
  }
  else {
    printf("failed to open database\n");
  }
  
  if(db != NULL){
    sqlite3_close(db);
  }
}

I run my program and seems all works fine.

Thinking in this requirement: "Cipher your SQLite database with AES 128 for read and write".
My question is: this basic structure program accomplish the requirement?

No, you will have to distinguish several use cases:

1. For a new empty database it is enough to invoke sqlite3_key. An easy check for this use case would be to check that a database file with the given name does not already exist.
2. For an already existing unencrypted/plain database encryption can be enabled by invoking sqlite3_rekey.
3. For an already existing encrypted database function sqlite3_key needs to be invoked with the correct key. Thereafter, sqlite3_rekey can be used to change the key.

[Zapotecatl]

Hi @utelle , thank you very much, I am learning and it is more clear to me how I should proceed base on your use cases.

In particular, for my problem, there is a script (Command-line arguments) that previously always create a database if it doesn't exist. Then, the case new empty database is not possible in my C API environment. Therefore:

• The case already existing unencrypted/plain database occurs only the first time that script creates the database.
• The case already existing encrypted database is the case that commonly is execute.

Then my question is: how can I determine correctly if the database is already encrypted or not? I have tried with the function sqlite3mc_codec_data, but I always get NULL (I have encrypted previously the database successfully). Let me show you my program.

#include <stdio.h>
#include <string.h>
#include <sqlite3mc.h>

int main(){

    sqlite3 *db;
    sqlite3_stmt *res;
       
    int rc = sqlite3_open("/tmp/temp_db", &db);
     
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
	
	/*
	rc = sqlite3mc_config(db, "default:cipher", sqlite3mc_cipher_index("aes128cbc"));
    if (!(rc < 0)) {
      printf("configure the cipher successful\n");
    }
    else {
      printf("failed to configure the cipher\n");
    }*/
	
    
    const char *password = "demo";
    if (sqlite3mc_codec_data(db, NULL, "cipher_salt") != NULL) { // It is an encrypted database
       rc = sqlite3_key(db, password, strlen(password));
    }
    else { // It is an unencrypted database
       rc = sqlite3_rekey(db, password, strlen(password));
    }
    
    if (rc == SQLITE_OK) {
       printf("The engine was successfully initialized with the given key\n");
    }
    else {
      printf("The engine was not successfully initialized with the given key\n");
      sqlite3_close(db);
      return 1;
    }

    // To check whether the provided key was actually correct, you must execute a simple query
    rc = sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &res, 0);    
    
    // check whether that succeeds.
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to fetch data: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
   
    rc = sqlite3_step(res);
    if (rc == SQLITE_ROW) {
        printf("Version: %s\n", sqlite3_column_text(res, 0));
    }
		
	// HERE //////////////////////
    //  My stuff
    //////////////////////////////////////////
	   
    sqlite3_finalize(res);
    sqlite3_close(db);
	
}

Again, thanks a lot for the help!

[utelle]

I am learning and it is more clear to me how I should proceed base on your use cases.

I'm going to convert this issue into a discussion, because this is not an issue in the component itself, but in using the component.

In particular, for my problem, there is a script (Command-line arguments) that previously always create a database if it doesn't exist. Then, the case new empty database is not possible in my C API environment.

Ok. That is, the database file that should be handled by your application already exists (and probably already has some data content), but you don't know whether it is already encrypted or not. If it is a plain unencrypted database, you want to apply encryption. If the database file is already encrypted, you want to do nothing. Is that correct? If yes, the simple approach will be to perform the following steps:

1. Open database file with sqlite3_open
2. Execute sqlite3_rekey
3. If the previous step fails with return code 26 = SQLITE_NOTADB = "Not a database file", then the given database file is either an already encrypted database file or not a database file at all.
4. Optionally you may check whether the database file is actually a properly encrypted database by executing sqlite3_key and then trying to read some data from the database.

Then my question is: how can I determine correctly if the database is already encrypted or not?

SQLite does not provide a dedicated function for checking whether a database file is encrypted or not or is a database file at all. Instead SQLite reports an error when the database is actually accessed for the first time in a session, if the database could not be accessed successfully. The return code for the unsuccessful command will usually be 26 = SQLITE_NOTADB = "Not a database file". If sqlite3_key was not invoked, this return code indicates that the database file is either encrypted or not a database at all. If sqlite3_key was invoked, this return code indicates that a wrong key was given or that the file is not a database at all.

I have tried with the function sqlite3mc_codec_data, but I always get NULL (I have encrypted previously the database successfully). Let me show you my program.

Function sqlite3mc_codec_data can only return usuable information, if the encryption engine was previously initialized with sqlite3_key.

    const char *password = "demo";
    if (sqlite3mc_codec_data(db, NULL, "cipher_salt") != NULL) { // It is an encrypted database
       rc = sqlite3_key(db, password, strlen(password));
    }
    else { // It is an unencrypted database
       rc = sqlite3_rekey(db, password, strlen(password));
    }

As explained above this will not work as expected.

    // To check whether the provided key was actually correct, you must execute a simple query
    rc = sqlite3_prepare_v2(db, "SELECT SQLITE_VERSION()", -1, &res, 0);    

No, this select command will always work, even if the wrong key was given, because it does not access the database itself, but only the library.

One command, that allows to actually access the database without knowing the database schema, would be for example SELECT count(*) FROM sqlite_schema;.

[Zapotecatl]

Thanks a lot @utelle , with your help I have gained enough knowledge to solve my problem regarding of C interface. Now I am working with SQL Interface...