SQL预编译中order by后为什么不能参数化原因

SQL预编译中order by后为什么不能参数化原因

以Golang为例进行说明

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql" // Import MySQL driver
)

func main() {
	// 数据库连接信息
	db, err := sql.Open("mysql", "username:password@tcp(localhost:3306)/dbname")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 查询的参数
	userID := 123

	// 准备查询语句，使用占位符
	query := "SELECT username, email FROM users WHERE id = ?"
	rows, err := db.Query(query, userID)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var username, email string
		if err := rows.Scan(&username, &email); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("Username: %s, Email: %s\n", username, email)
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

db.Query(query, userID)会自动给值加上引号。比如假设id=“1”，那么拼凑成的语句会是SELECT username, email FROM users WHERE id ='1'

再看order by，order by后一般是接字段名，而字段名是不能带引号的，比如 order by id；如果带上引号成了order by ‘id’，那username就是一个字符串不是字段名了，这就产生了语法错误。

所以order by后不能参数化的本质是：一方面预编译又只有自动加引号的db.Query()方法，没有不加引号的方法；而另一方面order by后接的字段名不能有引号。

更本质的说法是：不只order by，凡是字符串但又不能加引号的位置都不能参数化；包括sql关键字、库名表名字段名函数名等等。

不能参数化位置的防sql注入办法

不能参数化的位置，不管怎么拼接，最终都是和使用“+”号拼接字符串的功效一样：拼成了sql语句但没有防sql注入的效果。

但好在的一点是，不管是sql关键字，还是库名表名字段名函数名对于后台开发者来说他的集合都是有限的，更准确点应该说也就那么几个。

这时我们应可以使用白名单的这种针对有限集合最常用的处理办法进行处理，如果传来的参数不在白名单列表中，直接返回错误即可。

代码类似如下：

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 假设输入的排序列是由用户提供的
	userInput := "columnName" // 假设用户输入
	orderBy := "id"          // 默认排序列

	// 验证用户提供的排序列是否是允许的列，避免注入
	allowedColumns := map[string]bool{"columnName": true, "columnName2": true, "columnName3": true} // 列出所有允许的列名
	if _, ok := allowedColumns[userInput]; !ok {
		log.Fatal("Invalid input for order by")
	}

	// 使用预编译语句
	stmt, err := db.Prepare("SELECT * FROM table_name ORDER BY " + userInput)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	// 执行查询
	rows, err := stmt.Query()
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	// 处理结果
	for rows.Next() {
		// 处理每一行的数据
	}
	if err = rows.Err(); err != nil {
		log.Fatal(err)
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49