英文:
Go Select In using a list of uuid strings
问题
我有一个 UUID 字符串列表，我想用它来过滤查询。如果我循环遍历列表中的元素，我可以使查询正常工作，像这样：
for i, fileUID := range fileUIDs {
db.Exec("DELETE FROM files WHERE uid = $1::uuid", fileUID)
}
但是我想使用列表来使其工作：
db.Exec("DELETE FROM files WHERE uid IN $1::uuid[]", fileUIDs)
这种方式可行吗？我似乎无法使其工作。
我尝试了在 https://stackoverflow.com/questions/20271123/how-to-execute-an-in-lookup-in-sql-using-golang 中的解决方案，但是当我使用普通的 ? 时，我会得到类似 pq: syntax error at or near "," 的错误，当我使用 ?:uuid 时，我会得到类似 pq: syntax error at or near "::" 的错误。我使用了以下代码：
fileUIDArgs := make([]interface{}, len(fileUIDs))
for i, fileUID := range fileUIDs {
fileUIDArgs[i] = interface{}(fileUID)
}
// 也尝试过使用 "?::uuid"
myPsql := "DELETE FROM files WHERE uid IN (" + "?" + strings.Repeat(",?", len(uidStrings)-1) + ")"
db.Exec(myPsql, fileUIDArgs...)
请注意，我只翻译了代码部分，其他内容不做翻译。
英文:
I have a list of uuid strings that I want to use to filter a query. I can get the query to work if I loop over elements in my list like so:
for i, fileUID := range fileUIDs {
db.Exec("DELETE FROM files WHERE uid = $1::uuid", fileUID)
}
But I'd like to get it working using the list:
db.Exec("DELETE FROM files WHERE uid IN $1::uuid[]", fileUIDs)
Is this possible? I can't seem to get it working.
I tried the solution in https://stackoverflow.com/questions/20271123/how-to-execute-an-in-lookup-in-sql-using-golang but I get errors like pq: syntax error at or near "," when using plain ? or pq: syntax error at or near "::" when using ?:uuid. I used the following:
fileUIDArgs := make([]interface{}, len(fileUIDs))
for i, fileUID := range fileUIDs {
fileUIDArgs[i] = interface{}(fileUID)
}
//also tried using "?::uuid"
myPsql := "DELETE FROM files WHERE uid IN (" + "?" + strings.Repeat(",?", len(uidStrings)-1) + ")"
db.Exec(myPsql, fileUIDArgs...)
答案1
得分: 2
这是一个旧问题，但为了那些将被引导到这里的人，如果你正在使用Postgres数据库，你可以使用这种更简单的方法：
DELETE FROM files WHERE uid=ANY($1);
$1 是一个UUID数组，所以你的查询变成了：
toBeDeleted:= []uuid.UUID{....}
_, err = tx.Exec("DELETE FROM files WHERE uid=ANY($1);", toBeDeleted)
//或者
_, err = tx.Exec("DELETE FROM files WHERE uid=ANY($1);", pq.Array(toBeDeleted))
任何一种方法都应该适用于你。
英文:
This is an old question but for the sake of people who will be directed here, if you are using postgres db you can use this easier way:
DELETE FROM files WHERE uid=ANY($1);
$1 is an array of uuids. so your query becomes:
toBeDeleted:= []uuid.UUID{....}
_, err = tx.Exec("UDELETE FROM files WHERE uid=ANY($1);",toBeDeleted)
//or
_, err = tx.Exec("UDELETE FROM files WHERE uid=ANY($1);",pq.Array(toBeDeleted))
either should work for you.
答案2
得分: 1
使用fmt包确保你的uuid不包含任何SQL注入。
ary := []string{
"1442edc8-9e1f-4213-8622-5610cdd66790",
"0506ca17-d254-40b3-9ef0-bca6d15ad49d",
"e46f3708-6da5-4b82-9c92-f89394dffe5d",
"fb8bf848-73a2-4253-9fa3-e9d5e16ef94a",
"84691fa5-3391-4c02-9b16-82389331b7ac",
"adba3c9d-b4ab-4e62-a650-414970645be7",
}
query := fmt.Sprintf(`DELETE FROM files WHERE uid IN ('%s'::uuid);`,
strings.Join(ary, "'::uuid,'"))
db.Exec(query) // etc
消除潜在的SQL注入风险：
ary := []string{ /* uuid列表 */ }
query := `DELETE FROM files WHERE uid IN (`
aryInterfaces := make([]interface{}, len(ary))
for i, v := range ary {
query += "$" + strconv.FormatInt(int64(i+1), 10)
if i < len(ary)-1 {
query += ","
}
aryInterfaces[i] = v
}
query += ")"
db.Exec(query, aryInterface...)
额外内容
PostgreSQL使用$1, $2, $3等代替?, ?, ?。这里有一个小的辅助函数，以及这里是它的概念验证。
英文:
Using fmt. Make sure that your uuids doesn't contain any SQL-injection.
<!-- lang:lang-go -->
ary := []string{
"1442edc8-9e1f-4213-8622-5610cdd66790",
"0506ca17-d254-40b3-9ef0-bca6d15ad49d",
"e46f3708-6da5-4b82-9c92-f89394dffe5d",
"fb8bf848-73a2-4253-9fa3-e9d5e16ef94a",
"84691fa5-3391-4c02-9b16-82389331b7ac",
"adba3c9d-b4ab-4e62-a650-414970645be7",
}
query := fmt.Sprintf(`DELETE FROM files WHERE uid IN ('%s'::uuid);`,
strings.Join(ary, "'::uuid,'"))
db.Exec(query) // etc
Rid out of potential SQL-injections:
ary := []string{ /* list of uuids */ }
query := `DELETE FROM files WHERE uid IN (`
aryInterfaces := make([]interface{}, len(ary))
for i, v := range ary {
query += "$" + strconv.FormatInt(int64(i+1), 10)
if i < len(ary)-1 {
query += ","
}
aryInterfaces[i] = v
}
query += ")"
db.Exec(query, aryInterface...)
BONUS
Postgresql uses $1, $2, $3 etc instead of ?, ?, ?. Here is a little helper function and here is its proof of concept.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论