How to parametrize a dynamic query in Go

huangapple go评论78阅读模式
英文:

How to parametrize a dynamic query in Go

问题

用户可以根据多个不同的条件请求产品价格,这可能会导致访问表中的不同列。我正在循环遍历请求的产品并构建一系列查询,但遇到了一些问题。

逐个运行它们并合并结果比将它们联合起来花费更长的时间。因此,我尝试构建以下查询,它可以正常工作并且速度很快,但容易受到注入攻击。

有没有更好的方法在没有使用 UNION 的情况下完成这个任务?或者有没有简单的方法可以对这样的动态查询进行参数化?

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)

因此,在某些情况下,我可能会有以下查询:

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

而在其他情况下(取决于请求),我可能会有类似这样的查询:

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

如果我确定查询的内容,我可以使用 $1、$2 等等,但我认为在这里我不能这样做,因为我不知道会有多少个参数,而且它们都需要是不同的。

英文:

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.

答案1

得分: 0

我已经理解了,以下是翻译好的内容:

找到了解决办法,以下是一个粗略的未经测试的示例,展示了我是如何解决这个问题的,以防其他人遇到相同的情况。

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

在查询之前仍然需要验证列名,以防止注入攻击。

英文:

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


Will still need to validate column names prior to querying to prevent injection.

</details>



huangapple
  • 本文由 发表于 2021年12月30日 22:34:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/70533049.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定