Postgresql backend fails when schema name contains dashes

[cpinto@…]

I was in the process of creating environments for some SVN repositories, some repository names contained dashes, and trac-admin kept failing to create the environments.

It was tracked to a faulty line in the postgres backend module, I'm attaching a patch to fix the problem.

Thanks.

[cpinto@…]

Fix for postgres schemas

[Matthew Good]

possible alternative patch using parameterized query

[Matthew Good]

I attached another patch which uses a parameterized query instead. I don't have a Postgres database to test this on, so can someone give it a try and see if that works.

[cpinto@…]

mgood, your patch still has the problem of not quoting schema names so if the schema name is something like my-project, creation of the schema will fail. The SQL statement must be: CREATE SCHEMA "my-project"

[Matthew Good]

Replying to cpinto@lisa.gov.pt:

mgood, your patch still has the problem of not quoting schema names so if the schema name is something like my-project, creation of the schema will fail. The SQL statement must be: CREATE SCHEMA "my-project"

Did you try the patch, or are you just basing that on looking at the change?

In Python the preferred way to pass variables into a SQL query is through "parameterized queries". When you execute a query like cursor.execute(sql, params) the database driver handles substituting the parameters into the query and making sure they are properly escaped, so you shoudn't need to do any quoting in the SQL string.

[cpinto@…]

Replying to mgood:

Did you try the patch, or are you just basing that on looking at the change?

In Python the preferred way to pass variables into a SQL query is through "parameterized queries". When you execute a query like cursor.execute(sql, params) the database driver handles substituting the parameters into the query and making sure they are properly escaped, so you shoudn't need to do any quoting in the SQL string.

You're right, your patch is better. I still haven't used it though.

[Jonas Borgström]

Replying to mgood:

I attached another patch which uses a parameterized query instead. I don't have a Postgres database to test this on, so can someone give it a try and see if that works.

This doesn't work. With postgresql identifiers need to be quoted with double quotes, so a parametrerized query can't be used:

>>> cnx = psycopg2.connect('dbname=test')
>>> cursor = cnx.cursor()
>>> cursor.execute('CREATE SCHEMA %s', ('fo-o',))
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
psycopg2.ProgrammingError: syntax error at or near "'fo-o'" at character 15

But I think it's safe to use the approach proposed by the reporter since the the schema name comes from trac.ini. I'll do some more testing before committing.

[Jonas Borgström]

Fixed in [3689]. Thanks!