Sample python script to create a sqlite data store,
#! /usr/bin/env/python3
#
# Author: Paul Nelson - JMP Statistical Discovery, From SAS
# Date: 9 Mar 2020
#
# Description: This sample shows the python code using sqlite to create a data data store
# for saving passwords, credentails... While Python has hash functions, the standard library
# does not include ciphers. For purposes of this sample I used lzma compression to 'encode' and
# decompression to 'decrypt' For use from JSL, I would turn this into a python module and then
# import 'my_dataStore_api' to cut down code in the JSL string.
#
# sample python program using sqlite to create a secure store for passwords and
# key, value data storage.
#
# While Python contains hash functions, does not include encryption primitives in the standard library.
# an example using PyCrypto can be found
# https://stackoverflow.com/questions/12524994/encrypt-decrypt-using-pycrypto-aes-265
# however pycrypto has no been updated since 2014... https://github.com/dlitz/pycrypto
#
# pyca/cryptography - https://cryptography.io/ https://github.com/pyca/cryptography
# seems to be currently maintained.
#
# Don't create your own encryption! The above packages or, openssl, or many other providers are
# better than writing your own.
#
# It is an exercise left to the reader to replace the code using lzma compression with real encrypt
# and decrypt code. :)
#
#
import sqlite3
import hashlib
import os
from pathlib import Path
import lzma
# Salt should be random quantity, get from OS entropy source
salt = os.urandom(16);
mypasswd = b'JMP in a dried up lake'
dk = hashlib.pbkdf2_hmac('sha256', mypasswd, salt, 1000)
# dk.hex() will be the actual hashed password saved to db
# but the binary dk, will be what we use as the encryption key
# dkhex = dk.hex()
# binDk = bytes.fromhex(dkhex)
#
# Path to my credential store , my home directory + AppData
data_path = str(Path.home()) + '/AppData/'
filename = 'dataStore'
# Create directory if it doesn't exist
os.makedirs(data_path, exist_ok=True)
# will create database if it doesn't exist
db = sqlite3.connect(data_path + filename + '.db')
# my username
me = 'jmpuser'
# create our password table storing hashed passwords
db.execute('CREATE TABLE IF NOT EXISTS passwd (uid INTEGER PRIMARY KEY, name TEXT, salt TEXT, key TEXT)')
# should check to see if user is in database and only insert if not found
# exercise left to the reader
db.execute("INSERT INTO passwd (name, salt, key) VALUES( ?,?,? ) ", (me, salt.hex(), dk.hex()) )
db.commit()
print('******* passwd database *******')
for row in db.execute('SELECT * from passwd'):
print(row)
print('*******************************')
# create our credentials table which will store the name of the credential, and its value
db.execute('CREATE TABLE IF NOT EXISTS credentials (id INTEGER PRIMARY KEY, uid INTEGER, key TEXT, value TEXT)')
# first we need to have validated that user gave propper password, and we authenticated against passwd
# table assuming user is authenticated
# should check to see if user is in database and only insert if not found, potentially update if found
# exercise left to the reader.
# my user id in this scheme
id = 0
#The value data I'm going to store into the database
myson1 = """
{
user: paul
magic_key: JMP Rocks!
}"""
myson2 = """
{
user: paul
magic_key: supercalifragilisticexpialidocious
}"""
# 'encrypt' my data
lvalue = lzma.compress( myson2.encode() )
lvalue2 = lzma.compress( myson1.encode() )
# you could encrypt the 'key' as well as the value if you really want security
db.execute("INSERT INTO credentials (uid, key, value) VALUES( ?,?,? ) ", (id, 'JMPCommunity', lvalue.hex()) )
db.execute("INSERT INTO credentials (uid, key, value) VALUES( ?,?,? ) ", (id, 'WDW', lvalue2.hex()) )
db.commit()
print('******* credentials database *******')
for row in db.execute('SELECT * from credentials WHERE uid=?', (id,) ):
print(row)
value = bytes.fromhex(row[3])
print(lzma.decompress(value).decode())
print('************************************')
db.close()
