15

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()
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Kai
  • 2,205
  • 3
  • 32
  • 43

4 Answers4

17

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'
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 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.) – Michał Pawłowski Dec 28 '15 at 16:34
  • @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. – Clodoaldo Neto Dec 28 '15 at 17:30
10

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;-).

Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • 1
    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? – Kai Aug 01 '10 at 16:18
  • @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!!!-). – Alex Martelli Aug 01 '10 at 16:28
  • 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 ;) – Kai Aug 01 '10 at 16:41
8

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.

Community
  • 1
  • 1
Yann Vernier
  • 15,414
  • 2
  • 28
  • 26
  • 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. – Kai Aug 01 '10 at 15:12
  • It would appear the problem is %s is used for data fields, and a user name in [CREATE USER](http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html) 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. – Yann Vernier Aug 01 '10 at 15:56
  • 1
    [Relevant psycopg discussion.](http://lists.initd.org/pipermail/psycopg/2009-March/thread.html#6344) – Yann Vernier Aug 01 '10 at 16:02
  • 1
    @Yann That discussion moved to [http://osdir.com/ml/python-db-psycopg-devel/2009-03/msg00011.html](http://osdir.com/ml/python-db-psycopg-devel/2009-03/msg00011.html) since ... – ᴠɪɴᴄᴇɴᴛ Sep 02 '14 at 13:09
2

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.

Richard G
  • 21
  • 1
  • Postgres has the `quote_ident()` function for quoting identifiers https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE – raphael Aug 18 '16 at 21:26