Software Architecture in Go: Security - Preventing SQL Injection

Oct 22, 2021

Disclaimer: This post includes Amazon affiliate links. If you click on one of them and you make a purchase I’ll earn a commission. Please notice your final price is not affected at all by using those links.

Welcome to another post part of the series covering Quality Attributes / Non-Functional Requirements, this time I’m talking about Security specifically when working with Databases to prevent SQL injection.

What is SQL Injection?

The code used for this post is available on Github.

According to Wikipedia:

SQL Injection is a code injection technique in which malicious SQL statements are inserted into an entry field for execution.

For example consider the following code meant to execute a DELETE SQL command that receives a string value in the id variable:

1query := fmt.Sprintf("DELETE FROM users WHERE id = '%s'", id)
2
3if _, err := db.Exec(query); err != nil {
4	log.Fatalln("Couldn't delete", err)
5}

So if id happens to be 33ca99ce-f1d1-46c2-b26e-a0ff45011b18, then the record matching that value will be deleted working as expected, but what happens if a malicious user passes in the following value?:

"33ca99ce-f1d1-46c2-b26e-a0ff45011b18' OR ''='"

Something we don’t want will happen: we are going to delete all our records! This is because the final SQL statement in our code will be the following:

DELETE FROM users WHERE id = '33ca99ce-f1d1-46c2-b26e-a0ff45011b18' OR ''=''

If we look closely this SQL statement means:

Delete any record that matches the id 33ca99ce-f1d1-46c2-b26e-a0ff45011b18 OR if ' is equal to '.

So in other words: delete every single record in our table! because ' is always going to be equal to '.

How can we prevent SQL injection?

The key to prevent SQL injection is to avoid, as much as possible, the use of string concatenation when building SQL Statements, and to prefer using the corresponding database engine placeholder instead.

For example the code above should be replaced with something like the following:

1// XXX: The placeholder value depends the database driver, some of them use
2// `?` instead of sequential $n values
3query := "DELETE FROM users WHERE id = $1"
4
5if _, err := db.Exec(query, id); err != nil {
6	log.Fatalln("Couldn't delete", err)
7}

In this case we let the database engine handle the parameters and deal with the corresponding validation for the expected field, so if we pass in a value like 33ca99ce-f1d1-46c2-b26e-a0ff45011b18 it will work as expected, but if we try our previous nasty argument: '33ca99ce-f1d1-46c2-b26e-a0ff45011b18' OR ''='' it will not work giving us an error like:

invalid input syntax for type uuid

What about when working with Dynamic SQL Statements?

Dynamic Statements in this context means those statements we need to build dynamically depending on different conditional arguments we define, for example if we want to delete users by different conditions we still need a way to create this query dynamically avoiding explicit string concatenation.

For doing things like that I like using a third party package called Masterminds/squirrel, which I covered when I discussed Accessing PostgreSQL using Statements Builder and Code generators.

For example:

 1psql := sq.Delete("users").Where("is_admin = ?", *isAdmin)
 2
 3if *birthYear > 0 {
 4	psql = psql.Where("birth_year > ?", *birthYear)
 5}
 6
 7sql, args, err := psql.PlaceholderFormat(sq.Dollar).ToSql()
 8if err != nil {
 9	log.Fatalln("Couldn't create SQL statement", err)
10}
11
12fmt.Println("query", sql, "args", args)
13
14//-
15
16stmt, err := db.PrepareContext(context.Background(), sql)
17if err != nil {
18	log.Fatalln("Couldn't prepare", err)
19}
20
21if _, err := stmt.ExecContext(context.Background(), args...); err != nil {
22	log.Fatalln("Couldn't delete", err)
23}

L1: Instantiates the query
L3-5: Adds the condition to delete only those with birth_year greater than the received argument
L7-10: We generate the final SQL statement
L16-23: We execute it

Conclusion

When working with Relational Databases is always recommended to use prepared statements and their corresponding engine placeholders to avoid having issues like SQL Injection, in some cases some packages (like pgx) cache the statements behind the scenes giving us a bit of performance improvement as well.

If there’s the need to create dynamic SQL statements consider using a package like squirrel and perhaps use concrete database users with explicit permissions that way the scope of their access is limited only by what they are supposed to do.

What is SQL Injection?

How can we prevent SQL injection?

What about when working with Dynamic SQL Statements?

Conclusion

Recommended reading