CODEWITHSUNDEEP
sqlgoslice
Michael Mallett
Michael Mallett

Reputation: 744

Golang db query using slice IN clause

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.

Upvotes: 6

Views: 13065

Answers (4)

Wachira
Wachira

Reputation: 388

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

Upvotes: -1

Toivo Lainevool
Toivo Lainevool

Reputation: 171

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)

Upvotes: 3

Kenny Grant
Kenny Grant

Reputation: 9623

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.

Upvotes: 3

Himanshu
Himanshu

Reputation: 12675

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

Upvotes: 5

Related Questions