25
Do you wanna keep your embedded database encrypted?
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.
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.
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
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

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.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.
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: createdb.py
.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()
sqlite3
is the library we have to import in order to work with database in python.Every Database handling under sqlite3 follows simple process:
CREATE TABLE ROUTINE (
routineID VARCHAR(10000) PRIMARY KEY,
date DATE,
startTime VARCHAR(5),
endTime VARCHAR(5),
status VARCHAR(10));
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.generateKey.py
.from cryptography.fernet import Fernet
def generate():
key = Fernet.generate_key()
with open("universal.key","wb") as key_files:
key_files.write(key)
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
.As shown there is a filename
encrypt.py
.The purpose of this file is to encrypt the secret message passed as parameter.encrypt.py
.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)
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.As shown there is a filename
decrypt.py
.The purpose of this file is to decrypt the encrypted message passed as parameter.decrypt.py
.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()
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.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: dataentry.py
.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()
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:
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.INSERT INTO ROUTINE(routineID,date,startTIme,endTime,status)
VALUES (?,?,?,?,?);
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: dataprinting.py
.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()
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:
printit()
fetch all rows(tuple) of database. And then decrypt each cell of each tuple and then print it. SELECT * FROM ROUTINE
main.py
.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()
We are importing
path from os
, self made modules createdb
,generateKey
,dataentry
anddataprinting
. 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.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== |
Hence, the blog concludes with the demonstration of understanding of Data base encryption using Python and SQLite.
By Akash Srivastava
25