Create User in postgres with pgx (SQLSTATE 42601)

[Moulick]

I am trying to create a user in postgres. I have the below

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v4"
)

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "host=localhost port=5432 user=postgres password=postgres dbname=postgres")
    if err != nil {
        panic(err)
    }
    defer conn.Close(ctx)

    // create user
    _, err = conn.Exec(ctx, "CREATE USER $1 WITH PASSWORD $2", "moulick", "testpass")
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
}

But I get this ERROR: syntax error at or near "$1" (SQLSTATE 42601)

I don't get what's the problem here ?

[jackc]

Prepared or parameterized statements are only possible with SELECT, INSERT, UPDATE, DELETE, and VALUES. (see https://www.postgresql.org/docs/current/sql-prepare.html). You will need to use string interpolation.

[Moulick]

@jackc though, now, how do I prevent against SQL injection attacks if I am using string interpolation?

[paudley]

You'll have to sanitize the strong yourself first. There is a fairly long-running request for non-driver dependant quoting in Go Issue #18478 with a few potential solutions that might be helpful.

[Moulick]

@paudley there seems to be a Santinize function in pgx, would that not be good enough?

[jackc]

https://pkg.go.dev/github.com/jackc/pgx/v4#Identifier will do what you want.

[maxlengdell]

Hi! Another question related to this, why does it not work with positional arguments for the password?

s := "CREATE USER username WITH PASSWORD $1;"
_, err := conn.Exec(context.Background(), s)

Throws error:
ERROR: syntax error at or near "$1" (SQLSTATE 42601)"
I can somewhat understand why the username is not allowed as positional argument but the password is not an identifier right?

[jackc]

@maxlengdell See earlier reply: #1173 (comment)