Echo SQL Injection Prevention

Introduction

SQL injection is one of the most common and dangerous web application vulnerabilities. It occurs when an attacker is able to insert or "inject" malicious SQL code into queries that your application sends to its database. In Echo applications, like any web framework, preventing SQL injection is crucial for maintaining application security.

This guide will help you understand what SQL injection is, how it happens in Echo applications, and most importantly, how to prevent it. By following these best practices, you'll be able to build Echo applications that are resistant to SQL injection attacks.

Understanding SQL Injection

What is SQL Injection?

SQL injection happens when user-supplied data is included in SQL queries without proper sanitization. Consider this vulnerable Go code:

// Vulnerable code - DON'T DO THIS
func GetUserByUsername(c echo.Context) error {
    username := c.Param("username")
    query := "SELECT * FROM users WHERE username = '" + username + "'"
    
    // Execute query...
    // ...
}

If a user supplies admin' OR '1'='1 as the username, the resulting query becomes:

SELECT * FROM users WHERE username = 'admin' OR '1'='1'

This modified query returns all users because '1'='1' is always true, potentially exposing sensitive data.

Prevention Techniques

1. Use Parameterized Queries

The most effective way to prevent SQL injection is to use parameterized queries (also known as prepared statements):

// Safe code - Using parameterized queries
func GetUserByUsername(c echo.Context) error {
    username := c.Param("username")
    
    // Using database/sql package with parameterized query
    row := db.QueryRow("SELECT * FROM users WHERE username = ?", username)
    
    // Process the results...
    // ...
    
    return c.JSON(http.StatusOK, user)
}

In this example, the ? is a placeholder, and the actual value is passed separately, ensuring that user input is treated as data, not executable code.

2. Use an ORM

Object-Relational Mapping (ORM) libraries like GORM provide an additional layer of protection:

// Using GORM for database operations
func GetUserByUsername(c echo.Context) error {
    username := c.Param("username")
    
    var user User
    // GORM uses parameterized queries internally
    result := db.Where("username = ?", username).First(&user)
    
    if result.Error != nil {
        return c.JSON(http.StatusNotFound, map[string]string{"error": "User not found"})
    }
    
    return c.JSON(http.StatusOK, user)
}

3. Input Validation

Always validate and sanitize user inputs before using them in queries:

func GetUserByUsername(c echo.Context) error {
    username := c.Param("username")
    
    // Validate username format
    if !validateUsername(username) {
        return c.JSON(http.StatusBadRequest, map[string]string{
            "error": "Invalid username format",
        })
    }
    
    // Proceed with parameterized query
    // ...
}

func validateUsername(username string) bool {
    // Only allow alphanumeric characters and underscore
    match, _ := regexp.MatchString("^[a-zA-Z0-9_]+$", username)
    return match
}

4. Implement Database User Privileges

Limit database user permissions to only what's necessary:

// Example of setting up a connection with a limited-privilege user
func setupDBConnection() (*sql.DB, error) {
    // Use a database user with only the required permissions
    db, err := sql.Open("mysql", "app_readonly_user:password@/database_name")
    if err != nil {
        return nil, err
    }
    
    return db, nil
}

Real-world Examples

Example 1: Secure User Search Feature

Here's how to implement a secure search feature in an Echo application:

// Route setup in main.go
e.GET("/users/search", searchUsers)

// Handler implementation
func searchUsers(c echo.Context) error {
    query := c.QueryParam("q")
    
    // Validate query parameter
    if len(query) < 3 {
        return c.JSON(http.StatusBadRequest, map[string]string{
            "error": "Search query must be at least 3 characters",
        })
    }
    
    // Using parameterized query with LIKE
    rows, err := db.Query("SELECT id, username, email FROM users WHERE username LIKE ? OR email LIKE ?", 
        "%"+query+"%", "%"+query+"%")
    if err != nil {
        return c.JSON(http.StatusInternalServerError, map[string]string{
            "error": "Database error",
        })
    }
    defer rows.Close()
    
    // Process results and return them
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Username, &user.Email); err != nil {
            continue
        }
        users = append(users, user)
    }
    
    return c.JSON(http.StatusOK, users)
}

Example 2: Secure User Registration with Validation

// User registration handler
func registerUser(c echo.Context) error {
    // Bind request body to User struct
    u := new(User)
    if err := c.Bind(u); err != nil {
        return c.JSON(http.StatusBadRequest, map[string]string{
            "error": "Invalid request format",
        })
    }
    
    // Validate user data
    if err := validateUserData(u); err != nil {
        return c.JSON(http.StatusBadRequest, map[string]string{
            "error": err.Error(),
        })
    }
    
    // Hash password
    hashedPassword, err := bcrypt.GenerateFromPassword([]byte(u.Password), bcrypt.DefaultCost)
    if err != nil {
        return c.JSON(http.StatusInternalServerError, map[string]string{
            "error": "Error processing request",
        })
    }
    
    // Store user with parameterized query
    _, err = db.Exec("INSERT INTO users (username, email, password) VALUES (?, ?, ?)",
        u.Username, u.Email, string(hashedPassword))
    if err != nil {
        // Check for duplicate username/email
        if strings.Contains(err.Error(), "duplicate") {
            return c.JSON(http.StatusConflict, map[string]string{
                "error": "Username or email already exists",
            })
        }
        return c.JSON(http.StatusInternalServerError, map[string]string{
            "error": "Error creating user",
        })
    }
    
    return c.JSON(http.StatusCreated, map[string]string{
        "message": "User registered successfully",
    })
}

func validateUserData(u *User) error {
    // Check required fields
    if u.Username == "" || u.Email == "" || u.Password == "" {
        return errors.New("username, email, and password are required")
    }
    
    // Validate username (alphanumeric + underscore, 3-30 chars)
    if !regexp.MustCompile(`^[a-zA-Z0-9_]{3,30}$`).MatchString(u.Username) {
        return errors.New("username must be 3-30 alphanumeric characters or underscores")
    }
    
    // Validate email
    if !regexp.MustCompile(`^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$`).MatchString(u.Email) {
        return errors.New("invalid email format")
    }
    
    // Validate password (8+ chars, must include number and special char)
    if len(u.Password) < 8 {
        return errors.New("password must be at least 8 characters")
    }
    if !regexp.MustCompile(`\d`).MatchString(u.Password) {
        return errors.New("password must include at least one number")
    }
    if !regexp.MustCompile(`[^a-zA-Z0-9]`).MatchString(u.Password) {
        return errors.New("password must include at least one special character")
    }
    
    return nil
}

Common Mistakes to Avoid

Dynamic SQL Construction: Avoid building SQL queries by concatenating strings.

// BAD:
query := "SELECT * FROM products WHERE category = '" + category + "'"

// GOOD:
query := "SELECT * FROM products WHERE category = ?"
rows, err := db.Query(query, category)

Not Validating Inputs: Always validate and sanitize all user-supplied data.

Revealing Error Details: Don't expose database error messages to users.

// BAD:
if err != nil {
    return c.String(http.StatusInternalServerError, "Database error: " + err.Error())
}

// GOOD:
if err != nil {
    log.Printf("Database error: %v", err) // Log the actual error for debugging
    return c.String(http.StatusInternalServerError, "An error occurred")
}

Using Root Database User: Always use database users with limited privileges.

Summary

SQL injection remains one of the most critical web application vulnerabilities. In Echo applications, preventing SQL injection requires:

Using parameterized queries instead of concatenating strings
Leveraging ORMs that handle parameter sanitization
Validating all user inputs before using them in queries
Implementing proper error handling that doesn't reveal sensitive information
Following the principle of least privilege for database users

By following these best practices, you can significantly reduce the risk of SQL injection attacks in your Echo applications. Remember that security is an ongoing process - staying updated with the latest security practices and regularly auditing your code is essential.

Additional Resources

Exercises

Take an existing Echo handler that uses string concatenation for SQL queries and refactor it to use parameterized queries.
Create a user registration form that validates inputs and securely stores user data.
Implement a role-based access control system that uses different database users based on the required privilege level.
Audit an existing Echo application for potential SQL injection vulnerabilities.
Create a test suite that attempts SQL injection attacks against your API endpoints to verify their security.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Understanding SQL Injection​

What is SQL Injection?​

Prevention Techniques​

1. Use Parameterized Queries​

2. Use an ORM​

3. Input Validation​

4. Implement Database User Privileges​

Real-world Examples​

Example 1: Secure User Search Feature​

Example 2: Secure User Registration with Validation​

Common Mistakes to Avoid​

Summary​

Additional Resources​

Exercises​