Day 69 — SQL Injection Detection

Learning Objectives

• Implement SQLi detection with multiple techniques: keywords, quote balance, comment patterns
• Tune for zero false positives on 15 legitimate inputs
• Understand why WAF-only SQLi defense is insufficient (defense-in-depth)

Key Concepts

SQLi Attack Categories:
  Classic:    ' OR '1'='1   (breaks out of string context)
  Union:      ' UNION SELECT username,password FROM users--
  Blind:      ' AND 1=1--  / ' AND 1=2-- (boolean-based)
  Time-based: ' AND SLEEP(5)-- (detects via response time)
  Stacked:    '; DROP TABLE users--

Common Evasion Techniques (why normalization matters):
  Case:       SeLeCt, sElEcT
  Comments:   SE/*comment*/LECT, UN/**/ION
  URL encode: %53%45%4C%45%43%54
  Whitespace: SELECT\t\nFROM (tab/newline instead of space)

Exercise: SQLi Detection Rule

package waf

import (
    "net/http"
    "regexp"
    "strings"
    "unicode"
)

// Pre-compiled at package init — NEVER inside Match()
var (
    // Keywords that appear in SQLi but rarely in legitimate data
    sqliKeywordsRe = regexp.MustCompile(`(?i)\b(UNION\s+(?:ALL\s+)?SELECT|INSERT\s+INTO|UPDATE\s+\w+\s+SET|DELETE\s+FROM|DROP\s+(?:TABLE|DATABASE)|CREATE\s+(?:TABLE|DATABASE|USER)|EXEC(?:UTE)?|CAST\s*\(|CONVERT\s*\(|INFORMATION_SCHEMA|SLEEP\s*\(|BENCHMARK\s*\(|LOAD_FILE\s*\(|INTO\s+OUTFILE)\b`)

    // Comment patterns used in SQLi
    sqliCommentRe = regexp.MustCompile(`(--|#|\/\*.*?\*\/)`)

    // Dangerous tautologies
    sqliTautologyRe = regexp.MustCompile(`(?i)'\s*(?:OR|AND)\s+['"]?[0-9a-z]+['"]?\s*=\s*['"]?[0-9a-z]+['"]?`)
)

type SQLiRule struct{}

func (r *SQLiRule) ID() string       { return "SQLI-001" }
func (r *SQLiRule) Category() string { return "SQLI" }

func (r *SQLiRule) Match(req *http.Request, body []byte) (bool, int, string) {
    inputs := extractInputs(req, body)
    for _, input := range inputs {
        normalized := normalizeSQLi(input)

        // Check 1: keywords (score: 5)
        if m := sqliKeywordsRe.FindString(normalized); m != "" {
            return true, 5, fmt.Sprintf("SQLi keyword: %q", m)
        }

        // Check 2: tautology patterns (score: 8)
        if m := sqliTautologyRe.FindString(normalized); m != "" {
            return true, 8, fmt.Sprintf("SQLi tautology: %q", m)
        }

        // Check 3: unbalanced quotes + SQL comment (score: 7)
        stripped := sqliCommentRe.ReplaceAllString(normalized, "")
        if unbalancedQuotes(stripped) && sqliCommentRe.MatchString(normalized) {
            return true, 7, "SQLi: unbalanced quotes with comment"
        }
    }
    return false, 0, ""
}

// normalizeSQLi prepares input for rule matching
func normalizeSQLi(input string) string {
    // URL decode
    decoded, _ := url.QueryUnescape(input)
    // Collapse whitespace (including tabs, newlines)
    decoded = strings.Map(func(r rune) rune {
        if unicode.IsSpace(r) { return ' ' }
        return r
    }, decoded)
    // Collapse multiple spaces
    for strings.Contains(decoded, "  ") {
        decoded = strings.ReplaceAll(decoded, "  ", " ")
    }
    return strings.TrimSpace(decoded)
}

// unbalancedQuotes returns true if single quotes are not balanced
func unbalancedQuotes(s string) bool {
    count := 0
    for _, c := range s {
        if c == '\'' { count++ }
    }
    return count%2 != 0
}

// Test with these payloads (all should trigger):
var sqliTestPayloads = []string{
    "' OR '1'='1",
    "1' UNION SELECT * FROM users--",
    "admin'--",
    "1; DROP TABLE users--",
    "' OR 1=1#",
    "' UNION ALL SELECT NULL,NULL,NULL--",
    "1 AND SLEEP(5)--",
    "1' AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT version())))--",
}

// Legitimate inputs that must NOT trigger (tune to 0 false positives):
var sqliLegitimateInputs = []string{
    "O'Brien",                   // Irish name with apostrophe
    "It's a great product",      // natural language apostrophe
    "SELECT from dropdown",      // SELECT in natural language
    "DROP shipping address",     // DROP in context
    "2024-01-01",               // date
    "user@example.com",         // email
    "Product #123",             // hash in context
    "price > 100 AND category = 'electronics'", // URL query params
}

Real-World Context

SQLi is still #3 in OWASP Top 10 despite 20 years of awareness. The 2017 Equifax breach was SQL injection. WAF as the last line of defense is correct — parameterized queries are the real fix, but WAF catches application bugs. The normalizeSQLi function is why ModSecurity has hundreds of normalization rules.