Go database/sql SQL Injection Vulnerability

Critical Risk SQL Injection
gosql-injectiondatabase-sqldatabasemysqlpostgresqlinjectionuser-inputparameterized-queriesprepared-statements

What it is

A critical vulnerability that occurs when Go applications using the standard database/sql package construct SQL queries by concatenating user input directly into query strings. This allows attackers to inject malicious SQL code, potentially leading to data theft, database corruption, or unauthorized access.

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)

type User struct {
    ID       int    `json:"id"`
    Username string `json:"username"`
    Email    string `json:"email"`
    Status   string `json:"status"`
}

func getUserByID(db *sql.DB, userID string) (*User, error) {
    // VULNERABLE: Direct string concatenation
    query := "SELECT id, username, email, status FROM users WHERE id = " + userID

    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    if rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.Username, &user.Email, &user.Status)
        return &user, err
    }
    return nil, fmt.Errorf("user not found")
}

func searchUsers(db *sql.DB, name, department string) ([]User, error) {
    // VULNERABLE: fmt.Sprintf with user input
    query := fmt.Sprintf(
        "SELECT id, username, email FROM users WHERE name LIKE '%%%s%%' AND department = '%s'",
        name, department
    )

    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.Username, &user.Email)
        if err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

// Malicious inputs:
// userID: "1 OR 1=1 --"
// name: "'; DROP TABLE users; --"
// department: "IT'; UPDATE users SET salary=999999; --"
package main

import (
    "database/sql"
    "fmt"
    "strconv"
    _ "github.com/go-sql-driver/mysql"
)

type User struct {
    ID       int
    Username string
    Email    string
}

func getUserByID(db *sql.DB, userIDStr string) (*User, error) {
    // Basic validation
    userID, err := strconv.Atoi(userIDStr)
    if err != nil || userID <= 0 {
        return nil, fmt.Errorf("invalid user ID")
    }

    // SECURE: ? placeholder
    query := "SELECT id, username, email FROM users WHERE id = ?"

    var user User
    err = db.QueryRow(query, userID).Scan(&user.ID, &user.Username, &user.Email)
    return &user, err
}

func searchUsers(db *sql.DB, name string) ([]User, error) {
    // SECURE: ? placeholder
    query := "SELECT id, username, email FROM users WHERE name LIKE ?"

    rows, err := db.Query(query, "%"+name+"%")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        rows.Scan(&user.ID, &user.Username, &user.Email)
        users = append(users, user)
    }
    return users, nil
}

💡 Why This Fix Works

The vulnerable code uses string concatenation and fmt.Sprintf to build SQL queries, allowing injection attacks. The fixed version uses parameterized queries with ? placeholders, implements input validation, and demonstrates proper use of context and prepared statements.

Why it happens

Using string concatenation to build SQL queries with user input creates direct injection vulnerabilities. This bypasses all built-in security mechanisms and allows attackers to inject arbitrary SQL code.

Root causes

String Concatenation in SQL Queries

Using string concatenation to build SQL queries with user input creates direct injection vulnerabilities. This bypasses all built-in security mechanisms and allows attackers to inject arbitrary SQL code.

Preview example – GO 
// VULNERABLE: String concatenation
func getUserByID(db *sql.DB, userID string) (*User, error) {
    query := "SELECT id, username, email FROM users WHERE id = " + userID
    rows, err := db.Query(query)
    // Malicious input: "1 OR 1=1 --"
}

fmt.Sprintf in Database Queries

Using fmt.Sprintf to format SQL queries with user input provides no protection against injection attacks. String formatting treats user input as part of the SQL structure rather than as data.

Preview example – GO 
// VULNERABLE: fmt.Sprintf formatting
func searchUsers(db *sql.DB, name, department string) ([]User, error) {
    query := fmt.Sprintf(
        "SELECT id, username, email FROM users WHERE name LIKE '%%%s%%' AND department = '%s'",
        name, department
    )
    rows, err := db.Query(query)
    // Malicious input: name = "'; DROP TABLE users; --"

Lack of Input Validation

Failing to validate user input before using it in database queries compounds injection vulnerabilities. Without proper type checking and validation, any user input can potentially become part of executable SQL code.

Preview example – GO 
// VULNERABLE: No input validation
func updateUser(db *sql.DB, userID, status string) error {
    // No validation - userID could be "1; DROP TABLE users; --"
    query := "UPDATE users SET status = '" + status + "' WHERE id = " + userID
    _, err := db.Exec(query)
    return err
}

Dynamic Query Building

Building SQL queries dynamically based on user input without proper parameterization creates multiple injection points. Each concatenated user input represents a potential attack vector.

Preview example – GO 
// VULNERABLE: Dynamic query building
func advancedSearch(db *sql.DB, filters map[string]string) ([]User, error) {
    conditions := []string{}
    for field, value := range filters {
        conditions = append(conditions, field + " = '" + value + "'")
    }
    where := strings.Join(conditions, " AND ")
    query := "SELECT * FROM users WHERE " + where
    // Each filter value is a potential injection point

Fixes

1

Use Parameterized Queries with ? Placeholders

Always use parameterized queries with ? placeholders instead of string concatenation. This separates SQL logic from data and ensures user input is treated as data rather than executable code.

View implementation – GO 
// SECURE: Parameterized query
func getUserByID(db *sql.DB, userIDStr string) (*User, error) {
    // Input validation
    userID, err := validateUserID(userIDStr)
    if err != nil {
        return nil, err
    }

    query := "SELECT id, username, email FROM users WHERE id = ?"
    row := db.QueryRow(query, userID)

    var user User
    err = row.Scan(&user.ID, &user.Username, &user.Email)
    return &user, err
}
2

Implement Type-Safe Input Validation

Validate and convert user input to appropriate Go types before using in database operations. Use Go's type system to ensure data integrity and implement comprehensive validation functions.

View implementation – GO 
// Input validation functions
func validateUserID(userID string) (int, error) {
    id, err := strconv.Atoi(userID)
    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 {
    validDepts := map[string]bool{
        "IT": true, "HR": true, "Finance": true,
    }
    if !validDepts[department] {
        return fmt.Errorf("invalid department: %s", department)
    }
    return nil
}
3

Use Prepared Statements for Repeated Operations

For frequently executed queries, use prepared statements to improve both security and performance. Prepared statements are compiled once and parameterized for each execution.

View implementation – GO 
// SECURE: Prepared statement
func getUsersByStatusPrepared(db *sql.DB, status string) ([]User, error) {
    stmt, err := db.Prepare("SELECT id, username, email FROM users WHERE status = ?")
    if err != nil {
        return nil, err
    }
    defer stmt.Close()

    rows, err := stmt.Query(status)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.Username, &user.Email)
        if err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}
4

Build Dynamic Queries Safely

When building dynamic queries, use whitelisted field names and parameterized values. Separate the construction of SQL structure from user data insertion.

View implementation – GO 
// SECURE: Safe dynamic query building
func searchUsers(db *sql.DB, name, department string) ([]User, error) {
    args := []interface{}{}
    conditions := []string{}
    baseQuery := "SELECT id, username, email FROM users WHERE 1=1"

    if name != "" {
        if len(name) > 100 {
            return nil, fmt.Errorf("search name too long")
        }
        conditions = append(conditions, " AND name LIKE ?")
        args = append(args, "%"+name+"%")
    }

    if department != "" {
        if err := validateDepartment(department); err != nil {
            return nil, err
        }
        conditions = append(conditions, " AND department = ?")
        args = append(args, department)
    }

    query := baseQuery + strings.Join(conditions, "")
    rows, err := db.Query(query, args...)
    // Handle results...
}
5

Use Context for Query Cancellation and Timeouts

Use context-aware query methods like QueryContext and ExecContext to implement timeouts and cancellation. This provides additional protection against long-running malicious queries.

View implementation – GO 
// SECURE: Context-aware queries with timeout
func getUserByIDWithTimeout(db *sql.DB, userIDStr string) (*User, error) {
    userID, err := validateUserID(userIDStr)
    if err != nil {
        return nil, err
    }

    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

    query := "SELECT id, username, email FROM users WHERE id = ?"
    row := db.QueryRowContext(ctx, query, userID)

    var user User
    err = row.Scan(&user.ID, &user.Username, &user.Email)
    if err == sql.ErrNoRows {
        return nil, fmt.Errorf("user not found")
    }
    return &user, err
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies go database/sql sql injection vulnerability and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities