英文:
Create User in postgres with pgx (SQLSTATE 42601)
问题
我正在尝试在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)
// 创建用户
_, err = conn.Exec(ctx, "CREATE USER $1 WITH PASSWORD $2", "moulick", "testpass")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
}
但是我得到了这个错误 ERROR: syntax error at or near "$1" (SQLSTATE 42601)。
我不明白问题出在哪里？
英文:
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 ?
答案1
得分: 3
"我不明白问题出在哪里?" -- 问题在于位置参数只能用于值，而不能用于标识符。
> 位置参数引用用于指示外部提供给SQL语句的值。
你不能像在SELECT t.<column_name> FROM <table_name> AS t中一样，在CREATE USER <user_name>中使用位置参数。
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标识符和关键字必须以字母（a-z，还包括带变音符号和非拉丁字母的字母）或下划线（_）开头。标识符或关键字中的后续字符可以是字母、下划线、数字（0-9）或美元符号（$）。请注意，根据SQL标准的规定，标识符中不允许使用美元符号，因此它们的使用可能会降低应用程序的可移植性。SQL标准不会定义一个包含数字或以下划线开头或结尾的关键字，因此这种形式的标识符不会与将来的标准扩展可能发生冲突。
英文:
"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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论