Skip to main content

SQL injection - Code

Need​

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

Context​

  • Usage of Go 1.16 for building high-performance and scalable applications
  • Usage of gin-gonic/gin for building web applications in Go
  • Usage of go-sql-driver/mysql for connecting to and interacting with MySQL databases

Description​

Non compliant code​

package main

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

func main() {
	db, err := sql.Open("mysql", "user:password@/dbname")

	if err != nil {
		panic(err.Error())
	}

	defer db.Close()

	r := gin.Default()

	r.POST("/user", func(c *gin.Context) {
		username := c.PostForm("username")
		password := c.PostForm("password")

		query := fmt.Sprintf("INSERT INTO users(username, password) VALUES('%s', '%s')", username, password)
		_, err := db.Exec(query)

		if err != nil {
			panic(err.Error())
		}

		c.JSON(http.StatusOK, gin.H{
			"message": "user created successfully",
		})
	})

	r.Run()
}

In this Go code, we're using the Gin framework to set up a simple web server with a POST route at "/user". This route is intended to create a new user in a MySQL database with a username and password provided in the POST data.

The vulnerability lies in the following line of code:

query := fmt.Sprintf("INSERT INTO users(username, password) VALUES('%s', '%s')", username, password)

Here, we're using string formatting to create an SQL query that inserts the provided username and password directly into the query string. This is a textbook example of an SQL injection vulnerability.

An attacker could potentially provide a malicious string as the 'username' or 'password', which could manipulate the SQL query to perform unintended actions. For example, an attacker could provide a username like admin'; --, which would result in the following SQL query: INSERT INTO users(username, password) VALUES('admin'; --', 'password'). This would effectively comment out the password check, potentially allowing unauthorized access.

The vulnerability could be fixed by using parameterized SQL queries, which ensure that user input is always treated as literal values, not part of the SQL command.

Steps​

  • Use parameterized statements or prepared statements to execute SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Avoid dynamically generating SQL queries with user input.
  • Implement proper input validation and data sanitization techniques.
  • Use an ORM (Object-Relational Mapping) library to handle database operations.
  • Follow secure coding practices and guidelines for database access and query execution.

Compliant code​

package main

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

func main() {
	db, err := sql.Open("mysql", "user:password@/dbname")

	if err != nil {
		panic(err.Error())
	}

	defer db.Close()

	r := gin.Default()

	r.POST("/user", func(c *gin.Context) {
		username := c.PostForm("username")
		password := c.PostForm("password")

		stmt, err := db.Prepare("INSERT INTO users(username, password) VALUES(?, ?)")
		if err != nil {
			panic(err.Error())
		}

		_, err = stmt.Exec(username, password)
		if err != nil {
			panic(err.Error())
		}

		c.JSON(http.StatusOK, gin.H{
			"message": "user created successfully",
		})
	})

	r.Run()
}

The original code was vulnerable to SQL Injection attacks because it was dynamically generating SQL queries using user input without any validation or sanitization. This could allow an attacker to manipulate the SQL query and perform unauthorized actions on the database.

The fixed code uses parameterized statements (also known as prepared statements) to execute SQL queries. This means that the SQL query is defined once and then executed multiple times with different parameters. This approach separates the SQL logic from the data, which makes it much harder for an attacker to inject malicious SQL code.

In the fixed code, the db.Prepare function is used to create a prepared statement. The ? placeholders in the SQL query are used to represent the parameters that will be inserted into the query. The stmt.Exec function is then used to execute the prepared statement with the provided parameters.

This approach effectively mitigates the risk of SQL Injection attacks. However, it's also recommended to implement proper input validation and data sanitization techniques to further enhance the security of the application. For example, you could check if the username and password meet certain criteria (e.g., length, character types, etc.) before using them in the SQL query.

Additionally, using an ORM (Object-Relational Mapping) library can help to further abstract and secure database operations. An ORM library can automatically handle many of the complexities of database access and query execution, including the prevention of SQL Injection attacks.

Finally, always follow secure coding practices and guidelines when working with databases and executing SQL queries. This includes using least privilege principles, regularly reviewing and updating your code, and staying informed about the latest security vulnerabilities and how to mitigate them.

References​

Last updated on