SQL injection from user data in concatenated SQL string in database/sql

Critical Risk SQL Injection

godatabase-sqlsql-injectionwebhttp

What it is

SQL injection vulnerability in Go applications using database/sql where HTTP request data is inserted into SQL via string concatenation or formatting instead of parameters, allowing injection through user-controlled input.

Language:

// VULNERABLE: Go HTTP handlers with SQL string concatenation
package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "net/url"
    "strconv"
    "strings"

    "github.com/gorilla/mux"
    _ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func init() {
    var err error
    dsn := "user:password@tcp(localhost:3306)/myapp"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
}

// Attack examples:
// GET /users/search?name=admin'%20OR%20'1'='1&sortBy=id;DROP%20TABLE%20users;--
// POST /users/bulk-update [{"id": "1", "role": "admin'; DROP TABLE users; --"}]

// SECURE: Parameterized queries prevent SQL injection
package main

import (
    "database/sql"
    "encoding/json"
    "log"
    "net/http"

    "github.com/gorilla/mux"
    _ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func init() {
    var err error
    dsn := "user:password@tcp(localhost:3306)/myapp"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
}

// SECURE: Using parameterized query with placeholders
func searchUsers(w http.ResponseWriter, r *http.Request) {
    name := r.URL.Query().Get("name")
    department := r.URL.Query().Get("department")
    role := r.URL.Query().Get("role")

    // Use ? placeholders instead of string concatenation
    query := "SELECT id, name, email FROM users WHERE name LIKE ? AND department = ? AND role = ?"

    rows, err := db.Query(query, "%"+name+"%", department, role)
    if err != nil {
        http.Error(w, "Query error", 500)
        return
    }
    defer rows.Close()

    // Process results...
}

// SECURE: INSERT with parameterized query
type User struct {
    Name       string `json:"name"`
    Email      string `json:"email"`
    Department string `json:"department"`
}

func createUser(w http.ResponseWriter, r *http.Request) {
    var user User
    json.NewDecoder(r.Body).Decode(&user)

    // Use ? placeholders for all values
    insertSQL := "INSERT INTO users (name, email, department) VALUES (?, ?, ?)"

    _, err := db.Exec(insertSQL, user.Name, user.Email, user.Department)
    if err != nil {
        http.Error(w, "Insert failed", 500)
        return
    }

    w.WriteHeader(201)
}

💡 Why This Fix Works

The vulnerable code directly concatenates HTTP request data into SQL strings, allowing injection attacks through various endpoints. The fixed version uses parameterized queries with placeholders, comprehensive input validation against whitelists, database transactions for consistency, and proper error handling.

Why it happens

HTTP request data from forms, query parameters, or JSON payloads is directly concatenated into SQL strings without parameterization.

Root causes

HTTP Request Data in SQL String Concatenation

HTTP request data from forms, query parameters, or JSON payloads is directly concatenated into SQL strings without parameterization.

Preview example – GO

// VULNERABLE: HTTP request data in SQL concatenation
package main

import (
    "database/sql"
    "fmt"
    "net/http"

    _ "github.com/go-sql-driver/mysql"
)

func searchUsers(w http.ResponseWriter, r *http.Request) {
    name := r.URL.Query().Get("name")
    department := r.URL.Query().Get("department")
    role := r.URL.Query().Get("role")

    // Direct request data concatenation vulnerability
    query := fmt.Sprintf("SELECT * FROM users WHERE name LIKE '%%%s%%' AND department = '%s' AND role = '%s'",
        name, department, role)

    db, _ := sql.Open("mysql", dsn)
    defer db.Close()

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

Form Data and JSON Payloads in SQL

Using form data or JSON request payloads directly in SQL string construction without proper parameterization or validation.

Preview example – GO

// VULNERABLE: Form data in SQL strings
import (
    "encoding/json"
    "fmt"
    "strings"
)

type User struct {
    Name       string `json:"name"`
    Email      string `json:"email"`
    Department string `json:"department"`
}

func createUser(w http.ResponseWriter, r *http.Request) {
    var user User
    json.NewDecoder(r.Body).Decode(&user)

    // String formatting vulnerability
    insertSQL := fmt.Sprintf(
        "INSERT INTO users (name, email, department) VALUES ('%s', '%s', '%s')",
        user.Name, user.Email, user.Department)

    db, _ := sql.Open("mysql", dsn)
    defer db.Close()

    _, err := db.Exec(insertSQL)
    if err != nil {
        http.Error(w, err.Error(), 500)
    }
}

Fixes

Use database/sql Prepared Statements

Replace string concatenation with database/sql prepared statements. Use placeholders and pass values as arguments.

View implementation – GO

// SECURE: Prepared statements with database/sql
func searchUsers(w http.ResponseWriter, r *http.Request) {
    name := r.URL.Query().Get("name")
    department := r.URL.Query().Get("department")
    role := r.URL.Query().Get("role")

    // Validate inputs
    if !isValidDepartment(department) || !isValidRole(role) {
        http.Error(w, "Invalid parameters", 400)
        return
    }

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        http.Error(w, "Database error", 500)
        return
    }
    defer db.Close()

    // Use parameterized query with placeholders
    query := "SELECT id, name, email, department, role FROM users WHERE name LIKE ? AND department = ? AND role = ?"

    rows, err := db.Query(query, "%"+name+"%", department, role)
    if err != nil {
        http.Error(w, "Query error", 500)
        return
    }
    defer rows.Close()

    // Process results...
}

Implement Input Validation and Sanitization

Validate all HTTP request data before database operations. Use whitelists for dynamic SQL components and sanitize user input.

View implementation – GO

// SECURE: Input validation with prepared statements
import (
    "regexp"
    "strings"
)

var (
    validDepartments = map[string]bool{
        "engineering": true,
        "marketing":   true,
        "sales":       true,
        "hr":          true,
    }

    validRoles = map[string]bool{
        "employee": true,
        "manager":  true,
        "admin":    true,
    }

    nameRegex  = regexp.MustCompile(`^[a-zA-Z\s]{1,50}$`)
    emailRegex = regexp.MustCompile(`^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$`)
)

func isValidDepartment(dept string) bool {
    return validDepartments[strings.ToLower(dept)]
}

func isValidRole(role string) bool {
    return validRoles[strings.ToLower(role)]
}

func createUser(w http.ResponseWriter, r *http.Request) {
    var user User
    if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
        http.Error(w, "Invalid JSON", 400)
        return
    }

    // Validate all input fields
    if !isValidName(user.Name) || !isValidEmail(user.Email) || !isValidDepartment(user.Department) {
        http.Error(w, "Invalid input data", 400)
        return
    }

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        http.Error(w, "Database error", 500)
        return
    }
    defer db.Close()

    // Use parameterized query
    insertSQL := "INSERT INTO users (name, email, department) VALUES (?, ?, ?)"

    _, err = db.Exec(insertSQL, user.Name, user.Email, user.Department)
    if err != nil {
        http.Error(w, "Insert failed", 500)
        return
    }

    w.WriteHeader(201)
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection from user data in concatenated sql string in database/sql and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities