Should I call pgx.Identifier.Sanitize method explicitly when I build the sql string?
- 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
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