[Unix x86][C/CPP]Encrypt and Decrypt a database using sqlCipher API

ranjankumar23 · October 27, 2022, 6:33am

Team,

Thanks for creating sqlCipher.

I want to encrypt and decrypt a database on unix x86 platform in C/CPP program.

Encryption

sqlite3_open(my_db, &qdb) != SQLITE_OK) 
sqlite3_key(qdb, "ranjan", 6) != SQLITE_OK)

Now to update this db in run time, my C/CPP program will need to decrypt this DB. right?

- Open the empty DB.
- Encrypt the DB.
Everytime application wants to update this encrypted db;
- Decrypt the DB,
- update the table
- Encrypt the DB again

Which sqlcipher APIs i can use in my C/CPP program to decrypt the DB?
what is license key for opensource community version of sqlcipher?

Thanks in advance for looking into this issue

developernotes · October 27, 2022, 1:14pm

Hi @ranjankumar23,

Thanks for your interest in SQLCipher! Calling sqlite3_key does not encrypt the database, rather it sets the password material to be used once a read/write operation is required by the database. When a read/write operation is invoked, it will take the password material and determine whether the encryption key needs to be derived, or if a raw key was provided to use that as the encryption key. All subsequent database operations (i.e., INSERT, UPDATE, DELETE, SELECT) will encrypt/decrypt the data on demand for you (presuming you are working on a SQLCipher database.)

You will use the standard SQLite C API ^[1] for interfacing with the database after it has been keyed. A license code is only required with the Commercial and Enterprise editions of SQLCipher, the Community edition does not require a license code.

As an aside, if you already have a plaintext SQLite database and wish to encrypt it with SQLCipher, you will want to use the sqlcipher_export(...) ^[2] convenience function.

ranjankumar23 · October 28, 2022, 5:44am

Thank you Nick.

It means if i am creating new encrypted database, then only this function call is required.
sqlite3_key(qdb, “ranjan”, 6)

Everything else will remain same in existing code for all subsequent database operations (i.e., INSERT , UPDATE , DELETE , SELECT ).

Somehow encrypted DB file is getting created, but it is not getting updated. i will debug and come up with more info to seek your help.

developernotes · October 28, 2022, 1:56pm

Hi @ranjankumar23

You will want to make sure your application is properly linking SQLCipher, instead of SQLite. The recommended approach to doing this is by executing the following SQL statement:

PRAGMA cipher_version;

This will return the SQLCipher library version as a string, however, if you are linking against SQLite, you will not receive a value.

ranjankumar23 · November 1, 2022, 3:43pm

Thanks Nick.
It was issue with incorrect linking of sqlcipher.

ranjankumar23 · November 3, 2022, 11:39am

Nick,

Now i want to complete this use case.

Create an encrypted db using following APIs.
sqlite3_open(my_db, &qdb) != SQLITE_OK)
sqlite3_key(qdb, “ranjan”, 6) != SQLITE_OK)
Do all subsequent database operations (i.e., INSERT , UPDATE , DELETE , SELECT ) using other sqlite3 APIs.
Now change the key for database created in step-1. Is this understanding correct?
sqlite3_open(my_db, &qdb) != SQLITE_OK)
sqlite3_key(qdb, “ranjan”, 6) != SQLITE_OK)
sqlite3_rekey(qdb, “kumar”, 5) != SQLITE_OK)

developernotes · November 3, 2022, 1:12pm

Hi @ranjankumar23

Yes, in order to perform a rekey operation ^[1] you must open the database and provide the current key prior to invoking the rekey step.

SQLCipher API - Zetetic ↩︎

ranjankumar23 · November 6, 2022, 3:44am

Nick, latest sqlcipher 4.5.2 is compatible with openssl 1.0, openssl 1.1 and openssl 3.0 ?

ranjankumar23 · November 7, 2022, 12:03pm

@developernotes Nick, i have last two questions for this thread.

latest sqlcipher 4.5.2 is compatible with openssl 1.0, openssl 1.1 and openssl 3.0 ?
If application create a encrypted db using key ‘KEY1’ and system is rebooted. After reboot application needs to provide the same key ‘KEY1’ before any operation on DB?

developernotes · November 7, 2022, 1:43pm

Hi @ranjankumar23

SQLCipher can be used with OpenSSL 1.0.*, 1.1.*, however, I cannot speak to the 3.0.* series branch. Anytime a SQLCipher-encrypted database is closed, you will need to provide the same key again prior to any database access on that file.

ranjankumar23 · November 7, 2022, 2:21pm

Thanks Nick. You have been very helpful.

developernotes · November 7, 2022, 2:23pm

Hi @ranjankumar23

As a follow-up, I have confirmed that OpenSSL 3.0.* series does also work with SQLCipher.

ranjankumar23 · November 7, 2022, 2:48pm

Yes Nick, i am using openssl-3.0 and it has been working without any issue.

ranjankumar23 · November 11, 2022, 12:24pm

Nick @developernotes

I need your help.

I am trying to convert plaintext DB to encrypted DB in C program.

Command line is working fine.

$ ./sqlcipher test-me.db
sqlite> ATTACH DATABASE ‘encrypted.db’ AS encrypted KEY ‘testkey’;
sqlite> SELECT sqlcipher_export(‘encrypted’);
sqlite> DETACH DATABASE encrypted;

But in code it is not working. It is creating encrypted.db with size 0, whereas with command line encrypted.db size is same as test-me.db size.

CODE SNIPPET

if (sqlite3_open("/home/config/test-me.db", &db_persist_1) != SQLITE_OK) {
 
  sqlite3_close(qdb_persist_1);
  qdb_persist_1 = NULL;
  return -1;
}

char *zErrMsg = 0;
const char *sql = " "          \
"CREATE TABLE ABC_KEY("        \
   "ID   KEY  CHAR(128),"  \
   "LEN       UINT(1),"    \
   "TIMESTAMP CHAR(20),"   \
   "a_ID   CHAR(65), UNIQUE(ID));";


/* Execute SQL statement */
if( sqlite3_exec(db_persist_1, (const char*)sql, _callback, 0, &zErrMsg) != SQLITE_OK ){
    sqlite3_free(zErrMsg);
    return QERR_TABLE_CREATE_VOLATILE;
}



const char *sql1 = " "          \
"ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'test123';"  \
   "SELECT sqlcipher_export(encrypted);"  \
   "DETACH DATABASE encrypted;";

/* Execute SQL statement */
if( sqlite3_exec(db_persist_1, (const char*)sql1, _callback, 0, &zErrMsg) != SQLITE_OK ){
    sqlite3_free(zErrMsg);
   return -1;
}

developernotes · November 11, 2022, 2:24pm

Hi @ranjankumar23

There are a couple of things to check. First, in your C program, change the behavior to execute the following SQL command after you open a database connection:

PRAGMA cipher_version;

Verify that a string value is returned to confirm you are properly linking SQLCipher within the application. Next, execute the individual SQL statements separately and inspect their individual result codes. You might find it easier to create a prepared statement and stepping through it. An introduction to the C/C++ interface can be found here ^[1].

An Introduction To The SQLite C/C++ Interface ↩︎

ranjankumar23 · November 12, 2022, 5:16am

@developernotes Nick,

This is the output.
cipher_version = 4.5.2 community
RANJAN-no such column: encrypted

Error is “no such column: encrypted” if i execute this code.

if (sqlite3_open(“/home/config/test-me.db”, &db_persist_1) != SQLITE_OK) {

sqlite3_close(qdb_persist_1);
qdb_persist_1 = NULL;
return -1;
}

const char *sql1 = " "
“ATTACH DATABASE ‘encrypted.db’ AS encrypted KEY ‘qnulabs123’;”
“SELECT sqlcipher_export(encrypted);”
“DETACH DATABASE encrypted;”;

/* Execute SQL statement */
if( sqlite3_exec(qdb_persist_1, (const char*)sql1, _callback, 0, &zErrMsg) != SQLITE_OK ){
   
    printf("RANJAN-%s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  
}

sjlombardo · November 12, 2022, 4:56pm

@ranjankumar23 you are missing the Single quote marks around the encrypted parameter to sqlcipher_export().

ranjankumar23 · November 13, 2022, 4:08pm

Thanks Stephen. It is working now.

Topic		Replies	Views
The sqlcipher key security!	1	759	October 19, 2020
Unable to read encrypted db in command line SQLCipher	8	2456	April 13, 2022
SQLCipher decryption Issues	1	713	October 19, 2018
Access SQL Cipher 4 Encrypted database for Automation Issues	4	1070	May 1, 2019
Simple instructions for creating and reading encrypted DB SQLCipher	3	5646	April 6, 2015

[Unix x86][C/CPP]Encrypt and Decrypt a database using sqlCipher API

Encryption

Command line is working fine.

CODE SNIPPET

Related topics