Hello and happy New Year!
On Sun, Dec 30, 2012 at 09:46:00PM +0100, Tomas Vondra <t...@fuzzy.cz> 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/ p...@phdru.name
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
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
