Do you wanna keep your embedded database encrypted?
Tags: python, sqlite, hacktoberfest, security
Published: 2021-10-04 02:58:41

#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](https://docs.microsoft.com/en-us/windows/win32/seccrypto/data-encryption-and-decryption)

### Flow Diagram
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9tgdxclbonzxzo4no0u8.png) 
 
## Software Setup
1. Tool: VS-Code: [Download](https://code.visualstudio.com/download)
2. Language: Python [Download](https://www.python.org/downloads/), SQLite [Download](https://www.sqlite.org/download.html)
3. Libraries: Cryptography Command: `pip install cryptography`-[Read Here](https://pypi.org/project/cryptography/),
Fernet [Read Here](https://cryptography.io/en/latest/fernet/)


## 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
![filestructure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9r1bf4p9bksm8k9gwre7.JPG)

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`,`dataentry`and`dataprinting`. 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**
![opendb](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pxhoygu41jqxi059lq52.JPG)
**Data inside DB:**
<table><tr><th>routineID</th><th>date</th><th>startTime</th><th>endTime</th><th>status</th><tr><tr><td>gAAAAABhWDfTHxfYmU4kHl9uSqt-bF0LpSlZZyxyk2cXbyummG0HRfLRH6Ouja8u7yt12Ii-qAHzX_hkohLhg9GKglzEZhT2Hg==</td><td>gAAAAABhWDfTh4WTHW0ezh8kpWnr7Ccs5ZvdI12jtbxVRKa4pmKOx-Fm6cZ1NGbV_uTkk9--GG97LEjUSEEPRN-8Cj3RYtWSjQ==</td><td>gAAAAABhWDfTeGHg8foPNYIZxchL61UDmORidKvFgvdy_eiBX8vEV-24qk5fTrjinehW51AAs3ZZHWN3R-tvnJdfUpcykvMpsg==</td><td>gAAAAABhWDfTNFTmf7Cxgc48IUmrrKakT4-CSi4FYLp57DTzlqy3s9TFuEKXpIAPRa2gNWif_vO2Nc_6_Na1CpmvdrHNRdaTZQ==</td><td>gAAAAABhWDfT_hCJYgSVWZjCTy3YqWyhvORmVDi4tS4EZHsHJLe6P3t4ljZRFro21eFKemOnK9SZh3uzl7NOM7c54nDTBEQkSg==</td></tr><tr><td>gAAAAABhWDhKooyIOnSHEJWhkj8QUaNw1YPIPzxyYHUE7yoBGWsIDlVy5_s7_SJPnF6yj5kxf_Lykl6SwYpVRMNbmJ9dGU7NkA==</td><td>gAAAAABhWDhKF9ATmebjCCNW_dLK74LvNm9l8sBk7PbxPG-70KsHolHNjZ5mFE3aj62XJS1TaGrZTxtMBYUiNuBs1WZwcqzymQ==</td><td>gAAAAABhWDhKJgoYW2tihp7tEgKEF1QG5EMHYkZ3nA6I27T_bArxt7V3-PZ1snhlkWN2OSR4ITfJw-0bIQh30Og08PFmaFDedA==</td><td>gAAAAABhWDhKgz0fI5cefWYeQknGfQ44vnAZvuSTFZdGPsN_dVAduMsTfkqhrwCe-HaX3Lv47QorlypTQRJnC0dQaGHvvRqXZg==</td><td>gAAAAABhWDhKNSG8qx9le0TOcdHIMW8_iJX-OPAxTdQvJCuK6Krr0vABAdiuZGw8hyU1D42Cm8YOWIBkKeURrZli4-G3J7M3sQ==</td></tr><tr><td>gAAAAABhWDiG3kN7BLWKwKQo-VESvAw-Wqt8rEHfWUD1mn5sdB8qbQcnpsA0pFMWD3Mp85AWtqTxfEO1rkd62Nc6FMO3K_AWrA==</td><td>gAAAAABhWDiGsTxqUZT3r9wWit7UwrYuQ-bcg_19nK1qJXNPo7oN3L2tBTkigDVvAW1fLpfqt-0T5Uu6pw_8UzT-utP50rI6gw==</td><td>gAAAAABhWDiG66j46ciB5Z62meatN0cqcJcIRgrR36wfOyQ9WgEx14tETC_hHwOXshywTP9xxDHuBjJe7L4mLul-qhE-oJ5ZiQ==</td><td>gAAAAABhWDiGF4Oz_wG2wlwLqLTPNQreDbVZGx96-cCtCE93pduRM5K_Tvgo8Jz5Ef0jnuWqmcaD378X7x8zXMwImzjjJW6VCg==</td><td>gAAAAABhWDiGP8WxV-OFxEfhXRGNBr7bmTc-vH1Hlfrd9jAX4wfjV3Y4W8HldHJY0tp1UQ_VyhRdyc08HMiRh4oEOHLn-nkoPg==</td></tr><tr><td>gAAAAABhWDjN6O28PEj0_xDYcJWu4hWQO0jcW4labsJ-HM4OfTOQoguXMFlXZpdbOpER4jdAEYfHaU6zK5NEtA5RiCSb-XV5lg==</td><td>gAAAAABhWDjNRmeDI-vv3N4PbLedclWNbdjBzVQ935Fws4RsHbgiq2JP92o7oaOQsyY-kwChlzSijk6PcQy1J0uDelXbFjZcEg==</td><td>gAAAAABhWDjNBAt6T-s6KoLkL7zRBrM-TS4gjmTtgN6fPJrJl2BtevCHlSoizbafsNHioaBEJv0fmivKeu6vUaCcaMawTW4Fnw==</td><td>gAAAAABhWDjNTtJctnIsAr7ltrCbxTn2MNWpDcD32kjfCLuFOUrkQp-sQBWoxoxcTM6C5SbGUiUCrxiYuwTlpFIB2QBChEmN7w==</td><td>gAAAAABhWDjNII9CHW1E0AluwHousw7O1vQzVDXhU4QtJyVvq9Y1sAeL6bMO1WY9Rqfuj0K6B1sWoUZOFrHdqv60cEnuR8GVWg==</td></tr><tr><td>gAAAAABhWDkMNAwdIZ1cNqzFvdqSbH_NRYYO0jWx4GOfUn29DVg910VH1z_eyztdeVcv4bxgYD3y8ZGTGfkxUQyhZruYICB-FA==</td><td>gAAAAABhWDkMNoRf6A3ySmmsWyjAYljqpbQx5SfUtn5J_IpmV0cRi5CJvPMqZD1P7wL8WGWLaa7WshAST35xn5-LXfLaVbGhYA==</td><td>gAAAAABhWDkMbPjQ7HX8oWV7jj1ISoH54_7ED3GmrJjuMfjSPf1cmHrRhCYxyM2tsXFFiyEqJoQ8BnQqXt9fiUlfXWAtJq088w==</td><td>gAAAAABhWDkMMcEFFoCtHHEAkDOFVMtqHVD6VTRVFV64iWpIgpBYUsy8cjRIECtS8qtCMNhXJM7DW0OjU0LYhHswtlfrDLNIHQ==</td><td>gAAAAABhWDkMfuJ9TC81M2uXGx4XioqSnC9s0VcRSk9y6GDlf_jxoyy-ovo1iwg8r8OUz2UyQY7WqhHf4R4N1OG9axIRGkIYmg==</td></tr><tr><td>gAAAAABhWDkyXbUR3KEJMjAG7M58zCdRA_b_TECSX9Ux9ZLBdnqgp76b6csZeaL61RHsvgdgNocem6nsF3u52FLVQl0wkYL3dQ==</td><td>gAAAAABhWDky51NPI0NSi7w-pyFrGfGSV2Ultb-QOZJSBx70XKTmlpgbJqs-GdmJ-Q4tppl5E66_10mRYoyjWeY8P1JzRwPtyw==</td><td>gAAAAABhWDkyyo4wgA_tQAuL4PBIpufjiddpTPDL9VeJT9D4kzk6RjIunNo_AqTwxrXJ-FK5Doamfc1ph2thuVrkxO9_8-HakA==</td><td>gAAAAABhWDkyt8KS19BrKYEKVWW3KeRvpJCR6j1k9B-0IxaS-5EYniPpEBPhglxHdDkxyCeZAGqOFdym17X_ab7wE-Xcn__xgA==</td><td>gAAAAABhWDkyoEmGhGn6X5Mb0a2KIutYKdl9jqmBTXTyS3vjmDKvHwsl56vCwJ_39IJqGv0KrFz2FX095QUSCNQ-nCS6xZ1lRw==</td></tr><tr><td>gAAAAABhWDmUe3eLl4H6YLBc1VEtJShGqSDtLZeFVfwlm2AvGiMqhVI0K_iA4N1q1LEu3GLHK6jLX8j3Djx9qaM2OHfLTv4ImA==</td><td>gAAAAABhWDmUhtF-96H5ZAi3Ro9l4bViohqTDaRP2ZtTk3wolHKFdkHMyPkMWpXy1R94pBNHdA6hhjHBDGItVMADLv2ZEmFqDw==</td><td>gAAAAABhWDmU9JHb_krh3d-gj9twSgwlKrBfaHHjs-Qv0HB8RaNdZZ0Ztunia59QElWa5Jk9EwPNAg7c5jb_tTltkYkVpjTT9Q==</td><td>gAAAAABhWDmU3Vsk99Wnkxo03vQVtbALL3P9HynbAzkJMiXvmX8JG08yu4xMi96YIEELRpbqk7wb1VTExtDHtbyjFQXVDOIb6w==</td><td>gAAAAABhWDmU7_Hrs5jm_Y4h7VL0aKz4MuPXktr6XDBLqIVmnWtozvDbOCukZQSgQz5PnNxWPCokb1HXZ4EIIWuahd3AT64ybg==</td></tr><tr><td>gAAAAABhWDnGa0YHTkv1VXM5BsYVw68C-VfSyFp9q5p3aU6wyus1fzta-xfNRQF4lI-N5hzvEjShv2sYWrBSfSl2s4b8f4hDIQ==</td><td>gAAAAABhWDnGlXHA31rr0Hs7zU_daF51zDtNSKEdtqCjrbhH9P1_U4n9AXn_IDKpRrxPn_r6yNTPUE1CVClOPqRCosYkIKT6FQ==</td><td>gAAAAABhWDnGeg7sWOwNrAHVkxZlPkyqJBcc-HI1QqU01mEWHv-LWsYo-tKUUNldMrz7yeAZTmtQB0RMsN-pdERZmrEd2ylmBg==</td><td>gAAAAABhWDnGnnqaJ53FvFpv4rkkoMpTzm398pGEHad6cMde9VZ4RY1xqeJf4sGfMBYSbQHW849DBSlQVhKn8V9samDXisEuOQ==</td><td>gAAAAABhWDnGm-l6Bn_IlD_SCJhHvR_FaD4asZaEp1oJ_pAXPIB1B74hpglV7Ajl-ARaQlX0kcaJjuy3w9tXKNlNFvoY5nWb4Q==</td></tr></table>
**Printing Output:**
![print](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/492g6u48x9yadhzwomsh.JPG)

##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]](https://www.linkedin.com/in/akash-s-233ab3160/)[[GitHub]](https://github.com/AkashSrivastava1721)[[Twitter]](https://twitter.com/AkashSr1234)