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);
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.
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)
}
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)
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.
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)
name := "foobar" q,args,err := sqlx.In("SELECT id,username FROM users WHERE id IN(?) AND name = (?);", ids, name)
important to include the '...' after the Slice(array) saved my life.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
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...)
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...)
//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
}
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)
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.
