👨‍💻
Application Security Handbook
  • Application Security Handbook
  • Web Application
    • Authentication
      • Authentication with Login and Password
      • Authentication with Phone Number
      • OAuth 2.0 Authentication
      • Multi-factor Authentication
      • Default Passwords
      • Password Change
      • Password Policy
      • Password Reset
      • Password Storage
      • One Time Password (OTP)
      • Email Address Confirmation
    • Authorization
    • Concept of Trusted Devices
    • Content Security Policy (CSP)
    • Cookie Security
    • Cryptography
      • Cryptographic Keys Management
      • Encryption
      • Hash-based Message Authentication Code (HMAC)
      • Hashing
      • Random Generators
      • Universal Unique Identifier (UUID)
    • Error and Exception Handling
    • File Upload
    • Input Validation
    • JSON Web Token (JWT)
    • Logging and Monitoring
    • Output Encoding
    • Regular Expressions
    • Sensitive Data Management
    • Session Management
    • Transport Layer Protection
    • Vulnerability Mitigation
      • Brute-force
      • Command Injection
      • Cross-Site Request Forgery (CSRF)
      • Cross-Site Scripting (XSS)
      • Mass Parameter Assignment
      • Parameter Pollution
      • Path Traversal
      • Regular Expression Denial of Service (ReDoS)
      • SQL Injection (SQLi)
      • XML External Entity (XXE) Injection
Powered by GitBook
On this page
  • Overview
  • General
  • Prepared statements
  • References
  1. Web Application
  2. Vulnerability Mitigation

SQL Injection (SQLi)

PreviousRegular Expression Denial of Service (ReDoS)NextXML External Entity (XXE) Injection

Last updated 1 year ago

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 .

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

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

Use a database driver from the following list of available drivers: .

https://github.com/golang/go/wiki/SQLDrivers
OWASP Cheat Sheet Series: SQL Injection Prevention Cheat Sheet
PortSwigger Web Security Academy: SQL injection