Golang ORDER BY问题与MySql [英] Golang ORDER BY issue with MySql
问题描述
我似乎无法动态ORDER BY与db.Select()。我没有任何运气的谷歌...
WORKS
rows,err:= db.Query(SELECT * FROM Apps ORDER BY title DESC)
NOT WORK
rows,err:= db.Query(SELECT * FROM Apps ORDER BY?DESC )
我没有收到任何错误,查询根本无法订购。
WHERE
部分),其中应显示数据值,而不是SQL关键字,标识符等。您不能使用它动态指定 ORDER BY
OR GROUP BY
值。 你仍然可以做,例如你可以使用 fmt.Sprintf()
汇编动态查询文本如下:
ordCol:=title
qtext:= fmt.Sprintf SELECT * FROM Apps ORDER BY%s DESC,ordCol)
rows,err:= db.Query(qtext)
$ b b
注意事项:
这样做,您必须手动保护vs SQL注入,例如如果列名的值来自用户,则不能接受任何值,只是将其直接插入到查询中,否则用户将能够做各种坏事。平常你应该只接受英文字母+数字+下划线('_'
)的信。
提供一个完整的,全面的检查器或转义函数,你可以使用这个简单的regexp只接受英文字母,数字和'_'
:
valid:= regexp.MustCompile(^ [A-Za-z0-9 _] + $)
if!valid.MatchString ordCol){
//无效的列名,不要继续为了防止SQL注入
}
b $ b
示例(在 Go Playground 上试用):
fmt.Println(valid.MatchString(title))// true
fmt.Println(valid.MatchString(another_col_2 ))// true
fmt.Println(valid.MatchString(it's a trap!))// false
fmt.Println(valid.MatchString((trap)))// false
fmt.Println(valid.MatchString(also * trap))// false
I can't seem to dynamically ORDER BY with db.Select(). I've Googled without any luck...
WORKS
rows, err := db.Query("SELECT * FROM Apps ORDER BY title DESC")
DOES NOT WORK
rows, err := db.Query("SELECT * FROM Apps ORDER BY ? DESC", "title")
I'm not getting any errors, the query simply fails to order.
Placeholders ('?'
) can only be used to insert dynamic, escaped values for filter parameters (e.g. in the WHERE
part), where data values should appear, not for SQL keywords, identifiers etc. You cannot use it to dynamically specify the ORDER BY
OR GROUP BY
values.
You can still do it though, for example you can use fmt.Sprintf()
to assemble the dynamic query text like this:
ordCol := "title"
qtext := fmt.Sprintf("SELECT * FROM Apps ORDER BY %s DESC", ordCol)
rows, err := db.Query(qtext)
Things to keep in mind:
Doing so you will have to manually defend vs SQL injection, e.g. if the value of the column name comes from the user, you cannot accept any value and just insert it directly into the query else the user will be able to do all kinds of bad things. Trivially you should only accept letters of the English alphabet + digits + underscore ('_'
).
Without attempting to provide a complete, all-extensive checker or escaping function, you can use this simple regexp which only accepts English letters, digits and '_'
:
valid := regexp.MustCompile("^[A-Za-z0-9_]+$")
if !valid.MatchString(ordCol) {
// invalid column name, do not proceed in order to prevent SQL injection
}
Examples (try it on the Go Playground):
fmt.Println(valid.MatchString("title")) // true
fmt.Println(valid.MatchString("another_col_2")) // true
fmt.Println(valid.MatchString("it's a trap!")) // false
fmt.Println(valid.MatchString("(trap)")) // false
fmt.Println(valid.MatchString("also*trap")) // false
这篇关于Golang ORDER BY问题与MySql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!