Create User in postgres with pgx (SQLSTATE 42601)

Question

I am trying to create a user in postgres. currently trying to use https://github.com/jackc/pgx as the driver to connect to the db. 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 ?

mkopriva · Accepted Answer · 2022-03-26 12:01:12Z

5

"I don't get what's the problem here ?" -- The problem is that positional parameters can be used only for values, and not for identifiers.

A positional parameter reference is used to indicate a value that is supplied externally to an SQL statement.

You cannot use positional parameters in CREATE USER <user_name> the same way you cannot use them in SELECT t.<column_name> FROM <table_name> AS t.

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

If the user_name is not hardcoded, but instead comes from an unknown user input, you need to validate it yourself to avoid the possibility of SQL injections. This is not a difficult task since the lexical structure of identifiers is limited to a small set of rules, which you can further reduce to an even smaller subset if you like (e.g. disallowing diacritical marks and non-Latin letters):

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

edited Mar 26, 2022 at 12:01

answered Mar 26, 2022 at 11:52

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tiago Peczenyj Over a year ago

Please use some function to concatenate strings. On this example, ok, but it can lead to sql injection

mkopriva Over a year ago

@TiagoPeczenyj What is the point you are trying to make? The answer already explicitly addresses SQL injections and how to avoid them in this specific case, does it not? And OP seemingly already knows that it's important to use positional parameters, wherever possible, to avoid SQL injections.

Tiago Peczenyj Over a year ago

I was thinking in something like this: github.com/feiin/sqlstring

Moulick Over a year ago

Yeah, SQL injection was what I was afraid of. But seems that pgx/v4 has a Sanitize function that I can probably use to prevent against SQL injection.

dubek Over a year ago

Note that the use of $1 makes pgx use PREPARE, and that works only for SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES statements (docs). I think you must concatenate both the username and the password to create one SQL string to pass to Exec(). The username can be escaped with pgx.Identifier.Sanitizer(); the password needs a generic SQL string like PgConn.EscapeString().

dubek · Accepted Answer · 2024-05-03 20:55:51Z

Postgres prepared statements (those with $1, $2, etc.) are supported only for SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES statements (see documentation of PREPARE).

Since the question is about CREATE USER, we need to build the SQL statement ourselves, properly escaping the different parts. The username is an identifier (use Identifier.Sanitize()), and the password is a string (use PgConn.EscapeString()). The following program escapes both parts correctly to create the sql string, which is then passed as-is to Exec().

package main

import (
    "context"
    "fmt"

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

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)

    username := "user with spaces"
    password := "test pass ' with \" special chars"

    escapedUsername := pgx.Identifier{username}.Sanitize()
    escapedPassword, err := conn.PgConn().EscapeString(password)
    if err != nil {
        panic(err)
    }
    sql := fmt.Sprintf("CREATE USER %s WITH PASSWORD '%s'", escapedUsername, escapedPassword)
    _, err = conn.Exec(ctx, sql)
    if err != nil {
        panic(err)
    }
}

Collectives™ on Stack Overflow

Create User in postgres with pgx (SQLSTATE 42601)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related