Detecting SQL Injection Patterns via Logs

Detecting SQL Injection Patterns via Logs

In this lesson, you will learn how to write diagnostic SQL queries to identify anomalous patterns in your audit logs that signify SQL injection (SQLi) attempts.

While robust audit policies (Lesson 25) provide the foundation, raw logs are often too voluminous to review manually. To detect SQLi, you must move beyond generic logging and target specific, syntactical "fingerprints" often left behind by attackers attempting to manipulate query logic.

Identifying Injection Heuristics

Attackers typically inject SQL commands by terminating a string and appending new logic, such as OR 1=1, UNION SELECT, or comment characters like -- or /*. When your audit system captures the raw text of executed statements, you can analyze these logs to flag suspicious activity.

Consider a scenario where your audit_logs table stores the executed_sql column. You can search for these common injection markers using pattern matching:

SELECT 
    db_user, 
    executed_sql, 
    timestamp
FROM audit_logs
WHERE executed_sql REGEXP '(?i)(OR|AND)\\s+[\'"]?\\d+[\'"]?\\s*=\\s*[\'"]?\\d+'
   OR executed_sql LIKE '%UNION%SELECT%'
   OR executed_sql LIKE '%--%'
   OR executed_sql LIKE '%/*%';

Analyzing Anomalous Patterns

Beyond simple keyword matching, look for structural anomalies. For example, a legitimate application query often maintains a consistent length and character distribution. Injection attempts frequently involve:

1. Unexpected Keyword Density: A high frequency of reserved keywords (e.g., INFORMATION_SCHEMA, HEX, SLEEP) appearing in logs that typically only contain standard SELECT or UPDATE statements.
2. String Length Variance: If your application generates predictable queries, an sudden spike in query length—often caused by long injection strings—is a high-fidelity alert.
3. Encoding Shifts: Attempts to bypass filters often use HEX or CHAR() encoding. Querying for these functions within your logs can reveal obfuscated payloads that escaped application-side sanitization (Lesson 5).

By querying your audit store for these patterns, you turn reactive logs into a proactive detection system. Always ensure these logs are stored securely, as they now contain evidence of active threats to your infrastructure.

Key Takeaway: Use pattern-matching queries on audit logs to identify structural anomalies and reserved-keyword abuse, effectively turning passive logs into an active SQL injection detection mechanism.