MySql 的 Golang ORDER BY 问题
我似乎无法使用 db.Select() 动态 ORDER BY.我在谷歌上没有任何运气...
I can't seem to dynamically ORDER BY with db.Select(). I've Googled without any luck...
工作
rows, err := db.Query("SELECT * FROM Apps ORDER BY title DESC")
不起作用
rows, err := db.Query("SELECT * FROM Apps ORDER BY ? DESC", "title")
我没有收到任何错误，查询只是无法订购.
I'm not getting any errors, the query simply fails to order.
推荐答案
Placeholders ('?') 只能用于为过滤器参数插入动态的转义值(例如在 WHERE 部分)，数据值应出现的位置，而不是 SQL 关键字、标识符等.您不能使用它来动态指定 ORDER BY OR GROUP BY 值.
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.
您仍然可以这样做，例如您可以使用 fmt.Sprintf() 像这样组合动态查询文本:
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)
注意事项:
这样做您将不得不手动防御 SQL 注入，例如如果列名的值来自用户，则不能接受任何值而直接将其插入查询中，否则用户将能够做各种坏事.简单地说，您应该只接受英文字母 + 数字 + 下划线 ('_') 的字母.
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
}
示例(在 Go Playground 上尝试):
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