How to Execute an in Lookup in SQL Using Golang

How to execute an IN lookup in SQL using Golang?

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

Golang db query using slice IN clause

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.

golang slice in mysql query with where in clause

You can do something like this:

args := make([]interface{}, len(asID))
for i, id := range asID {
    args[i] = id
}
stmt := `SELECT * from table2 where id in (?` + strings.Repeat(",?", len(args)-1) + `)`
anotherRow, err := db.Query(stmt, args...)

Just note you will want to put in a guard if asID can ever have len == 0.

If you have any other arguments to pass in, you'll have to add them to the args slice.

Also to note, you should explicitly name the columns you want so you can guarantee you are scanning in the correct columns to the correct fields.

Go and IN clause in Postgres

Pre-building the SQL query (preventing SQL injection)

If you're generating an SQL string with a param placeholder for each of the values, it's easier to just generate the final SQL right away.

Note that since values are strings, there's place for SQL injection attack, so we first test if all the string values are indeed numbers, and we only proceed if so:

tags := []string{"1", "2", "3"}
buf := bytes.NewBufferString("SELECT COUNT(id) FROM tags WHERE id IN(")
for i, v := range tags {
    if i > 0 {
        buf.WriteString(",")
    }
    if _, err := strconv.Atoi(v); err != nil {
        panic("Not number!")
    }
    buf.WriteString(v)
}
buf.WriteString(")")

Executing it:

num := 0
if err := Db.QueryRow(buf.String()).Scan(&num); err != nil {
    log.Println(err)
}

Using ANY

You can also use Postgresql's ANY, whose syntax is as follows:

expression operator ANY (array expression)

Using that, our query may look like this:

SELECT COUNT(id) FROM tags WHERE id = ANY('{1,2,3}'::int[])

In this case you can declare the text form of the array as a parameter:

SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])

Which can simply be built like this:

tags := []string{"1", "2", "3"}
param := "{" + strings.Join(tags, ",") + "}"

Note that no check is required in this case as the array expression will not allow SQL injection (but rather will result in a query execution error).

So the full code:

tags := []string{"1", "2", "3"}

q := "SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])"
param := "{" + strings.Join(tags, ",") + "}"

num := 0
if err := Db.QueryRow(q, param).Scan(&num); err != nil {
    log.Println(err)
}

IN Lookup Postgres query in prepared statement, Golang

Adding to @a_horse_with_no_name's answer,
In Golang, the psql driver github.com/lib/pq contains a method Array() that can be used to convert a Golang slice into a psql Array.

...

import (
"github.com/lib/pq"
)

...

select col1 
from awesome_table 
where col2 <> ALL ($1) 
  and col3 <> ALL ($2);

where

slice1 := []string{val1, val2}
slice2 := []string{val3, val4}

pq.Array(slice1) can be passed for $1 and pq.Array(slice2) can be passed for $2 placeholder while passing the values in the prepared statements.

More about ANY and ALL functions can be found at here

Go Select In using a list of uuid strings

Using fmt. Make sure that your uuids doesn't contain any SQL-injection.

ary := []string{
    "1442edc8-9e1f-4213-8622-5610cdd66790",
    "0506ca17-d254-40b3-9ef0-bca6d15ad49d",
    "e46f3708-6da5-4b82-9c92-f89394dffe5d",
    "fb8bf848-73a2-4253-9fa3-e9d5e16ef94a",
    "84691fa5-3391-4c02-9b16-82389331b7ac",
    "adba3c9d-b4ab-4e62-a650-414970645be7",
}
query := fmt.Sprintf(`DELETE FROM files WHERE uid IN ('%s'::uuid);`,
             strings.Join(ary, "'::uuid,'"))
db.Exec(query) // etc

play.golang.org

---

Rid out of potential SQL-injections:

ary := []string{ /* list of uuids */ }
query := `DELETE FROM files WHERE uid IN (`
aryInterfaces := make([]interface{}, len(ary))
for i, v := range ary {
    query += "$" + strconv.FormatInt(int64(i+1), 10)
    if i < len(ary)-1 {
        query += ","
    }
    aryInterfaces[i] = v
}
query += ")"
db.Exec(query, aryInterface...)

play.golang.org

---

BONUS
Postgresql uses $1, $2, $3 etc instead of ?, ?, ?. Here is a little helper function and here is its proof of concept.

Golang MySQL querying undefined amount of args using IN operator

Stmt.Query() has a variadic parameter:

func (s *Stmt) Query(args ...interface{}) (*Rows, error)

This means you can use the ellipsis ... to pass a slice value as the value of the variadic parameter, but that slice must be of type []interface{}, e.g.:

var args []interface{}
for _, v := range r.Form["type"] {
    t, _ := strconv.Atoi(v)
    args = append(args, t)
}

// ...

rows, err := stmt.Query(args...)

As an alternative, you could pre-build the SQL query and execute without passing query arguments, for an example see Go and IN clause in Postgres.

Prevent SQL Injection in Go Postgresql

Firstly, usually prefer to use the db placeholders ? $1 etc.

1. Yes it is safe to use fmt.Sprintf with integer parameters to build SQL, though worth avoiding if you can, but your third param is %s - avoid that and use ?
2. Yes it is safe to use fmt.Sprintf with integer parameters, but %s or %v is far more risky and I'd avoid, can't think why you'd need it.
3. Use placeholders here, then yes it is safe.

General rules:

• Use placeholders by default, it should be rare to use %d (as in your IN query for example)
• Parse params into types like integer before any validation or use
• Avoid string concat if you can, and be particularly wary of string params
• Always hard code things like column and table names, never generate them from user input (e.g. ?sort=mystringcolname)
• Always validate that the params you get are only those authorised for that user

Go SQL driver get interface{} column values

See this https://stackoverflow.com/questions/20271123/go-lang-sql-in-parameters answer which my answer is based on. Using that you can do something like this:

var myMap = make(map[string]interface{})
rows, err := db.Query("SELECT * FROM myTable")
defer rows.Close()
if err != nil {
    log.Fatal(err)
}
colNames, err := rows.Columns()
if err != nil {
    log.Fatal(err)
}
cols := make([]interface{}, len(colNames))
colPtrs := make([]interface{}, len(colNames))
for i := 0; i < len(colNames); i++ {
    colPtrs[i] = &cols[i]
}
for rows.Next() {
    err = rows.Scan(colPtrs...)
    if err != nil {
        log.Fatal(err)
    }
    for i, col := range cols {
        myMap[colNames[i]] = col
    }
    // Do something with the map
    for key, val := range myMap {
        fmt.Println("Key:", key, "Value Type:", reflect.TypeOf(val))
    }
}

Using the reflect package you can then get the Type for each column as needed as demonstrated with the loop at the end.

This is generic and will work with any table, number of columns etc.

---

Related Topics