3

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.

Michael Mallett
  • 715
  • 1
  • 11
  • 28
  • 1
    *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

3 Answers3

2

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

Himanshu
  • 10,714
  • 6
  • 40
  • 58
2

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.

Kenny Grant
  • 8,790
  • 2
  • 30
  • 45
  • 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
1

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)
Toivo Lainevool
  • 121
  • 4