Golang db query using slice IN clause

Question

Can someone explain to me why this does not work?

inq := "6,7" //strings.Join(artIds, ",")
rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (?)", inq)

And this does

rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (6,7)", inq)

I'm trying to do a simple IN clause with a slice of ints, and every solution suggested doesn't seem very idiomatic

Tried to do this, but the problem appears to be the string substitution.

inq := strings.Join(artIds, ",")

I'm a bit surprised that go doesn't seem to have a graceful way to handle this query.

*Why* is because `inq` is a string so if you parameterize it your going to end up with `IN ('6,7')` which is valid but a very different thing. As a general rule an `IN` cannot be parameterized. Have you seen: https://stackoverflow.com/questions/20271123/how-to-execute-an-in-lookup-in-sql-using-golang — Alex K., Jul 26 '18 at 12:25
Thanks, I suspected something like that. Yeah I saw the interface repeat thing, but it just smells a bit funky to me. — Michael Mallett, Jul 27 '18 at 05:14

score 4 · Answer 1 · answered Jul 26 '18 at 12:35

Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:

SELECT * FROM users WHERE level IN (?);

When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice

var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)

There is a way to handle these types of queries using sqlx package which provide more control over database queries.

This pattern is possible by first processing the query with sqlx.In:

var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)

For more information Go through Godoc for InQueries

Thanks for the information, very valuable. That package looks good. — Michael Mallett, Jul 27 '18 at 05:15

Kenny Grant · Accepted Answer · 2018-07-26T19:55:19.693

3

If you have been careful to build your inq string from real ints (to avoid injection), you can just build the string yourself and avoid using ?:

inq := "6,7" 
sql := fmt.Sprintf("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (%s)",inq)
rows, err := db.Query(sql)

If you do it a lot, better to have a WhereIn function that does this for you, or use an orm. Be careful which args you accept though, as if you accept arbitrary strings anything could be injected.

edited Jul 26 '18 at 19:55

answered Jul 26 '18 at 19:38

Kenny Grant

9,360
2
33
47

I'm quite new to go so this is something I would be a bit wary of in a dynamically typed language. However, in my code inq is generated from a slice of type ints, so they are definitely ints. Is this good practice tho? – Michael Mallett Jul 26 '18 at 23:07
If you make sure they are ints, I see no problem with it. Any function you write should take ints, not a string. – Kenny Grant Jul 27 '18 at 03:47
Ok thanks, this seems like the simplest solution. It's actually for a technical test so I might just caveat the hell out of it and show it's been considered. – Michael Mallett Jul 27 '18 at 05:13

score 3 · Answer 3 · answered Jun 09 '21 at 20:21

3

You need the number of "?" in the "IN" clause to match the number of arguments , so you need to do something like this:

inq := "6,7" //strings.Join(artIds, ",")
qms := strings.Repeat("?,", len(inq))
qms = params[:len(params)-1] // remove the trailing ","

rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (" + qms + ")", inq)

answered Jun 09 '21 at 20:21

Toivo Lainevool

151
4

Thanks, this is the answer i was looking for. – Benjamin Collins Jul 20 '21 at 14:13

score 0 · Answer 4 · answered May 11 '23 at 08:51

I just had the same problem then I came up with another solution in more secure way. So, you don't need to aware of SQL injection.

params := []string{"A", "B"}
jsonParams, err := json.Marshal(params)
if err != nil {
    return err
}

rows, err = db.Query(`
 WITH compared_values as(
   SELECT * 
     FROM JSON_TABLE(
      ?,
      "$[*]" COLUMNS( 
       value TEXT PATH "$"
     )
   ) as compared_values
 ) 
 SELECT * FROM target_table
 WHERE target_value IN (SELECT value FROM compared_values);
`, jsonParams)

you can also use pure select part in SQL query like this but I'm not sure about performace. I think using WITH cause can prevent repeatly JSON parsing.

 SELECT * FROM  target_table
 WHERE target_value IN (
   SELECT value FROM JSON_TABLE(
     ?,
     "$[*]" COLUMNS( 
       value TEXT PATH "$"
     )
   ) as compared_values
  );

Golang db query using slice IN clause

4 Answers4