gmane.comp.python.sqlobject

Re: Storing password hashes using built-in functions like PASSWORD() (where available)

Florian Haas — 2008-09-04T19:41:03

That almost did it; I also had to specifically cater for _set_passwd receiving "None". For the list archives, here's the method as it works for me: def _set_passwd(self, value): # if the database has a built-in password hashing function, # use it. Otherwise, store a SHA256 password hash if value is None: self._SO_set_passwd(None) else: try: self._SO_set_passwd(func.PASSWORD(value)) except OperationalError: digest = SHA256.new(value).hexdigest() self._SO_set_passwd(digest) ... and then initially create the instance with an empty passwd and later update it. Thanks again, Oleg! Cheers, Florian ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: Storing password hashes using built-in functionslike PASSWORD() (where available)

Oleg Broytmann — 2008-09-04T19:20:38

When the object is being created SQLObject doesn't set attributes one by one - it collects all name/value pairs and then issues one INSERT query. I.e., self._SO_set_passwd(func.PASSWORD(value)) doesn't access the SQL backend and hence doesn't raise an exception; the exception is raised later, when SQLObject really does INSERT. See main.py, method _SO_setValue() for details: if self.sqlmeta._creating: self._SO_createValues[name] = dbValue return (I simplified the real code a bit to stress the important points.) self._SO_set_passwd(func.PASSWORD(value)) will issue an immediate UPDATE on any subsequent attribute assignment and your try/except will catch it. So for your magic to work you should create an object without a password and then update the password: c = Credential() c.password = 'password' Change 'passwd' to StringCol(default=None). Oleg.

Re: Storing password hashes using built-in functions like PASSWORD() (where available)

Florian Haas — 2008-09-04T18:59:55

Hello, I have to follow up on this as I'm running into something that makes me feel totally stupid. Here's how I implemented this: class Credential(SQLObject): class sqlmeta: # necessary for the _set_passwd magic defined below cacheValues = False username = StringCol(alternateID=True, unique=True) passwd = StringCol() def _set_passwd(self, value): # if the database has a built-in password hashing function, # use it. Otherwise, store a SHA256 password hash try: self._SO_set_passwd(func.PASSWORD(value)) except: digest = SHA256.new(value).hexdigest() self._SO_set_passwd(digest) So, thanks to Oleg's suggestions, this happily applies PASSWORD() when running on MySQL. Beautiful. However, on a platform without PASSWORD() (tried sqlite), this happens: Traceback (most recent call last): File "", line 1, in [rest of stack trace...] File "/usr/lib/python2.5/site-packages/sqlobject/sqlite/sqliteconnection.py", line 183, in _executeRetry raise OperationalError(ErrorMessage(e)) sqlobject.dberrors.OperationalError: no such function: PASSWORD Huh? I said try..except, didn't I? As I said, this makes me feel totally braindead. Can someone whack me in the head and point me to the obvious thing I am missing? Cheers, Florian ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: SQLObject (was: How do I use a postgreSQL functionlike date_part?)

Oleg Broytmann — 2008-09-04T13:45:59

Thank you! Unfortunately these days I don't have much spare time to do real work on SQLObject, and I don't know if I will have more time this autumn. :( Oleg.

Re: How do I use a postgreSQL function like date_part?

Matthew Wilson — 2008-09-04T12:49:11

Oleg -- thanks so much for the help. You're a fantastic benefit to the SQLObject community. Matt ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: Storing password hashes using built-in functions like PASSWORD() (where available)

Florian Haas — 2008-09-04T11:32:15

Oleg, precisely what I was looking for. Thank you very much! Cheers, Florian Oleg Broytmann wrote: ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: Storing password hashes using built-in functionslike PASSWORD() (where available)

Oleg Broytmann — 2008-09-04T11:01:59

The problem with this approach is that when you set (UPDATE) an attribute (SQL field) SQLObject caches the values so it doesn't need to ask SQL every time. Unfortunately when you set an attribute using SQL function, there is no value to cache. You have to turn off SQLObject caching for the class (table). This example works for me: class Test(SQLObject): class sqlmeta: cacheValues = False s = StringCol() Test.createTable() t = Test(s='') t.s = func.upper('oops!') print t.s and prints "OOPS!". But remember - with caching turned off SQLObject issues a separate SELECT for every attribute access. Oleg.

Storing password hashes using built-in functions like PASSWORD() (where available)

Florian Haas — 2008-09-04T10:38:02

[First post to list, hello everyone!] Hi, I'm failing to find a solution to an issue that seems fairly commonplace to me, so I'm assuming it's been answered before and I failed to dig up the answer (I tried). So feel free to alert me to any resources where this type of question has previously been answered. I'm currently trying to figure out a way to store a password hash in a table, allowing the password to be passed in in plain. I'd prefer to use a built-in password hashing functions on connections that support them, like so: from sqlobject import * from Crypto.Hash import SHA256 class Credential(SQLObject): username = StringCol(unique=True, alternateID=True) password = StringCol() def _set_password(self, newpass): try: # FIXME: update field with hash generated # from PASSWORD(newpass) except dberrors.OperationalError: # assume internal password function is # not available, do my own hashing hash = SHA256.new(newpass).hexdigest() self._SO_set_password(hash) So the "do my own hashing" part is easily achieved. It's the use of the internal password function that's baffling me. I'd like to use PASSWORD() on MySQL, and I believe this should work for MS SQL Server too as it also appears to have a built-in password hashing function. Is there a way to achieve this, combining _SO_set_* and sqlbuilder.func.PASSWORD perhaps? If so, how would I do that? Grateful for any insights. Thanks much in advance. Cheers, Florian ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: How do I use a postgreSQL function like date_part?

Oleg Broytmann — 2008-09-03T19:33:23

Something like from sqlobject.sqbuilder import func People.select(func.date_part('day', createddate) == '2008-09-01') 'func' is a pretty simple object whose attributes are passed unchanged (unquoted, unescaped) to SQL. Oleg.

How do I use a postgreSQL function like date_part?

Matthew Wilson — 2008-09-03T13:43:54

I want to do a select like this with SQLObject: select * from people where date_part('day', createddate) = date '2008-09-01'; That query should choose people created any time on September 1st. In SQLObject, this is what I'm doing: People.select("date_part('day', createddate) = date '2008-09-01'") Is there some way to avoid using a string of raw SQL? Matt ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: Having a modified column filled in automagically...

Buck Golemon — 2008-08-27T17:48:23

------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/_______________________________________________ sqlobject-discuss mailing list sqlobject-discuss< at >lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: Having a modified column filled in automagically...

Sam's Lists — 2008-08-27T01:15:20

Re: Having a modified column filled in automagically...

Andres Freund — 2008-08-26T10:46:36

Re: Having a modified column filled in automagically...

Sam's Lists — 2008-08-26T08:59:57

Re: SQLObject's joins; GROUP BY

Oleg Broytmann — 2008-08-20T21:20:56

SQLObject-style thinking is, as far as I understand it, to declare relationships explicitly. So if SQLObject know there are Table1, Table2 and Table3, and Table1 linked to Table2 with a straight join Table1.q.t2_id == Table2.q.id Table2 is joined with Table3 Table2.q.t3_id == Table3.q.id then SQLObject can construct 3-tables join just AND'ing these conditions, right? (Table1.q.t2_id == Table2.q.id) & (Table2.q.t3_id == Table3.q.id) Isn't it enough? GROUP BY is used with aggregate functions. Your examples lists columns to GROUP BY by, but where is an aggregate? For MyTable.select() SQLObject knows what columns to return and how to convert them from SQL to Python. With your proposed API SQLObject doesn't know what columns to return, how to name them in SQL and in Python, how to convert values from SQL to Python... Oleg.

Re: Can SQLObject do this?

Golemon, Buck — 2008-08-20T20:52:51

To do multiple-table joins needs to do a graph search. More specifically a Steiner graph search. I have an implementation that I was planning to put on top of SQLObject to achieve this. If you want to think about adding this as a feature to SQLO, I'd be very happy to talk about it. For the groupby api, I was thinking something like: MyTable.select().groupby("col1", "col2") or maybe MyTable.select().groupby(MyTable.col1, MyTable.col2) or even MyTable.select(..., groupby=("col1", "col2")) Whichever fits the existing style best. Assuming select() returns some query object, that object would need to grow a groupby(*argv) method (in the first two options). I'm not sure how the column types are relevant here. --Buck -----Original Message----- From: sqlobject-discuss-bounces< at >lists.sourceforge.net [mailto:sqlobject-discuss-bounces< at >lists.sourceforge.net] On Behalf Of Oleg Broytmann Sent: Wednesday, August 20, 2008 11:57 AM To: sqlobject-discuss< at >lists.sourceforge.net Subject: Re: [SQLObject] Can SQLObject do this? On Wed, Aug 20, 2008 at 11:42:56AM -0700, Golemon, Buck wrote: Two-tables join is done automatically - just declare relationship using ForeigmKey/MultipleJoin/RelatedJoin. Three (or more) tables joins, unfortunately, require more work. IWBN to solve this. It is not that easy. Look, when you declare a table you also declare the names and types of the columns: class MyTable(SQLObject): col1 = Type1Col() col2 = Type2Col() and when you call MyTable.select() SQLObject knows the list of columns. With GROUP BY you have to pass a different list of columns; what would be an API? sqlbuilder.Select() can do groupBy because it knows the list of columns and ignores their types altogether. Oleg.

Re: Can SQLObject do this?

Oleg Broytmann — 2008-08-20T18:57:07

Two-tables join is done automatically - just declare relationship using ForeigmKey/MultipleJoin/RelatedJoin. Three (or more) tables joins, unfortunately, require more work. IWBN to solve this. It is not that easy. Look, when you declare a table you also declare the names and types of the columns: class MyTable(SQLObject): col1 = Type1Col() col2 = Type2Col() and when you call MyTable.select() SQLObject knows the list of columns. With GROUP BY you have to pass a different list of columns; what would be an API? sqlbuilder.Select() can do groupBy because it knows the list of columns and ignores their types altogether. Oleg.

Re: Can SQLObject do this?

Golemon, Buck — 2008-08-20T18:42:56

Thanks for being so helpful. Neglecting the 'group by', how would you do this? If you want, I can look into submitting a patch to add 'group by' capability. --Buck -----Original Message----- From: sqlobject-discuss-bounces< at >lists.sourceforge.net [mailto:sqlobject-discuss-bounces< at >lists.sourceforge.net] On Behalf Of Oleg Broytmann Sent: Tuesday, August 19, 2008 11:25 AM To: sqlobject-discuss< at >lists.sourceforge.net Subject: Re: [SQLObject] Can SQLObject do this? On Tue, Aug 19, 2008 at 11:17:00AM -0700, Golemon, Buck wrote: joins SQLObject can do joins only when the user declares what (s)he wants using ForeignKey/MultipleJoin/RelatedJoin, but it cannot combine these automatic joins with GROUP BY. Oleg.

Re: problem clearing the cache

Oleg Broytmann — 2008-08-19T21:17:10

.select() (SelectResult.__iter__, actually) draws all rows into one huge list. Try for a in SOClass.select().lazyIter(): ... Oleg.

problem clearing the cache

sophana — 2008-08-19T21:07:40

Hi I need to iterate over all the rows to check something. When doing this, I saw memory consumption raising infinitely. Then I remembered that sqlobjects caches everything. Then I tried: for a in SOClass.select(): connection.cache.clear() memory is still growing. Is it a python problem or a SQLObject problem? What do you suggest for this? Thanks ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Re: Can SQLObject do this?

Oleg Broytmann — 2008-08-19T18:25:18

SQLObject can do joins only when the user declares what (s)he wants using ForeignKey/MultipleJoin/RelatedJoin, but it cannot combine these automatic joins with GROUP BY. Oleg.