psycopg2 E' on table, field and schema

Question

I am having the same problem as: python adds "E" to string

All the answers given are relevant, but I am breaking my neck on this one.

The problem is that psycopg2 not only escapes values, but also schema, table and column names like this:

CREATE TABLE E'Tablename' (E'identificatie' VARCHAR(16))

Which it simply shouldn't! How van I get rid of the E and '' for table names and columns but maintain them for field values?

the alternative

'CREATE TABLE ' + tablename + ' (' + fieldname... %

makes it vulnerable to sql injection all over again.

Stuck between a rock and a hard place..

Where are the values for `tablename` and `fieldname` coming from that might make it vulnerable to injection? — MattH, Jun 09 '11 at 14:17
See [my answer](http://stackoverflow.com/a/13891511/131874) to that question — Clodoaldo Neto, Dec 15 '12 at 10:52

score 1 · Accepted Answer · answered Jun 09 '11 at 18:57

1

It is, for better or worse, generally not supported by the Python interfaces and Psycopg in particular to substitute user-supplied identifiers into SQL commands. You will have to roll your own. It can be done with a few lines of code.

answered Jun 09 '11 at 18:57

Peter Eisentraut

35,221
12
85
90

score 0 · Answer 2 · answered Jun 15 '11 at 05:17

Ok, thanks Peter, at least I know not to look any further. I decided to take a different approach:

Use a script file to generate the database instead of generating it from code. This will make it more easy to have "versioning" on the database.

Meanwhile, I am taking a look at sqlalchemy http://www.sqlalchemy.org/ which pretty much does what I want but is currently a step to far as it requires a drastic restructure of the application I am rebuilding

psycopg2 E' on table, field and schema

2 Answers2

Linked