Preventing SQL Injection with Raw SQL and ORM in Golang

Secure Golang Database Interactions: Preventing SQL Injection

In today's development landscape, secure coding practices are paramount. This article focuses on safeguarding Golang applications from SQL injection vulnerabilities, a common threat when interacting with databases. We'll explore prevention techniques using both raw SQL and Object-Relational Mapping (ORM) frameworks.

---

Understanding SQL Injection

SQL Injection (SQLi) is a critical web security flaw. Attackers exploit it by injecting malicious SQL code into database queries, potentially compromising data integrity and application security.

A vulnerable query example:

<code class="language-go">query := "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
rows, err := db.Query(query)</code>

Malicious input in username or password can alter the query's logic.

For a deeper understanding of SQL injection, refer to this other post.

---

Securing Raw SQL Queries

When working directly with SQL, prioritize these security measures:

1. Prepared Statements: Go's database/sql package offers prepared statements, a crucial defense against SQLi.

Vulnerable Example:

<code class="language-go">query := "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
rows, err := db.Query(query) // Vulnerable to SQL injection</code>

Secure Version (Prepared Statement):

<code class="language-go">query := "SELECT * FROM users WHERE username = ? AND password = ?"
rows, err := db.Query(query, username, password)
if err != nil {
    log.Fatal(err)
}</code>

Prepared statements automatically escape user inputs, preventing injection.

2. Parameterized Queries: Use db.Query or db.Exec with placeholders for parameterized queries:

<code class="language-go">query := "INSERT INTO products (name, price) VALUES (?, ?)"
_, err := db.Exec(query, productName, productPrice)
if err != nil {
    log.Fatal(err)
}</code>

Avoid string concatenation or fmt.Sprintf for dynamic queries.

3. QueryRow for Single Records: For single-row retrieval, QueryRow minimizes risk:

<code class="language-go">query := "SELECT id, name FROM users WHERE email = ?"
var id int
var name string
err := db.QueryRow(query, email).Scan(&id, &name)
if err != nil {
    log.Fatal(err)
}</code>

4. Input Validation and Sanitization: Even with prepared statements, validate and sanitize inputs:

• Sanitization: Removes unwanted characters.
• Validation: Checks input format, type, and length.

Go Input Validation Example:

<code class="language-go">func isValidUsername(username string) bool {
    re := regexp.MustCompile(`^[a-zA-Z0-9_]+$`)
    return re.MatchString(username)
}

if len(username) > 50 || !isValidUsername(username) {
    log.Fatal("Invalid input")
}</code>

5. Stored Procedures: Encapsulate query logic within database stored procedures:

<code class="language-sql">CREATE PROCEDURE AuthenticateUser(IN username VARCHAR(50), IN password VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE username = username AND password = password;
END;</code>

Call from Go:

<code class="language-go">_, err := db.Exec("CALL AuthenticateUser(?, ?)", username, password)
if err != nil {
    log.Fatal(err)
}</code>

---

Preventing SQL Injection with ORMs

ORMs like GORM and XORM simplify database interactions, but safe practices are still vital.

1. GORM:

Vulnerable Example (Dynamic Query):

<code class="language-go">db.Raw("SELECT * FROM users WHERE name = '" + userName + "'").Scan(&user)</code>

Secure Example (Parameterized Query):

<code class="language-go">db.Raw("SELECT * FROM users WHERE name = ? AND email = ?", userName, email).Scan(&user)</code>

GORM's Raw method supports placeholders. Prefer GORM's built-in methods like Where:

<code class="language-go">query := "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
rows, err := db.Query(query)</code>

2. Avoid Raw SQL for Complex Queries: Use placeholders even with complex raw queries.

3. Struct Tags for Safe Mapping: Use struct tags for safe ORM mapping:

<code class="language-go">query := "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'"
rows, err := db.Query(query) // Vulnerable to SQL injection</code>

Common Mistakes to Avoid:

1. Avoid String Concatenation in Queries.
2. Avoid ORM Functions Bypassing Safety Checks.
3. Never Trust User Input Without Validation.

---

Conclusion

Golang provides robust tools for secure database interaction. By using prepared statements, parameterized queries, ORMs correctly, and diligently validating and sanitizing user input, you significantly reduce the risk of SQL injection vulnerabilities.

Connect with me on:

• LinkedIn
• GitHub
• Twitter/X

The above is the detailed content of Preventing SQL Injection with Raw SQL and ORM in Golang. For more information, please follow other related articles on the PHP Chinese website!