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
}