Golang ORDER BY问题与MySql

Question

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.

Answer 1

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