right way to quote sql queries including table names, usernames etc

[arekm]

Hello.

What's a proper way to quote and construct sql queries like this:

CREATE DATABASE db_name OWNER db_user ENCODING encoding

"Identifiers usually represent names of database objects, such as tables or fields."

Does that apply to usernames, encoding etc, too? Like

query = psycopg2.sql.SQL("CREATE DATABASE {} OWNER {} ENCODING {}).format(psycopg2.sql.Identifier(db_name),
                psycopg2.sql.Identifier(db_user), psycopg2.sql.Identifier(encoding))

Other problematic queries:

ALTER ROLE abc WITH PASSWORD def

[dvarrazzo]

If the value doesn't take single quotes, it is likely a PostgreSQL name value, and can be represented adequately by the sql.Identifier object. Database name, username, encoding are all representable as identifiers. See Identifiers PostgreSQL docs.

If the value must be surrounded by single quotes, it is a string literal: see PostgreSQL docs. You can use the sql.Literal object to represent it. For instance your second query can be represented by:

sql.SQL("ALTER ROLE {} WITH PASSWORD {}").format(sql.Identifier("abc"), sql.Literal("def")).as_string(conn)
# ALTER ROLE "abc" WITH PASSWORD 'def'