Go Select In using a list of uuid strings

Question

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

Possible duplicate of [How to execute an IN lookup in SQL using Golang?](http://stackoverflow.com/questions/20271123/how-to-execute-an-in-lookup-in-sql-using-golang) — Brian, Mar 22 '16 at 16:34
This part is breaking `IN (", "?"+`. The comma is outside of the string. Try `IN (?"+` — Brian, Mar 22 '16 at 20:18
Sorry, that was a copy paste error, it's fixed in the question. — Charles L., Mar 22 '16 at 20:41

score 2 · Answer 1 · answered Feb 10 '21 at 15:26

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.

Ivan Black · Accepted Answer · 2016-03-22T22:25:55.393

1

Using fmt. Make sure that your uuids doesn't contain any SQL-injection.

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

play.golang.org

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

play.golang.org

BONUS Postgresql uses $1, $2, $3 etc instead of ?, ?, ?. Here is a little helper function and here is its proof of concept.

edited Mar 22 '16 at 22:25

answered Mar 22 '16 at 20:23

Ivan Black

4,827
1
35
33

Before calling this the uuids are converted to 128 bit ints, then formatted back into uid strings, so I am safe, thanks! – Charles L. Mar 22 '16 at 21:08
This is answer for Java and operator in is used in select, but does not matter. Limitation is the same for each language http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives – lofcek Mar 22 '16 at 21:40
Do not use `fmt.Sprintf` for building queries ever. Even with the proper input validation the potential for sql injection is too great of a risk. The below answer by @Patrick Aboagye is the proper way to go about this now. – JDWardle Sep 17 '21 at 23:25

Go Select In using a list of uuid strings

2 Answers2