Defeating SQL Injection Attacks in Go: Preventing at scale

We all know that the most effective way to prevent SQLi is to use Prepared Statement. But to apply Prepared Statement at scale to all repositories in an organization is not as simple as imagined.

SQL Injection vulnerability

SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database.

Source: https://portswigger.net/web-security/sql-injection#:~:text=SQL%20injection%20(SQLi)%20is%20a,not%20normally%20able%20to%20retrieve.

Prepared Statement

Sure! We all know that the most effective way to prevent SQLi is to use Prepared Statement. A Prepared Statement is a database feature that allows you to define a SQL query template with placeholders for parameters. The template is precompiled and stored on the database server, and then you can supply specific parameter values to execute the query multiple times without recompiling it.

The process of using a Prepared Statement involves two steps:

1. Prepare: The database server receives the SQL query template and compiles it into an execution plan, optimizing the query for performance. The placeholders in the template represent parameters that will be provided with specific values later.

2. Execute: Once the Prepared Statement is created, you can supply parameter values to it and execute the query. The database server uses the precompiled execution plan and only needs to substitute the parameter values, making subsequent executions more efficient than regular SQL queries.

Prepared Statement can prevent SQL injection as it automatically handle escaping and parameter binding, preventing SQL injection attacks because the parameter values are treated as data, not executable code.

Squirrel

squirrel library is a popular SQL query builder for Golang that helps you construct SQL queries that simplifies database interactions and enhances developer productivity. But this does not mean using squirrel keeps your application safe from SQL Injection.

The scope of this article will cover the use of squirrel.

Bad practices

func Vul_Func(tableName string, ids []int64, name string) (string, []interface{}, error) {
	idString := make([]string, 0)

	for _, id := range ids {
		idString = append(idString, fmt.Sprintf("%d", id))
	}

	query := squirrel.Select("*").
		From(tableName).
		Where(squirrel.Expr(fmt.Sprintf("id IN (%v)", strings.Join(idString, ",")))).
		Where(squirrel.Expr(fmt.Sprintf("name LIKE '%%%v%%'", name)))

	sqlQuery, args, err := query.ToSql()
	if err != nil {
		log.Fatal(err)
	}
// Print the generated query and arguments
	fmt.Println("query:", sqlQuery)
	fmt.Println("agrs:", args)
}

Above block code using string builder func is fmt.Sprintf to build query string within squirrel.Expr func. Building a query like that means you concatenating directly unsafe input to your query, that make your code be vulnerable by SQLi Running the block code, the output will look like this:

query: SELECT * FROM users WHERE id IN (1,2,3) AND name LIKE '%fuzz OR 1=1%'
args: []

Good practices

func Safe_Func(tableName string, ids []int64, name string) (string, []interface{}, error) {
	idString := make([]string, 0)

	for _, id := range ids {
		idString = append(idString, fmt.Sprintf("%d", id))
	}

	query := squirrel.Select("*").
		From(tableName).
		Where(squirrel.Expr("id IN ("+squirrel.Placeholders(len(idsArgs))+")", idsArgs...)).
		Where(squirrel.Expr("name LIKE ?", fmt.Sprintf("%%%v%%", name)))

	sqlQuery, args, err := query.ToSql()
	if err != nil {
		log.Fatal(err)
	}
// Print the generated query and arguments
	fmt.Println("query:", sqlQuery)
	fmt.Println("agrs:", args)
}

To fix it, we need to use proper query parameter. Let's update the code to use the ? placeholders for parameters.

Running the block code, the output will look like this:

query: SELECT * FROM users WHERE id IN (?,?,?) AND name LIKE ?
args: [1 2 3 %fuzz OR 1=1%]

As you can see, the unsafe input now passed as arguments of the query.

Detect using string builder at scale

If you are in the role of a security guy and your task is to handle all locations using string builder similar to how I showed in the Bad practices section. The requirement is that you have to find the current code positions and the new positions as soon as they appear (may come from an untrained programmer). How would you handle this task?

There are a few ways to handle this problem, but you can refer to the following simple way.

If you are observant, you can see the answer through the two examples that I have shown above. You can scan all queries processed by the driver to detect queries that have 2 criteria:

1. use a where clause and

2. do not have any arguments.

At the driver layer, you can scan a query using the following simple logic

sqlQuery, args, err := query.ToSql()
lenArgs = len(args)
if strings.Contains(query, "where") && lenArgs == 0 {
   // return Alert location is using string builder to build query.
}

Thanks for reading!