Safe Squirrel - fluent SQL generator for Go

"safe" squirrel is a fork of github.com/Masterminds/squirrel that enforces more secure/safe usage via the Golang type system.

It can be adopted as a drop-in replacement ^{(see caveats)} by replacing all squirrel imports with github.com/bored-engineer/safe-squirrel instead.

Why?

The squirrel package already encourages the use of parameterized queries (aka placeholders) to reduce the risk of SQL injection, ex:

username := "bored-engineer" // untrusted input

// "SELECT * FROM users WHERE github = ?"
sq.Select("*").From("users").Where(sq.Eq{"github": username}).ToSql()

However, not all methods/parameters in squirrel are safe/protected against SQL injection, ex:

provider := "is_superadmin=true OR github" // untrusted input
username := "uh oh" // untrusted input

// "SELECT * FROM users WHERE is_superadmin=true OR github = ?"
sq.Select("*").From("users").Where(sq.Eq{provider: username}).ToSql()

While this is a contrived example, SQL injection vulnerabilities have been found in real-world applications/services that use squirrel due to incorrect usage of these APIs by developers.

This package aims to systemically prevent these SQL injection vulnerabilities in squirrel at compile-time with minimal/no refactoring.

How?

By taking advantage of the Golang type system/compiler, it is possible to create a function that will only accept a const string at compile-time, ex:

type safeString string

func refuseDynamicStrings(foo safeString) {
    println(foo)
}

When this function is invoked Golang will automatically cast const strings to the private (otherwise inaccessible) safeString type:

pkg.refuseDynamicStrings("this is an implicit const string")
const foo = "this is an explicit const string"
pkg.refuseDynamicStrings(foo)

However, if we try to pass a dynamic string (such as one generated from user input), it will fail to build/compile:

var bar = fmt.Sprintf("this is a %s string", "dynamic")
pkg.refuseDynamicStrings(bar) // cannot use bar (variable of type string) as safeString value in argument to refuseDynamicStrings

This package/fork takes advantage of this "feature" to enforce that all parameters passed to squirrel are const strings at compile-time. APIs that are already secure due to their use of parameterized queries like sq.Expr("foo = ?", untrustedStringVar) or sq.Eq{"column": untrustedStringVar} continue to work as-is, accepting dynamic values on the relevant types.

Caveats

Most of the squirrel APIs were directly converted from string to safeString requiring no refactoring for an application to adopt this fork. However, the Where, Having and Case APIs now only accept a Sqlizer type. If you were previously using the less common (sql string, args ...interface{}) invocation, some simple refactoring to insert sq.Expr(...) will be required, ex:

// before
builder.Where("foo = ?", untrustedStringVar)

// after
builder.Where(sq.Expr("foo = ?", untrustedStringVar))

Notably this is not required if the application is already using the comparison types from squirrel, ex:

builder.Where(sq.Eq{"foo": untrustedStringVar})

Finally, if an unsafe/insecure Sqlizer that was defined outside of the safe-squirrel package is used, this could still result in a SQL injection vulnerability as the value returned by ToSql is used as-is.

Exceptions

While rare, sometimes a dynamic string is still required/expected, such as loading the name of a SQL table from a configuration file (at runtime). To support these use-cases, a DangerouslyCastDynamicStringToSafeString method is exposed which should be used with extreme caution, ex:

table := sq.DangerouslyCastDynamicStringToSafeString(cfg.TableName)

sq.Select("*").From(table).Where(sq.Eq{"github": "bored-engineer"}).ToSql()