python adds "E" to string

Question

This string:

"CREATE USER %s PASSWORD %s", (user, pw)

always gets expanded to:

CREATE USER E'someuser' PASSWORD E'somepassword'

Can anyone tell me why?

Edit: The expanded string above is the string my database gives me back in the error message. I'm using psycopg2 to access my postgres database. The real code looks like this:

conn=psycopg2.connect(user=adminuser, password=adminpass, host=host)
cur = conn.cursor()

#user and pw are simple standard python strings the function gets as parameter
cur.execute("CREATE USER %s PASSWORD %s", (user, pw))
conn.commit()

Hirsh

Can you give full code with user and pw declarations? – Migraine

This must be caused by the behavior of the __str__ function of the type of user and pw. – Haaf

Is that a comma after the string literal? If so, the expression is simply a nested tuple, and nothing ever gets expanded. Please show the real code. – Haaf

Answer 1

Not only the E but the quotes appear to come from whatever type user and pw have. %s simply does what str() does, which may fall back to repr(), both of which have corresponding methods __str__ and __repr__. Also, that isn't the code that generates your result (I'd assumed there was a %, but now see only a comma). Please expand your question with actual code, types and values.

Addendum: Considering that it looks like SQL, I'd hazard a guess that you're seeing escape string constants, likely properly generated by your database interface module or library.

Dichroism

You are right. I'm using Psycopg2 and this is the string my database gives me back as an error. I'm going to put the actual code in the question now. – Hirsh

It would appear the problem is %s is used for data fields, and a user name in CREATE USER appears to be an identifier - so a string literal won't work there. Psycopg2 does not appear to have any validation or quoting functions for such identifiers. – Dichroism

Relevant psycopg discussion. – Dichroism

@Yann That discussion moved to http://osdir.com/ml/python-db-psycopg-devel/2009-03/msg00011.html since ... – Reeding

Answer 2

To pass identifiers to postgresql through psycopg use AsIs from the extensions module

from psycopg2.extensions import AsIs
import psycopg2
connection = psycopg2.connect(database='db', user='user')
cur = connection.cursor()
cur.mogrify(
    'CREATE USER %s PASSWORD %s', (AsIs('someuser'), AsIs('somepassword'))
    )
'CREATE USER someuser PASSWORD somepassword'

That works also for passing conditions to clauses like order by:

cur.mogrify(
    'select * from t order by %s', (AsIs('some_column, another column desc'),)
    )
'select * from t order by some_column, another column desc'

Admonitory

Too bad it defeats the point of query parametrization: >>> cursor.mogrify('CREATE USER %s PASSWORD %s', (AsIs('someuser'), AsIs('somepassword; drop table users;'))) 'CREATE USER someuser PASSWORD somepassword; drop table users;' It should be validated with something like: 'input_table'.replace('_', '').isalnum(). (Beware, not tested.) – Spatola

@MichałPawłowski: Yes, AsIs should not be used for user entered data. And using any not thoroughly tested solution, as yours, do to so, is a recipe for disaster. – Admonitory

Answer 3

As the OP's edit reveals he's using PostgreSQL, the docs for it are relevant, and they say:

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'.

In other words, psycopg is correctly generating escape string constants for your strings (so that, as the docs also say:

Within an escape string, a backslash character () begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value.

(which as it happens are also the escape conventions of non-raw Python string literals).

The OP's error clearly has nothing to do with that, and, besides the excellent idea of studying PostgreSQL's excellent docs, he should not worry about that E'...' form in this case;-).

Dichlorodifluoromethane

I just read that by myself. So the string is correct, but why does my postgres server give it back to me with a syntax error at the E? – Hirsh

@Kai, maybe you're using an obsolete version of PgSQL? Or perhaps @Yann's comments on the other answer are right and you need an identifier, not a quoted string at all, in CREATE USER (in which case you'll have to insert it by string manipulation, before the execute, to avoid the escaping -- be sure to check it very thoroughly against SQL injections attacks of course!!!-). – Dichlorodifluoromethane

Looks like Yann's comment is right. Don't like this but at the moment I don't need to handle user generated values in these statements. So i will just use the standard python string manipulations. Still, I don't like it ;) – Hirsh

Answer 4

Before attempting something like:

statement = "CREATE USER %s PASSWORD %s" % (user, pw)

Please ensure you read: http://www.initd.org/psycopg/docs/usage.html

Basically the issue is that if you are accepting user input (I assume so as someone is entering in the user & pw) you are likely leaving yourself open to SQL injection.

As PsyCopg2 states:

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

As has been identified, Postgres (or Psycopg2) doesn't seem to provide a good answer to escaping identifiers. In my opinion, the best way to resolve this is to provide a 'whitelist' filtering method.

ie: Identify what characters are allowed in a 'user' and a 'pw'. (perhaps A-Za-z0-9_). Be careful that you don't include escape characters (' or ;, etc..) or if you do, that you escape these values.

Cavity

Postgres has the quote_ident() function for quoting identifiers postgresql.org/docs/current/static/… – Ladonnalady