headers
Matthew Wilson | 3 Sep 15:43
Gravatar

How do I use a postgreSQL function like date_part?

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=/
Permalink | Reply | 3 comments |
headers
Oleg Broytmann | 3 Sep 21:33
X-Face
Picon
Favicon

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

On Wed, Sep 03, 2008 at 01:43:54PM +0000, Matthew Wilson wrote:
> 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?

   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.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
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
(Continue reading)

Permalink | Reply | 2 comments |
headers
Florian Haas | 4 Sep 12:38
Favicon

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

[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
(Continue reading)

Permalink | Reply | 5 comments |
headers
Oleg Broytmann | 4 Sep 13:01
X-Face
Picon
Favicon

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

On Thu, Sep 04, 2008 at 12:38:02PM +0200, Florian Haas wrote:
>     def _set_password(self, newpass):
>         try:
>             # FIXME: update field with hash generated
>             # from PASSWORD(newpass)

   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.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
(Continue reading)

Permalink | Reply | 4 comments |
headers
Florian Haas | 4 Sep 13:32
Favicon

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

Oleg,

precisely what I was looking for. Thank you very much!

Cheers,
Florian

Oleg Broytmann wrote:
> On Thu, Sep 04, 2008 at 12:38:02PM +0200, Florian Haas wrote:
>>     def _set_password(self, newpass):
>>         try:
>>             # FIXME: update field with hash generated
>>             # from PASSWORD(newpass)
> 
>    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!')
(Continue reading)

Permalink | Reply | 3 comments |
headers
Matthew Wilson | 4 Sep 14:49
Gravatar

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

On Wed 03 Sep 2008 03:33:23 PM EDT, Oleg Broytmann wrote:
> On Wed, Sep 03, 2008 at 01:43:54PM +0000, Matthew Wilson wrote:
>> 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?
>
>    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.

Oleg -- thanks so much for the help.  You're a fantastic benefit to the
SQLObject community.

Matt
(Continue reading)

Permalink | Reply | 1 comment |
headers
Oleg Broytmann | 4 Sep 15:45
X-Face
Picon
Favicon

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

On Thu, Sep 04, 2008 at 12:49:11PM +0000, Matthew Wilson wrote:
> Oleg -- thanks so much for the help.  You're a fantastic benefit to the
> SQLObject community.

   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.
--

-- 
     Oleg Broytmann            http://phd.pp.ru/            phd <at> phd.pp.ru
           Programmers don't die, they just GOSUB without RETURN.

-------------------------------------------------------------------------
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=/
Permalink | Reply | 0 comments |
headers
Florian Haas | 4 Sep 20:59
Favicon

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

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:

>>> c = Credential(username="foo", passwd="bar")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
(Continue reading)

Permalink | Reply | 2 comments |
headers
Oleg Broytmann | 4 Sep 21:20
X-Face
Picon
Favicon

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

On Thu, Sep 04, 2008 at 08:59:55PM +0200, Florian Haas wrote:
> 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:
> 
> >>> c = Credential(username="foo", passwd="bar")
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
>   [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
(Continue reading)

Permalink | Reply | 1 comment |
headers
Florian Haas | 4 Sep 21:41
Favicon

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

Oleg Broytmann wrote:
>    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.

That almost did it; I also had to specifically cater for _set_passwd
receiving "None".
(Continue reading)

Permalink | Reply | 0 comments |

Gmane