CVE-2023-30625: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: rudder-server is part of RudderStack, an open source Customer Data Platform (CDP). Versions of rudder-server prior to 1.3.0-rc.1 are vulnerable to SQL injection. This issue may lead to Remote Code Execution (RCE) due to the `rudder` role in PostgresSQL having superuser permissions by default. Version 1.3.0-rc.1 contains patches for this issue.
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: 8.8
Published: 2023-06-16T17:15Z
Repository: https://github.com/rudderlabs/rudder-server
Language: Go
Files changed: warehouse/warehouse.go
Commit date: 2022-11-04 23:26:55 +0530
Commit message: fix: changed query to accept user input in prepared sql statement (#2652)

* Changed query to accept user input in prepared sql statement

* Changed query to accept user input in parameterized query

* Changed query to accept user input in parameterized query

* Changed query to accept user input in parameterized query

* Changed query to accept user input in parameterized query
Diff: diff --git a/warehouse/warehouse.go b/warehouse/warehouse.go
index 79cba136..3395403e 100644
--- a/warehouse/warehouse.go
+++ b/warehouse/warehouse.go
@@ -1792,10 +1792,11 @@ func pendingEventsHandler(w http.ResponseWriter, r *http.Request) {
 
 	w.Write(resBody)
 }
 
 func getPendingStagingFileCount(sourceOrDestId string, isSourceId bool) (fileCount int64, err error) {
+	sourceOrDestId = pq.QuoteIdentifier(sourceOrDestId)
 	sourceOrDestColumn := ""
 	if isSourceId {
 		sourceOrDestColumn = "source_id"
 	} else {
 		sourceOrDestColumn = "destination_id"
@@ -1805,20 +1806,18 @@ func getPendingStagingFileCount(sourceOrDestId string, isSourceId bool) (fileCou
 		SELECT
 		  MAX(end_staging_file_id)
 		FROM
 		  %[1]s
 		WHERE
-		  %[1]s.%[3]s = '%[2]s';
+		  %[2]s = $1;
 `,
 		warehouseutils.WarehouseUploadsTable,
-		sourceOrDestId,
 		sourceOrDestColumn,
 	)
-
-	err = dbHandle.QueryRow(sqlStatement).Scan(&lastStagingFileIDRes)
+	err = dbHandle.QueryRow(sqlStatement, sourceOrDestId).Scan(&lastStagingFileIDRes)
 	if err != nil && err != sql.ErrNoRows {
-		err = fmt.Errorf("query: %s failed with Error : %w", sqlStatement, err)
+		err = fmt.Errorf("query: %s run failed with Error : %w", sqlStatement, err)
 		return
 	}
 	lastStagingFileID := int64(0)
 	if lastStagingFileIDRes.Valid {
 		lastStagingFileID = lastStagingFileIDRes.Int64
@@ -1828,22 +1827,20 @@ func getPendingStagingFileCount(sourceOrDestId string, isSourceId bool) (fileCou
 		SELECT
 		  COUNT(*)
 		FROM
 		  %[1]s
 		WHERE
-		  %[1]s.id > %[2]v
-		  AND %[1]s.%[4]s = '%[3]s';
+		  id > %[2]v
+		  AND %[3]s = $1;
 `,
 		warehouseutils.WarehouseStagingFilesTable,
 		lastStagingFileID,
-		sourceOrDestId,
 		sourceOrDestColumn,
 	)
-
-	err = dbHandle.QueryRow(sqlStatement).Scan(&fileCount)
+	err = dbHandle.QueryRow(sqlStatement, sourceOrDestId).Scan(&fileCount)
 	if err != nil && err != sql.ErrNoRows {
-		err = fmt.Errorf("query: %s failed with Error : %w", sqlStatement, err)
+		err = fmt.Errorf("query: %s run failed with Error : %w", sqlStatement, err)
 		return
 	}
 
 	return fileCount, nil
 }

Vulnerable code: 		  %[1]s.%[3]s = '%[2]s';
		sourceOrDestId,

	err = dbHandle.QueryRow(sqlStatement).Scan(&lastStagingFileIDRes)
		err = fmt.Errorf("query: %s failed with Error : %w", sqlStatement, err)
		  %[1]s.id > %[2]v
		  AND %[1]s.%[4]s = '%[3]s';
		sourceOrDestId,

	err = dbHandle.QueryRow(sqlStatement).Scan(&fileCount)
		err = fmt.Errorf("query: %s failed with Error : %w", sqlStatement, err)
Fixed code: 	sourceOrDestId = pq.QuoteIdentifier(sourceOrDestId)
		  %[2]s = $1;
	err = dbHandle.QueryRow(sqlStatement, sourceOrDestId).Scan(&lastStagingFileIDRes)
		err = fmt.Errorf("query: %s run failed with Error : %w", sqlStatement, err)
		  id > %[2]v
		  AND %[3]s = $1;
	err = dbHandle.QueryRow(sqlStatement, sourceOrDestId).Scan(&fileCount)
		err = fmt.Errorf("query: %s run failed with Error : %w", sqlStatement, err)