Skip to main content

SQL injection

Need​

Implementation of proper input validation and use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context​

  • Usage of Go 1.16 for building high-performance and concurrent applications
  • Usage of gin-gonic/gin for building web applications in Go
  • Usage of go-sql-driver/mysql for connecting to a MySQL database in Go

Description​

Non compliant code​

package main

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

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

	r := gin.Default()
	r.GET("/user/:name", func(c *gin.Context) {
		name := c.Param("name")
		rows, err := db.Query(fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", name))
		if err != nil {
			panic(err)
		}
		defer rows.Close()

		var result []string
		for rows.Next() {
			var name string
			if err := rows.Scan(&name); err != nil {
				panic(err)
			}
			result = append(result, name)
		}

		c.JSON(200, gin.H{
			"users": result,
		})
	})
	r.Run()
}

The above code is a simple Go backend application using the Gin framework and interacting with a MySQL database. The application has a single endpoint /user/:name that fetches a user from the database by their name.

The vulnerability lies in the way SQL query is constructed in the handler function for the endpoint. The user-provided name is directly embedded into the SQL query string without any validation or parameterization:

rows, err := db.Query(fmt.Sprintf("SELECT * FROM users WHERE name = '%s'", name))

This leaves the application open to SQL Injection attacks. An attacker could provide a specially crafted name that alters the SQL query to reveal sensitive information or perform unauthorized actions. For example, an attacker could provide a name like ' OR '1'='1 which would return all users, not just the one specified by the name.

The correct way to perform this operation would be to use parameterized queries or prepared statements, where the SQL query defines placeholders for the user-provided values, and the database driver automatically escapes the values to prevent SQL Injection.

Steps​

  • Use parameterized statements or prepared statements to execute SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Avoid constructing SQL queries dynamically with user input.
  • Implement proper input validation and data sanitization techniques.
  • Implement access controls and limit privileges to prevent unauthorized access to the database.
  • Regularly update and patch the database software to fix any known vulnerabilities.
  • Implement a web application firewall (WAF) to detect and block SQL injection attacks.
  • Educate developers about secure coding practices and the risks associated with SQL injection.
  • Perform regular security audits and penetration testing to identify and fix vulnerabilities.

Compliant code​

package main

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

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

	r := gin.Default()
	r.GET("/user/:name", func(c *gin.Context) {
		name := c.Param("name")
		stmt, err := db.Prepare("SELECT * FROM users WHERE name = ?")
		if err != nil {
			panic(err)
		}
		defer stmt.Close()

		rows, err := stmt.Query(name)
		if err != nil {
			panic(err)
		}
		defer rows.Close()

		var result []string
		for rows.Next() {
			var name string
			if err := rows.Scan(&name); err != nil {
				panic(err)
			}
			result = append(result, name)
		}

		c.JSON(200, gin.H{
			"users": result,
		})
	})
	r.Run()
}

The original code was vulnerable to SQL Injection attacks because it was dynamically constructing SQL queries using user input. This is a common vulnerability that allows an attacker to manipulate the SQL query to gain unauthorized access to the database, modify the database structure, or access sensitive information.

The fixed code uses parameterized queries, also known as prepared statements, to prevent SQL Injection attacks. In this approach, SQL code and data are sent to the SQL server separately, and the SQL server ensures that the data cannot be treated as SQL code. This effectively eliminates the possibility of SQL Injection attacks.

In the fixed code, the db.Prepare function is used to create a prepared statement. The ? placeholder is used in the SQL query to indicate where the user input should be inserted. The stmt.Query function is then used to execute the prepared statement with the user input as a parameter. This ensures that the user input is properly escaped and cannot be used to manipulate the SQL query.

In addition to using prepared statements, it's also important to validate and sanitize user input, implement access controls, regularly update and patch the database software, use a web application firewall, educate developers about secure coding practices, and perform regular security audits and penetration testing.

References​

Last updated on