Preventing SQL Injection with Raw SQL and ORM in Golang

In modern development, secure coding is crucial. Golang applications can also be prone to SQL injection when interacting with databases, unless proper precautions are taken.

This article covers how to prevent SQL injection in Golang using raw SQL and ORM frameworks.

---

🛑 What is SQL Injection?

SQL Injection (SQLi) is a web security vulnerability that allows an attacker to INJECT malicious SQL code into the queries an application makes to its database.

Example of a vulnerable SQL query:

query := "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
rows, err := db.Query(query)

If username or password contains malicious input, attackers can manipulate the query logic.

You can check out my another post on SQL injection to know more about it.

---

🔐 How to Prevent SQL Injection in Raw SQL

When writing raw SQL queries in Go, adhere to best practices for security.

1. Use Prepared Statements

Go’s database/sql package provides prepared statements that safely handle user input.

Vulnerable Example:

query := "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
rows, err := db.Query(query) // Potential SQL injection

With Prepared Statements:

query := "SELECT * FROM users WHERE username = ? AND password = ?"
rows, err := db.Query(query, username, password)

if err != nil {
    log.Fatal(err)
}

Prepared statements automatically escape user input to prevent injection.

2. Use Parameterized Queries

In Go, parameterized queries can be written using db.Query or db.Exec with placeholders:

query := "INSERT INTO products (name, price) VALUES (?, ?)"
_, err := db.Exec(query, productName, productPrice)

if err != nil {
    log.Fatal(err)
}

Avoid string concatenation or formatting with fmt.Sprintf for dynamic queries.

3. Use QueryRow for Single Row Queries

If fetching a single record, use QueryRow to avoid risks:

query := "SELECT id, name FROM users WHERE email = ?"

var id int
var name string
err := db.QueryRow(query, email).Scan(&id, &name)

if err != nil {
    log.Fatal(err)
}

4. Input Sanitization and Validation

Even when using prepared statements, input sanitization and validation remain crucial in preventing injection attacks and maintaining data integrity.

• Sanitization: Cleans input data by removing unwanted characters to ensure it meets the application's expectations.

• Validation: Ensures that input conforms to a specific format, type, or length before processing.

Example of Input Validation in Go:

func isValidUsername(username string) bool {
    re := regexp.MustCompile(`^[a-zA-Z0-9_]+$`)
    return re.MatchString(username)
}

if len(username) > 50 || !isValidUsername(username) {
    log.Fatal("Invalid input")
}

5. Use Stored Procedures

Stored procedures encapsulate query logic on the database side and limit direct input handling in code:

CREATE PROCEDURE AuthenticateUser(IN username VARCHAR(50), IN password VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = username AND password = password;
END;

Call stored procedures from Go using:

_, err := db.Exec("CALL AuthenticateUser(?, ?)", username, password)

if err != nil {
    log.Fatal(err)
}

---

🔄 Preventing SQL Injection with ORMs

Many Golang applications use ORM libraries such as GORM or XORM to simplify database interactions. While these tools offer built-in protection against SQL injection, you must follow best practices.

1. GORM

GORM is one of the most popular ORMs in Go.

Vulnerable Example (Dynamic Query Construction):

db.Raw("SELECT * FROM users WHERE name = '" + userName + "'").Scan(&user)

This can lead to injection if userName contains malicious input.

Safe Example:

db.Raw("SELECT * FROM users WHERE name = ? AND email = ?", userName, email).Scan(&user)

GORM’s Raw method supports parameterized queries.

Using Struct-Based Queries (safer default):

db.Where("email = ? AND status = ?", email, status).First(&user)

Using GORM’s query methods like Where is the safer default and should be preferred for database interactions.

2. Avoid Raw Strings for Complex Queries

When using raw SQL queries for complex operations, use placeholders:

result := db.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, accountId)

if result.Error != nil {
    log.Println(result.Error)
}

3. Use Struct Tags for Safe ORM Mapping

When mapping Go structs to database tables, use tags to define field names and constraints:

type User struct {
    ID       uint   `gorm:"primaryKey"`
    Name     string `gorm:"size:100"`
    Email    string `gorm:"uniqueIndex"`
    Password string
}

This helps ensure safe ORM mappings by defining constraints, data types, and relationships directly within the Go struct.

⚠️ Common Mistakes to Avoid

1. Avoid Building Queries with String Concatenation

Always use placeholders or prepared statements instead of concatenating variables into queries.

2. Avoid ORM Functions that Bypass Safety Checks

Some ORM functions allow executing raw SQL with direct string input. Use parameterized alternatives whenever possible.

3. Do Not Trust User Input

Always carefully validate and sanitize inputs before processing.

---

🛡️ Conclusion

Golang provides powerful tools for handling database queries securely. By implementing best practices—such as leveraging prepared statements, parameterized queries, and ORM frameworks while also sanitizing and validating inputs — you create a robust defense against SQL injection vulnerabilities.

Follow me to stay updated with my future posts:

• Linkedin
• Github
• Twitter/ X