Sqlite: Managing the SQL Database
This tutorial demonstrates how you can use the open source modules, whose functions provide access to SQLite and OpenSSL and demonstrate how to encrypt and store application data. The open source modules supported by Tizen are listed in the API modules.
Warm-up
Become familiar with the basics of the Sqlite API by learning about:
-
Initializing the Database
Initialize the database for use.
-
Storing Encrypted Data
Store AES encrypted data in the SQL database.
Initializing the Database
Initialize external open source libraries:
-
To operate on an SQL database provided by SQLite:
- Configure the database interface sqlite3_config (SQLITE_CONFIG_URI, 1). It allows setting the path to the database using URI.
- Initialize the interface with the sqlite3_initialize() function.
- Open database using the sqlite3_open(file_path, &db) function. It creates a new database file, if the URI points to a non-existing one. The handle to the database is stored in a db variable.
- Create a table, this step is described later.
-
To operate with OpenSSL AES, salt and initial vectors are needed. Those are auxiliary variables, which are used to generate a cryptographic key:
- RAND_bytes() generates a random chain of bytes.
- An additional character is appended after the last character of a chain to point the end of the salt[8]=0x00 variable.
- PrepareToSQL() removes some of the SQL special characters from the chain to make it possible to insert it to the database.
-
Include the required headers:
- #include <string.h>
- #include <time.h>
- #include <stdint.h>
- #include <stdlib.h>
- #include <sqlite3.h>
- #include <openssl/aes.h>
- #include <openssl/crypto.h>
- #include <openssl/rand.h>
- #include <openssl/evp.h>
The time.h is obtained to create an example message to encrypt and store.
This scenario is only a draft of encryption and database usage. For more information, see OpenSSL and SQLite Web pages.
| Note |
|---|
| While operating with real data, make sure that you fulfill all security requirements. Code shown in this tutorial demonstrates only how to use libraries APIs on Tizen but not how to perform fully secure encryption. |
#define BUFLEN 300 // Buffer size, used in functions
const int key_len = 256; // AES key length
static const unsigned char password[] = {"DummyPassword"}; // Password to generate key
sqlite3 *db; // Database handle
unsigned char salt[9]; // Encryption salt
unsigned char iv[17]; // Encryption initial vector
sqlite3_shutdown(); sqlite3_config(SQLITE_CONFIG_URI, 1); sqlite3_initialize(); char file_path[BUFLEN]; char *document_path; // Fill the variable with the value obtained using storage_foreach_device_supported() int internal_storage_id = 0; storage_get_directory(internal_storage_id, STORAGE_DIRECTORY_DOCUMENTS, &document_path); snprintf(file_path, size, "%s/test.db", document_path); free(document_path); sqlite3_open(file_path, &db); free(file_path); CreateTable(); RAND_bytes(salt, 8); RAND_bytes(iv, 16); salt[8]=0x00; iv[16]=0x00; PrepareToSQL(salt); PrepareToSQL(iv);
To create a table, an SQL query has to be prepared. The sqlite3_exec() function executes the query in the defined database.
An SQL query:
CREATE TABLE IF NOT EXISTS EncryptedData(TEXT NOT NULL, ENCRYPTED INT NOT NULL, SALT TEXT NOT NULL, IV TEXT NOT NULL, PART INTEGER, KEY INTEGER PRIMARY KEY);
A return message is passed to the callback function as an array of strings. A database is created only if it does not exist already. The created database name is EncryptedData, and it contains the following fields:
- DATA
- ENCRYPTED
- SALT
- IV
- PART
- KEY
static int
CreateTable()
{
int ret;
char *ErrMsg;
char *sql = "CREATE TABLE IF NOT EXISTS EncryptedData(" \
"DATA TEXT NOT NULL," \
"ENCRYPTED INT NOT NULL,"\
"SALT TEXT NOT NULL," \
"IV TEXT NOT NULL," \
"PART INTEGER," \
"KEY INTEGER PRIMARY KEY);";
sqlite3_exec(db, sql, callback, 0, &ErrMsg);
return 0;
}
The string delimiting characters are removed. In a real implementation, replace some more characters, such as the comment sequence.
void
PrepareToSQL(unsigned char* msg)
{
int i = 0;
while (msg[i]!=0x00)
{
if (msg[i]=='\'')msg[i]='a';
if (msg[i]=='\"')msg[i]='b';
++i;
}
}
Storing Encrypted Data
This scenario shows how to:
- Encrypt a message with OpenSSL
- Prepare it to store in the database with Base64
- Store in the SQLite database
- Get the database content
- Encrypt the message
To store encrypted data:
- Store a short message.
OpenSSL AES_Encrypt allows encrypting up to 16 characters at once. Adding a short message (length<=16 characters) is divided in 2 steps:
- Encrypt plain text EncryptMsg().
- Execute database insertion InsertRecord().
char *ShortMsg = "Short Msg."; EncryptMsg(ShortMsg, decrypted_out, password, salt, iv); InsertRecord(decrypted_out, 1, 0, strlen(ShortMsg));
- Encrypt.
- Generate a string for key generation from password and salt using PKCS5_PBKDF2_HMAC_SHA1(). Also, an initial vector can be used in different hash functions. In this case, only the first byte of the initial vector is used as an iteration variable for a hash algorithm.
- Generate the encryption key using the AES_set_encrypt_key() function. A key len defines the AES key length. Check the actual recommended length and encoding type before use. This tutorial uses AES 256, which is defined in the initialization section.
- Encrypt the data.
- Encode Base64. Data is encoded to make it safe to insert it to the database. Base 64 does not contain any special characters. Encoding and decoding functions are listed at the end of the tutorial.
- Add a delimiting 0x00 character at the end of the byte array.
static int EncryptMsg(char* in, unsigned char* out, const unsigned char* password, unsigned char *localsalt, unsigned char *vector) { AES_KEY encryption_key; int iter = (int)vector[0]; unsigned char key[key_len+1]; char *msgbuff; unsigned char buf[BUFLEN]; unsigned int retlen; int x; memset(buf, 0x00, BUFLEN); PKCS5_PBKDF2_HMAC_SHA1((char *)password, sizeof(password)/sizeof(unsigned char), localsalt, sizeof(localsalt)/sizeof(unsigned char), iter, key_len, key); AES_set_encrypt_key(key, key_len, &encryption_key); AES_encrypt((unsigned char *)in, (unsigned char *)buf, &encryption_key); msgbuff = base64_encode(buf, 16, &retlen); memcpy(buf, msgbuff, retlen); buf[retlen+1]=0x00; free(msgbuff); memcpy(out, buf, retlen+1); for (x=0; buf[x]!=0x00; x++); return x; } - Insert a record into the database.
To insert data to the database, use the sqlite3_exec() function. A query is prepared with common C functions. The encrypted variable is stored in the database and indicates whether the DATA field in the database is encrypted.
static int InsertRecord(unsigned char *msg, int encrypted, int part, int len) { char sqlbuff[BUFLEN]; char *ErrMsg; snprintf(sqlbuff, BUFLEN, "INSERT INTO EncryptedData VALUES(\'%s\', %d, \'%s\', \'%s\', %d, NULL);", msg, encrypted, salt, iv, part); ret = sqlite3_exec(db, sqlbuff, callback, 0, &ErrMsg); if (ret) { dlog_print(DLOG_DEBUG, LOG_TAG, "Error: %s\n", ErrMsg); sqlite3_free(ErrMsg); } return 0; } - Store long messages.
Any message longer than 16 characters has to be split into shorter parts. In this scenario, each part is stored in its own record to simplify the decoding procedure.
ret = InsertMessage((unsigned char *)text);
A message is divided into blocks 16 characters long. Each block is tagged by its own number with a parts variable. A tag is stored in the database in the PART field.
static int InsertMessage(unsigned char* text) { unsigned char encrypted_out[BUFLEN]; int ret = 0; int x; int len, retlen, parts=0, pos; char membuf[17]; for (len = 0; text[len]!=0x00; len++); for (pos = 0; (len-pos)>16; pos+=16) { memcpy(membuf, &text[pos], 16); membuf[16] = 0x00; EncryptMsg((char *)membuf, encrypted_out, password, salt, iv); for (x = 0; encrypted_out[x]!=0x00; x++); InsertRecord(encrypted_out, 1, parts, x); parts++; } if (len-pos>0) { retlen = EncryptMsg((char *)&text[pos], encrypted_out, password, salt, iv); InsertRecord(encrypted_out, 1, parts, retlen); } return 0; } - List the database content.
After previous actions, the database contains AES encrypted content, stored in a Base64 notation.
ShowRecords();
Example database content:
0: DATA = xPRnJYwvQTc4VJKkW4EroQ== 1: DATA = EZI+uOGxcnUseJnbH57/Bg==
To list all records, use the SELECT * FROM EncryptedData query. The callback function is invoked for each record returned by SQLite. SQLite allows passing user data to the callback function. User data is the 4th parameter of sqlite3_exec(..., ..., ..., &counter, ...).
static void ShowRecords() { char *sql = "select * from EncryptedData"; int counter = 0, ret = 0; char *ErrMsg; sqlite3_exec(db, sql, callback, &counter, &ErrMsg); return; }Callback simply prints an obtained record. The record is passed in the form of a few arrays of strings. Argv contains the column content, azColName contains the column name. Argc is the number of columns. Counter is user data. Here it is used to indicate the record number in a row (not an SQL primary key) while listing. If the content of any column is empty, the NULL string is printed.
static int callback(void *counter, int argc, char **argv, char **azColName) { int *localcounter = (int *)counter; int i; dlog_print(DLOG_DEBUG, LOG_TAG, "-%d: ", *localcounter); for (i = 0; i<argc; i++) dlog_print(DLOG_DEBUG, LOG_TAG, "%s = %s | ", azColName[i], argv[i] ? argv[i] : "NULL"); (*localcounter)++; dlog_print(DLOG_DEBUG, LOG_TAG, "\n"); return 0; } - Decrypt.
All encrypted records are selected from database, and they are decrypted and listed.
DecryptRecords();
The SELECT * FROM EncryptedData query where ENCRYPTED='1' lists all encrypted records. The callbackdecrypt() function is invoked for each record returned by the sqlite3_exec() function. In case of an error, the SQLite API requires releasing error message memory with the sqlite3_free() function.
static void DecryptRecords() { char *sql = "select * from EncryptedData where ENCRYPTED='1'"; int counter = 0; int ret = 0; char *ErrMsg; ret = sqlite3_exec(db, sql, callbackdecrypt, &counter, &ErrMsg); if (ret) { dlog_print(DLOG_DEBUG, LOG_TAG, "Error: %s\n", ErrMsg); sqlite3_free(ErrMsg); } }The callback function is similar to the function used to list records. There is an action added for the argv[ i ] && i==0 case. It means that if a column is not empty and the column is DATA, you must perform further actions. From an SQL query, we know that the DATA content is encrypted.
To decrypt the message:
- Data is decoded from Base64
- Delimiting character is added
- DecryptMsg() is called for decryption
- Decrypted data is listed
static int callbackdecrypt(void *counter, int argc, char **argv, char **azColName) { unsigned char decrypted_out[BUFLEN]; int *localcounter = (int *)counter; int i; unsigned int olen; dlog_print(DLOG_DEBUG, LOG_TAG, "-%d: ", *localcounter); for (i = 0; i<argc; i++) { if (argv[i]&&i == 0) { unsigned char *basebuffer = base64_decode(argv[i], strlen(argv[i]), &olen); unsigned char *decryptbuffer = malloc(sizeof(char)* olen +1); memset(decryptbuffer, 0x00, olen+1); memcpy(decryptbuffer, basebuffer, olen); decryptbuffer[olen] = 0x00; free(basebuffer); DecryptMsg((unsigned char *)decryptbuffer, decrypted_out, password, (unsigned char *)argv[2], (unsigned char *)argv[3]); free(decryptbuffer); dlog_print(DLOG_DEBUG, LOG_TAG, "%s = %s | ", azColName[i], (char*)decrypted_out); } else { dlog_print(DLOG_DEBUG, LOG_TAG, "%s = %s | ", azColName[i], argv[i] ? argv[i] : "NULL"); } } (*localcounter)++; dlog_print(DLOG_DEBUG, LOG_TAG, "\n"); return 0; }The decryption routine is similar to encryption:
static int DecryptMsg(unsigned char* in, unsigned char * out, const unsigned char* password, unsigned char* localsalt, unsigned char* vector) { AES_KEY decryption_key; int iter = (int)vector[0]; unsigned char key[key_len+1]; int x; memset(out, 0x00, BUFLEN); PKCS5_PBKDF2_HMAC_SHA1((char *)password, sizeof(password)/sizeof(unsigned char), localsalt, sizeof(localsalt)/sizeof(unsigned char), iter, key_len, key); AES_set_decrypt_key(key, key_len, &decryption_key); AES_decrypt((unsigned char *)in, out, &decryption_key); for (x = 0; out[x]!=0x00; x++); return x; } - Clean up.
The SQLite library has to be closed properly when no longer needed:
base64_cleanup(); sqlite3_close(db); sqlite3_shutdown();
- Base64
The following example shows the Base 64 encoding and decoding interface:
static char encoding_table[] = {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/'}; static char *decoding_table = NULL; static int mod_table[] = {0, 2, 1}; void build_decoding_table() { decoding_table = malloc(256); int i; for (i = 0; i < 64; i++) decoding_table[(unsigned char) encoding_table[i]] = i; } char *base64_encode(const unsigned char *data, size_t input_length, size_t *output_length) { *output_length = 4 * ((input_length + 2) / 3); char *encoded_data = malloc(*output_length); if (encoded_data == NULL) return NULL; int i, j; for (i = 0, j = 0; i < input_length;) { uint32_t octet_a = i < input_length ? (unsigned char)data[i++] : 0; uint32_t octet_b = i < input_length ? (unsigned char)data[i++] : 0; uint32_t octet_c = i < input_length ? (unsigned char)data[i++] : 0; uint32_t triple = (octet_a << 0x10) + (octet_b << 0x08) + octet_c; encoded_data[j++] = encoding_table[(triple >> 3 * 6) & 0x3F]; encoded_data[j++] = encoding_table[(triple >> 2 * 6) & 0x3F]; encoded_data[j++] = encoding_table[(triple >> 1 * 6) & 0x3F]; encoded_data[j++] = encoding_table[(triple >> 0 * 6) & 0x3F]; } for (i = 0; i < mod_table[input_length % 3]; i++)encoded_data[*output_length - 1 - i] = '='; return encoded_data; } unsigned char *base64_decode(const char *data, size_t input_length, size_t *output_length) { if (decoding_table == NULL) build_decoding_table(); if (input_length % 4 != 0) return NULL; *output_length = input_length / 4 * 3; if (data[input_length - 1] == '=') (*output_length)--; if (data[input_length - 2] == '=') (*output_length)--; unsigned char *decoded_data = malloc(*output_length); if (decoded_data == NULL) return NULL; int i, j; for (i = 0, j = 0; i < input_length;) { uint32_t sextet_a = data[i] == '=' ? 0 & i++ : decoding_table[(int)data[i++]]; uint32_t sextet_b = data[i] == '=' ? 0 & i++ : decoding_table[(int)data[i++]]; uint32_t sextet_c = data[i] == '=' ? 0 & i++ : decoding_table[(int)data[i++]]; uint32_t sextet_d = data[i] == '=' ? 0 & i++ : decoding_table[(int)data[i++]]; uint32_t triple = (sextet_a << 3 * 6) + (sextet_b << 2 * 6) + (sextet_c << 1 * 6) + (sextet_d << 0 * 6); if (j < *output_length) decoded_data[j++] = (triple >> 2 * 8) & 0xFF; if (j < *output_length) decoded_data[j++] = (triple >> 1 * 8) & 0xFF; if (j < *output_length) decoded_data[j++] = (triple >> 0 * 8) & 0xFF; } return decoded_data; } void base64_cleanup() { free(decoding_table); decoding_table = NULL; } - Main function source code:
int OpenSQL_AES_example_1(void) { unsigned char decrypted_out[BUFLEN]; int ret; char *hellomsg = {"Hello Tizen! SQLite OpenSSL"}; char text[BUFLEN]; time_t rawtime; struct tm *timeinfo; if (init_err) { dlog_print(DLOG_DEBUG, LOG_TAG, "Initial error\n"); return 1; } time(&rawtime); timeinfo = localtime(&rawtime); sprintf(text, "%s %s", hellomsg, asctime(timeinfo)); // Insert long message ret = InsertMessage((unsigned char *)text); if (ret) { dlog_print(DLOG_DEBUG, LOG_TAG, "Insert ENCRYPTED MessageError\n"); return 1; } // Insert message up to 16 characters char *ShortMsg = "Short Msg."; EncryptMsg(ShortMsg, decrypted_out, password, salt, iv); ret = InsertRecord(decrypted_out, 1, 0, strlen(ShortMsg)); if (ret) { dlog_print(DLOG_DEBUG, LOG_TAG, "Insert ENCRYPTED MessageError\n"); return 1; } // Show raw database ShowRecords(); // Show decrypted database DecryptRecords(); return 0; }
