CVE-2022-3751: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: SQL Injection in GitHub repository owncast/owncast prior to 0.0.13.
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: 2022-11-29T21:15Z
Repository: https://github.com/owncast/owncast
Language: Go
Files changed: core/chat/persistence.go
Commit date: 2022-10-28 12:32:42 -0700
Commit message: Using prepared statements for SQL queries. (#2257)

* using prepared statements for sql query for fixing sql injection

* returning error in getChat instead of logging
Diff: diff --git a/core/chat/persistence.go b/core/chat/persistence.go
index d50bb06c..fe17e0ab 100644
--- a/core/chat/persistence.go
+++ b/core/chat/persistence.go
@@ -1,9 +1,9 @@
 package chat
 
 import (
-	"fmt"
+	"database/sql"
 	"strings"
 	"time"
 
 	"github.com/owncast/owncast/core/chat/events"
 	"github.com/owncast/owncast/core/data"
@@ -205,24 +205,18 @@ type rowData struct {
 	userAuthenticatedAt *time.Time
 	userScopes          *string
 	userType            *string
 }
 
-func getChat(query string) []interface{} {
+func getChat(rows *sql.Rows) ([]interface{}, error) {
 	history := make([]interface{}, 0)
-	rows, err := _datastore.DB.Query(query)
-	if err != nil || rows.Err() != nil {
-		log.Errorln("error fetching chat history", err)
-		return history
-	}
-	defer rows.Close()
 
 	for rows.Next() {
 		row := rowData{}
 
 		// Convert a database row into a chat event
-		if err = rows.Scan(
+		if err := rows.Scan(
 			&row.id,
 			&row.userID,
 			&row.body,
 			&row.title,
 			&row.subtitle,
@@ -239,13 +233,11 @@ func getChat(query string) []interface{} {
 			&row.userNameChangedAt,
 			&row.userAuthenticatedAt,
 			&row.userScopes,
 			&row.userType,
 		); err != nil {
-			log.Errorln(err)
-			log.Errorln("There is a problem converting query to chat objects. Please report this:", query)
-			break
+			return nil, err
 		}
 
 		var message interface{}
 
 		switch row.eventType {
@@ -264,35 +256,104 @@ func getChat(query string) []interface{} {
 		}
 
 		history = append(history, message)
 	}
 
-	return history
+	return history, nil
 }
 
 var _historyCache *[]interface{}
 
 // GetChatModerationHistory will return all the chat messages suitable for moderation purposes.
 func GetChatModerationHistory() []interface{} {
 	if _historyCache != nil {
 		return *_historyCache
 	}
 
+	tx, err := _datastore.DB.Begin()
+	if err != nil {
+		log.Errorln("error fetching chat moderation history", err)
+		return nil
+	}
+
+	defer tx.Rollback() // nolint
+
 	// Get all messages regardless of visibility
 	query := "SELECT messages.id, user_id, body, title, subtitle, image, link, eventType, hidden_at, timestamp, display_name, display_color, created_at, disabled_at, previous_names, namechanged_at, authenticated_at, scopes, type FROM messages INNER JOIN users ON messages.user_id = users.id ORDER BY timestamp DESC"
-	result := getChat(query)
+	stmt, err := tx.Prepare(query)
+	if err != nil {
+		log.Errorln("error fetching chat moderation history", err)
+		return nil
+	}
+
+	rows, err := stmt.Query()
+	if err != nil {
+		log.Errorln("error fetching chat moderation history", err)
+		return nil
+	}
+
+	defer stmt.Close()
+	defer rows.Close()
+
+	result, err := getChat(rows)
+
+	if err != nil {
+		log.Errorln(err)
+		log.Errorln("There is a problem enumerating chat message rows. Please report this:", query)
+		return nil
+	}
 
 	_historyCache = &result
 
+	if err = tx.Commit(); err != nil {
+		log.Errorln("error fetching chat moderation history", err)
+		return nil
+	}
+
 	return result
 }
 
 // GetChatHistory will return all the chat messages suitable for returning as user-facing chat history.
 func GetChatHistory() []interface{} {
+	tx, err := _datastore.DB.Begin()
+	if err != nil {
+		log.Errorln("error fetching chat history", err)
+		return nil
+	}
+
+	defer tx.Rollback() // nolint
+
 	// Get all visible messages
-	query := fmt.Sprintf("SELECT messages.id, messages.user_id, messages.body, messages.title, messages.subtitle, messages.image, messages.link, messages.eventType, messages.hidden_at, messages.timestamp, users.display_name, users.display_color, users.created_at, users.disabled_at, users.previous_names, users.namechanged_at, users.authenticated_at, users.scopes, users.type FROM users JOIN messages ON users.id = messages.user_id WHERE hidden_at IS NULL AND disabled_at IS NULL ORDER BY timestamp DESC LIMIT %d", maxBacklogNumber)
-	m := getChat(query)
+	query := "SELECT messages.id, messages.user_id, messages.body, messages.title, messages.subtitle, messages.image, messages.link, messages.eventType, messages.hidden_at, messages.timestamp, users.display_name, users.display_color, users.created_at, users.disabled_at, users.previous_names, users.namechanged_at, users.authenticated_at, users.scopes, users.type FROM users JOIN messages ON users.id = messages.user_id WHERE hidden_at IS NULL AND disabled_at IS NULL ORDER BY timestamp DESC LIMIT ?"
+
+	stmt, err := tx.Prepare(query)
+	if err != nil {
+		log.Errorln("error fetching chat history", err)
+		return nil
+	}
+
+	rows, err := stmt.Query(maxBacklogNumber)
+	if err != nil {
+		log.Errorln("error fetching chat history", err)
+		return nil
+	}
+
+	defer stmt.Close()
+	defer rows.Close()
+
+	m, err := getChat(rows)
+
+	if err != nil {
+		log.Errorln(err)
+		log.Errorln("There is a problem enumerating chat message rows. Please report this:", query)
+		return nil
+	}
+
+	if err = tx.Commit(); err != nil {
+		log.Errorln("error fetching chat history", err)
+		return nil
+	}
 
 	// Invert order of messages
 	for i, j := 0, len(m)-1; i < j; i, j = i+1, j-1 {
 		m[i], m[j] = m[j], m[i]
 	}
@@ -305,23 +366,58 @@ func GetChatHistory() []interface{} {
 func SetMessageVisibilityForUserID(userID string, visible bool) error {
 	defer func() {
 		_historyCache = nil
 	}()
 
+	tx, err := _datastore.DB.Begin()
+	if err != nil {
+		log.Errorln("error while setting message visibility", err)
+		return nil
+	}
+
+	defer tx.Rollback() // nolint
+	query := "SELECT messages.id, user_id, body, title, subtitle, image, link, eventType, hidden_at, timestamp, display_name, display_color, created_at, disabled_at,  previous_names, namechanged_at, authenticated_at, scopes, type FROM messages INNER JOIN users ON messages.user_id = users.id WHERE user_id IS ?"
+
+	stmt, err := tx.Prepare(query)
+	if err != nil {
+		log.Errorln("error while setting message visibility", err)
+		return nil
+	}
+
+	rows, err := stmt.Query(userID)
+	if err != nil {
+		log.Errorln("error while setting message visibility", err)
+		return nil
+	}
+
+	defer stmt.Close()
+	defer rows.Close()
+
 	// Get a list of IDs to send to the connected clients to hide
 	ids := make([]string, 0)
-	query := fmt.Sprintf("SELECT messages.id, user_id, body, title, subtitle, image, link, eventType, hidden_at, timestamp, display_name, display_color, created_at, disabled_at,  previous_names, namechanged_at, authenticated_at, scopes, type FROM messages INNER JOIN users ON messages.user_id = users.id WHERE user_id IS '%s'", userID)
-	messages := getChat(query)
+
+	messages, err := getChat(rows)
+
+	if err != nil {
+		log.Errorln(err)
+		log.Errorln("There is a problem enumerating chat message rows. Please report this:", query)
+		return nil
+	}
 
 	if len(messages) == 0 {
 		return nil
 	}
 
 	for _, message := range messages {
 		ids = append(ids, message.(events.UserMessageEvent).ID)
 	}
 
+	if err = tx.Commit(); err != nil {
+		log.Errorln("error while setting message visibility ", err)
+		return nil
+	}
+
 	// Tell the clients to hide/show these messages.
 	return SetMessagesVisibility(ids, visible)
 }
 
 func saveMessageVisibility(messageIDs []string, visible bool) error {

Vulnerable code: 	"fmt"
func getChat(query string) []interface{} {
	rows, err := _datastore.DB.Query(query)
	if err != nil || rows.Err() != nil {
		log.Errorln("error fetching chat history", err)
		return history
	}
	defer rows.Close()
		if err = rows.Scan(
			log.Errorln(err)
			log.Errorln("There is a problem converting query to chat objects. Please report this:", query)
			break
	return history
	result := getChat(query)
	query := fmt.Sprintf("SELECT messages.id, messages.user_id, messages.body, messages.title, messages.subtitle, messages.image, messages.link, messages.eventType, messages.hidden_at, messages.timestamp, users.display_name, users.display_color, users.created_at, users.disabled_at, users.previous_names, users.namechanged_at, users.authenticated_at, users.scopes, users.type FROM users JOIN messages ON users.id = messages.user_id WHERE hidden_at IS NULL AND disabled_at IS NULL ORDER BY timestamp DESC LIMIT %d", maxBacklogNumber)
	m := getChat(query)
	query := fmt.Sprintf("SELECT messages.id, user_id, body, title, subtitle, image, link, eventType, hidden_at, timestamp, display_name, display_color, created_at, disabled_at,  previous_names, namechanged_at, authenticated_at, scopes, type FROM messages INNER JOIN users ON messages.user_id = users.id WHERE user_id IS '%s'", userID)
	messages := getChat(query)
Fixed code: 	"database/sql"
func getChat(rows *sql.Rows) ([]interface{}, error) {
		if err := rows.Scan(
			return nil, err
	return history, nil
	tx, err := _datastore.DB.Begin()
	if err != nil {
		log.Errorln("error fetching chat moderation history", err)
		return nil
	}

	defer tx.Rollback() // nolint

	stmt, err := tx.Prepare(query)
	if err != nil {
		log.Errorln("error fetching chat moderation history", err)
		return nil
	}

	rows, err := stmt.Query()
	if err != nil {
		log.Errorln("error fetching chat moderation history", err)
		return nil
	}

	defer stmt.Close()
	defer rows.Close()

	result, err := getChat(rows)

	if err != nil {
		log.Errorln(err)
		log.Errorln("There is a problem enumerating chat message rows. Please report this:", query)
		return nil
	}
	if err = tx.Commit(); err != nil {
		log.Errorln("error fetching chat moderation history", err)
		return nil
	}

	tx, err := _datastore.DB.Begin()
	if err != nil {
		log.Errorln("error fetching chat history", err)
		return nil
	}

	defer tx.Rollback() // nolint

	query := "SELECT messages.id, messages.user_id, messages.body, messages.title, messages.subtitle, messages.image, messages.link, messages.eventType, messages.hidden_at, messages.timestamp, users.display_name, users.display_color, users.created_at, users.disabled_at, users.previous_names, users.namechanged_at, users.authenticated_at, users.scopes, users.type FROM users JOIN messages ON users.id = messages.user_id WHERE hidden_at IS NULL AND disabled_at IS NULL ORDER BY timestamp DESC LIMIT ?"

	stmt, err := tx.Prepare(query)
	if err != nil {
		log.Errorln("error fetching chat history", err)
		return nil
	}

	rows, err := stmt.Query(maxBacklogNumber)
	if err != nil {
		log.Errorln("error fetching chat history", err)
		return nil
	}

	defer stmt.Close()
	defer rows.Close()

	m, err := getChat(rows)

	if err != nil {
		log.Errorln(err)
		log.Errorln("There is a problem enumerating chat message rows. Please report this:", query)
		return nil
	}

	if err = tx.Commit(); err != nil {
		log.Errorln("error fetching chat history", err)
		return nil
	}
	tx, err := _datastore.DB.Begin()
	if err != nil {
		log.Errorln("error while setting message visibility", err)
		return nil
	}

	defer tx.Rollback() // nolint
	query := "SELECT messages.id, user_id, body, title, subtitle, image, link, eventType, hidden_at, timestamp, display_name, display_color, created_at, disabled_at,  previous_names, namechanged_at, authenticated_at, scopes, type FROM messages INNER JOIN users ON messages.user_id = users.id WHERE user_id IS ?"

	stmt, err := tx.Prepare(query)
	if err != nil {
		log.Errorln("error while setting message visibility", err)
		return nil
	}

	rows, err := stmt.Query(userID)
	if err != nil {
		log.Errorln("error while setting message visibility", err)
		return nil
	}

	defer stmt.Close()
	defer rows.Close()


	messages, err := getChat(rows)

	if err != nil {
		log.Errorln(err)
		log.Errorln("There is a problem enumerating chat message rows. Please report this:", query)
		return nil
	}
	if err = tx.Commit(); err != nil {
		log.Errorln("error while setting message visibility ", err)
		return nil
	}