SQL Injection (SQLi)

Overview

SQL injection (SQLi) is a vulnerability that allows an attacker to interfere with queries that an application makes to its database. It generally allows an attacker to inject SQL and gain the ability to read, modify and delete data.

For example, consider an application that displays products in different categories. The following URL will return a list of products on the Gifts category:

https://vulnerable-website.local/products?category=Gifts

The application makes the following SQL query to retrieve data from a database:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

The restriction released = 1 is being used to hide products that are not released. If the category variable is directly passed to the SQL query, an attacker can craft the following URL:

https://vulnerable-website.local/products?category=Gifts'--

This results in the SQL query:

SELECT * FROM products WHERE category = 'Gifts'--' AND released = 1

Since -- is a comment in SQL, an attacker can retrieve all products, whether they are released or not.

You can find more details at PortSwigger Web Security Academy: SQL injection.

This page contains recommendations for the implementation of protection against SQL injection (SQLi) attacks.

General

• Do not use string formatting or concatenation to assemble SQL queries.

• Use prepared statements (or parametrized queries) to assemble SQL queries.

Prepared statements

Go

Use a database driver from the following list of available drivers: https://github.com/golang/go/wiki/SQLDrivers.

Example for PostgreSQL

func handler(w http.ResponseWriter, r *http.Request) {
    userId := r.URL.Query().Get("id")
    if userId != "" {
        rows, err := db.Query("SELECT name FROM users WHERE id = $1", userId)
        // ...
    }
}

Remember that placeholders are database specific:

MySQL

Placeholder

WHERE col = ?

Example

db.Query("SELECT name FROM users WHERE favorite_fruit = ? OR age BETWEEN ? AND ?", "orange", 64, 67)

PostgreSQL

Placeholder

WHERE col = $1

Example

db.Query("SELECT name FROM users WHERE favorite_fruit = $1 OR age BETWEEN $2 AND $3", "orange", 64, 67)

Oracle

Placeholder

WHERE col = :col

Example

stmt, err := conn.Prepare("SELECT name FROM users WHERE favorite_fruit = :1 OR age BETWEEN :2 AND :3")
rows, err := stmt.Query("orange", 64, 67)

References

• OWASP Cheat Sheet Series: SQL Injection Prevention Cheat Sheet