Do you wanna keep your embedded database encrypted?

Introduction

Data privacy and security is the priority of every software product and services. During developing any software product or services as a developer first thing we should have to keep in mind that the storage and fetching of data should be secured and protected.
In this blog I am using SQLite Database and Python Language to show how to encrypt Data inside database and how to show the decrypted data on client side.
I am dividing the process into several parts to get good understanding.

• Understanding of Data Encryption
• Software Setup
• Core Coding Understanding
• Inferences

Theory of Data Encryption

Theory

Encryption is the process of translating plain text data (plaintext) into something that appears to be random and meaningless (ciphertext). Decryption is the process of converting ciphertext back to plaintext.
To encrypt more than a small amount of data, symmetric encryption is used. A symmetric key is used during both the encryption and decryption processes. To decrypt a particular piece of ciphertext, the key that was used to encrypt the data must be used.
The goal of every encryption algorithm is to make it as difficult as possible to decrypt the generated ciphertext without using the key. If a really good encryption algorithm is used, there is no technique significantly better than methodically trying every possible key. For such an algorithm, the longer the key, the more difficult it is to decrypt a piece of ciphertext without possessing the key.
It is difficult to determine the quality of an encryption algorithm. Algorithms that look promising sometimes turn out to be very easy to break, given the proper attack. When selecting an encryption algorithm, it is a good idea to choose one that has been in use for several years and has successfully resisted all attacks.
Reference

Flow Diagram

Software Setup

1. Tool: VS-Code: Download
2. Language: Python Download, SQLite Download
3. Libraries: Cryptography Command: pip install cryptography-Read Here, Fernet Read Here

Core Coding

Scenario to understand Coding

Let us take a part of application which maintains the daily routine of ours in a database embedded in our system. But there is a risk of keeping the .db file in the system as plain text because the data is readable. So, we have to keep this database encrypted hence we will use following method to encrypt the data base.

File Structure and Understanding

We will be using modular form development. So for each functionality we will be creating a module. And finally use each module in main module by importing and calling it.

Creation of Database

As shown there is a file name createdb.py.The purpose of this file is to create a new database mySecret.db having following columns:

• Routine ID: It is the description of routine such as morning routine, breakfast, office hour etc.
• Date: The date when this routine is or was happened.
• Start Time: The time when this routine is starting/started.
• End Time: The time when this routine is ending/ended.
• Status: The Routine is done/pending/in progress/not done etc.

Steps

1. Create a file name as createdb.py.
2. Code the program as shown in Python Code to implement the db creation.
3. Save it.

Python Code to implement the db creation

import sqlite3

def create():
    conn = sqlite3.connect("mySecret.db")
    crsr = conn.cursor()
    sql_command = """CREATE TABLE ROUTINE (
    routineID VARCHAR(10000) PRIMARY KEY,
    date DATE,
    startTime VARCHAR(5),
    endTime VARCHAR(5),
    status VARCHAR(10));"""
    crsr.execute(sql_command)
    conn.commit()
    conn.close()

Code Explanation

sqlite3 is the library we have to import in order to work with database in python.
Every Database handling under sqlite3 follows simple process:

• Connection with database
• Taking the curser of database
• Giving the SQL Query to Execute
• Commit the Execution
• Closing the Database.

SQL Query to create Table

CREATE TABLE ROUTINE (
routineID VARCHAR(10000) PRIMARY KEY,
date DATE,
startTime VARCHAR(5),
endTime VARCHAR(5),
status VARCHAR(10));

Generation of Universal Key

As shown there is a filename generateKey.py.The purpose of this file is to generate a universal key which would be use while encryption and decryption of a cell in database.

Steps

1. Create a file name as generateKey.py.
2. Code the program as shown in Python Code to implement the key generation.
3. Save it.

Python Code to implement the key generation

from cryptography.fernet import Fernet

def generate():
    key = Fernet.generate_key()
    with open("universal.key","wb") as key_files:
        key_files.write(key)

Code Explanation

Fernet is the library from Cryptography which is being used in this code to generate the key which we will be using as universal key as of now. The generated key is stored in the file universal.key.

Process of Encrypting Data

As shown there is a filename encrypt.py.The purpose of this file is to encrypt the secret message passed as parameter.

Steps

1. Create a file name as encrypt.py.
2. Code the program as shown in Python Code to implement the encryption.
3. Save it.

Python Code

from cryptography.fernet import Fernet

def loadKey():
    key = open("universal.key","rb").read()
    return key

def Encrypt(secret):
    key = loadKey()
    encodeSecret = secret.encode()
    fer  = Fernet(key)
    return fer.encrypt(encodeSecret)

Code Explanation

Fernet is the library from Cryptography which is being used in this code to encrypt the data. We will made a block name loadKey() which will load the universal key stored inside the universal.key. Then we will make an Encrypt() block accepting "secret" as the parameter. For encryption we will first encode the secret message then pollute it with the salt made by Fernet(key)by encrypting with the encoded message.

Process of Decrypting Data

As shown there is a filename decrypt.py.The purpose of this file is to decrypt the encrypted message passed as parameter.

Steps

1. Create a file name as decrypt.py.
2. Code the program as shown in Python Code to implement the decryption.
3. Save it.

Python Code

from cryptography.fernet import Fernet

def loadKey():
    key = open("universal.key","rb").read()
    return key

def Decrypt(encryptSecret):
    key = loadKey()
    fer  = Fernet(key)
    decryptSecret = fer.decrypt(encryptSecret)
    return decryptSecret.decode()

Code Explanation

Fernet is the library from Cryptography which is being used in this code to decrypt the data. We will made a block name loadKey() which will load the universal key stored inside the universal.key. Then we will make an Decrypt() block accepting "encryptSecret" as the parameter. For decryption we will first depollute the secret message from the salt made by Fernet(key)by decrypting with the encrypted message then decode it to view the original secret message.

Data Entry of encrypted data to database.

As shown there is a file name dataentry.py.The purpose of this file is to insert encrypted data into the database mySecret.db having following columns:

• Routine ID: It is the description of routine such as morning routine, breakfast, office hour etc.
• Date: The date when this routine is or was happened.
• Start Time: The time when this routine is starting/started.
• End Time: The time when this routine is ending/ended.
• Status: The Routine is done/pending/in progress/not done etc.

Steps

1. Create a file name as dataentry.py.
2. Code the program as shown in Python Code to implement data entry of encrypted data.
3. Save it.

Python Code

import sqlite3
import encrypt 

def enter(id,dt,st,et,status):
    conn = sqlite3.connect("mySecret.db")
    crsr = conn.cursor()
    sql_command = """INSERT INTO ROUTINE(routineID,date,startTIme,endTime,status)
    VALUES (?,?,?,?,?);"""
    id = encrypt.Encrypt(id)
    dt = encrypt.Encrypt(dt)
    st = encrypt.Encrypt(st)
    et = encrypt.Encrypt(et)
    status = encrypt.Encrypt(status)

    tup = (id,dt,st,et,status)
    crsr.execute(sql_command,tup)
    conn.commit()
    conn.close()  

Code Explanation

sqlite3 is the library we have to import in order to work with database in python. And we are also importing 'encrypt' module made by us.
Every Database handling under sqlite3 follows simple process:

• Connection with database
• Taking the curser of database
• Giving the SQL Query to Execute
• Commit the Execution
• Closing the Database. The enter() block takes 5 parameters as input. And then encrypt each parameter and then insert into the db in the form of tuple as shown in the code.

SQL Query to insert into db

INSERT INTO ROUTINE(routineID,date,startTIme,endTime,status)
    VALUES (?,?,?,?,?);

Printing of Decrypted data

As shown there is a file name dataprinting.py.The purpose of this file is to print decrypted data from the database mySecret.db having following columns:

• Routine ID: It is the description of routine such as morning routine, breakfast, office hour etc.
• Date: The date when this routine is or was happened.
• Start Time: The time when this routine is starting/started.
• End Time: The time when this routine is ending/ended.
• Status: The Routine is done/pending/in progress/not done etc.

Steps

1. Create a file name as dataprinting.py.
2. Code the program as shown in Python Code to implement data printing of decrypted data.
3. Save it.

Python Code

import sqlite3
import decrypt

def printit():
    conn = sqlite3.connect("mySecret.db")
    crsr = conn.cursor()
    crsr.execute("SELECT * FROM ROUTINE")
    rows = crsr.fetchall()
    for row in rows:
        for cell in row:
            print(decrypt.Decrypt(cell))

    conn.commit()
    conn.close()

Code Explanation

sqlite3 is the library we have to import in order to work with database in python. And we are also importing 'decrypt' module made by us.
Every Database handling under sqlite3 follows simple process:

• Connection with database
• Taking the curser of database
• Giving the SQL Query to Execute
• Commit the Execution
• Closing the Database. The printit() fetch all rows(tuple) of database. And then decrypt each cell of each tuple and then print it.

SQL Query to select all elements

SELECT * FROM ROUTINE

The Main Module

Steps

1. Create a file name as main.py.
2. Code the program as shown in Python Code.
3. Save it.

Python Code

from os import path
import createdb
import generateKey
import dataentry
import dataprinting

def main():
    if(path.exists('mySecret.db')==False):
        generateKey.generate()
        createdb.create()
    exit = int(input("enter 0 if you want to exit or 1 to continue entring data"))
    while(exit==1):
        id = input("input routineID")
        dt = input("input date in DD-MM-YYYY")
        st = input("input start time in hh:mm")
        et = input("input end time in hh:mm")
        status = input("not done/pending/inprogress/done")
        dataentry.enter(id,dt,st,et,status)
        exit = int(input("enter 0 if you want to exit or 1 to continue entering data"))
    dataprinting.printit()

main()

Code Explanation

We are importing path from os, self made modules createdb,generateKey,dataentryanddataprinting. The purpose of path is to check whether the database 'mySecret.db' already exists or not. If it is not exist we will create the db and generate one universal key otherwise leave these two steps as shown in the main() block. Now we are asking user to select either 1 "if they want to insert data in db" or 0 "if they do not want to insert data in db". Then in while loop we are taking 5 inputs and entering in database using module dataentry.Once the data is being entered control comes out of while loop and using module dataprinting print the data.

Code Run

Once you run main.py the code it will ask enter 0 if you want to exit or 1 to continue entering data'. Click 1 and enter insert all data as asked. once all rows are finished press 0 to exit out of loop. And all the data will be printed. But in behind the scene some process happened.mySecret.db` and 'univeral.key' are created.

Output
Universal Key
yn1ZrHZ2VBLqq7iZ38pTMvRf9-3pyAVHFlWjADIPO20=
Consider below image to open db

Data inside DB:

routineID	date	startTime	endTime	status
gAAAAABhWDfTHxfYmU4kHl9uSqt-bF0LpSlZZyxyk2cXbyummG0HRfLRH6Ouja8u7yt12Ii-qAHzX_hkohLhg9GKglzEZhT2Hg==	gAAAAABhWDfTh4WTHW0ezh8kpWnr7Ccs5ZvdI12jtbxVRKa4pmKOx-Fm6cZ1NGbV_uTkk9--GG97LEjUSEEPRN-8Cj3RYtWSjQ==	gAAAAABhWDfTeGHg8foPNYIZxchL61UDmORidKvFgvdy_eiBX8vEV-24qk5fTrjinehW51AAs3ZZHWN3R-tvnJdfUpcykvMpsg==	gAAAAABhWDfTNFTmf7Cxgc48IUmrrKakT4-CSi4FYLp57DTzlqy3s9TFuEKXpIAPRa2gNWif_vO2Nc_6_Na1CpmvdrHNRdaTZQ==	gAAAAABhWDfT_hCJYgSVWZjCTy3YqWyhvORmVDi4tS4EZHsHJLe6P3t4ljZRFro21eFKemOnK9SZh3uzl7NOM7c54nDTBEQkSg==
gAAAAABhWDhKooyIOnSHEJWhkj8QUaNw1YPIPzxyYHUE7yoBGWsIDlVy5_s7_SJPnF6yj5kxf_Lykl6SwYpVRMNbmJ9dGU7NkA==	gAAAAABhWDhKF9ATmebjCCNW_dLK74LvNm9l8sBk7PbxPG-70KsHolHNjZ5mFE3aj62XJS1TaGrZTxtMBYUiNuBs1WZwcqzymQ==	gAAAAABhWDhKJgoYW2tihp7tEgKEF1QG5EMHYkZ3nA6I27T_bArxt7V3-PZ1snhlkWN2OSR4ITfJw-0bIQh30Og08PFmaFDedA==	gAAAAABhWDhKgz0fI5cefWYeQknGfQ44vnAZvuSTFZdGPsN_dVAduMsTfkqhrwCe-HaX3Lv47QorlypTQRJnC0dQaGHvvRqXZg==	gAAAAABhWDhKNSG8qx9le0TOcdHIMW8_iJX-OPAxTdQvJCuK6Krr0vABAdiuZGw8hyU1D42Cm8YOWIBkKeURrZli4-G3J7M3sQ==
gAAAAABhWDiG3kN7BLWKwKQo-VESvAw-Wqt8rEHfWUD1mn5sdB8qbQcnpsA0pFMWD3Mp85AWtqTxfEO1rkd62Nc6FMO3K_AWrA==	gAAAAABhWDiGsTxqUZT3r9wWit7UwrYuQ-bcg_19nK1qJXNPo7oN3L2tBTkigDVvAW1fLpfqt-0T5Uu6pw_8UzT-utP50rI6gw==	gAAAAABhWDiG66j46ciB5Z62meatN0cqcJcIRgrR36wfOyQ9WgEx14tETC_hHwOXshywTP9xxDHuBjJe7L4mLul-qhE-oJ5ZiQ==	gAAAAABhWDiGF4Oz_wG2wlwLqLTPNQreDbVZGx96-cCtCE93pduRM5K_Tvgo8Jz5Ef0jnuWqmcaD378X7x8zXMwImzjjJW6VCg==	gAAAAABhWDiGP8WxV-OFxEfhXRGNBr7bmTc-vH1Hlfrd9jAX4wfjV3Y4W8HldHJY0tp1UQ_VyhRdyc08HMiRh4oEOHLn-nkoPg==
gAAAAABhWDjN6O28PEj0_xDYcJWu4hWQO0jcW4labsJ-HM4OfTOQoguXMFlXZpdbOpER4jdAEYfHaU6zK5NEtA5RiCSb-XV5lg==	gAAAAABhWDjNRmeDI-vv3N4PbLedclWNbdjBzVQ935Fws4RsHbgiq2JP92o7oaOQsyY-kwChlzSijk6PcQy1J0uDelXbFjZcEg==	gAAAAABhWDjNBAt6T-s6KoLkL7zRBrM-TS4gjmTtgN6fPJrJl2BtevCHlSoizbafsNHioaBEJv0fmivKeu6vUaCcaMawTW4Fnw==	gAAAAABhWDjNTtJctnIsAr7ltrCbxTn2MNWpDcD32kjfCLuFOUrkQp-sQBWoxoxcTM6C5SbGUiUCrxiYuwTlpFIB2QBChEmN7w==	gAAAAABhWDjNII9CHW1E0AluwHousw7O1vQzVDXhU4QtJyVvq9Y1sAeL6bMO1WY9Rqfuj0K6B1sWoUZOFrHdqv60cEnuR8GVWg==
gAAAAABhWDkMNAwdIZ1cNqzFvdqSbH_NRYYO0jWx4GOfUn29DVg910VH1z_eyztdeVcv4bxgYD3y8ZGTGfkxUQyhZruYICB-FA==	gAAAAABhWDkMNoRf6A3ySmmsWyjAYljqpbQx5SfUtn5J_IpmV0cRi5CJvPMqZD1P7wL8WGWLaa7WshAST35xn5-LXfLaVbGhYA==	gAAAAABhWDkMbPjQ7HX8oWV7jj1ISoH54_7ED3GmrJjuMfjSPf1cmHrRhCYxyM2tsXFFiyEqJoQ8BnQqXt9fiUlfXWAtJq088w==	gAAAAABhWDkMMcEFFoCtHHEAkDOFVMtqHVD6VTRVFV64iWpIgpBYUsy8cjRIECtS8qtCMNhXJM7DW0OjU0LYhHswtlfrDLNIHQ==	gAAAAABhWDkMfuJ9TC81M2uXGx4XioqSnC9s0VcRSk9y6GDlf_jxoyy-ovo1iwg8r8OUz2UyQY7WqhHf4R4N1OG9axIRGkIYmg==
gAAAAABhWDkyXbUR3KEJMjAG7M58zCdRA_b_TECSX9Ux9ZLBdnqgp76b6csZeaL61RHsvgdgNocem6nsF3u52FLVQl0wkYL3dQ==	gAAAAABhWDky51NPI0NSi7w-pyFrGfGSV2Ultb-QOZJSBx70XKTmlpgbJqs-GdmJ-Q4tppl5E66_10mRYoyjWeY8P1JzRwPtyw==	gAAAAABhWDkyyo4wgA_tQAuL4PBIpufjiddpTPDL9VeJT9D4kzk6RjIunNo_AqTwxrXJ-FK5Doamfc1ph2thuVrkxO9_8-HakA==	gAAAAABhWDkyt8KS19BrKYEKVWW3KeRvpJCR6j1k9B-0IxaS-5EYniPpEBPhglxHdDkxyCeZAGqOFdym17X_ab7wE-Xcn__xgA==	gAAAAABhWDkyoEmGhGn6X5Mb0a2KIutYKdl9jqmBTXTyS3vjmDKvHwsl56vCwJ_39IJqGv0KrFz2FX095QUSCNQ-nCS6xZ1lRw==
gAAAAABhWDmUe3eLl4H6YLBc1VEtJShGqSDtLZeFVfwlm2AvGiMqhVI0K_iA4N1q1LEu3GLHK6jLX8j3Djx9qaM2OHfLTv4ImA==	gAAAAABhWDmUhtF-96H5ZAi3Ro9l4bViohqTDaRP2ZtTk3wolHKFdkHMyPkMWpXy1R94pBNHdA6hhjHBDGItVMADLv2ZEmFqDw==	gAAAAABhWDmU9JHb_krh3d-gj9twSgwlKrBfaHHjs-Qv0HB8RaNdZZ0Ztunia59QElWa5Jk9EwPNAg7c5jb_tTltkYkVpjTT9Q==	gAAAAABhWDmU3Vsk99Wnkxo03vQVtbALL3P9HynbAzkJMiXvmX8JG08yu4xMi96YIEELRpbqk7wb1VTExtDHtbyjFQXVDOIb6w==	gAAAAABhWDmU7_Hrs5jm_Y4h7VL0aKz4MuPXktr6XDBLqIVmnWtozvDbOCukZQSgQz5PnNxWPCokb1HXZ4EIIWuahd3AT64ybg==
gAAAAABhWDnGa0YHTkv1VXM5BsYVw68C-VfSyFp9q5p3aU6wyus1fzta-xfNRQF4lI-N5hzvEjShv2sYWrBSfSl2s4b8f4hDIQ==	gAAAAABhWDnGlXHA31rr0Hs7zU_daF51zDtNSKEdtqCjrbhH9P1_U4n9AXn_IDKpRrxPn_r6yNTPUE1CVClOPqRCosYkIKT6FQ==	gAAAAABhWDnGeg7sWOwNrAHVkxZlPkyqJBcc-HI1QqU01mEWHv-LWsYo-tKUUNldMrz7yeAZTmtQB0RMsN-pdERZmrEd2ylmBg==	gAAAAABhWDnGnnqaJ53FvFpv4rkkoMpTzm398pGEHad6cMde9VZ4RY1xqeJf4sGfMBYSbQHW849DBSlQVhKn8V9samDXisEuOQ==	gAAAAABhWDnGm-l6Bn_IlD_SCJhHvR_FaD4asZaEp1oJ_pAXPIB1B74hpglV7Ajl-ARaQlX0kcaJjuy3w9tXKNlNFvoY5nWb4Q==

Printing Output:

Inference

Hence, the blog concludes with the demonstration of understanding of Data base encryption using Python and SQLite.

By Akash Srivastava

Do Follow me on
[LinkedIn][GitHub][Twitter]

Comments

[VICTOR ISAAC]

Nice work dev, can this be used with Django Web development too?