CVE-2023-33967: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: EaseProbe is a tool that can do health/status checking. An SQL injection issue was discovered in EaseProbe before 2.1.0 when using MySQL/PostgreSQL data checking. This problem has been fixed in v2.1.0.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2023-05-31T18:15Z
Repository: https://github.com/megaease/easeprobe
Language: Go
Files changed: global/global.go, global/global_test.go, probe/client/mysql/mysql.go, probe/client/postgres/postgres.go
Commit date: 2023-04-25 08:01:22 +0000
Commit message: fix the SQL Injection (#330)


Diff: diff --git a/global/global.go b/global/global.go
index 88bf6ce..1d6bf59 100644
--- a/global/global.go
+++ b/global/global.go
@@ -309,5 +309,24 @@ func CommandLine(cmd string, args []string) string {
 	for _, arg := range args {
 		result += " " + arg
 	}
 	return result
 }
+
+// EscapeQuote escape the string the single quote, double quote, and backtick
+func EscapeQuote(str string) string {
+	type Escape struct {
+		From string
+		To   string
+	}
+	escape := []Escape{
+		{From: "`", To: ""}, // remove the backtick
+		{From: `\`, To: `\\`},
+		{From: `'`, To: `\'`},
+		{From: `"`, To: `\"`},
+	}
+
+	for _, e := range escape {
+		str = strings.ReplaceAll(str, e.From, e.To)
+	}
+	return str
+}
diff --git a/global/global_test.go b/global/global_test.go
index e17a1d9..379f58d 100644
--- a/global/global_test.go
+++ b/global/global_test.go
@@ -436,5 +436,14 @@ func TestCommandLine(t *testing.T) {
 	assert.Equal(t, "echo hello world", s)
 
 	s = CommandLine("kubectl", []string{"get", "pod", "--all-namespaces", "-o", "json"})
 	assert.Equal(t, "kubectl get pod --all-namespaces -o json", s)
 }
+
+func TestEscape(t *testing.T) {
+	assert.Equal(t, "test", EscapeQuote("test"))
+	assert.Equal(t, "test", EscapeQuote("`test`"))
+	assert.Equal(t, `\'test\'`, EscapeQuote("'test'"))
+	assert.Equal(t, `\"test\"`, EscapeQuote(`"test"`))
+	assert.Equal(t, `\\test\\`, EscapeQuote(`\test\`))
+
+}
diff --git a/probe/client/mysql/mysql.go b/probe/client/mysql/mysql.go
index 572a330..a4a4031 100644
--- a/probe/client/mysql/mysql.go
+++ b/probe/client/mysql/mysql.go
@@ -104,54 +104,69 @@ func (r *MySQL) Probe() (bool, string) {
 	}
 	defer db.Close()
 
 	// Check if we need to query specific data
 	if len(r.Data) > 0 {
-		for k, v := range r.Data {
-			log.Debugf("[%s / %s / %s] - Verifying Data - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
-			sql, err := r.getSQL(k)
-			if err != nil {
-				return false, err.Error()
-			}
-			log.Debugf("[%s / %s / %s] - SQL - [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, sql)
-			rows, err := db.Query(sql)
-			if err != nil {
-				return false, err.Error()
-			}
-			if !rows.Next() {
-				rows.Close()
-				return false, fmt.Sprintf("No data found for [%s]", k)
-			}
-			//check the value is equal to the value in data
-			var value string
-			if err := rows.Scan(&value); err != nil {
-				rows.Close()
-				return false, err.Error()
-			}
-			if value != v {
-				rows.Close()
-				return false, fmt.Sprintf("Value not match for [%s] expected [%s] got [%s] ", k, v, value)
-			}
-			rows.Close()
-			log.Debugf("[%s / %s / %s] - Data Verified Successfully! - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
-		}
-	} else {
-		err = db.Ping()
-		if err != nil {
+		if err := r.ProbeWithDataVerification(db); err != nil {
 			return false, err.Error()
 		}
-		row, err := db.Query("show status like \"uptime\"") // run a SQL to test
-		if err != nil {
+	} else {
+		if err := r.ProbeWithPing(db); err != nil {
 			return false, err.Error()
 		}
-		defer row.Close()
 	}
 
 	return true, "Check MySQL Server Successfully!"
 
 }
 
+// ProbeWithPing do the health check with ping
+func (r *MySQL) ProbeWithPing(db *sql.DB) error {
+	if err := db.Ping(); err != nil {
+		return err
+	}
+	row, err := db.Query("show status like \"uptime\"") // run a SQL to test
+	if err != nil {
+		return err
+	}
+	defer row.Close()
+	return nil
+}
+
+// ProbeWithDataVerification do the health check with data verification
+func (r *MySQL) ProbeWithDataVerification(db *sql.DB) error {
+	for k, v := range r.Data {
+		log.Debugf("[%s / %s / %s] - Verifying Data - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
+		sql, err := r.getSQL(k)
+		if err != nil {
+			return err
+		}
+		log.Debugf("[%s / %s / %s] - SQL - [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, sql)
+		rows, err := db.Query(sql)
+		if err != nil {
+			return err
+		}
+		if !rows.Next() {
+			rows.Close()
+			return fmt.Errorf("No data found for [%s]", k)
+		}
+		//check the value is equal to the value in data
+		var value string
+		if err := rows.Scan(&value); err != nil {
+			rows.Close()
+			return err
+		}
+		if value != v {
+			rows.Close()
+			return fmt.Errorf("Value not match for [%s] expected [%s] got [%s] ", k, v, value)
+		}
+		rows.Close()
+		log.Debugf("[%s / %s / %s] - Data Verified Successfully! - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
+	}
+	return nil
+}
+
 // getSQL get the SQL statement
 // input: database:table:column:key:value
 // output: SELECT column FROM database.table WHERE key = value
 func (r *MySQL) getSQL(str string) (string, error) {
 	if len(strings.TrimSpace(str)) == 0 {
@@ -159,18 +174,18 @@ func (r *MySQL) getSQL(str string) (string, error) {
 	}
 	fields := strings.Split(str, ":")
 	if len(fields) != 5 {
 		return "", fmt.Errorf("Invalid SQL data - [%s]. (syntax: database:table:field:key:value)", str)
 	}
-	db := fields[0]
-	table := fields[1]
-	field := fields[2]
-	key := fields[3]
-	value := fields[4]
+	db := global.EscapeQuote(fields[0])
+	table := global.EscapeQuote(fields[1])
+	field := global.EscapeQuote(fields[2])
+	key := global.EscapeQuote(fields[3])
+	value := global.EscapeQuote(fields[4])
 	//check value is int or not
 	if _, err := strconv.Atoi(value); err != nil {
 		return "", fmt.Errorf("Invalid SQL data - [%s], the value must be int", str)
 	}
 
-	sql := fmt.Sprintf("SELECT %s FROM %s.%s WHERE %s = %s", field, db, table, key, value)
+	sql := fmt.Sprintf("SELECT `%s` FROM `%s`.`%s` WHERE `%s` = %s", field, db, table, key, value)
 	return sql, nil
 }
diff --git a/probe/client/postgres/postgres.go b/probe/client/postgres/postgres.go
index ca1f01c..43fcf47 100644
--- a/probe/client/postgres/postgres.go
+++ b/probe/client/postgres/postgres.go
@@ -188,18 +188,18 @@ func (r *PostgreSQL) getSQL(str string) (string, string, error) {
 	}
 	fields := strings.Split(str, ":")
 	if len(fields) != 5 {
 		return "", "", fmt.Errorf("Invalid SQL data - [%s]. (syntax: database:table:field:key:value)", str)
 	}
-	db := fields[0]
-	table := fields[1]
-	field := fields[2]
-	key := fields[3]
-	value := fields[4]
+	db := global.EscapeQuote(fields[0])
+	table := global.EscapeQuote(fields[1])
+	field := global.EscapeQuote(fields[2])
+	key := global.EscapeQuote(fields[3])
+	value := global.EscapeQuote(fields[4])
 	//check value is int or not
 	if _, err := strconv.Atoi(value); err != nil {
 		return "", "", fmt.Errorf("Invalid SQL data - [%s], the value must be int", str)
 	}
 
-	sql := fmt.Sprintf("SELECT %s FROM %s WHERE %s = %s", field, table, key, value)
+	sql := fmt.Sprintf(`SELECT "%s" FROM "%s" WHERE "%s" = %s`, field, table, key, value)
 	return db, sql, nil
 }

Vulnerable code: 		for k, v := range r.Data {
			log.Debugf("[%s / %s / %s] - Verifying Data - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
			sql, err := r.getSQL(k)
			if err != nil {
				return false, err.Error()
			}
			log.Debugf("[%s / %s / %s] - SQL - [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, sql)
			rows, err := db.Query(sql)
			if err != nil {
				return false, err.Error()
			}
			if !rows.Next() {
				rows.Close()
				return false, fmt.Sprintf("No data found for [%s]", k)
			}
			//check the value is equal to the value in data
			var value string
			if err := rows.Scan(&value); err != nil {
				rows.Close()
				return false, err.Error()
			}
			if value != v {
				rows.Close()
				return false, fmt.Sprintf("Value not match for [%s] expected [%s] got [%s] ", k, v, value)
			}
			rows.Close()
			log.Debugf("[%s / %s / %s] - Data Verified Successfully! - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
		}
	} else {
		err = db.Ping()
		if err != nil {
		row, err := db.Query("show status like \"uptime\"") // run a SQL to test
		if err != nil {
		defer row.Close()
	db := fields[0]
	table := fields[1]
	field := fields[2]
	key := fields[3]
	value := fields[4]
	sql := fmt.Sprintf("SELECT %s FROM %s.%s WHERE %s = %s", field, db, table, key, value)
	db := fields[0]
	table := fields[1]
	field := fields[2]
	key := fields[3]
	value := fields[4]
	sql := fmt.Sprintf("SELECT %s FROM %s WHERE %s = %s", field, table, key, value)
Fixed code: 
// EscapeQuote escape the string the single quote, double quote, and backtick
func EscapeQuote(str string) string {
	type Escape struct {
		From string
		To   string
	}
	escape := []Escape{
		{From: "`", To: ""}, // remove the backtick
		{From: `\`, To: `\\`},
		{From: `'`, To: `\'`},
		{From: `"`, To: `\"`},
	}

	for _, e := range escape {
		str = strings.ReplaceAll(str, e.From, e.To)
	}
	return str
}

func TestEscape(t *testing.T) {
	assert.Equal(t, "test", EscapeQuote("test"))
	assert.Equal(t, "test", EscapeQuote("`test`"))
	assert.Equal(t, `\'test\'`, EscapeQuote("'test'"))
	assert.Equal(t, `\"test\"`, EscapeQuote(`"test"`))
	assert.Equal(t, `\\test\\`, EscapeQuote(`\test\`))

}
		if err := r.ProbeWithDataVerification(db); err != nil {
	} else {
		if err := r.ProbeWithPing(db); err != nil {
// ProbeWithPing do the health check with ping
func (r *MySQL) ProbeWithPing(db *sql.DB) error {
	if err := db.Ping(); err != nil {
		return err
	}
	row, err := db.Query("show status like \"uptime\"") // run a SQL to test
	if err != nil {
		return err
	}
	defer row.Close()
	return nil
}

// ProbeWithDataVerification do the health check with data verification
func (r *MySQL) ProbeWithDataVerification(db *sql.DB) error {
	for k, v := range r.Data {
		log.Debugf("[%s / %s / %s] - Verifying Data - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
		sql, err := r.getSQL(k)
		if err != nil {
			return err
		}
		log.Debugf("[%s / %s / %s] - SQL - [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, sql)
		rows, err := db.Query(sql)
		if err != nil {
			return err
		}
		if !rows.Next() {
			rows.Close()
			return fmt.Errorf("No data found for [%s]", k)
		}
		//check the value is equal to the value in data
		var value string
		if err := rows.Scan(&value); err != nil {
			rows.Close()
			return err
		}
		if value != v {
			rows.Close()
			return fmt.Errorf("Value not match for [%s] expected [%s] got [%s] ", k, v, value)
		}
		rows.Close()
		log.Debugf("[%s / %s / %s] - Data Verified Successfully! - [%s] : [%s]", r.ProbeKind, r.ProbeName, r.ProbeTag, k, v)
	}
	return nil
}

	db := global.EscapeQuote(fields[0])
	table := global.EscapeQuote(fields[1])
	field := global.EscapeQuote(fields[2])
	key := global.EscapeQuote(fields[3])
	value := global.EscapeQuote(fields[4])
	sql := fmt.Sprintf("SELECT `%s` FROM `%s`.`%s` WHERE `%s` = %s", field, db, table, key, value)
	db := global.EscapeQuote(fields[0])
	table := global.EscapeQuote(fields[1])
	field := global.EscapeQuote(fields[2])
	key := global.EscapeQuote(fields[3])
	value := global.EscapeQuote(fields[4])
	sql := fmt.Sprintf(`SELECT "%s" FROM "%s" WHERE "%s" = %s`, field, table, key, value)