jackc / jackc/pgx

Should I call pgx.Identifier.Sanitize method explicitly when I build the sql string?

Open
#764 12 comments 0 reactions 0 assignees View on GitHub
question
Dominant language
Go
Stars
14.3k
Forks
1.1k
Avg merge
6d 9h
Merged PRs (30d)
11

Description

I write some functions for building SQL string, should I call `pgx.Identifier.Sanitize` method explicitly to sanitize the identifiers? Or, just call `strings.Join(cols, ",")` and `pgx` will sanitize the column Identifier internally? Need clarification, thanks.
E.g.

`sql.go`:
```go
func BuildInsertSQL(tableName string, cols []string, vals []interface{}) string {
sqlStatement := "insert into " + Sanitize(tableName) + "(" + MulSanitize(cols) + ") values ("
for i := 1; i <= len(vals); i++ {
if i == len(vals) {
sqlStatement = sqlStatement + "$" + strconv.Itoa(i) + ") returning *"
break
}
if i%len(cols) != 0 {
sqlStatement = sqlStatement + "$" + strconv.Itoa(i) + ","
} else {
sqlStatement = sqlStatement + "$" + strconv.Itoa(i) + "),("
}
}
return sqlStatement
}

func Sanitize(ident string) string {
return pgx.Identifier.Sanitize([]string{ident})
}

func MulSanitize(idents []string) string {
var ret []string
for _, id := range idents {
ret = append(ret, Sanitize(id))
}
return strings.Join(ret, ",")
}
```

Unit tests:
`sql_test.go`:
```go
func TestBuildInsertSQL(t *testing.T) {
type arg struct {
tableName string
cols []string
vals []interface{}
}
tests := []struct{
name string
arg arg
want string
} {
{
name: "should return valid sql string for inserting one row",
arg: arg{
tableName: "TEST",
cols: []string{"id", "name", "email"},
vals: []interface{}{1, "a", "e@gmail.com"},
},
want: "insert into \"TEST\"(\"id\",\"name\",\"email\") values ($1,$2,$3) returning *",
},
{
name: "should return valid sql string for bulk inserting",
arg: arg{
tableName: "TEST",
cols: []string{"id", "name", "email"},
vals: []interface{}{1, "a", "e@gmail.com", 2, "b", "d@gmail.com"},
},
want: "insert into \"TEST\"(\"id\",\"name\",\"email\") values ($1,$2,$3),($4,$5,$6) returning *",
},
}

for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got := sql.BuildInsertSQL(tt.arg.tableName, tt.arg.cols, tt.arg.vals)
test.Equals(t, tt.want, got)
})
}
}

func TestSanitize(t *testing.T) {
tests := []struct{
name string
arg string
want string
} {
{"should return sanitized identifier", "USER", "\"USER\""},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got := sql.Sanitize(tt.arg)
test.Equals(t, tt.want, got)
})
}
}

func TestMulSanitize(t *testing.T) {
tests := []struct{
name string
arg []string
want string
} {
{"should return sanitized identifier", []string{"id", "name"}, "\"id\",\"name\""},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got := sql.MulSanitize(tt.arg)
test.Equals(t, tt.want, got)
})
}
}
```

Contributor guide

Open the contributing guide

Research direction

Start by reading the pgx.Identifier.Sanitize reference alongside the sql.go example and the cases in sql_test.go. Determine whether the issue calls for documentation or only an answer about identifier handling; done should be a clear, project-backed clarification that resolves the distinction between explicit sanitization and strings.Join.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases
Issue type
Documentation
Difficulty
1/5
Estimated time
Under an hour
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
20/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.