golang slice in mysql query with where in clause

Question

I am running the below query, but only get the first id value:-

select * from `table` where table`.`id` in ('1', '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17') and `table`.`deleted_at` is null

I have done the following:-

var aID = make([]string, 0)
var in India // india is struct

for rows.Next() {
    cook := rows.Scan(&in.ID)

    aID = append(aID, strconv.Itoa(in.ID))
}

asID = strings.Join(aID, ",")

anotherRow,err := db.Query("SELECT * from table2 where id in (?)", asID)
if err != nil { fmt.Printf("Error: ", err) }
// ... Other line follows up with "for anotherRow.Next() and fetching"

While fetching data, it only returns value of "1" and ignores all other ID passed to it, which are '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17'.

How can I pass it correctly?

I am using go-sql-driver/mysql.

FAQ :

aID does contain all those numbers as string and
table has all the rows available with provided above id.
table is from where id is fetched and appended to aID and another record with id stored in aID are fetched with in statement from table2.

Thanks

obviously yes, I am able to run mysql query inside mysql cli with all those id and getting return for all. — John Cargo
– John Cargo, Commented Jul 27, 2017 at 13:14
You can use sqlx.In to solve this. Take a look at stackoverflow.com/questions/40565805/… — yazgazan
– yazgazan, Commented Jul 27, 2017 at 13:15
@JohnCargo your example query shows the ids quoted in single quotes, strconv.Itoa and strings.Join will not result in '1','2','3',... but in 1,2,3,.... — mkopriva
– mkopriva, Commented Jul 27, 2017 at 13:24
@JohnCargo the '?' placeholder gets replaced with the string of numbers separated by commas, so the query results in WHERE id IN ('1,2,3,4...') and mysql matches only the first value in that string, so id equal to 1 or '1' will match but id equal to 2 or '2' will not — mkopriva
– mkopriva, Commented Jul 27, 2017 at 13:43

Gavin · Accepted Answer · 2017-07-28 12:10:55Z

39

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.

edited Jul 28, 2017 at 12:10

answered Jul 27, 2017 at 13:54

Gavin

4,5252 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Koodimetsa Over a year ago

This is a good solution, because it also prevents sql injection.

Jairo Lozano Over a year ago

I was just about to do something similar but thought "somebody must have done it already", very clever

Illud Over a year ago

Why not SELECT * from table2 where id in ( + strings.Trim(string(asID), "[]") + )

Gavin Over a year ago

@Illud 1) Because that does not compile (cannot convert []string{…} (value of type []string) to type string). 2) Even if that did compile, it seems a bit hacky. 3) Using parameters allows you to avoid SQL injection, while your proposed way would not.

Alex Efimov · Accepted Answer · 2020-06-24 10:02:33Z

8

Try

q,args,err := sqlx.In("SELECT * FROM table2 WHERE id IN(?);", asID) //creates the query string and arguments
rows, err := db.Query(q,args...)

You could also use the Masterminds/squirrel package:

import sq "github.com/Masterminds/squirrel"

...

users := sq.Select("*").From("table2")
active := users.Where(sq.Eq{"id":[]string{"1","2","3"}})
sql, args, err := active.ToSql()

Which will do the in clause automatically when using sq.Eq struct with a slice.

edited Jun 24, 2020 at 10:02

answered Jul 27, 2017 at 13:35

Alex Efimov

3,7531 gold badge27 silver badges31 bronze badges

3 Comments

John Cargo Over a year ago

Any possibility without importing any further package ?

Alex Efimov Over a year ago

have you tried? db.Query("SELECT * from table2 where id in (?)", aID...)

thwd Over a year ago

@JohnCargo you need to make aID of type []interface{}. The why is too long for a comment.

Daniel · Accepted Answer · 2021-05-05 08:14:30Z

0

maybe something like this.

func GetPlaceholders(values ...string) (placeholders string, parameters []interface{}) {
    n := len(values)
    p := make([]string, n)
    parameters = make([]interface{}, n)
    for i := 0; i < n; i++ {
        p[i] = "?"
        parameters[i] = values[i]
    }
    placeholders = strings.Join(p, ",")
    return placeholders, parameters
}

and calling the function like this

placeholders, params := GetPlaceholders("1", "2", "3")

rows, err := db.Query(`select language, textkey, text 
        from language where textkey in (`+placeholders+`)
        order by language, textkey`, params...)

edited May 5, 2021 at 8:14

answered May 4, 2021 at 18:07

Daniel

213 bronze badges

Comments

Debasish Mitra · Accepted Answer · 2018-10-28 03:51:52Z

-1

The most elegant solution for queries to make array/slice work directly with sql queries. This also sql injection proof as you are not using string concatenation rather using sql prepared statement

idAry := []string{"1", "2", "3"}
q := "SELECT * FROM table WHERE id = any($1);"
rows, err := db.Exec(q, pq.Array(authors))

answered Oct 28, 2018 at 3:51

Debasish Mitra

1,5353 gold badges17 silver badges19 bronze badges

1 Comment

Gavin Over a year ago

OP states they are using mysql, so postgresql syntax won't work here.

mkopriva · Accepted Answer · 2017-07-27 14:12:14Z

-2

Since you're dealing with ids from your own database and if you are certain there is no way someone could inject malicious "ids" into that code, don't use the placeholder ? and just use the fmt package.

fmt.Sprintf("SELECT * from table2 where id in (%s)", asID)

this will result in SELECT * from table2 where id in (1,2,3,4...) as opposed to SELECT * from table2 where id in ('1,2,3,4...')

answered Jul 27, 2017 at 14:12

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

2 Comments

John Cargo Over a year ago

Just leaving note for future readers : Though i have selected this answer and this answer is safe for given question. But as the answer above says, the query runs without escaping the arguments , so, this code should not be used where malicious user can inject malicious ID.

Tomasz Swider Over a year ago

This is how sql injection happens, one genius is writing some code that is 'only' used with strings of known origin and then 1 year later, second genius is using that already written and battle tested function in different context :), usualy after the first genius already left the company.

girish946 · Accepted Answer · 2018-07-18 13:00:18Z

-3

example:

idAry := []string{"1", "2", "3"}
ids := strings.Join(idAry, "','")
sqlRaw := fmt.Sprintf(`SELECT * FROM table WHERE id IN ('%s')`, ids)
rows, err := db.Query(sqlRaw)

It works fine

edited Jul 18, 2018 at 13:00

girish946

7459 silver badges24 bronze badges

answered Jul 18, 2018 at 12:01

Cary

353 bronze badges

2 Comments

gnuns Over a year ago

This solution can allow a SQL injection attack

Tomasz Swider Over a year ago

This allows sql injection when the strings are user controlled

Collectives™ on Stack Overflow

golang slice in mysql query with where in clause

6 Answers 6

4 Comments

3 Comments

Comments

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

3 Comments

Comments

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related