DAL Connection String

[Hugh Barker]

Hi,

I have a bit of a problem with my connection string in db.py - specifically, the database password has a '@' symbol, and so web2py chokes because it assumes this is the delimiter between password and host.

I had a look at dal.py and I guess the regex needs modifying to take this case into account. Unfortunately my regex skills are pretty rudimentary - I spent an hour or so trying to nut it out, but didn't get anywhere, so I thought I'd ask you guys. The code is around line 1800 of dal.py:

        uri = uri.split('://')[1]

        m = re.compile('^(?P<user>[^:@]+)(\:(?P<password>[^@]*))?@(?P<host>[^\:@/]+)(\:(?P<port>[0-9]+))?/(?P<db>[^\?]+)(\?sslmode=(?P<sslmode>.+))?$').match(uri)

        if not m:

            raise SyntaxError, "Invalid URI string in DAL"

I can see what it's doing and where it's going wrong, just not how to fix it :(

Any help is appreciated.. I guess the easy option is to change the DB password, but unfortunately that isn't an option for me.

Cheers,

Hugh.

[Niphlod]

in gluon/dal.py you can find this....

def __init__(self, uri='sqlite://dummy.db',
pool_size=0, folder=None,
db_codec='UTF-8', check_reserved=None,
migrate=True, fake_migrate=False,
migrate_enabled=True, fake_migrate_all=False,
decode_credentials=False, driver_args=None,
adapter_args=None, attempts=5, auto_import=False):

if not decode_credentials:
credential_decoder = lambda cred: cred
else:
credential_decoder = lambda cred: urllib.unquote(cred)

try to quote the credentials and set decode_credentials to True

[Hugh Barker]

Thanks for that.

I tried setting it to true, but no luck.

From what I understand from the code though, I wouldn't expect it to -
the credential_decoder lambda that gets set up based on that flag
isn't used until after the regex tries to split the URI, ie:

m = re.compile('^(?P<user>[^:@]+)(\:(?P<password>[^@]*))?@(?P<host>[^\:@/]+)(\:(?P<port>[0-9]+))?/(?P<db>[^\?]+)(\?sslmode=(?P<sslmode>.+))?$').match(uri)
if not m:
raise SyntaxError, "Invalid URI string in DAL"

user = credential_decoder(m.group('user'))

The match is failing (ie m is None) when passed in a credential string
like this, from db.py:

from gluon.tools import Service

db = DAL('postgres://user:p@ssword@host/db')
response.generic_patterns = ['*']
service = Service()

Forgot to mention - running web2py 1.99.4 against PostgreSQL 9.1.

To my mind, the bug lies with the regex. I asked a question on Stack
Overflow to see if I could come up with a solution with their help:

http://stackoverflow.com/questions/9156195/python-regex-escaping/9160293

Using the regex in the answer I accepted works when I try it in a mini
test script, ie:

uri = 'postgres://username:p@ssword@host/database'

uri = uri.split('://')[1]

m = re.compile('((?P<user>.*):)((?P<pass>.*)@)((?P<host>.*)/)((?P<db>.*))').match(uri)
if not m:
raise SyntaxError, "Invalid URI string in DAL " + uri
else:
print m.groupdict()

will result in:

{'host': 'host', 'db': 'database', 'user': 'username', 'pass': 'p@ssword'}

(note I've ignored port and SSL)

However if I modify dal.py, swapping line 1877 with:

m = re.compile('((?P<user>.*):)((?P<pass>.*)@)((?P<host>.*)/)((?P<db>.*))').match(uri)

I get:

Traceback (most recent call last):
File "/var/www/web2py/gluon/restricted.py", line 204, in restricted
exec ccode in environment
File "/var/www/web2py/applications/geospatial/models/db.py", line 3,
in <module>
db = DAL('postgres://username:p@ssword@host/database')
File "/var/www/web2py/gluon/dal.py", line 4736, in __init__
driver_args or {}, adapter_args or {})
File "/var/www/web2py/gluon/dal.py", line 1878, in __init__
if not m:
SyntaxError: Invalid URI string in DAL

IE no match (m is None).

Any help would be much appreciated. Happy to provide any more
information as required.

Cheers,
Hugh.

[Niphlod]

Ok, I was a little unclear. There's no need to change any regex in DAL code.

Let's say we have a database named "bbb", on localhost, port 5432, username "hello" and password "password".

DB URI will be : postgres://hello:password@localhost:5432/bbb

Now, the decode_credential part. With the regex, credentials are whatever goes from "postgres://" to the "@".

Let's say we have the password changed from the example abow, from "password" to "p@ssword".

Regex is going to fail, but we have decode_credential enabled....we'll take the default lambda that is urllib.unquote().
To reverse that, we need to call a urllib.quote() not on the full connection string, but only on the "parts" costituting it.

With that, "p@ssword" needs to be quoted "p%40ssword" .
DB URI is now postgres://hello:p%40ssword@localhost:5432/bbb .
Regex works fine, extracts hello:p%40ssword, unquoted password is p@ssword and everything goes fine.

Just tested on my machine, works perfectly!

[Niphlod]

PS : final DAL initiation code is

db = DAL('postgres://hello:p%40ssword@localhost:5432/bbb', decode_credentials=True)

[Hugh Barker]

Excellent, thanks very much. This solved my issue.

2012/2/8 Niphlod <nip...@gmail.com>: