60

What does Go want for the second param in this SQL query. I am trying to use the IN lookup in postgres.

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field IN $2")
rows, err := stmt.Query(10, ???)

What I really want:

SELECT * FROM awesome_table WHERE id=10 AND other_field IN (this, that);
030
  • 10,842
  • 12
  • 78
  • 123
a.m.
  • 2,108
  • 5
  • 24
  • 29

9 Answers9

62

It looks like you may be using the pq driver. pq recently added Postgres-specific Array support via pq.Array (see pull request 466). You can get what you want via:

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field = ANY($2)")
rows, err := stmt.Query(10, pq.Array([]string{'this','that'})

I think this generates the SQL:

SELECT * FROM awesome_table WHERE id=10 AND other_field = ANY('{"this", "that"}');

Note this utilizes prepared statements, so the inputs should be sanitized.

Pete
  • 10,310
  • 7
  • 53
  • 59
  • 9
    This is the best option, since it has no injection risks and allows re-use of the same prepared statement (which improves performance compared to the currently accepted answer) – Dave Sep 08 '16 at 19:44
57

Query just takes varargs to replace the params in your sql so, in your example, you would just do

rows, err := stmt.Query(10)

say, this and that of your second example were dynamic, then you'd do

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id=$1 AND other_field IN ($2, $3)")
rows, err := stmt.Query(10,"this","that")

If you have variable args for the "IN" part, you can do (play)

package main

import "fmt"
import "strings"

func main() {
    stuff := []interface{}{"this", "that", "otherthing"}
    sql := "select * from foo where id=? and name in (?" + strings.Repeat(",?", len(stuff)-1) + ")"
    fmt.Println("SQL:", sql)
    args := []interface{}{10}
    args = append(args, stuff...)
    fakeExec(args...)
    // This also works, but I think it's harder for folks to read
    //fakeExec(append([]interface{}{10},stuff...)...)
}

func fakeExec(args ...interface{}) {
    fmt.Println("Got:", args)
}
David Budworth
  • 11,248
  • 1
  • 36
  • 45
  • 9
    What if there are a varying amount of items for the in clause? – a.m. Nov 29 '13 at 02:03
  • 8
    So I get this and this would work. I guess I'm expecting the sql driver to convert a slice or something to the appropriate thing. Ex: ```stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field IN $2")``` ```args:= []int{1,3,4,5}``` ```rows, err := stmt.Query(10, args)``` – a.m. Nov 30 '13 at 04:18
  • 1
    Yep, that would be better. Always wanted that on the Java side as well. Databases would need to natively support it as the SQL part is sent unchanged to the server and the args are sent separately (seems like no database supports list/array args) – David Budworth Nov 30 '13 at 05:34
  • You could opt to replace the construction of the series of question marks with `strings.Join(strings.Split(strings.Repeat("?", len(stuff)), ""), ",")`. I feel it's slightly less of an mental exercise. Of course you would need a guard statement against empty `stuff`. – harm Jul 02 '14 at 12:09
  • 1
    Note that this solution gets a bit more involved in conjunction with [lib/pq](http://godoc.org/github.com/lib/pq#hdr-Queries) due to its use of positional placeholders ($1, $2, etc.). – Alec Thomas Sep 22 '14 at 05:52
  • with pq, you can do `where other_field = ANY($2)` then pass `pq.StringArray(stuff)`. Way better than computing a where clause – David Budworth Apr 02 '17 at 17:12
25

Incase anyone like me was trying to use an array with a query, here is an easy solution.

get https://github.com/jmoiron/sqlx

ids := []int{1, 2, 3}
q,args,err := sqlx.In("SELECT id,username FROM users WHERE id IN(?);", ids) //creates the query string and arguments
//you should check for errors of course
q = sqlx.Rebind(sqlx.DOLLAR,q) //only if postgres
rows, err := db.Query(q,args...) //use normal POSTGRES/ANY SQL driver important to include the '...' after the Slice(array)
Krtko
  • 1,055
  • 18
  • 24
  • 5
    I shall add that it also supports multiple parameters: `name := "foobar"` `q,args,err := sqlx.In("SELECT id,username FROM users WHERE id IN(?) AND name = (?);", ids, name)` – eduncan911 Jun 03 '16 at 15:48
  • How can we make below query: select * from student where (name, surname) IN (("eduncan", "911"), ("somesh","sing") .....) ? – Somesh Feb 02 '18 at 18:26
  • `important to include the '...' after the Slice(array)` saved my life. – user11809641 Aug 20 '21 at 23:49
20

With PostgreSQL, at least, you have the option of passing the entire array as a string, using a single placeholder:

db.Query("select 1 = any($1::integer[])", "{1,2,3}")

That way, you can use a single query string, and all the string concatenation is confined to the parameter. And if the parameter is malformed, you don't get an SQL injection; you just get something like: ERROR: invalid input syntax for integer: "xyz"

https://groups.google.com/d/msg/golang-nuts/vHbg09g7s2I/RKU7XsO25SIJ

Ivan Rave
  • 1,759
  • 18
  • 15
6

if you use sqlx, you can follow this way: https://github.com/jmoiron/sqlx/issues/346

arr := []string{"this", "that"}
query, args, err := sqlx.In("SELECT * FROM awesome_table WHERE id=10 AND other_field IN (?)", arr)
 
query = db.Rebind(query) // sqlx.In returns queries with the `?` bindvar, rebind it here for matching the database in used (e.g. postgre, oracle etc, can skip it if you use mysql)
rows, err := db.Query(query, args...)
yihao ye
  • 331
  • 3
  • 4
1
var awesome AwesomeStruct
var awesomes []*AwesomeStruct

ids := []int{1,2,3,4}
q, args, err := sqlx.In(`
  SELECT * FROM awesome_table WHERE id=(?) AND other_field IN (?)`, 10, ids)

// use .Select for multiple return
err = db.Select(&awesomes, db.SQL.Rebind(q), args...)

// use .Get for single return
err = db.Get(&awesome, db.SQL.Rebind(q), args...)
7urkm3n
  • 6,054
  • 4
  • 29
  • 46
0
//I tried a different way. A simpler and easier way, maybe not too efficient.
stringedIDs := fmt.Sprintf("%v", ids)
stringedIDs = stringedIDs[1 : len(stringedIDs)-1]
stringedIDs = strings.ReplaceAll(stringedIDs, " ", ",")
query := "SELECT * FROM users WHERE id IN ("  + stringedIDs + ")"
//Then follow your standard database/sql Query
rows, err := db.Query(query)
//error checking
if err != nil {
    // Handle errors
} else {
    // Process rows
}
-1

Rather pedestrian and only to be used if server generated. Where UserIDs is a slice (list) of strings:

sqlc := `select count(*) from test.Logins where UserID 
                in ("` + strings.Join(UserIDs,`","`) + `")`
errc := db.QueryRow(sqlc).Scan(&Logins)
user2099484
  • 4,417
  • 2
  • 21
  • 9
-3

You can also use this direct conversion.

awesome_id_list := []int{3,5,8}

var str string
for _, value := range awesome_id_list {
        str += strconv.Itoa(value) + ","
}

query := "SELECT * FROM awesome_table WHERE id IN (" + str[:len(str)-1] + ")"

WARNING
This is method is vulnerable to SQL Injection. Use this method only if awesome_id_list is server generated.

Thellimist
  • 3,757
  • 5
  • 31
  • 49