Golang SQL Injection By Example

If you are just getting into backend development in Golang, you want to be aware of secure coding conventions, and one application security aspect of that is understanding SQL injection meaning in Go and how to fix SQL injection code vulnerabilities.

We will use the Gin web application framework for Golang, an SQLite library, and expose HTTP server routes to interact with Golang SQL interfaces. Through this experiment, we’ll learn how to execute exploits that demonstrate SQL injection in an SQLite database and other databases.

A Golang SQL program

Before diving into a Golang SQL injection example, it's crucial that we set up a proper development environment. This involves installing necessary Go modules and configuring the project to manage dependencies efficiently.

We will use the github.com/mattn/go-sqlite3 and github.com/jmoiron/sqlx packages for the SQL interface.

Using Go modules to manage dependencies

Go modules have risen to become the standard way to manage dependencies in Go projects. They allow you to specify the versions of the packages your project depends on, ensuring consistency across different environments. To use Go modules, you need to initialize your project with a go.mod file.

Note, Snyk has great support for Golang through Snyk Code which is the SAST tool, and Snyk Open Source which is the SCA tool for third-party dependencies. Always best to scan your Go code for vulnerabilities and Snyk fits right in!

To get started with our Golang SQL example, navigate to your project directory and run the following command:

go mod init your_project_name

This command creates a go.mod file in your project directory, which will track the dependencies and their versions.

Install the Golang SQL interface package

The github.com/mattn/go-sqlite3 package is a Go library that provides an interface to SQLite databases. It is essential for executing SQL queries against SQLite databases in your Golang SQL programs and we’ll be using that in our example Go app. To install this package, use the following command:

go get github.com/mattn/go-sqlite3

This command downloads the package and adds it to your go.mod file. The package will be available for use in your project, allowing you to interact with SQLite databases.

Similarly, we are going to also install the github.com/jmoiron/sqlx package which is an extension to Go's standard database/sql package. It provides additional functionality, such as named query support and struct scanning, which can simplify database interactions in Golang SQL applications. To install this package, run:

go get github.com/jmoiron/sqlx

Then, we will be requiring these Go packages as part of the program as follows:

package main

import (
    "encoding/json"
    "fmt"
    "io"
    "net/http"
    "net/url"
    "os"
    "os/exec"
    "time"

    "log/slog"

    // Gin web framework
    "github.com/gin-gonic/gin"

    // SQLite database driver and query builder
	_ "github.com/mattn/go-sqlite3"
	"github.com/jmoiron/sqlx"
)

As you can see from the above Go modules import instructions, the Golang application is going to be an HTTP API that exposes GET and POST routes for us to build on the demonstration of a Golang SQL program.

Golang SQL interfaces

In our Golang web application, the program interfaces with an SQL database using an HTTP route. The HTTP route at /cloudpawnery/image serves GET requests and takes a query parameter of tenantID, for which it will look up all file records on which image conversions were performed in the SQLite database.

Subtly, the Golang SQL interface is demonstrated in the following GET HTTP route API that returns a JSON response of files:

 router.GET("/cloudpawnery/image", func(c *gin.Context) {
        // get tenantID from query parameter
        tenantID := c.Query("tenantID")

	 // pseudo-code :
        for loop-over-db-rows
            if err != nil {
                // handle errors
            }
	     // populate an array of files:
            files = append(files, f)
        }

	 // return files as an object in the JSON response
        c.JSON(http.StatusOK, gin.H{"files": files})
    })

Let’s now focus on the Golang SQL interface part of the program.

We’ll use the sqlx Go module to open the SQLite database and then use the Queryx function to send a SELECT query to the database with a WHERE filter that uses the tenantID from the query parameter in the HTTP GET request.

     db, err := sqlx.Open("sqlite3", "./mydb.db")
        defer db.Close()

        if err != nil {
            slog.Error("Failed to open database", "error", err)
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to open database"})
            return
        }

        rows, err := db.Queryx("SELECT * FROM files WHERE tenant_id = '" + tenantID + "'")
        if err != nil {
            slog.Error("Failed to query database", "error", err)
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to query database"})
            return
        }
        defer rows.Close()

Now that we have obtained a reference to the database records via the rows variable, we can loop through it and assign the SQL query results to the empty File-type array files.

   var files []File
        for rows.Next() {
            var f File
            err := rows.StructScan(&f)
            if err != nil {
                slog.Error("Failed to scan database row", "error", err)
                c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to scan database row"})
                return
            }
            files = append(files, f)
        }

For as long as there are SQL query results to be returned, we enter the loop (hence the rows.Next() call). We then prepare a single File entry via var f File and use the rows.StructScan() query from the sqlx Go module to extract the information from the query and assign it to the f File-type variable.

The following is our HTTP GET route API in full:

 router.GET("/cloudpawnery/image", func(c *gin.Context) {
        // get tenantID from query parameter
        tenantID := c.Query("tenantID")

        db, err := sqlx.Open("sqlite3", "./mydb.db")
        defer db.Close()

        if err != nil {
            slog.Error("Failed to open database", "error", err)
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to open database"})
            return
        }

        rows, err := db.Queryx("SELECT * FROM files WHERE tenant_id = '" + tenantID + "'")
        if err != nil {
            slog.Error("Failed to query database", "error", err)
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to query database"})
            return
        }
        defer rows.Close()

        var files []File
        for rows.Next() {
            var f File
            err := rows.StructScan(&f)
            if err != nil {
                slog.Error("Failed to scan database row", "error", err)
                c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to scan database row"})
                return
            }
            files = append(files, f)
        }

        c.JSON(http.StatusOK, gin.H{"files": files})
    })

But… what about application security? What can go wrong with the above code? Well, SQL injection. Let’s find out more!

SQL Injection in SQLite

SQL injection is a critical security vulnerability that allows attackers to interfere with the SQL queries a Golang application makes to its database. Even by 2024, SQL injection is still a top concern and often featured on cybersecurity exploitation and risk lists. By manipulating input data, attackers can execute arbitrary SQL code, potentially gaining unauthorized access to sensitive data, modifying database contents, or even executing administrative operations. The impact of SQL injection can be severe, leading to data breaches, data loss, and significant reputational damage to organizations.

Demonstrating an SQL injection vulnerability in a Golang application

To illustrate a SQL injection vulnerability in a Golang application, let's build on the above application in which we are making use of the sqlx package as the Golang SQL interface.

We know that the HTTP API interface is a GET request that returns JSON. It receives a tenant identifier via a tenantID query parameter. A request by a consumer (or an attacker) might look like this:

GET http://localhost:6000/cloudpawnery/image?tenantID=3971533981712
Content-Type: application/json

If we got the tenantID correct (and we’re authorized to access this data) we would get a reply as follows:

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Date: Sun, 01 Dec 2024 12:37:05 GMT
Content-Length: 248
Connection: close

{
  "files": [
    {
      "ID": 1,
      "Filename": "john-smith-profile.jpg",
      "Signature": "",
      "TenantID": "3971533981712",
      "CreatedAt": "0001-01-01T00:00:00Z"
    }
  ]
}

Looks great.

However, we could attempt to reverse engineer the SQL query that the Golang program is running to provide this data. For example, it probably queries a files table and it probably filters the files results based on a tenant identifier field such as tenantID or tenant_ID or tenant_identifier.

In fact, if we examine the query code we wrote for the GET route parameter we will find that it is very similar indeed:

rows, err := db.Queryx("SELECT * FROM files WHERE tenant_id = '" + tenantID + "'")

This right there is a vulnerable SQL injection code that we’ve made in our Golang program.

The root of the problem lies with our code that performs string concatenation to append the tenantID variable from user input (the query parameter) into the SQL query itself. By following this insecure SQL code convention, we introduce a vulnerability of SQL injection in SQLite.

With this knowledge, we can see how easy it would be to manipulate the tenantID query parameter so that it changes the meaning of the original SQL query in the Golang program. We can launch the following HTTP request:

GET http://localhost:6000/cloudpawnery/image?tenantID=3971533981712' OR tenant_id='432423
Content-Type: application/json

The value of tenantID is now changed to use a single quote to “close” the where clause field assignment and create a new logical expression with OR tenant_id=’<some-number> which filters on another record in the database and elegantly doesn’t include a closing single quote. Why doesn’t include the closing single quote? Because the query itself is going to do that as part of the SQL query code.

With this knowledge, sending the above HTTP request will result in records returned for both tenants:

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Date: Sun, 01 Dec 2024 12:37:05 GMT
Content-Length: 248
Connection: close

{
  "files": [
    {
      "ID": 1,
      "Filename": "john-smith-profile.jpg",
      "Signature": "",
      "TenantID": "3971533981712",
      "CreatedAt": "0001-01-01T00:00:00Z"
    },
    {
      "ID": 2,
      "Filename": "john-smith-profile.jpg",
      "Signature": "",
      "TenantID": "432423",
      "CreatedAt": "0001-01-01T00:00:00Z"
    }
  ]
}

A Golang SQL Injection for authentication

To provide a more classic example of a Golang SQL injection scenario that demonstrates the SQL injection meaning to developers, see the following Golang code that:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "./example.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	username := "admin' OR '1'='1"
	query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s'", username)

	rows, err := db.Query(query)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		if err := rows.Scan(&id, &name); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("User: %d, %s\n", id, name)
	}
}

The above Golang code snippet builds on the popular authentication bypass for SQL injection known as OR 1=1. The above Golang code, of course, has many other security vulnerabilities which I highly recommend you learn more about if you didn’t recognize them by reading it:

• Using plaintext passwords and insecure hash.

• Improper input validation

• SQL injection

The consequences of SQL injection vulnerabilities in applications can be dire. Attackers can:

• Access sensitive data: Unauthorized access to user data, like we’ve seen with the tenant identifier injection, and other records such as financial information.

• Modify or delete data: While we’ve only shown how to select more data, altering or removing critical data, leading to data integrity issues, are also a significant SQL injection risk.

• Compromise application security: Using SQL injection as a stepping stone to launch other types of attacks, because hackers often chain different vulnerabilities to achieve a higher security impact.

How to fix SQL injection

So now that we have established that SQL injection is a prevalent security vulnerability that can have severe consequences if not addressed properly, let’s learn more about how to fix SQL injection.

Use of prepared statements and parameterized queries

One of the most effective ways to prevent SQL injection is by using prepared statements and parameterized queries as part of the SQL query construction. These techniques, commonly followed in many libraries and other programming languages and platforms, ensure that user input is treated as data, not executable code, thereby preventing malicious SQL code from being executed.

In Golang, we can still keep using the sqlx package to facilitate the use of prepared statements (which we didn’t opt-in before in the vulnerable application above).

Here's an example of how to implement secure SQL queries using sqlx:

package main

import (
	"fmt"
	"log"

	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

type User struct {
	ID   int    `db:"id"`
	Name string `db:"name"`
}

func main() {
	db, err := sqlx.Connect("sqlite3", "example.db")
	if err != nil {
		log.Fatalln(err)
	}

	var user User
	username := "john_doe"
	query := "SELECT id, name FROM users WHERE name = ?"
	err = db.Get(&user, query, username)
	if err != nil {
		log.Fatalln(err)
	}

	fmt.Printf("User ID: %d, Name: %s\n", user.ID, user.Name)
}

In this Golang code, the ? placeholder character is used to safely insert the username variable into the SQL query. This tells the database engine that this is a data type to work on rather than part of the SQL query, and that the input is then properly escaped, preventing SQL injection attacks in Golang.

Utilizing Snyk Code to detect and fix SQL injection vulnerabilities

While following best practices is essential like SQL prepared statements, automated tools can further enhance the security of your code by detecting vulnerabilities you might miss. Snyk Code is a powerful tool that can help you identify and fix SQL injection vulnerabilities in your Golang applications.

Integrating Snyk into your development workflow offers several benefits:

• Automated scanning: Snyk Code automatically scans your source code for vulnerabilities, including SQL injection, providing you with actionable insights.

• Continuous monitoring: Snyk continuously monitors your codebase for new vulnerabilities, ensuring that your applications remain secure over time.

• Developer-friendly: Snyk integrates seamlessly with IDEs such as VS Code and other development workflows, making it easy for developers to incorporate security into their workflow without disrupting productivity.

To take advantage of Snyk's powerful security features and protect your applications from SQL injection vulnerabilities, sign up for a free Snyk account today. With Snyk, you can ensure your Golang applications are secure and compliant with industry standards.

I highly recommend the following continued learning resources to dive deeper into SQL injection vulnerabilities:

• Brian Vermeer’s SQL injection cheat sheet that teaches you about 8 best practices to prevent SQL injection attacks.

• Eric Smalling’s Go security cheatsheet

• Lastly, Understanding Command Injection vulnerabilities in Go