英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论