psycopg2 having double quotes around placeholder

Question

Postgres CLI:

Using single quotes around username I get an error:

some_db=> CREATE USER 'foobar' WITH PASSWORD 'pass';
                      ^      ^
ERROR:  syntax error at or near "'foobar'"
LINE 1: CREATE USER 'foobar' WITH PASSWORD 'pass';

But double quotes are fine:

some_db=> CREATE USER "foobar" WITH PASSWORD 'pass';
                      ^      ^
CREATE ROLE

Python code:

When executing this code piece:

db_connection.cursor().execute(
  "CREATE USER %s WITH PASSWORD %s",
  (username, password,)
)

I also get an error:

Error creating User: syntax error at or near "'foobar'"
LINE 1: CREATE USER 'foobar' WITH PASSWORD '9f...

Which is expected as single quotes are added automatically when query parameters are used.

Question: is there a way to configure psycopg2 to use double quotes instead or I need to embed username variable directly in query? Or may be there's another solution? If the second case is the way to go, I need to escape it too. What do you think?

Answer 1

From the psycopg2 module sql:

import psycopg2
from psycopg2 import sql
con = psycopg2.connect("dbname=test user=postgres port=5432")
cur = con.cursor()
cur.execute(sql.SQL("create user {} with password %s").format(sql.Identifier('test_usr')), ['test'])
con.commit()
cur.execute("select usename from pg_user where usename = 'test_usr'")
cur.fetchone()
('test_usr',)

This is the safe way for building dynamic SQL.

UPDATE Clarification of what is going on:

user_sql = sql.SQL("create user {} with password %s").format(sql.Identifier('test_usr'))

print(user_sql.as_string(con))
create user "test_usr" with password %s

The sql module builds a SQL string where the component parts are properly escaped to make them safe. Caveat, it is possible to do unsafe things using sql.SQL when not properly formatting the variable bits:

class psycopg2.sql.SQL(string)

A Composable representing a snippet of SQL statement.

SQL exposes join() and format() methods useful to create a template where to merge variable parts of a query (for instance field or table names).

The string doesn’t undergo any form of escaping, so it is not suitable to represent variable identifiers or values: you should only use it to pass constant strings representing templates or snippets of SQL statements; use other objects such as Identifier or Literal to represent variable parts.

Be sure and read this sql to make sure you are doing the correct thing.