Need

Prevention of SQL Injection in headers and application fields

Context

• Usage of Go 1.16 for developing high-performance and efficient applications
• Usage of Gin framework for building web applications in Go
• Usage of database/sql for interacting with SQL databases
• Usage of go-sql-driver/mysql for connecting and interacting with a MySQL database in Go
• Usage of fmt for formatting text output

Description

Non compliant code

        package main

import (
	"github.com/gin-gonic/gin"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "user:password@/dbname")
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	r := gin.Default()

	r.GET("/client", func(c *gin.Context) {
		idClient := c.GetHeader("idClient")
		var result string
		err := db.QueryRow(fmt.Sprintf("SELECT name FROM clients WHERE id = '%s'", idClient)).Scan(&result)
		if err != nil {
			c.JSON(500, gin.H{"error": err.Error()})
			return
		}
		c.JSON(200, gin.H{"client_name": result})
	})

	r.Run()
}
        
        

The vulnerable code is a simple Go application using the Gin framework and the go-sql-driver/mysql driver to interact with a MySQL database.

The vulnerability lies in the /client route handler function. The application retrieves the idClient header from the incoming request and directly uses it to construct a SQL query. This is a classic example of unparameterized SQL, which opens the door to SQL Injection attacks.

                idClient := c.GetHeader("idClient")
var result string
err := db.QueryRow(fmt.Sprintf("SELECT name FROM clients WHERE id = '%s'", idClient)).Scan(&result)

            

Steps

• Use parameterized queries or prepared statements to sanitize user input.
• Validate and sanitize the input received from the headers before using it in the SQL query.
• Implement proper input validation to ensure that only valid data is accepted.
• Avoid concatenating user input directly into the SQL query.
• Use a whitelist approach to validate and sanitize the input.
• Implement proper error handling to handle any potential SQL injection attempts.

Compliant code

        package main

import (
	"github.com/gin-gonic/gin"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "user:password@/dbname")
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	r := gin.Default()

	r.GET("/client", func(c *gin.Context) {
		idClient := c.GetHeader("idClient")
		stmt, err := db.Prepare("SELECT name FROM clients WHERE id = ?")
		if err != nil {
			c.JSON(500, gin.H{"error": err.Error()})
			return
		}
		defer stmt.Close()
		var result string
		err = stmt.QueryRow(idClient).Scan(&result)
		if err != nil {
			c.JSON(500, gin.H{"error": err.Error()})
			return
		}
		c.JSON(200, gin.H{"client_name": result})
	})

	r.Run()
}
        
        

The original code was vulnerable to SQL Injection through the idClient header. This was because the idClient header was directly concatenated into the SQL query, which allows an attacker to manipulate the SQL query by injecting SQL statements into the idClient header.

The fixed code uses parameterized queries to prevent SQL Injection. Instead of concatenating the idClient header directly into the SQL query, a placeholder ( ?) is used. The actual idClient value is passed as a parameter to the QueryRow method of the prepared statement. This ensures that the idClient value is properly escaped, preventing SQL Injection.

In addition, the fixed code also adds error handling for the preparation of the SQL statement. If there is an error while preparing the SQL statement (for example, if the SQL syntax is incorrect), the error will be returned as a JSON response with a 500 status code. This helps to prevent potential SQL Injection attempts from causing unhandled errors in the application.

References

• 155. SQL Injection - Headers