Subject: Creating dynamically-typed tables using psycopg2's built-in formatting

From: Daniel Cohen <[email-masked]>
Date: Thu, 13 Jun 2019 12:28:08 -0700

Hi!

I'm working on a project in Python that interacts with a PostgreSQL data
warehouse, and I'm using the psycopg2 API. I am looking to create
dynamically-typed tables.

For example, I would like to be able to execute the following code:

from psycopg2 import connect, sql

connection = connect(host="host", port="port", database="database",
user="user", password="pw")
def create_table(tbl_name, col_name, col_type):
    query = sql.SQL("CREATE TABLE {} ({}
{})".format(sql.Identifier(tbl_name), sql.Identifier(col_name),
sql.Identifier(column_type)))
    connection.execute(query)

create_table('animals', 'name', 'VARCHAR')

and end up with a table named "animals" that contains a column "name" of
type VARCHAR. However, when I attempt to run this, I get an error: *'type
"VARCHAR" does not exist'*. I assume psycopg2's built-in formatter is
putting double quotes around the VARCHAR type when there should not be any.
Normally, I would just work around this myself, but the documentation is
*very* clear that Python string concatenation should never be used for fear
of SQL injection attacks. Security is a concern for this project, so I
would like to know if it's possible to create dynamically-typed tables in
this fashion using pyscopg2, and if not, whether there exists another
third-party API that can do so securely.

A second issue I've had is that when creating tables with a similar
methodology, the sql.Identifier() function does not perform as I expect it
to. When I use it to dynamically feed in table names, for example, I get
varying results. See below:

CREATE TABLE tbl AS SELECT * FROM other_tbl;

in raw SQL creates a table called tbl, whereas

cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM
other_tbl").format(sql.Identifier(tbl))

creates a table called "tbl". The two are different, and

SELECT * FROM tbl;

returns a totally different table than

SELECT * FROM "tbl";

Please let me know if I can fix either of these problems; I want to be able
to dynamically feed types into SQL queries, and I want the tables created
to be of the form tbl not "tbl". Thank you!

Danny

---

From: Christophe Pettus <[email-masked]>
Date: Thu, 13 Jun 2019 13:00:20 -0700

Hi, Daniel,

First, tbl and "tbl" aren't "totally different":

> xof=# create table tbl (i integer);
> CREATE TABLE
> xof=# create table "tbl" (i integer);
> ERROR:  relation "tbl" already exists

The difference is that putting double quotes around an SQL identifier makes the comparison type-sensitive, and allows for characters not otherwise allowed in identifiers:

> xof=# select * from Tbl;
>  i 
> ---
> (0 rows)
> 
> xof=# select * from "Tbl";
> ERROR:  relation "Tbl" does not exist
> LINE 1: select * from "Tbl";
>                       ^

You can use SQL.identifier, but you need to make sure you are getting the case right; in general, PostgreSQL types are all lower-case, and it's only the lack of double quotes that makes this work:

xof=# create table x (i VARCHAR);
CREATE TABLE
xof=# create table y (i "VARCHAR");
ERROR:  type "VARCHAR" does not exist
LINE 1: create table y (i "VARCHAR");
                          ^
xof=# create table y (i "varchar");
CREATE TABLE

> On Jun 13, 2019, at 12:28, Daniel Cohen <[email-masked]> wrote:
> 
> Hi!
> 
> I'm working on a project in Python that interacts with a PostgreSQL data warehouse, and I'm using the psycopg2 API. I am looking to create dynamically-typed tables.
> 
> connection 
> = connect(host="host", port="port", database="database", user="user", password="pw")
> 
> 
> 
>     query 
> = sql.SQL("CREATE TABLE {} ({} {})".format(sql.Identifier(tbl_name), sql.Identifier(col_name), sql.Identifier(column_type)))
> 
>     connection
> .execute(query)
> 
> 
> create_table
> and end up with a table named "animals" that contains a column "name" of type VARCHAR. However, when I attempt to run this, I get an error: 'type "VARCHAR" does not exist'. I assume psycopg2's built-in formatter is putting double quotes around the VARCHAR type when there should not be any. Normally, I would just work around this myself, but the documentation is very clear that Python string concatenation should never be used for fear of SQL injection attacks. Security is a concern for this project, so I would like to know if it's possible to create dynamically-typed tables in this fashion using pyscopg2, and if not, whether there exists another third-party API that can do so securely. 
> 
> A second issue I've had is that when creating tables with a similar methodology, the sql.Identifier() function does not perform as I expect it to. When I use it to dynamically feed in table names, for example, I get varying results. See below:
> 
> cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM other_tbl").format(sql.Identifier(tbl))
> Please let me know if I can fix either of these problems; I want to be able to dynamically feed types into SQL queries, and I want the tables created to be of the form tbl not "tbl". Thank you!
> 
> Danny 
> 
> 

--
-- Christophe Pettus
   [email-masked]

---

From: David Raymond <[email-masked]>
Date: Thu, 13 Jun 2019 20:29:04 +0000

SELECT * FROM tbl;

That's a little disconcerting.

For the capitalization thing basically what's going on is two things. (The way I understand it)
1) Postgres IS case sensitive.
2) When any query gets sent to the server, the server converts everything not inside quotes to lower case as step 1, _before_ trying to match names for tables, columns, functions, etc.

So if you send
SELECT * FROM TBL;
One of the first things the server does is change it to
select * from tbl;
at which point it will do a case-sensitive match for tbl

And if you run
SELECT * FROM "TBL";
it gets turned into
select * from "TBL";
at which point it will do a case-sensitive match for TBL

But in your case what is in quotes is the same as its lowercase version. So it "should" match up to the same thing. So if you are indeed getting different results from different tables then either my understanding is way off, or something weird is going on.

---

From: Christophe Pettus <[email-masked]>
Date: Thu, 13 Jun 2019 13:35:15 -0700

> On Jun 13, 2019, at 13:29, David Raymond <[email-masked]> wrote:

It's a bit more elaborate than that, although that's not a bad summary of the perceived result.  The details are here:

	https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS\

--
-- Christophe Pettus
   [email-masked]

---

From: Daniel Cohen <[email-masked]>
Date: Thu, 13 Jun 2019 13:54:53 -0700

Hi Christophe,

Thanks so much for your response. The uppercase --> lowercase fix worked
for the types, but I'm still only getting tables that can be searched by
double-quotations (i.e. > SELECT * FROM tbl; returns nothing, but > SELECT
* FROM "tbl"; returns the table I uploaded). I can't tell from your message
what the script should say to fix this. Thank you again!

Best,

Danny

On Thu, Jun 13, 2019 at 1:00 PM Christophe Pettus <[email-masked]> wrote:

> The difference is that putting double quotes around an SQL identifier
> makes the comparison type-sensitive, and allows for characters not
> otherwise allowed in identifiers:
>
> > xof=# select * from Tbl;
> >  i
> > ---
> > (0 rows)
> >
> You can use SQL.identifier, but you need to make sure you are getting the
> case right; in general, PostgreSQL types are all lower-case, and it's only
> the lack of double quotes that makes this work:
>
> > = sql.SQL("CREATE TABLE {} ({} {})".format(sql.Identifier(tbl_name),
> sql.Identifier(col_name), sql.Identifier(column_type)))
> >
> >     connection
> > .execute(query)
> >
> >
> > create_table
> type VARCHAR. However, when I attempt to run this, I get an error: 'type
> "VARCHAR" does not exist'. I assume psycopg2's built-in formatter is
> very clear that Python string concatenation should never be used for fear
> > Please let me know if I can fix either of these problems; I want to be
> able to dynamically feed types into SQL queries, and I want the tables
> created to be of the form tbl not "tbl". Thank you!
> >
> > Danny
> >
> >
>
> --
> -- Christophe Pettus
>    [email-masked]
>
>

---

From: David Raymond <[email-masked]>
Date: Thu, 13 Jun 2019 21:17:16 +0000

Could you let us know what version the server is, and also show a table listing? (Connect with psql and do a \d for example)

From: Daniel Cohen <[email-masked]> 
Sent: Thursday, June 13, 2019 4:55 PM
To: Christophe Pettus <[email-masked]>
Cc: [email-masked]
Subject: Re: Creating dynamically-typed tables using psycopg2's built-in formatting

Hi Christophe,

Thanks so much for your response. The uppercase --> lowercase fix worked for the types, but I'm still only getting tables that can be searched by double-quotations (i.e. > SELECT * FROM tbl; returns nothing, but > SELECT * FROM "tbl"; returns the table I uploaded). I can't tell from your message what the script should say to fix this. Thank you again!

Best,

Danny

---

From: Federico Di Gregorio <[email-masked]>
Date: Fri, 14 Jun 2019 14:40:20 +0200

On 6/13/19 10:54 PM, Daniel Cohen wrote:
> double-quotations (i.e. > SELECT * FROM tbl; returns nothing, but > 
> SELECT * FROM "tbl"; returns the table I uploaded). I can't tell from 
> your message what the script should say to fix this. Thank you again!

 From psql use the "\d" command to list all tables and check their 
names. Send the output here so that we can help you understand why your 
qury doesn't do what you expect.

federico

-- 
Federico Di Gregorio                         [email-masked]
DNDG srl                                                  http://dndg.it
                       The number of the beast: vi vi vi. -- Delexa Jones

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 14 Jun 2019 09:07:09 -0700

On 6/13/19 12:28 PM, Daniel Cohen wrote:
> |frompsycopg2 importconnect,sql connection 
> =connect(host="host",port="port",database="database",user="user",password="pw")defcreate_table(tbl_name,col_name,col_type):query 
> =sql.SQL("CREATE TABLE {} ({} 
> {})".format(sql.Identifier(tbl_name),sql.Identifier(col_name),sql.Identifier(column_type)))connection.execute(query)create_table('animals','name','VARCHAR')|
> 
> type VARCHAR. However, when I attempt to run this, I get an error: 
> *'type "VARCHAR" does not exist'*. I assume psycopg2's built-in 
> formatter is putting double quotes around the VARCHAR type when there 
> should not be any. Normally, I would just work around this myself, but 
> the documentation is/very/clear that Python string concatenation should 
> never be used for fear of SQL injection attacks. Security is a concern 
> for this project, so I would like to know if it's possible to create 
> dynamically-typed tables in this fashion using pyscopg2, and if not, 
> whether there exists another third-party API that can do so securely.
> 
> methodology, the sql.Identifier() function does not perform as I expect 
> it to. When I use it to dynamically feed in table names, for example, I 
> get varying results. See below:|
> |
> 
> |CREATE TABLE tbl AS SELECT * FROM other_tbl;|
> 
> |cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM 
> other_tbl").format(sql.Identifier(tbl))|
> 

I'm not seeing it:

cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM 
t1").format(sql.Identifier("tbl")))

test_(aklaver)> \d
...
public | t1                     | table    | aklaver
public | tbl                    | table    | aklaver
...

The question then becomes how is the variable tbl in your script being 
assigned to?

> |SELECT * FROM tbl;|
> ||

-- 
Adrian Klaver
[email-masked]

---

From: Christophe Pettus <[email-masked]>
Date: Fri, 14 Jun 2019 09:12:10 -0700

> On Jun 13, 2019, at 13:54, Daniel Cohen <[email-masked]> wrote:
> 
> Thanks so much for your response. The uppercase --> lowercase fix worked for the types, but I'm still only getting tables that can be searched by double-quotations (i.e. > SELECT * FROM tbl; returns nothing, but > SELECT * FROM "tbl"; returns the table I uploaded). 

By "returns nothing," do you mean you get an error, or that you get zero rows?

I'd connect to the database using psql and use \d to see what tables actually exist.  
--
-- Christophe Pettus
   [email-masked]

---

From: Daniel Cohen <[email-masked]>
Date: Fri, 14 Jun 2019 09:41:28 -0700

Hi again,

I realize the error was that I specified the name as "schema.tbl", and I
think because there was a period in the name, it converted to double-quote.
I tried again as just "tbl" and it worked perfectly. Thanks so much for all
your help.

Best,

Danny

On Fri, Jun 14, 2019 at 9:12 AM Christophe Pettus <[email-masked]> wrote:

> * FROM "tbl"; returns the table I uploaded).
>

---

From: Sebastiaan Mannem <[email-masked]>
Date: Fri, 14 Jun 2019 19:44:07 +0200

Yeah,
"schema.tbl" will search all schemas in search path for a table explicitly called "schema.tbl".
So that will never find a table called "tbl", and most probably never find a table at all.

And without quotes, it will search for a table called "tbl" in a schema called "schema".
So your issue makes perfect sense now.

FWIW, if you where to query "schema"."tbl", it would look for a table (or view) called "tbl" in a schema called "schema",
Which seems to be what you want.

Sebastiaan Alexander Mannem
Senior Consultant
Anthony Fokkerweg 1
1059 CM Amsterdam, The Netherlands
T: +31 6 82521560
www.edbpostgres.com

> On 14 Jun 2019, at 18:41, Daniel Cohen <[email-masked]> wrote:
> 
> Hi again,
> 
> I realize the error was that I specified the name as "schema.tbl", and I think because there was a period in the name, it converted to double-quote. I tried again as just "tbl" and it worked perfectly. Thanks so much for all your help.
> 
> Best,
> 
> Danny 
> 
> On Fri, Jun 14, 2019 at 9:12 AM Christophe Pettus <[email-masked] <mailto:[email-masked]>> wrote:
> 
> 
> > On Jun 13, 2019, at 13:54, Daniel Cohen <[email-masked] <mailto:[email-masked]>> wrote:
> > 
>    [email-masked] <mailto:[email-masked]>
>

---

From: Daniele Varrazzo <[email-masked]>
Date: Fri, 14 Jun 2019 20:12:54 +0100

> So that will never find a table called "tbl", and most probably never find

Also note that from psycopg 2.8 you can use Identifier("schema", "tbl") to
represent a dot-separated sequence of identifiers.

http://initd.org/psycopg/docs/sql.html#psycopg2.sql.Identifier

-- Daniele

---

From: Daniel Cohen <[email-masked]>
Date: Fri, 14 Jun 2019 13:41:02 -0700

Awesome, thanks!