A SQLite thread safe password store

Prompted by one of the reviewers of my upcoming book I decided I needed a simple, thread safe, and reasonably secure password store backed by SQLite.

The design criteria were straight forward and based in part on Storing passwords - done right! and the practical recommendations onPythonSecurity.org:

• based on SQLite
• allow for a reasonable amount of threads (its intended use is within a CherryPy application)
• able to use a salt with a configurable number of random bits
• able to apply key stretching with a configurable number of iterations
• use any secure hash algorithm from Python's hashlib module

A few simple test show that the recommended defaults, i.e. 64 bits of randomness in the salt and a 1000 iterations on the hash will allow for a password in check in well under a second even on my humble netbook with an atom processor. Obviously the hashing algorithms in Python's underlying OpenSSL library are very efficient. But we do have to choose some default that strikes a reasonable balance between providing a single user that tries to log in a good response and slowing down a brute force attack. For now we stick with 1000 iterations as the default but feel free to use 10000 or even more.

Example

view plainprint?

1. from dbpassword import dbpassword  
2.   
3. dbpw = dbpassword('/var/password.db')  
4.   
5. # later, from any thread  
6.   
7. dbpw.update(user,plaintextpassword) # update or set a new password  
8.   
9.   
10. if dbpw.check(user,plaintextpassword) :  
11.      ... do stuff ...  
12. else:  
13.      ... warn off user ...  

from dbpassword import dbpassword

dbpw = dbpassword('/var/password.db')

# later, from any thread

dbpw.update(user,plaintextpassword) # update or set a new password


if dbpw.check(user,plaintextpassword) :
     ... do stuff ...
else:
     ... warn off user ...

The dbpassword module

Warning! I am not a cryptographer so I cannot guarantee the following code is safe enough for your needs.

view plainprint?

1. ''''' 
2.     dbpassword.py Copyright 2011, Michel J. Anders 
3.  
4.     This program is free software: you can redistribute it 
5.     and/or modify it under the terms of the GNU General Public 
6.     License as published by the Free Software Foundation, 
7.     either version 3 of the License, or (at your option) any 
8.     later version. 
9.  
10.     This program is distributed in the hope that it will be  
11.     useful, but WITHOUT ANY WARRANTY; without even the implied 
12.     warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR 
13.     PURPOSE. See the GNU General Public License for more 
14.     details. 
15.  
16.     You should have received a copy of the GNU General Public 
17.     License along with this program.  If not, see  
18.     <http: www.gnu.org="" licenses="">. 
19. '''  
20. import sqlite3  
21. import hashlib  
22. from random import SystemRandom as sr  
23. import threading  
24.   
25. class dbpassword:  
26.  
27.     @staticmethod  
28.     def hashpassword(name,salt,plaintextpassword,n=10):  
29.         if n<1 : raise ValueError("n < 1")  
30.         d = hashlib.new(name,(salt+plaintextpassword).encode()).digest()  
31.         while n:  
32.             n -= 1  
33.             d = hashlib.new(name,d).digest()  
34.         return hashlib.new(name,d).hexdigest()  
35.  
36.     @staticmethod  
37.     def getsalt(randombits=64):  
38.         if randombits<16 : raise ValueError("randombits < 16")  
39.         return "%016x"%sr().getrandbits(randombits)  
40.   
41.     def __connect(self):  
42.         if not hasattr(self.local,'con') or self.local.con is None:  
43.             self.local.con = sqlite3.connect(self.db)  
44.             self.local.con.create_function('crypt',2,  
45.                 lambda s,p:dbpassword.hashpassword(  
46.                   self.secure_hash,s,p,self.iterations))  
47.         return self.local.con  
48.       
49.     def __init__(self,db,  
50.                    secure_hash='sha256',iterations=1000,saltbits=64):  
51.         self.db = db  
52.         self.local = threading.local()  
53.         self.secure_hash = secure_hash  
54.         self.iterations = iterations  
55.         self.saltbits = 64  
56.         with self.__connect() as con:  
57.             cursor=con.cursor()  
58.             sql='create table if not exists pwdb (user unique, salt, password)'  
59.             cursor.execute(sql)      
60.       
61.     def update(self,user,plaintextpassword):  
62.         with self.__connect() as con:  
63.             cursor=con.cursor()  
64.             sql1='insert or replace into pwdb (user,salt) values(?,?)'  
65.             sql2='update pwdb set password=? where user = ?'  
66.             salt=dbpassword.getsalt(self.saltbits)  
67.             cursor.execute(sql1,(user,salt))  
68.             cursor.execute(sql2,(dbpassword.hashpassword(  
69.                     self.secure_hash,salt,plaintextpassword,  
70.                     self.iterations),user))  
71.   
72.     def check(self,user,plaintextpassword):  
73.         cursor=self.__connect().cursor()  
74.         sql='select user from pwdb where user = ? and crypt(salt,?) = password'  
75.         cursor.execute(sql,(user,plaintextpassword))  
76.         found=list(cursor) # can only create a list form this iterator once!  
77.         return len(found)==1  
78. </http:>  

'''
    dbpassword.py Copyright 2011, Michel J. Anders

    This program is free software: you can redistribute it
    and/or modify it under the terms of the GNU General Public
    License as published by the Free Software Foundation,
    either version 3 of the License, or (at your option) any
    later version.

    This program is distributed in the hope that it will be 
    useful, but WITHOUT ANY WARRANTY; without even the implied
    warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
    PURPOSE. See the GNU General Public License for more
    details.

    You should have received a copy of the GNU General Public
    License along with this program.  If not, see 
    .
'''
import sqlite3
import hashlib
from random import SystemRandom as sr
import threading

class dbpassword:

    @staticmethod
    def hashpassword(name,salt,plaintextpassword,n=10):
        if n<1 : raise ValueError("n < 1")
        d = hashlib.new(name,(salt+plaintextpassword).encode()).digest()
        while n:
            n -= 1
            d = hashlib.new(name,d).digest()
        return hashlib.new(name,d).hexdigest()

    @staticmethod
    def getsalt(randombits=64):
        if randombits<16 : raise ValueError("randombits < 16")
        return "%016x"%sr().getrandbits(randombits)

    def __connect(self):
        if not hasattr(self.local,'con') or self.local.con is None:
            self.local.con = sqlite3.connect(self.db)
            self.local.con.create_function('crypt',2,
                lambda s,p:dbpassword.hashpassword(
                  self.secure_hash,s,p,self.iterations))
        return self.local.con
    
    def __init__(self,db,
                   secure_hash='sha256',iterations=1000,saltbits=64):
        self.db = db
        self.local = threading.local()
        self.secure_hash = secure_hash
        self.iterations = iterations
        self.saltbits = 64
        with self.__connect() as con:
            cursor=con.cursor()
            sql='create table if not exists pwdb (user unique, salt, password)'
            cursor.execute(sql)    
    
    def update(self,user,plaintextpassword):
        with self.__connect() as con:
            cursor=con.cursor()
            sql1='insert or replace into pwdb (user,salt) values(?,?)'
            sql2='update pwdb set password=? where user = ?'
            salt=dbpassword.getsalt(self.saltbits)
            cursor.execute(sql1,(user,salt))
            cursor.execute(sql2,(dbpassword.hashpassword(
                    self.secure_hash,salt,plaintextpassword,
                    self.iterations),user))

    def check(self,user,plaintextpassword):
        cursor=self.__connect().cursor()
        sql='select user from pwdb where user = ? and crypt(salt,?) = password'
        cursor.execute(sql,(user,plaintextpassword))
        found=list(cursor) # can only create a list form this iterator once!
        return len(found)==1

Finding prime numbers using Python sets

Prompted by this blog I did some experiments to see whether it was convenient to implement the sieve of Eratosthenes using Python sets.

What I came up with was decidedly slower (a factor two to be precise) but also a lot less convoluted than Ram's solution:

view plainprint?

1. def sieve(n):  
2.     a=range(1,n)  
3.     return set(a)-{1}.union(*[set(a[i-1::i][1:]) for i in a[1:]])  

def sieve(n):
    a=range(1,n)
    return set(a)-{1}.union(*[set(a[i-1::i][1:]) for i in a[1:]])

The whole idea is to generate all sets of integer multiples for integers 2 and up. That is what set(a[i-1::i][1:] is all about. a is a list of integers starting at 1, so given an integer i, the slice a[i-1::i] will generate a list of all multiples of i. The minus one is to compensate for the fact the the list of integers does not start at zero.

This list of multiples except the first one is converted to a set. We actually generate a whole list of sets and the union of all these sets together with the set consisting of just the number one is subtracted from the set of all integers, leaving us with a set consisting only of prime numbers. Because the union method accepts individual set arguments only we expand the generated list of sets to separate argument with the asterisk * operator.

All considered I think this solutions is rather neat, even though its performance is not tremendous.