Re: passwords hashed using pgcrypto (in the database)

Hello and happy New Year!

On Sun, Dec 30, 2012 at 09:46:00PM +0100, Tomas Vondra <[email protected]> wrote:
> I'm learning SQLObject - checking if we could use it on our projects, 
> and I got stuck at hashing passwords inside the database.
> 
> Imagine a simple table with info about users:
> 
> CREATE TABLE users (
>      id       INT PRIMARY KEY,
>      login    TEXT NOT NULL UNIQUE,
>      pwdhash  TEXT NOT NULL
> )
> 
> where "pwdhash" is a hashed password. We're using PostgreSQL and we 
> usually handle this inside the database using a pgcrypto module, that 
> provides various hash/crypto functions. An insert into the table then 
> looks like this
> 
>      INSERT INTO users VALUES (1, 'login', crypt('mypassword', 
> gen_salt('bf')))
> 
> which generates a salt, computes the hash and stores that into a single 
> text column (salt+hash). The authentication then looks like this:
> 
>      SELECT id, login FROM users WHERE login = 'login' AND pwdhash = 
> crypt('mypassword', pwdhash)
> 
> which reuses the salt stored in the column.
> 
> I'm investigating if we could do this with SQLObject

   I think it's possible with many lines of code. SQLObject doesn't send
raw values on INSERT/UPDATE -- it calls sqlrepr(value) which in turn
calls value.__sqlrepr__(dbname) if the value has __sqlrepr__ method. So
you have to return a wrapper with __sqlrepr__ method, and it can be
returned from a validator.
   See the following program as a small example:

from formencode import validators

class CryptValue(object):
    def __init__(self, value):
        self.value = value

    def __sqlrepr__(self, db):
        assert db == 'postgres'
        return "crypt('%s')" % self.value

class CryptValidator(validators.Validator):
    def from_python(self, value, state):
        return CryptValue(value)

class SOCryptCol(SOCol):
    def createValidators(self, dataType=None):
        return [CryptValidator()]

    def _sqlType(self):
        return 'TEXT NOT NULL'

class CryptCol(Col):
    baseClass = SOCryptCol

class Test(SQLObject):
    test1 = StringCol()
    test2 = CryptCol()

Test.createTable()

test = Test(test1='1', test2='2')
print test

   It produces the following debugging output:

 1/QueryR  :  CREATE TABLE test (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    test1 TEXT,
    test2 TEXT NOT NULL
)
 2/QueryIns:  INSERT INTO test (test1, test2) VALUES ('1', crypt('2'))

   I hope it'd be helpful as a starting point.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            [email protected]
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_123012