SQL queries in golang text/template

I initially considered using Go's text/template package to improve productivity when developing backend APIs by dynamically generating SQL queries. However, I discovered that this approach introduces a significant security risk—SQL injection—if not handled correctly.

Example of a Vulnerable SQL Query Using text/template

package main

import (
    "os"
    "text/template"
)

const queryTemplate = `SELECT * FROM users WHERE username = '{{.Username}}';`

func main() {
    tmpl, err := template.New("sql").Parse(queryTemplate)
    if err != nil {
        panic(err)
    }

    data := map[string]string{
        "Username": "admin' OR '1'='1", // Malicious input
    }

    tmpl.Execute(os.Stdout, data)
}

Output:

SELECT * FROM users WHERE username = 'admin' OR '1'='1';

Why is this Vulnerable?

Since the query is built using string interpolation, an attacker can manipulate the input ("admin' OR '1'='1") to bypass authentication or retrieve unauthorized data. If executed, this query would return all users instead of just the intended one.

A Secure Approach

To address this issue, I have developed a utility package that enables secure and dynamic SQL query generation using Go templates while ensuring protection against SQL injection.

Installation
You can install the package using:

go get github.com/rabbit-backend/template

Using the Execute Method for Safe Query Generation

The Execute method transforms SQL queries into parameterized queries, making them safe from SQL injection.

package main

import (
    "fmt"

    engine "github.com/rabbit-backend/template"
)

func main() {
    query, args := engine.Execute(
        "test/app.sql",
        map[string]map[string]string{
            "args": {"user": "admin' OR '1'='1"},
        },
    )

    fmt.Println(query, args)
}

SQL Template (test/app.sql)

SELECT * FROM users WHERE username = {{.args.user | __sql_arg__}};

output:

SELECT * FROM users WHERE username = $1  ["admin' OR '1'='1'"]

How This Works

Unlike raw string substitution, the Execute method sanitizes the SQL by converting it into a parameterized query. The query and arguments can then be passed to sql/db for safe execution against your database, preventing SQL injection attacks.

you can find the source code of the package at
https://github.com/rabbit-backend/template