This content originally appeared on DEV Community and was authored by rohit20001221
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}};
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
This content originally appeared on DEV Community and was authored by rohit20001221
rohit20001221 | Sciencx (2025-02-05T19:26:52+00:00) SQL queries in golang text/template. Retrieved from https://www.scien.cx/2025/02/05/sql-queries-in-golang-text-template/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.