How to parametrize a dynamic query in Go-CodePudding

Users can request product prices based on a number of different criteria, which will result in it potentially accessing different columns in a table. I'm looping through the requested products and building a bunch of queries, but am running into some trouble.

Running them one by one and combining the results takes a much longer time than unionizing them. So I tried building the query like follows, which works and is fast, but is now susceptible to injection.

Is there a better way to do this without the Union? Or is there a simple way I could parametrize a dynamic query like this?

    var fullQuery string
    var counter int
    for i, d:= range dataMap{
    if counter != 0 {
        fullQuery = fullQuery   " UNION "
    }
    var records string
    for _, p := range d{
        records = records   `'`   string(p)   `',`
    }
    recordLength:= len(records)
    if recordLength> 0 && records [recordLength-1] == ',' {
        records = records[:recordLength-1]
    }
    counter  
    fullQuery = fullQuery   fmt.Sprintf(`
SELECT 
    price_` fmt.Sprint(p.type) ` as price,                
  FROM products
  WHERE products.id in (%s) and products.store= %s
  
`, records, p.store)

}

err := sqlx.Select(db, &dataStruct, fullQuery)

So, in some situations, I might have the following query:

SELECT 
    price_` fmt.Sprint(p.type) ` as price,                
  FROM products
  WHERE products.id in (%s) and products.store= %s

And in others (depending on the request), I might have something like this:

SELECT 
    price_` fmt.Sprint(p.type) ` as price,                
  FROM products
  WHERE products.id in ('testid1', 'testid2') and products.store= 2
UNION
SELECT 
    price_` fmt.Sprint(p.type) ` as price,                
  FROM products
  WHERE products.id in ('testid3', 'testid4') and products.store= 1

If I knew for sure what the query was, I would just use $1, $2, etc.., but I don't think I can here because I don't know how many parameters there will be and they all need to be different.

CodePudding user response：

Figured it out, rough untested example of how I ended up doing it in case anyone else runs into this.

 var counter int = 1
 var parameters []interface{}

 for _, d:= range data{
    if counter != 1 {
        fullQuery = fullQuery   " UNION "
    }
    fullQuery = fullQuery   fmt.Sprintf(`
SELECT 
    price_` fmt.Sprint(d.type) ` as price,                
  FROM products
  WHERE products.id = ANY($%v) and products.store= $%d
  
`, counter, counter 1)
   counter =2
   parameters = append(parameters, pq.Array(d.ids), d.store)

}

err := sqlx.Select(db, &dataStruct, fullQuery, parameters...)