Introduction

SQL injection (SQLi) allows attackers to execute arbitrary SQL queries against your database, potentially reading, modifying, or deleting any data the application's database user can access. When SQLi is detected in logs, it may mean the attack has been ongoing for some time, and data exfiltration may have already occurred. Immediate containment, thorough investigation, and proper remediation are essential.

Symptoms

  • Web application firewall (WAF) logs show SQL injection patterns in request parameters
  • Database slow query log shows unusual queries with UNION, SELECT FROM information_schema
  • Application error logs show SQL syntax errors from malformed injection attempts
  • Unusual data export patterns (large result sets, full table dumps)
  • Database audit log shows queries from the application user accessing tables it normally does not query

Common Causes

  • User input directly concatenated into SQL queries without parameterization
  • Search forms, login pages, or URL parameters vulnerable to SQL injection
  • ORM misconfiguration allowing raw SQL injection
  • Stored SQL injection in database content (comments, profile fields)
  • Second-order SQL injection through data stored in the database

Step-by-Step Fix

  1. 1.Identify the vulnerable endpoint and attack vectors:
  2. 2.```bash
  3. 3.# Search web server logs for SQL injection patterns
  4. 4.sudo grep -iE "union.*select|or\s+1=1|drop\s+table|information_schema|sleep\(|benchmark\(" /var/log/nginx/access.log
  5. 5.# Find the most targeted endpoints
  6. 6.sudo grep -iE "union|select|insert|update|delete" /var/log/nginx/access.log | \
  7. 7.awk '{print $7}' | sort | uniq -c | sort -rn | head -10
  8. 8.`
  9. 9.Immediately block the attacking IPs:
  10. 10.```bash
  11. 11.# Block identified attacking IPs
  12. 12.sudo iptables -A INPUT -s ATTACKER_IP -j DROP
  13. 13.# Or at the application/WAF level
  14. 14.`
  15. 15.Assess what data may have been exfiltrated:
  16. 16.```bash
  17. 17.# Check database query logs for the attack period
  18. 18.# PostgreSQL
  19. 19.sudo -u postgres psql -c "SELECT query, pg_stat_activity.query_start FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start DESC;"

# MySQL (if general log was enabled) sudo grep -i "SELECT.*FROM.*users|SELECT.*password|SELECT.*credit" /var/log/mysql/mysql.log

# Check for data volume anomalies sudo -u postgres psql -c "SELECT schemaname, relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;" ```

  1. 1.Patch the SQL injection vulnerability:
  2. 2.```python
  3. 3.# BEFORE (vulnerable):
  4. 4.query = "SELECT * FROM users WHERE username = '" + username + "'"
  5. 5.cursor.execute(query)

# AFTER (parameterized query): query = "SELECT * FROM users WHERE username = %s" cursor.execute(query, (username,)) ```

  1. 1.Rotate all database credentials:
  2. 2.```bash
  3. 3.# Change the database user password
  4. 4.sudo -u postgres psql -c "ALTER USER app_user WITH PASSWORD 'new-strong-password';"
  5. 5.# Update application configuration
  6. 6.sudo nano /etc/myapp/config.yml
  7. 7.# Restart the application
  8. 8.sudo systemctl restart myapp
  9. 9.`
  10. 10.Implement WAF rules to block SQL injection patterns:
  11. 11.```nginx
  12. 12.# ModSecurity rules for SQL injection
  13. 13.SecRule ARGS "@rx (?i:union.*select|select.*from|insert.*into|delete.*from|drop\s+table)" \
  14. 14."id:1001,phase:2,deny,status:403,msg:'SQL Injection Detected'"
  15. 15.`

Prevention

  • Use parameterized queries (prepared statements) for all database operations
  • Implement input validation and sanitization at the application boundary
  • Deploy a Web Application Firewall (WAF) with SQL injection rule sets
  • Use database users with minimum required permissions (no DROP, no cross-schema access)
  • Enable database audit logging and monitor for anomalous query patterns
  • Conduct regular penetration testing and code reviews focusing on SQL injection vectors