Difference between these PostgreSQL queries and fix the wrong one?

Question

I am trying to execute this query with psycopg2 in Python3:

query_clone_db_data = (
   'CREATE DATABASE (%s) WITH TEMPLATE (%s) OWNER (%s);'
)

conn.cursor.execute(query_clone_db_data, (new_dbname, original_dbname, owner, ))

And I am getting this error message:

syntax error at or near "'my_new_database'" LINE 1: CREATE DATABASE ('my_new_database') WITH TEMPLATE 'original...

On the other hand, this query works without any problem:

query_get_owner_dbs = (
   'SELECT d.datname, d.datallowconn, '
   'pg_catalog.pg_get_userbyid(d.datdba) as owner '
   'FROM pg_catalog.pg_database d '
   'WHERE pg_catalog.pg_get_userbyid(d.datdba) = (%s);'
)

conn.cursor.execute(query_get_owner_dbs, (owner, ))

Why the second one has not syntax errors and the first one yes? I tried to remove parenthesis in the first one and then the problem is the quotes... Anyone knows what is happening?

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

Table and database names can not be parametrized. As far as I know, only column values can be parametrized. Therefore, you'll need to string formatting:

query_clone_db_data = (
   'CREATE DATABASE %s WITH TEMPLATE %s OWNER %s'
    % (new_dbname, original_dbname, owner))

conn.cursor.execute(query_clone_db_data)

Be sure that new_dbname, original_dbname and owner do not come from user input, since string formatting makes this code vulnerable to sql injection.

edited May 23 '17 at 12:15

Community

1
1

answered May 08 '14 at 11:21

unutbu

842,883
184
1,785
1,677

Thank you! I did not know that. However, it gives this error: CREATE DATABASE cannot run inside a transaction block – forvas May 08 '14 at 11:25
One way is to use a "white dict" -- they keys are the acceptable values for user input, and the values *which you specify* are the values inserted into SQL query. An exception will be raised if the user specifies something which is not a key in the dict. If the keys and values are the same, you could use a set instead, and just check that the user input is in the acceptable set. – unutbu May 08 '14 at 11:32
1

@user3198727: I don't know the solution regarding the "CREATE DATABASE" error. I suspect, [from reading this](http://comments.gmane.org/gmane.comp.python.db.pypgsql.user/440), that the solution has to do with calling commit before trying to create a new database, but I'm not familiar enough with psycopg2 to tell you for sure. – unutbu May 08 '14 at 11:46
In that forum is the solution, I am going to post it. Thank you so much! – forvas May 08 '14 at 14:04

Denis de Bernardy · Answer 2 · 2014-05-08T11:39:24.830

In the first query, your variables are identifiers, whereas in the second they're literal strings.

You shouldn't be putting parenthesis with the identifiers, and they shouldn't be wrapped in single quotes unless you further wrap them with double quotes.

For instance, this would create a table called 'foo' rather than foo:

create table "'foo'" ();

And these would all spit out errors:

create table 'foo' ();   -- requires double quotes
create table (foo) ();   -- unexpected parenthesis
create table ('foo') (); -- both problems

If you really want to use parameters for identifiers, you'll need to use a dynamic SQL statement. In this case, you'll also need to worry about quoting the identifiers using PG if applicable, e.g.:

DO $$
BEGIN
 PERFORM ' CREATE TABLE ' || quote_ident(%s) || ' ()';
END;
$$;

In the above, the outer string is delimited by $$, and the inner strings are delimited by '. After parameters get replaced, it'll look like:

' CREATE TABLE ' || quote_ident('foo') || ' ()'

And then:

' CREATE DATABASE ' || '"foo"' || ' ()'

And finally:

' CREATE DATABASE "foo" ()'

This particular statement of yours, however, cannot be part of a transaction so cannot go in a do statement. As such, you'll need to resort to doing the string manipulation in Python directly.

What quote_ident() does is, in essence, wrap in double quotes after doubling all double quotes in the input. For instance:

# select quote_ident('foo"bar');
 quote_ident 
-------------
 "foo""bar"
(1 row)

I'm not familiar enough with Python to write that for you, but that's basically the subroutine you'll need to make it work. Then pass the final SQL string — with the parameters in it replaced — to Postgres directly.

Thank you for your help. Fixed this problem I get CREATE DATABASE cannot run inside a transaction block — forvas, May 08 '14 at 11:33
I got the solution, I am going to post it. Thank you for your help! — forvas, May 08 '14 at 14:05

score 1 · Accepted Answer · answered May 08 '14 at 14:09

Thanks to unutbu and Denis. The whole solution is:

query_clone_db_data = (
   'CREATE DATABASE %s WITH TEMPLATE %s OWNER %s;'
)

formated_query_clone_db_data = query_clone_db_data % (new_dbname, original_dbname, owner)

conn.cursor.execute('commit')  # This one is important!
conn.cursor.execute(formated_query_clone_db_data)

score 0 · Answer 4 · edited Jan 23 '16 at 09:00

You can use the psycopg2.extensions.AsIs to pass in parameters for database name etc.

As said here: http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

Your code would look like:

from psycopg2.extensions import AsIs
query_clone_db_data = (
   'CREATE DATABASE %s WITH TEMPLATE %s OWNER %s;'
)
conn.autocommit = True
conn.cursor.execute(query_clone_db_data, (AsIs(new_dbname), AsIs(original_dbname), AsIs(owner)))

The conn.autocommit=True is there because you can't create a database inside a transaction.

EDIT: Note that you will still be vulnerable to SQL injection when using AsIs, but you can at least execute queries in the usual way (and CREATE DATABASE cannot be executed from multi-command string, so it would still fail for new_dbname = my_db;DROP DATABASE another_db;--)

Difference between these PostgreSQL queries and fix the wrong one?

4 Answers4