使用 pgx 在 postgres 中创建用户 (SQLSTATE 42601)

Question

我正在尝试在 postgres 中创建一个用户。目前正在尝试使用 https://github.com/jackc/pgx 作为连接到的驱动程序数据库。我有以下内容

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)
    }
}

但我收到此错误：“$1”处或附近的语法错误（SQLSTATE 42601）

我不明白这里有什么问题？

Answer 1

Postgres 准备好的语句（带有 $1、$2 等的语句）仅支持 SELECT、INSERT、UPDATE、DELETE、MERGE 或 VALUES 语句（请参阅 PREPARE 文档）。

由于问题是关于 CREATE USER 的，我们需要自己构建 SQL 语句，正确地转义不同的部分。用户名是一个标识符（使用 Identifier.Sanitize( ))，密码是一个字符串(使用 PgConn.EscapeString()）。以下程序正确转义这两个部分以创建 sql 字符串，然后将其按原样传递给 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)
    }
}

Answer 2

“我不明白这里有什么问题？” -- 问题是 位置参数只能用于值，不能用于标识符。

位置参数引用用于指示从外部提供给 SQL 语句的值。

您无法在 CREATE USER 中使用位置参数，就像无法在 SELECT t. 中使用位置参数一样。 FROM <表名> 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)
}

如果user_name不是硬编码的，而是来自未知的用户输入，您需要自己验证它以避免SQL注入的可能性。这不是一项艰巨的任务，因为词汇结构标识符仅限于一小组规则，如果您愿意，您可以进一步将其减少到更小的子集（例如，不允许变音符号和非拉丁字母）：

SQL 标识符和关键字必须以字母（az，但也
带变音符号的字母和非拉丁字母）或下划线
(_)。标识符或关键字中的后续字符可以是
字母、下划线、数字 (0-9) 或美元符号 ($)。注意
根据以下字母，标识符中不允许使用美元符号
SQL 标准，因此它们的使用可能会降低应用程序的性能
便携的。 SQL 标准不会定义包含以下内容的关键字
数字或以下划线开头或结尾，因此此标识符
形式可以避免与未来扩展可能发生的冲突
标准。