CODEWITHSUNDEEP
postgresqlddlpg8000
kdgregory
kdgregory

Reputation: 39606

Does PG8000 or Postgres client-server protocol not support bind parameters in DDL statements?

I'm trying to create a new user with PG8000. This code works, but it puts passwords in the server logs, which is not desirable (I'm not concerned with SQL injection for this usecase):

with pg8000.dbapi.connect(**CONNECTION_INFO) as conn:
    csr = conn.cursor()
    csr.execute("create user example password 'password-123'")
    conn.commit()

I can then select information about that user with a query that uses bind parameters:

with pg8000.dbapi.connect(**CONNECTION_INFO) as conn:
    csr = conn.cursor()
    csr.execute("select * from pg_user where usename = %s", ("example",))
    result = csr.fetchall()

However, if I try to create my user with a bind parameter:

with pg8000.dbapi.connect(**CONNECTION_INFO) as conn:
    csr = conn.cursor()
    csr.execute("create user example password %s", ("password-123",))
    conn.commit()

The request fails with this client-side error:

DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near "$1"', 'P': '30', 'F': 'scan.l', 'L': '1145', 'R': 'scanner_yyerror'}

And this server-side error:

2022-08-26 12:30:02.029 UTC [205] LOG:  statement: begin transaction
2022-08-26 12:30:02.029 UTC [205] ERROR:  syntax error at or near "$1" at character 30
2022-08-26 12:30:02.029 UTC [205] STATEMENT:  create user example password $1

The same thing happens if I use the PG8000 "native" interface.

If I switch to psycopg2, I am able to execute the create command as written with parameters, however the server log indicates that the client did the parameter replacement, and sent a literal SQL statement:

2022-08-26 12:30:55.317 UTC [206] LOG:  statement: BEGIN
2022-08-26 12:30:55.317 UTC [206] LOG:  statement: create user example2 password 'password-123'
2022-08-26 12:30:55.317 UTC [206] LOG:  statement: COMMIT

Upvotes: 0

Views: 290

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248295

You found the answer that you cannot use parameters with DDL statements, and you properly used PREPARE to test that assertion.

Let me add that this is documented, although not necessarily in a place where you would look first: in the documentation for PREPARE. This tells you which statements are allowed:

statement

       Any SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES statement.

No other statements support parameters in PostgreSQL.

Upvotes: 0

kdgregory
kdgregory

Reputation: 39606

It appears that Postgres as a whole does not support invoking DDL from prepared statements. I was unable to find any authoritative documentation on this: not in the page for PREPARE, not in the Overview of PostgreSQL Internals, and not in the description of the client-server protocol. It is, however, easy to demonstrate:

postgres=# prepare ddl_example ( varchar(255) ) as
postgres-# create user example password '$1';
ERROR:  syntax error at or near "create"
LINE 2: create user example password '$1';
        ^

In some of the pages that turned up from Googling, people commented that DDL doesn't go through the planner, which is where bind variables are applied. This makes sense, and if someone has a reference to official Postgres docs that says so, please answer with that link.

Upvotes: 1

Related Questions