special characters in database credentials

[Josh J]

Hey all,

I've found an issue with SQLDB when developing my application. The
URI handling does not allow special characters in database passwords.
Unfortunately, I must connect to the database from my application
using a password with special characters.
eg. Consider the URI for a database with has an @ in the password:
postgres://username:P@ssword@localhost:5432/database

That is the simplest way to break the current URI handling. Consider
a more complex password like “a@b:3/c”, which is a valid postgres
password and probably valid in other DBMS as well. It would build a
URI that looks something like:
postgres://username:a@b:3/c@host:port/database

The regular expression CAN be carefully modified to allow all of
these characters in the password, but what about if you had special
characters in your username too? Imagine if you had a (valid but
contrived) postgres username like “user@host/group:subgroup” with the
same “a@b:3/c” password as before. Then your URI would look something
like:
postgres://user@host/group:subgroup:a@b:3/c@host:port/database

I think this exposes a problem in general with parsing username and
passwords from a URI, in that if you have these special characters you
can no longer parse them with a simple regular expression. If you look
at Section 3.1 of RFC 1738 - Uniform Resource Locators they already
thought of this, and they say that within the user and password field
you should encode any ":", "@", or "/".

I have tried modifying SQLDB to pass the username and password
through the urllib.unquote function as follows:
user = urllib.unquote(m.group("user"))
passwd = urllib.unquote(m.group("passwd"))

Then when opening the database do something like this:
SQLDB("postgres://%(user)s:%(pass)s@localhost:5432/database" % \
({'user': urllib.quote("test"),
'pass':urllib.quote("p@ssword"})))

This works fine for me. And, passwords without special characters
will be unmodified by urllib.unquote(). In this way backwards
compatibility is mostly intact. However consider a user who currently
has a password with a % character. Even though it works fine now, if
you were to pass the password through urllib.unquote then it would
assume the % was an escape sequence and produce unexpected results for
them.


What do you think?


Regards,

Josh Jaques
Seccuris Inc.

[mdipierro]

I think you are right. Can you send me a patch? Or I ca do it but not
today. ;-)

[mdipierro]

Ignore my previous email... there is no need with a patch for what you
are suggestion...let me think about this some more.

On Sep 29, 11:01 am, Josh J <jjaq...@seccuris.com> wrote:

[Josh J]

Sure I can provide the patch if you want,

Would basically just have to change

user = m.group("user")
passwd = m.group("passwd")

to

user = urllib.unquote(m.group("user"))
passwd = urllib.unquote(m.group("passwd"))

everywhere you parse the credentials.

[Josh J]

Hey Massimo,

I've taken the initiative and built a patch to allow encoded
credentials in database URIs.

The following patch adds an additional boolean argument to the SQLDB
initializer called decode_credentials (defaults to False). The
decode_credentials argument is used to build the 'credential_decoder'
lambda. If decode_credentials is False then the lambda doesn't do
anything, but if decode_credentials is True then the lambda will pass
the credential through urllib.unquote.

Anytime a username or password is parsed from the URI, it will be
passed through the credential_decoder lambda before being used with
the underlying database connection.

Let me know what you think, or if you want me to change anything about
it before you'll add it into trunk.

=====BEGIN PATCH=====

--- sql.py 2010-10-18 14:44:44.096823600 +0000
+++ sql_patched.py 2010-10-18 15:09:06.141587000 +0000
@@ -898,7 +898,13 @@

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=True, fake_migrate=False,
decode_credentials=False):
+ if not decode_credentials:
+ credential_decoder = lambda cred: cred
+ else:
+ import urllib
+ credential_decoder = lambda cred: urllib.unquote(cred)
+
self._uri = str(uri) # NOTE: assuming it is in utf8!!!
self._pool_size = pool_size
self._db_codec = db_codec
@@ -957,10 +963,10 @@
if not m:
raise SyntaxError, \
"Invalid URI string in SQLDB: %s" % self._uri
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
host = m.group('host')
@@ -992,10 +998,10 @@
).match(self._uri[11:])
if not m:
raise SyntaxError, "Invalid URI string in SQLDB"
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
host = m.group('host')
@@ -1067,10 +1073,10 @@
if not m:
raise SyntaxError, \
"Invalid URI string in SQLDB: %s" % self._uri
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
host = m.group('host')
@@ -1108,10 +1114,10 @@
if not m:
raise SyntaxError, \
"Invalid URI string in SQLDB: %s" % self._uri
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
host = m.group('host')
@@ -1137,10 +1143,10 @@
if not m:
raise SyntaxError, \
"Invalid URI string in SQLDB: %s" % self._uri
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
pathdb = m.group('path')
@@ -1165,10 +1171,10 @@
if not m:
raise SyntaxError, \
"Invalid URI string in SQLDB: %s" % self._uri
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
host = m.group('host')
@@ -1205,10 +1211,10 @@
).match(self._uri[11:])
if not m:
raise SyntaxError, "Invalid URI string in SQLDB"
- user = m.group('user')
+ user = credential_decoder(m.group('user'))
if not user:
raise SyntaxError, 'User required'
- passwd = m.group('passwd')
+ passwd = credential_decoder(m.group('passwd'))
if not passwd:
passwd = ''
host = m.group('host')


====END PATCH====

[mdipierro]

can you please email it to me?

[Josh J]

I sent it on monday to your @cs.depaul.edu account