<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Symfony\Component\HttpFoundation\Response;

class SqlInjectionProtection
{
    /**
     * SQL injection patterns to detect
     */
    protected $sqlPatterns = [
        // Basic SQL keywords
        '/\b(SELECT|INSERT|UPDATE|DELETE|DROP|UNION|ALTER|CREATE|TRUNCATE)\b/i',
        '/\b(AND|OR|NOT|LIKE|IN|BETWEEN|EXISTS)\b/i',
        '/\b(WHERE|FROM|JOIN|GROUP BY|ORDER BY|HAVING)\b/i',
        '/\b(COUNT|SUM|AVG|MAX|MIN)\b/i',
        '/\b(ASC|DESC|LIMIT|OFFSET)\b/i',
        '/\b(PRIMARY|FOREIGN|KEY|INDEX|UNIQUE)\b/i',
        '/\b(INNER|LEFT|RIGHT|FULL|CROSS)\b/i',
        '/\b(ON|AS|IS|NULL|NOT NULL)\b/i',
        '/\b(BEGIN|COMMIT|ROLLBACK|TRANSACTION)\b/i',
        '/\b(GRANT|REVOKE|DENY)\b/i',
        
        // SQL injection specific patterns
        '/(\%27)|(\')|(\-\-)|(\%23)|(#)/i',
        '/(\%3D)|(=)[^\n]*((\%27)|(\')|(\-\-)|(\%3B)|(;))/i',
        '/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/i',
        '/(\%27)|(\')union/i',
        '/exec(\s|\+)+(s|x)p\w+/i',
        '/(\%27)|(\')|(\')|(\-\-)|(\%23)|(#)/i',
        '/(\%3D)|(=)[^\n]*((\%27)|(\')|(\-\-)|(\%3B)|(;))/i',
        '/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/i',
        '/(\%27)|(\')union/i',
        '/exec(\s|\+)+(s|x)p\w+/i',
        
        // Advanced patterns
        '/(\%27)|(\')|(\')|(\-\-)|(\%23)|(#)/i',
        '/(\%3D)|(=)[^\n]*((\%27)|(\')|(\-\-)|(\%3B)|(;))/i',
        '/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/i',
        '/(\%27)|(\')union/i',
        '/exec(\s|\+)+(s|x)p\w+/i',
        '/concat\s*\(/i',
        '/group_concat\s*\(/i',
        '/@@version/i',
        '/@@datadir/i',
        '/user\(\)/i',
        '/database\(\)/i',
        '/version\(\)/i',
        '/benchmark\s*\(/i',
        '/sleep\s*\(/i',
        '/waitfor\s+delay/i',
        '/pg_sleep\s*\(/i',
        '/dbms_pipe\.receive_message/i',
        '/extractvalue\s*\(/i',
        '/updatexml\s*\(/i',
        '/load_file\s*\(/i',
        '/into\s+outfile/i',
        '/into\s+dumpfile/i',
        '/information_schema/i',
        '/mysql\.user/i',
        '/sysobjects/i',
        '/syscolumns/i',
        '/msysaccessobjects/i',
        '/msysaccessxml/i',
        '/msysrelationships/i',
        '/mdb-database/i',
        '/waitfor\s+time/i',
        '/sp_/i',
        '/xp_/i',
        '/sp_password/i',
        '/sp_makewebtask/i',
        '/xp_cmdshell/i',
        '/xp_regread/i',
        '/xp_regwrite/i',
        '/sp_OACreate/i',
        '/sp_OADestroy/i',
        '/sp_OAGetErrorInfo/i',
        '/sp_OAGetProperty/i',
        '/sp_OAMethod/i',
        '/sp_OASetProperty/i',
        '/sp_OAStop/i',
        '/xp_availablemedia/i',
        '/xp_dirtree/i',
        '/xp_enumdsn/i',
        '/xp_loginconfig/i',
        '/xp_makecab/i',
        '/xp_ntsec_enumdomains/i',
        '/xp_terminate_process/i',
        '/sp_addextendedproc/i',
        '/sp_adduser/i',
        '/sp_changedbowner/i',
        '/sp_dropdb/i',
        '/sp_executesql/i',
        '/xp_regdeletekey/i',
        '/xp_regdeletevalue/i',
        '/xp_regremovemultistring/i',
        '/xp_regwrite/i',
        '/bulk\s+insert/i',
        '/openrowset/i',
        '/opendatasource/i',
    ];

    /**
     * Handle an incoming request.
     */
    public function handle(Request $request, Closure $next): Response
    {
        // Check all input data for SQL injection patterns
        $this->checkForSqlInjection($request);

        return $next($request);
    }

    /**
     * Check request data for SQL injection patterns
     */
    protected function checkForSqlInjection(Request $request): void
    {
        // Check GET parameters
        $this->checkData($request->query->all(), $request, 'GET');

        // Check POST data
        $this->checkData($request->request->all(), $request, 'POST');

        // Check JSON data
        if ($request->isJson()) {
            $this->checkData($request->json()->all(), $request, 'JSON');
        }

        // Check headers
        $this->checkHeaders($request);

        // Check URL path
        $this->checkUrlPath($request);
    }

    /**
     * Check data array for SQL injection patterns
     */
    protected function checkData(array $data, Request $request, string $type): void
    {
        array_walk_recursive($data, function ($value, $key) use ($request, $type) {
            if (is_string($value) && $this->containsSqlInjection($value)) {
                $this->logSqlInjectionAttempt($request, $type, $key, $value);
                abort(403, 'Suspicious SQL pattern detected');
            }
        });
    }

    /**
     * Check headers for SQL injection patterns
     */
    protected function checkHeaders(Request $request): void
    {
        foreach ($request->headers->all() as $name => $values) {
            foreach ($values as $value) {
                if ($this->containsSqlInjection($value)) {
                    $this->logSqlInjectionAttempt($request, 'HEADER', $name, $value);
                    abort(403, 'Suspicious SQL pattern detected in headers');
                }
            }
        }
    }

    /**
     * Check URL path for SQL injection patterns
     */
    protected function checkUrlPath(Request $request): void
    {
        $path = $request->getPathInfo();
        if ($this->containsSqlInjection($path)) {
            $this->logSqlInjectionAttempt($request, 'URL', 'path', $path);
            abort(403, 'Suspicious SQL pattern detected in URL');
        }
    }

    /**
     * Check if string contains SQL injection patterns
     */
    protected function containsSqlInjection(string $value): bool
    {
        // Skip check for very short strings
        if (strlen($value) < 3) {
            return false;
        }

        // URL decode the value to catch encoded attacks
        $decodedValue = urldecode($value);
        
        foreach ($this->sqlPatterns as $pattern) {
            if (preg_match($pattern, $decodedValue)) {
                return true;
            }
        }

        return false;
    }

    /**
     * Log SQL injection attempt
     */
    protected function logSqlInjectionAttempt(Request $request, string $type, string $field, string $value): void
    {
        Log::warning('SQL Injection attempt detected', [
            'ip' => $request->ip(),
            'user_agent' => $request->userAgent(),
            'url' => $request->fullUrl(),
            'method' => $request->method(),
            'type' => $type,
            'field' => $field,
            'value' => substr($value, 0, 200), // Limit logged value length
            'referer' => $request->header('Referer'),
            'timestamp' => now()->toISOString(),
        ]);
    }
}
