add new heuristic rule SEC.004

sleep(), benchmark(), get_lock, release_lock() are dangerous function may not be used in online.
jerry-00 · Feb 12, 2019 · 9baca3f · 9baca3f
1 parent 1d09ee2
commit 9baca3f
Show file tree

Hide file tree

Showing 7 changed files with 88 additions and 0 deletions.
diff --git a/advisor/heuristic.go b/advisor/heuristic.go
@@ -2291,6 +2291,24 @@ func (q *Query4Audit) RuleDataDrop() Rule {
 	return rule
 }
 
+// RuleInjection SEC.004
+func (q *Query4Audit) RuleInjection() Rule {
+	var rule = q.RuleOK()
+	if q.TiStmt != nil {
+		json := ast.StmtNode2JSON(q.Query, "", "")
+		fs := common.JSONFind(json, "FnName")
+		for _, f := range fs {
+			functionName := gjson.Get(f, "L")
+			switch functionName.String() {
+			case "sleep", "benchmark", "get_lock", "release_lock":
+				// Ref: https://www.k0rz3n.com/2019/02/01/一篇文章带你深入理解%20SQL%20盲注/
+				rule = HeuristicRules["SEC.004"]
+			}
+		}
+	}
+	return rule
+}
+
 // RuleCompareWithFunction FUN.001
 func (q *Query4Audit) RuleCompareWithFunction() Rule {
 	var rule = q.RuleOK()

diff --git a/advisor/heuristic_test.go b/advisor/heuristic_test.go
@@ -2174,6 +2174,45 @@ func TestRuleDataDrop(t *testing.T) {
 	common.Log.Debug("Exiting function: %s", common.GetFunctionName())
 }
 
+// SEC.004
+func TestRuleInjection(t *testing.T) {
+	common.Log.Debug("Entering function: %s", common.GetFunctionName())
+	sqls := [][]string{
+		{
+			`select benchmark(10, rand())`,
+			`select sleep(1)`,
+			`select get_lock('lock_name', 1)`,
+			`select release_lock('lock_name')`,
+		},
+		{
+			"select * from `sleep`",
+		},
+	}
+	for _, sql := range sqls[0] {
+		q, err := NewQuery4Audit(sql)
+		if err == nil {
+			rule := q.RuleInjection()
+			if rule.Item != "SEC.004" {
+				t.Error("Rule not match:", rule.Item, "Expect : SEC.004")
+			}
+		} else {
+			t.Error("sqlparser.Parse Error:", err)
+		}
+	}
+	for _, sql := range sqls[1] {
+		q, err := NewQuery4Audit(sql)
+		if err == nil {
+			rule := q.RuleInjection()
+			if rule.Item != "OK" {
+				t.Error("Rule not match:", rule.Item, "Expect : OK")
+			}
+		} else {
+			t.Error("sqlparser.Parse Error:", err)
+		}
+	}
+	common.Log.Debug("Exiting function: %s", common.GetFunctionName())
+}
+
 // FUN.001
 func TestCompareWithFunction(t *testing.T) {
 	common.Log.Debug("Entering function: %s", common.GetFunctionName())

diff --git a/advisor/rules.go b/advisor/rules.go
@@ -1005,6 +1005,14 @@ func init() {
 			Case:     "delete from table where col = 'condition'",
 			Func:     (*Query4Audit).RuleDataDrop,
 		},
+		"SEC.004": {
+			Item:     "SEC.004",
+			Severity: "L0",
+			Summary:  "发现常见 SQL 注入函数",
+			Content:  `SLEEP(), BENCHMARK(), GET_LOCK(), RELEASE_LOCK() 等函数通常出现在 SQL 注入语句中，会严重影响数据库性能。`,
+			Case:     "SELECT BENCHMARK(10, RAND())",
+			Func:     (*Query4Audit).RuleInjection,
+		},
 		"STA.001": {
 			Item:     "STA.001",
 			Severity: "L0",

diff --git a/advisor/testdata/TestListHeuristicRules.golden b/advisor/testdata/TestListHeuristicRules.golden
@@ -1072,6 +1072,16 @@ create table test(id int,name varchar(20) not null,password varchar(200)not null
 ```sql
 delete from table where col = 'condition'
 ```
+## 发现常见 SQL 注入函数
+
+* **Item**:SEC.004
+* **Severity**:L0
+* **Content**:SLEEP(), BENCHMARK(), GET\_LOCK(), RELEASE\_LOCK() 等函数通常出现在 SQL 注入语句中，会严重影响数据库性能。
+* **Case**:
+
+```sql
+SELECT BENCHMARK(10, RAND())
+```
 ## '!=' 运算符是非标准的
 
 * **Item**:STA.001

diff --git a/advisor/testdata/TestMergeConflictHeuristicRules.golden b/advisor/testdata/TestMergeConflictHeuristicRules.golden
@@ -102,6 +102,7 @@ advisor.Rule{Item:"RES.009", Severity:"L2", Summary:"不建议使用连续判断
 advisor.Rule{Item:"SEC.001", Severity:"L0", Summary:"请谨慎使用TRUNCATE操作", Content:"一般来说想清空一张表最快速的做法就是使用TRUNCATE TABLE tbl_name;语句。但TRUNCATE操作也并非是毫无代价的，TRUNCATE TABLE无法返回被删除的准确行数，如果需要返回被删除的行数建议使用DELETE语法。TRUNCATE 操作还会重置 AUTO_INCREMENT，如果不想重置该值建议使用 DELETE FROM tbl_name WHERE 1;替代。TRUNCATE 操作会对数据字典添加源数据锁(MDL)，当一次需要 TRUNCATE 很多表时会影响整个实例的所有请求，因此如果要 TRUNCATE 多个表建议用 DROP+CREATE 的方式以减少锁时长。", Case:"TRUNCATE TABLE tbl_name", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}
 advisor.Rule{Item:"SEC.002", Severity:"L0", Summary:"不使用明文存储密码", Content:"使用明文存储密码或者使用明文在网络上传递密码都是不安全的。如果攻击者能够截获您用来插入密码的SQL语句，他们就能直接读到密码。另外，将用户输入的字符串以明文的形式插入到纯SQL语句中，也会让攻击者发现它。如果您能够读取密码，黑客也可以。解决方案是使用单向哈希函数对原始密码进行加密编码。哈希是指将输入字符串转化成另一个新的、不可识别的字符串的函数。对密码加密表达式加点随机串来防御“字典攻击”。不要将明文密码输入到SQL查询语句中。在应用程序代码中计算哈希串，只在SQL查询中使用哈希串。", Case:"create table test(id int,name varchar(20) not null,password varchar(200)not null)", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}
 advisor.Rule{Item:"SEC.003", Severity:"L0", Summary:"使用DELETE/DROP/TRUNCATE等操作时注意备份", Content:"在执行高危操作之前对数据进行备份是十分有必要的。", Case:"delete from table where col = 'condition'", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}
+advisor.Rule{Item:"SEC.004", Severity:"L0", Summary:"发现常见 SQL 注入函数", Content:"SLEEP(), BENCHMARK(), GET_LOCK(), RELEASE_LOCK() 等函数通常出现在 SQL 注入语句中，会严重影响数据库性能。", Case:"SELECT BENCHMARK(10, RAND())", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}
 advisor.Rule{Item:"STA.001", Severity:"L0", Summary:"'!=' 运算符是非标准的", Content:"\"<>\"才是标准SQL中的不等于运算符。", Case:"select col1,col2 from tbl where type!=0", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}
 advisor.Rule{Item:"STA.002", Severity:"L1", Summary:"库名或表名点后建议不要加空格", Content:"当使用 db.table 或 table.column 格式访问表或字段时，请不要在点号后面添加空格，虽然这样语法正确。", Case:"select col from sakila. film", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}
 advisor.Rule{Item:"STA.003", Severity:"L1", Summary:"索引起名不规范", Content:"建议普通二级索引以idx_为前缀，唯一索引以uk_为前缀。", Case:"select col from now where type!=0", Position:0, Func:func(*advisor.Query4Audit) advisor.Rule {...}}

diff --git a/database/show.go b/database/show.go
@@ -156,6 +156,8 @@ func (db *Connector) ShowTableStatus(tableName string) (*TableStatInfo, error) {
 	for res.Rows.Next() {
 		err := res.Rows.Scan(statusFields...)
 		if err != nil {
+			// MariaDB 中视图的 STATUS 信息大部分表都为 NULL，此时会打印如下 DEBUG 级别日志信息，看到后忽略即可。
+			// sql: Scan error on column index 4: converting driver.Value type <nil> ("<nil>") to uint64: invalid syntax
 			common.Log.Debug(err.Error())
 		}
 		tbStatus.Rows = append(tbStatus.Rows, ts)

diff --git a/doc/heuristic.md b/doc/heuristic.md
@@ -1072,6 +1072,16 @@ create table test(id int,name varchar(20) not null,password varchar(200)not null
 ```sql
 delete from table where col = 'condition'
 ```
+## 发现常见 SQL 注入函数
+
+* **Item**:SEC.004
+* **Severity**:L0
+* **Content**:SLEEP(), BENCHMARK(), GET\_LOCK(), RELEASE\_LOCK() 等函数通常出现在 SQL 注入语句中，会严重影响数据库性能。
+* **Case**:
+
+```sql
+SELECT BENCHMARK(10, RAND())
+```
 ## '!=' 运算符是非标准的
 
 * **Item**:STA.001