Go go-pg ORM SQL Injection Vulnerability

Critical Risk SQL Injection
gosql-injectiongo-pgormdatabasepostgresqlinjectionuser-inputparameterized-queries

What it is

A critical vulnerability that occurs when Go applications using the go-pg ORM library construct SQL queries by concatenating user input directly into WHERE clauses, ORDER BY clauses, or raw SQL queries. This bypasses the ORM's built-in protections and allows attackers to inject malicious SQL code, potentially leading to data theft, database corruption, or unauthorized access.

package main

import (
    "fmt"
    "github.com/go-pg/pg/v10"
)

type User struct {
    ID         int64
    Name       string
    Department string
    Salary     int
}

func getUserByID(db *pg.DB, userID string) (*User, error) {
    // VULNERABLE: String concatenation
    var user User
    err := db.Model(&user).Where("id = " + userID).Select()
    return &user, err
}

func searchUsers(db *pg.DB, name, department string) ([]User, error) {
    // VULNERABLE: fmt.Sprintf in WHERE clause
    var users []User
    condition := fmt.Sprintf("name ILIKE '%%%s%%' AND department = '%s'", name, department)
    err := db.Model(&users).Where(condition).Select()
    return users, err
}

func updateUserSalary(db *pg.DB, userEmail, newSalary string) error {
    // VULNERABLE: String interpolation in raw SQL
    query := fmt.Sprintf("UPDATE users SET salary = %s WHERE email = '%s'", newSalary, userEmail)
    _, err := db.Exec(query)
    return err
}

func getTopEarners(db *pg.DB, orderBy string) ([]User, error) {
    // VULNERABLE: Dynamic ORDER BY with user input
    var users []User
    err := db.Model(&users).
        Where("salary > 100000").
        Order(orderBy).  // Direct user input
        Select()
    return users, err
}

// Attack examples:
// userID: "1 OR 1=1 --"
// name: "'; DROP TABLE users; --"
package main

import (
    "fmt"
    "strconv"
    "github.com/go-pg/pg/v10"
)

type User struct {
    ID         int64
    Name       string
    Department string
    Salary     int
}

func getUserByID(db *pg.DB, userIDStr string) (*User, error) {
    userID, err := strconv.ParseInt(userIDStr, 10, 64)
    if err != nil {
        return nil, err
    }
    // SECURE: Parameterized query with ? placeholder
    var user User
    err = db.Model(&user).Where("id = ?", userID).Select()
    return &user, err
}

func searchUsers(db *pg.DB, name, department string) ([]User, error) {
    // SECURE: Parameterized query with ? placeholders
    var users []User
    err := db.Model(&users).
        Where("name ILIKE ?", "%"+name+"%").
        Where("department = ?", department).
        Select()
    return users, err
}

func updateUserSalary(db *pg.DB, userEmail, newSalary string) error {
    // SECURE: Parameterized update with ? placeholders
    user := &User{}
    _, err := db.Model(user).
        Set("salary = ?", newSalary).
        Where("email = ?", userEmail).
        Update()
    return err
}

func getTopEarners(db *pg.DB, orderBy string) ([]User, error) {
    // SECURE: Whitelist allowed ORDER BY values
    validOrderColumns := map[string]string{
        "salary": "salary DESC",
        "name":   "name ASC",
    }
    orderClause := validOrderColumns[orderBy]
    if orderClause == "" {
        return nil, fmt.Errorf("invalid order field")
    }
    // SECURE: Use validated ORDER BY clause
    var users []User
    err := db.Model(&users).
        Where("salary > ?", 100000).
        Order(orderClause).
        Select()
    return users, err
}

💡 Why This Fix Works

The vulnerable code uses string concatenation and fmt.Sprintf to build go-pg ORM conditions, allowing injection attacks. The fixed version uses parameterized queries with ? placeholders, implements input validation, whitelisting for dynamic fields, and demonstrates proper use of the go-pg ORM methods.

Why it happens

Using string concatenation or fmt.Sprintf to build WHERE conditions in go-pg queries bypasses the ORM's parameterization mechanisms. This creates direct injection vulnerabilities when user input is concatenated into the query string.

Root causes

String Concatenation in go-pg WHERE Clauses

Using string concatenation or fmt.Sprintf to build WHERE conditions in go-pg queries bypasses the ORM's parameterization mechanisms. This creates direct injection vulnerabilities when user input is concatenated into the query string.

Preview example – GO 
// VULNERABLE: String concatenation in WHERE clause
func getUserByID(db *pg.DB, userID string) (*User, error) {
    var user User
    err := db.Model(&user).Where("id = " + userID).Select()
    return &user, err
}
// Malicious input: "1 OR 1=1 --"

fmt.Sprintf in Dynamic Query Building

Using fmt.Sprintf to format SQL conditions with user input creates injection vulnerabilities. Even when using go-pg's query builder methods, direct string formatting of conditions allows attackers to inject malicious SQL code.

Preview example – GO 
// VULNERABLE: fmt.Sprintf in conditions
func searchUsers(db *pg.DB, name, department string) ([]User, error) {
    var users []User
    condition := fmt.Sprintf("name ILIKE '%%%s%%' AND department = '%s'", name, department)
    err := db.Model(&users).Where(condition).Select()
    return users, err
}
// Malicious input: name = "'; DROP TABLE users; --"

Raw SQL Execution with User Input

Executing raw SQL queries through go-pg's Exec method with user input concatenation completely bypasses ORM protections. This approach is particularly dangerous as it provides direct access to SQL execution without any safety mechanisms.

Preview example – GO 
// VULNERABLE: Raw SQL with concatenation
func updateUserSalary(db *pg.DB, userEmail, newSalary string) error {
    query := fmt.Sprintf("UPDATE users SET salary = %s WHERE email = '%s'", newSalary, userEmail)
    _, err := db.Exec(query)
    return err
}
// Malicious input: newSalary = "999999; DROP TABLE users; --"

Dynamic ORDER BY with User Input

Allowing user input to directly control ORDER BY clauses creates injection vulnerabilities. Attackers can inject additional SQL statements or subqueries through the ordering parameter, especially when validation is insufficient.

Preview example – GO 
// VULNERABLE: Dynamic ORDER BY
func getTopEarners(db *pg.DB, orderBy string) ([]User, error) {
    var users []User
    err := db.Model(&users).
        Where("salary > 100000").
        Order(orderBy).  // Direct user input
        Limit(10).
        Select()
    return users, err
}
// Malicious input: "salary; DROP TABLE users; --"

Fixes

1

Use go-pg Parameterized Queries with ? Placeholders

Always use go-pg's built-in parameterization with ? placeholders instead of string concatenation. This ensures user input is treated as data rather than executable SQL code and leverages the ORM's security features.

View implementation – GO 
// SECURE: Parameterized query with validation
func getUserByID(db *pg.DB, userIDStr string) (*User, error) {
    // Input validation
    userID, err := strconv.ParseInt(userIDStr, 10, 64)
    if err != nil || userID <= 0 {
        return nil, fmt.Errorf("invalid user ID: %v", err)
    }

    var user User
    err = db.Model(&user).Where("id = ?", userID).Select()
    if err == pg.ErrNoRows {
        return nil, fmt.Errorf("user not found")
    }
    return &user, err
}
2

Implement Input Validation and Type Conversion

Validate and convert user input to appropriate types before using in database queries. Use Go's type system to ensure data integrity and implement range checks for numeric values.

View implementation – GO 
// Input validation functions
func validateUserID(userID string) (int64, error) {
    id, err := strconv.ParseInt(userID, 10, 64)
    if err != nil {
        return 0, fmt.Errorf("invalid user ID format: %v", err)
    }
    if id <= 0 {
        return 0, fmt.Errorf("user ID must be positive")
    }
    return id, nil
}

func validateDepartment(department string) error {
    validDepartments := map[string]bool{
        "IT": true, "HR": true, "Finance": true, "Marketing": true,
    }
    if !validDepartments[department] {
        return fmt.Errorf("invalid department: %s", department)
    }
    return nil
}
3

Use Whitelisting for Dynamic Field Names and Sorting

When building dynamic queries, whitelist allowed field names and sorting options. Create a mapping of user-friendly names to actual column names to prevent injection through field manipulation.

View implementation – GO 
// SECURE: Whitelisted ORDER BY
func getTopEarners(db *pg.DB, orderBy string) ([]User, error) {
    validOrderColumns := map[string]string{
        "salary": "salary DESC",
        "name":   "name ASC",
        "email":  "email ASC",
        "dept":   "department ASC",
    }

    orderClause, ok := validOrderColumns[orderBy]
    if !ok {
        return nil, fmt.Errorf("invalid order field: %s", orderBy)
    }

    var users []User
    err := db.Model(&users).
        Where("salary > ?", 100000).
        Order(orderClause).
        Limit(10).
        Select()
    return users, err
}
4

Build Dynamic Queries Safely with Multiple Parameters

When building complex dynamic queries, use multiple parameterized conditions instead of string concatenation. Build condition arrays and parameter arrays separately to maintain separation between SQL logic and data.

View implementation – GO 
// SECURE: Dynamic query with multiple parameters
func searchUsers(db *pg.DB, name, department string) ([]User, error) {
    // Input validation
    if len(name) > 100 {
        return nil, fmt.Errorf("search name too long")
    }
    
    validDepartments := map[string]bool{
        "IT": true, "HR": true, "Finance": true,
    }
    if department != "" && !validDepartments[department] {
        return nil, fmt.Errorf("invalid department: %s", department)
    }

    var users []User
    query := db.Model(&users)

    if name != "" {
        query = query.Where("name ILIKE ?", "%"+name+"%")
    }
    if department != "" {
        query = query.Where("department = ?", department)
    }

    err := query.Limit(100).Select()
    return users, err
}
5

Use go-pg's ORM Methods Instead of Raw SQL

Leverage go-pg's ORM methods like Set(), Where(), and Update() instead of raw SQL execution. These methods provide built-in parameterization and are less prone to injection vulnerabilities.

View implementation – GO 
// SECURE: Using ORM methods with validation
func updateUserSalary(db *pg.DB, userEmail string, newSalaryStr string) error {
    // Input validation
    if len(userEmail) == 0 || len(userEmail) > 100 {
        return fmt.Errorf("invalid email")
    }

    newSalary, err := strconv.Atoi(newSalaryStr)
    if err != nil || newSalary < 0 {
        return fmt.Errorf("invalid salary amount")
    }

    // SECURE: Using ORM update methods
    user := &User{Email: userEmail}
    _, err = db.Model(user).
        Set("salary = ?", newSalary).
        Where("email = ?", userEmail).
        Update()

    return err
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies go go-pg orm sql injection vulnerability and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities