PHP+MySQL网站SQL注入攻击检测方法咨询（含日志解析方向）

Hey Mike, great question—securing PHP/MySQL apps isn’t just about preventing attacks upfront; having detection methods in place is critical for catching anything that slips through. Let’s break down practical approaches to spot SQL injection attempts, including ready-to-use scripts and tools you can implement right away.

Scanning access and error logs is a solid starting point. Most injection attempts leave traces in request parameters or error messages. Here are two simple scripts to automate this:

Bash Script for Apache/Nginx Access Logs #

This scans logs for common SQL injection patterns (adjust the log path to match your server setup):

# Scan Apache access logs for suspicious SQL injection indicators
grep -iE "union|select|insert|delete|drop|--|;|'\"|\bor\b|\band\b" /var/log/apache2/access.log

# For Nginx, use your log path (e.g., /var/log/nginx/access.log)

PHP Script for Log Analysis #

If you prefer a PHP-based solution (easier to integrate with your app’s workflow):

<?php
$logPath = '/var/log/apache2/access.log'; // Update to your log file
$handle = fopen($logPath, 'r');

// Define patterns linked to SQL injection attempts
$suspiciousPatterns = [
    '/union.*select/i',
    '/select.*from.*where/i',
    '/insert.*into.*values/i',
    '/delete.*from/i',
    '/drop.*table/i',
    '/--/', // SQL comment
    '/\';?/', // Unclosed single quote
    '/";?/', // Unclosed double quote
    '/\s*or\s*1=1/i', // Classic true condition
];

while (($line = fgets($handle)) !== false) {
    foreach ($suspiciousPatterns as $pattern) {
        if (preg_match($pattern, $line)) {
            echo "Potential SQL Injection Request:\n$line\n";
            // Optional: Send an alert (email, Slack, etc.) here
            break;
        }
    }
}

fclose($handle);
?>

Pro Tip: Filter results by request method (GET/POST) to reduce false positives—patterns in form parameters are far more suspicious than those in page URLs or search queries.

Add lightweight checks directly to your PHP code to flag injection attempts as they happen. This is great for catching attacks before they reach your database:

<?php
function detectSqlInjection($input) {
    $dangerousStrings = [
        'union select', 'select from', 'insert into',
        'delete from', 'drop table', 'alter table',
        '--', 'or 1=1', 'and 1=1', 'exec(', 'xp_cmdshell'
    ];

    foreach ($dangerousStrings as $string) {
        if (stripos($input, $string) !== false) {
            return true;
        }
    }
    return false;
}

// Scan all GET/POST parameters
foreach (array_merge($_GET, $_POST) as $paramName => $paramValue) {
    if (detectSqlInjection($paramValue)) {
        // Log the attempt
        error_log("SQL Injection Attempt - Param: $paramName | Value: $paramValue | IP: " . $_SERVER['REMOTE_ADDR']);
        
        // Optional: Block the request
        header("HTTP/1.1 403 Forbidden");
        exit("Invalid request detected.");
    }
}
?>

Note: Adjust the $dangerousStrings list to match your app’s normal behavior. For example, if you have a search feature that allows SQL-like terms, add exceptions for those parameters.

Your MySQL database can also help detect unusual activity:

• Enable General Query Log: This logs all SQL statements executed on the server. You can scan it for queries that don’t match your app’s normal pattern (e.g., unexpected DROP TABLE or UNION statements).

  -- Enable general query log temporarily (restart MySQL to disable)
  SET GLOBAL general_log = 'ON';
  SET GLOBAL general_log_file = '/var/log/mysql/general.log';
  

• Use MySQL Audit Plugins: Tools like the official audit_log plugin track all database activity, including who ran what query and from which IP. This is invaluable for post-attack forensics.
• Monitor User Behavior: Keep an eye on database users executing rare or high-risk commands (e.g., ALTER TABLE, GRANT permissions) from unfamiliar IP addresses.

For a more robust solution, use an open-source WAF like ModSecurity. It integrates with Apache/Nginx and uses pre-built rules (like the OWASP Core Rule Set) to automatically detect and block SQL injection attempts. You won’t need to write custom scripts—just configure the rules to fit your app.

• False Positives: Always test your detection rules with normal user behavior to avoid blocking legitimate requests. For example, a search for "how to select data in MySQL" shouldn’t trigger an alert.
• Log Retention: Ensure logs are stored long enough to investigate incidents (at least 30 days, ideally more).
• Defense in Depth: Detection is a supplement, not a replacement, for secure coding practices like prepared statements (PDO/mysqli) and input validation.

内容的提问来源于stack exchange，提问作者MikeBau