How Can I Implement Dynamic ORDER BY Clauses in Go with MySQL to Prevent SQL Injection?

Dynamic ORDER BY in Golang with MySql

Issue:

Difficulty in dynamically ordering query results using db.Select() with placeholders.

Analysis:

Unlike filter parameters, placeholders (?) cannot be utilized for SQL keywords or identifiers, including the ORDER BY clause.

Resolution:

To achieve dynamic ordering, one can employ fmt.Sprintf() to assemble the query text dynamically. For instance:

ordCol := "title"

qtext := fmt.Sprintf("SELECT * FROM Apps ORDER BY %s DESC", ordCol)
rows, err := db.Query(qtext)

Precautions:

When assembling queries dynamically, it's crucial to implement safeguards against SQL injection. This involves ensuring that values used for column names adhere to specific criteria, such as only permitting English letters, digits, and underscores:

valid := regexp.MustCompile("^[A-Za-z0-9_]+$")
if !valid.MatchString(ordCol) {
    // Invalid column name, prevent SQL injection
}

The above is the detailed content of How Can I Implement Dynamic ORDER BY Clauses in Go with MySQL to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!