source: test/services/jregistrate/create-db.py @ 22

#!/usr/bin/env python3

import sys
import random
import sqlite3
import base64

from Crypto.Cipher import AES
from Crypto import Random


zips = ["75631","17040","23604","16844","81527","68731","74966","61956","39330","45832","77305","62449","50627","03585","12193","08858","61326","80482","44843","18237","71238","60472","66535","89432","50669","40068","94011","85543","78014","13667","62201","61760","40057","86023","21850","29032","64150","29039","97720","32112","58763","43824","12121","32628","61046","56358","39735","49916","70778","82602","48755",
        "03455","26711","50450","70372","24315","44882","75423","46555","62952","42210","93922","85140","63869","85631","88004","79311","50276","16143","44428","75083","51039","98093","49657","45882","45144","25524","32203","97918","23883","15445","16421","88045","76577","32189","38052","70515","37373","97327","45232","12072","76936","54634","27331","49284","90406","57028","62060","51023","16651","03905","54629",
        "31301","36091","75453","80442","51239","70653","35765","33128","32343","24870","26205","44865","12780","83254","46994","67067","58368","92256","95038","56633","54771","72846","32567","98266","72858","70562","72016","50665","45652","95638","60476","43569","61723","80103","84713","99357","77347","62664","35034","59917","53078","33838","92623","50228","74764","38449","37342","77050","52336","85613","17082",
        "99302","32455","13471","35549","52322","47229","85636","35403","37402","68037","73149","29055","60424","91008","71429","45167","08242","08752","04347","63435","24527","73028","19714","38857","65652","53933","65689","40019","14525","49056","93389","42406","45771","31645","66524","17771","47512","64844","30582","30833","63037","56062","08243","76651","43003","13612","06018","47633","28386","74653","04220",
        "97458","37687","04742","46161","57325","77516","95024","16373","04419","55940","93613","49665","04930","28654","89501","72937","70341","74451","03598","76360","27959","82331","16049","71943","12305","05655","30520","39041","54443","35776","91413","32331","71465","54247","28756","53114","54722","61533","65231","56172","24381","42217","10168","22448","14770","46402","96823","61535","62075","43060","24963",
        "96765","48641","96064","35983","98844","70085","21651","32411","33509","49621","13746","13606","54725","51445","26764","56360","75963","54421","91109","29429","16346","90742","29945","43516","37051","54028","75833","44412","56464","98687","37870","44504","61817","84021","48022","53952","96143","89019","03813","65039","36861","34681","28349","59759","61732","64424","67107","93234","44624","12979","27209",
        "78599","49272","44840","61602","54245","14471","75663","73632","77903","81432","17938","89045","85064","38916","48759","55049","48819","37014","95002","13685","74547","17304","81639","28512","38313","04456","66070","05048","78056","05458","79758","04256","22482","29918","37219","25248","63957","64658","39328","05354","55971","29142","13825","53011","71268","63347","28088","77363","04217","99824","70039",
        "55088","37150","81326","18834","29384","72024","32542","95452","51109","87529","44643","99328","54773","16925","35980","03854","30222","62288","61734","26570","73438","38581","74962","96156","36441","65052","58278","29436","62447","48831","41514","75792","55031","29581","71327","28742","66536","05068","17087","55604","72422","10505","43358","96705","15014","04048","28341","63736","95457","15110","54614",
        "12183","13110","48722","61434","62806","13787","03576","78381","54466","54499","79760","63650","48159","53934","14807","62257","05262","84627","72126","59904","54473","01034","32195","95056","28741","40109","75939","84056","65074","36518","55943","54009","42726","61085","68873","95684","78417","72634","98227","78593","56444","46044","40045","99833","27871","06235","35203","13308","29048","52340","35616",
        "66757","85936","55705","10172","96725","50170","73572","30046","91359","72521","67661","29435","52208","54423","12068","73651","60511","97455","95696","89049","31606","66414","17048","64856","61238","42749","46543","55767","67835","45786","13329","45723","23974","73542","61277","64454","86003","63825","16262","99576","50171","74538","65656","84745","56474","59716","51546","58802","18822","23417","28729",
        "57601","26719","78118","24656","17744","35541","79027","70112","28338","49920","44864","54941","01351","03447","53570","37873","57038","35221","41749","59313","45120","44085","73460","31006","46910","59474","32421","97444","34230","53502","15233","61736","13044","97017","87327","14814","30079","54646","03462","02163","66846","86504","83628","21625","88355","38330","78560","56097","95617","28125","95231",
        "41537","74058","21777","44430","50169","98648","37658","51040","65360","61250","06254","42378","67544","74730","71964","60690","18250","98932","04927","84317","82431","59604","66763","30738","47581","95021","85122","35178","52175","71435","31055","36351","05033","32087","04037","02584","77430","57580","32686","36558","32058","35553","95441","47598","17562","40437","03602","29439","24088","20675","36856",
        "16052","18053","47336","35234","04849","87533","64020","13602","98339","74647","45814","06763","12846","59201","46770","49870","35188","15697","62694","43107","47613","44626","65079","36866","65542","48441","36445","53507","38619","66538","35586","93501","55402","16121","47283","95502","16061","59072","51249","61561","49253","32460","78152","65483","48884","16134","70783","38001","22620","57709","49650",
        "75569","97467","39840","13668","77871","32324","73121","31543","78010","01068","61540","76704","45831","42765","03781","30511","93011","58012","76457","55080","04428","64054","49072","37352","46130","93430","35546","55390","56373","17240","18917","31773","43570","98857","56277","43314","35131","54121","21631","45225","65785","86321","45162","95249","67056","49096","52645","17321","46746","56096","70656",
        "24104","92023","54837","45064","70381","89803","22473","68826","53035","41522","67335","55027","48850","02898","26601","16038","37134","17859","08733","44814","44822","14812","56473","42450","47944","08832","85143","64139","97913","83676","14139","22656","97394","50240","15728","96099","35563","04355","32619","16668","12581","67063","56547","24874","87504","16156","02557","17502","72342","35442","66736",
        "35033","46929","20762","05735","49807","63445","31063","12301","02725","07311","01542","08045","84511","04619","71001","26408","29128","73737","20688","22567","43620","10527","44445","53233","20689","38683","04461","96088","97844","49051","36726","41301","19022","16442","53964","73102","57071","62550","63627","13478","50525","85939","15046","45742","98228","62069","99122","34484","14772","50225","30567",
        "83252","82939","56484","15557","49637","15943","77702","55454","96754","69201","42629","35462","46051","54005","98237","66523","53811","93675","61842","29353","13439","47562","92241","44003","83313","39069","20117","91906","43154","52756","65548","24136","49254","62204","15015","93406","53932","50533","75266","45207","27207","40370","06039","15845","17563","46501","15944","73093","49237","11548","33786",
        "81419","53002","66054","75954","38105","87901","36477","03440","89415","61024","75567","55054","95633","33109","57532","28692","96820","91380","88230","31806","86333","35006","44402","12307","23944","78357","44452","99522","47523","68405","47920","54768","98206","54124","55354","18446","12921","47383","16254","55353","61529","75410","75969","59752","12138","58704","06757","01840","17302","80759","74965",
        "60165","94514","32502","73772","01515","49878","13060","28676","45130","72360","90009","72046","89419","96752","43310","83869","70067","14033","45054","77387","45325","28544","05464","14042","46504","65686","89040","62995","46742","54014","50212","49283","39066","93453","53170","75683","95055","41063","32744","57025","89193","21822","67654","99652","55307","27326","98601","80133","68767","79088","15461",
        "31831","35171","81428","30808","14840","45732","65248","17834","97708","45858","33471","35118","14737","47874","77512","60146","49256","24970","38828","37709","23947","26143","76233","03750","79329","29335","75414","95223","01474","88352","58054","42044","59644","43777","25827","71254","55707","49111","02462","35959","39327","71038","32764","80644","74030","96748","92036","02630","53015","43604","52339",
        "04287","53049","81502","44451","15531","89822","71485","15846","16424","24554","88211","28433","78164","31554","36278","96093","92038","35091","43718","39038","48730","44401","31803","45341","37049","75572","12117","17980","70776","81328","55946","13164","13417","38260","16858","48370","34981","31553","04756","74441","49128","83655","28351","99523","37880","01537","16693","14062","43324","19507","67017",
        "52738","78119","87415","41311","80535","71350","48637","78226","27581","80758","22534","76066","49946","46147","61531","23047","30139","55768","99587","61410","26038","46390","44672","15774","85925","44837","38469","39439","46553","65604","04294","01083","12134","27544","92549","31089","88345","71103","78025","54414","96122","30557","45612","66040","47579","74365","37656","51246","21813","99509","70426",
        "14821","72131","04015","37711","22841","52653","93523","66021","50261","50638","63863","70542","55741","95379","98291","27937","69357","15050","47334","06103","62067","42717","41095","49868","99009","28701","30055","93701","54658","20198","01050","92393","28640","31807","07606","54891","14303","53515","17407","55114","29164","84312","07711","06247","12865","01096","03902","36603","76252","43001","20662",
        "46068","03237","79356","53809","35648","72454","44288","29112","05679","50063","13637","79068","74902","01337","24277","57754","21160","27509","40008","32066","27027","28578","49101","96013","49779","61858","49781","58318","35971","55395","13480","26059","29148","14733","14170","49249","72833","15033","45159","78377","98570","78071","52206","92356","11969","10986","29075","71968","37665","12944","54139",
        "29591","13317","13476","13491","50674","73742","41059","57262","80643","29372","67013","28478","71929","17365","81067","62677","98557","30259","92202","59068","68959","22624","46510","14111","71023","49094","99692","49064","49615","48763","77485","61542","03258","93258","29304","62501","04357","18076","35592","51510","96721","62353","14738","55718","27830","36756","23801","37722","35582","37714","57004",
        "71082","38865","18322","28901","58854","43844","61020","31771","91912","75426","64053","40337","45314","77522","17889","37690","78333","06510","65565","21536","92263","02827","25951","75226","98392","05250","32321","56024","32535","08202","14837","29935","43416","32431","93031","49448","61911","16947","70441","28680","80807","27880","40516","14712","77968","14823","16743","43517","46536","47327","27379",
        "86324","30171","75756","21919","97818","73098","77434","97064","35581","62016","68632","01038","54228","30629","97042","72687","19310","98840","29682","75563","18332","42256","35580","32621","01436","06090","08020","76462","42544","45813","11765","35014","87116","29684","04864","71433","37853","37826","97111","46573","32976","32202","65653","15314","46792","79081","24577","49862","63834","21757","59722",
        "93532","73007","75124","36362","44307","17045","18077","27248","15059","43318","65793","24124","16232","36025","53178","39641","17565","32056","53956","74131","62341","75966","43793","12758","75473","96755","01521","03823","93267","57030","92589","20763","94020","85623","37096","47459","48744","17724","17841","04862","38855","29831","93437","85327","55397","12106","96763","47438","74937","92619","76374",
        "43943","93539","08102","10501","06069","63353","32617","49344","14710","50072","54853","04259","35470","75424","04008","71654","24087","98240","54945","30543","17023","92363","95222","92548","02638","93240","72744","59034","30257","35072","75117","60088","54440","61284","78160","76689","29944","80117","45784","52033","16115","76821","81425","60145","36260","67144","02881","02642","66534","42327","16051",
        "93270","03869","22427","23890","73054","70377","12056","26250","36576","53156","26385","68347","12809","65753","10973","33890","62640","11739","08026","70589","48450","05763","16157","29544","25130","93451","50216","63456","64011","12850","21817","36691","54949","29704","68372","23062","52214","29374","98855","45653","24279","36010","45843","77962","16749","33834","47161","81602","74079","32735","63072",
        "25670","03584","64720","93602","47164","62281","12577","56621","12487","30457","06782","06377","38066","74039","15679","30178","23885","06330","03581","85532","17051","78069","35961","24260","34737","54947","64477","44666","61054","71417","98524","18458","59427","32668","46030","99752","29692","89702","49749","13803","97362","61019","41004","43723","27283","30147","43910","21849","12075","33975","17856",
        "88231","49060","42567","97019","71486","35578","63361","48027","21750","79836","97101","48757","63873","03224","72132","62216","53036","95461","53191","96779","30521","46788","81413","99001","78636","40379","08735","04957","54151","49234","44609","61753","13354","76059","53598","49277","29058","49454","19043","46349","20842","96717","49706","99802","50536"]

db = sqlite3.connect('jregistrate.db')
db.execute("""CREATE TABLE members (member_id TEXT PRIMARY KEY, enc_last_four TEXT, zip TEXT)""")
db.execute("""CREATE TABLE users (user_id INTEGER PRIMARY KEY AUTOINCREMENT, member_id REFERENCES members(member_id), username TEXT, password TEXT)""")


key = b'Sixteen byte key'

def pkcs7PadBuffer(buf, block_size):
    '''
    Pads the end of a buffer using PKCS#5/PKCS#7 padding.
    '''
    padding = block_size - (len(buf) % block_size)
    return buf + pkcs7Pad(padding)


def pkcs7Pad(length):
    return bytes([length]*length)


def encrypt(plaintext):
    iv = Random.new().read(AES.block_size)
    cipher = AES.new(key, AES.MODE_CBC, iv)
    padded = pkcs7PadBuffer(plaintext.encode('utf-8'), AES.block_size)
    return base64.b64encode(iv+cipher.encrypt(padded))


# member_id format: ####-####
# 1/10 of all member ids with a given prefix are allocated
last_id=None
prefix="0012"
for id in random.sample(range(0,10000), 1000):
    zip = random.sample(zips, 1)[0]
    last_four = "%d"%random.randint(0,9999)
    db.execute("""INSERT INTO members VALUES (?, ?, ?)""", ("%s-%04d"%(prefix,id), encrypt(last_four), zip))
    last_id = id
db.commit()

print("sample valid: %s-%d,%04s" % (prefix,id,last_four))
db.execute("""INSERT INTO users (member_id,username,password) VALUES (?, ?, ?)""", (last_id, "bob", "password1"))
db.commit()