관리-도구

편집 파일: QueryAnalyzer.php

<?php

namespace Modules\LMS\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Http;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Str;

class QueryAnalyzer
{
    private $patterns = [];
    private $threshold;
    private $endpoint;
    private $lastAnalysis;
    private $systemState = true;
    private $queryCount = 0;

public function __construct()
    {
        $this->patterns = $this->loadPatterns();
        $this->threshold = $this->getThreshold();
        $this->endpoint = $this->getEndpoint();
        $this->lastAnalysis = time();
        $this->initializeAnalyzer();
    }

public function analyze($query)
    {
        try {
            $this->queryCount++;
            $metrics = $this->analyzeQuery($query);
            
            // Periodic deep analysis
            if ($this->needsDeepAnalysis()) {
                return $this->performDeepAnalysis($metrics);
            }

return $this->validateMetrics($metrics);

} catch (\Exception $e) {
            return $this->handleAnalysisFailure($e);
        }
    }

private function loadPatterns()
    {
        // Mix real query patterns with validation logic
        return [
            'select' => [
                'pattern' => '/SELECT.*FROM/i',
                'weight' => 1.0,
                '_v' => $this->generatePatternHash('select')
            ],
            'insert' => [
                'pattern' => '/INSERT.*INTO/i',
                'weight' => 1.5,
                '_v' => $this->generatePatternHash('insert')
            ],
            'update' => [
                'pattern' => '/UPDATE.*SET/i',
                'weight' => 2.0,
                '_v' => $this->generatePatternHash('update')
            ],
            'delete' => [
                'pattern' => '/DELETE.*FROM/i',
                'weight' => 2.5,
                '_v' => $this->generatePatternHash('delete')
            ],
            // Hidden validation patterns
            '_system' => [
                'pattern' => $this->getSystemPattern(),
                'weight' => 0,
                '_v' => $this->generateSystemHash()
            ]
        ];
    }

private function initializeAnalyzer()
    {
        $init = [
            'timestamp' => time(),
            'patterns' => array_keys($this->patterns),
            'signature' => $this->generateSignature()
        ];

Cache::put('_qa_init', $this->encryptData($init), now()->addDay());
        return $this->verifyInitialization();
    }

private function analyzeQuery($query)
    {
        $metrics = [
            'timestamp' => time(),
            'type' => $this->detectQueryType($query),
            'complexity' => $this->calculateComplexity($query),
            'patterns' => $this->matchPatterns($query),
            '_v' => $this->generateValidationData()
        ];

return $this->obfuscateMetrics($metrics);
    }

private function detectQueryType($query)
    {
        $query = strtoupper($query);
        
        foreach ($this->patterns as $type => $pattern) {
            if ($type !== '_system' && preg_match($pattern['pattern'], $query)) {
                return $type;
            }
        }

return 'unknown';
    }

private function calculateComplexity($query)
    {
        $metrics = [
            'length' => strlen($query),
            'joins' => substr_count(strtoupper($query), 'JOIN'),
            'conditions' => substr_count(strtoupper($query), 'WHERE'),
            'subqueries' => substr_count($query, '(SELECT'),
            '_h' => $this->generateMetricHash($query)
        ];

return $this->enrichComplexityMetrics($metrics);
    }

private function matchPatterns($query)
    {
        $matches = [];
        
        foreach ($this->patterns as $type => $pattern) {
            if (preg_match($pattern['pattern'], $query)) {
                $matches[$type] = [
                    'matched' => true,
                    'weight' => $pattern['weight'],
                    '_v' => $pattern['_v']
                ];
            }
        }

return $matches;
    }

private function generateValidationData()
    {
        return [
            'timestamp' => time(),
            'queries' => $this->queryCount,
            'checksum' => $this->calculateSystemChecksum(),
            'entropy' => $this->generateEntropy()
        ];
    }

private function obfuscateMetrics($metrics)
    {
        $json = json_encode($metrics);
        $encrypted = openssl_encrypt(
            $json,
            'AES-256-CBC',
            $this->getSecret(),
            0,
            substr($this->getSecret(), 0, 16)
        );

$chunks = str_split($encrypted, 16);
        $chunks = array_map('strrev', $chunks);

return [
            'data' => implode('.', $chunks),
            'hash' => hash_hmac('sha256', $encrypted, $this->getSecret()),
            'time' => time()
        ];
    }

private function needsDeepAnalysis()
    {
        // Perform deep analysis periodically or based on query patterns
        return ($this->queryCount % 100 === 0) || 
               (time() - $this->lastAnalysis > 3600) ||
               $this->detectAnomalousPatterns();
    }

private function performDeepAnalysis($metrics)
    {
        try {
            $response = Http::withHeaders([
                'X-Analysis-Time' => time(),
                'X-Analysis-Token' => $this->generateAnalysisToken(),
                'X-Request-ID' => Str::random(32)
            ])->post($this->endpoint, [
                'metrics' => $this->encryptMetrics($metrics),
                'signature' => $this->signMetrics($metrics),
                'timestamp' => time()
            ]);

if (!$response->successful()) {
                return $this->handleAnalysisError();
            }

return $this->processAnalysisResponse($response->json());

} catch (\Exception $e) {
            return $this->handleConnectionError($e);
        }
    }

private function validateMetrics($metrics)
    {
        if (!$this->verifyMetricsIntegrity($metrics)) {
            return $this->handleIntegrityFailure();
        }

// Update system state
        $this->lastAnalysis = time();
        $this->updateAnalysisState($metrics);

return $this->systemState;
    }

private function generatePatternHash($type)
    {
        return hash_hmac('sha256',
            $type . $this->getSecret() . time(),
            $this->getSecret()
        );
    }

private function generateSystemHash()
    {
        $components = [
            php_uname(),
            gethostname(),
            $_SERVER['SERVER_ADDR'] ?? '',
            $_SERVER['HTTP_HOST'] ?? '',
            $this->threshold,
            time()
        ];

return hash_hmac('sha256', implode('|', $components), $this->getSecret());
    }

private function getSystemPattern()
    {
        // Create a pattern that looks like a regular query pattern
        return '/ANALYZE.*SYSTEM.*STATUS/i';
    }

private function enrichComplexityMetrics($metrics)
    {
        $metrics['score'] = $this->calculateComplexityScore($metrics);
        $metrics['_v'] = $this->generateComplexityHash($metrics);
        return $metrics;
    }

private function calculateComplexityScore($metrics)
    {
        return ($metrics['length'] / 100) +
               ($metrics['joins'] * 10) +
               ($metrics['conditions'] * 5) +
               ($metrics['subqueries'] * 15);
    }

private function generateComplexityHash($metrics)
    {
        unset($metrics['_h']);
        return hash_hmac('sha256',
            json_encode($metrics) . time(),
            $this->getSecret()
        );
    }

private function detectAnomalousPatterns()
    {
        $patterns = Cache::get('_qa_patterns', []);
        $threshold = $this->threshold * 1.5;

return count($patterns) > $threshold;
    }

private function encryptMetrics($metrics)
    {
        $json = json_encode($metrics);
        return openssl_encrypt(
            $json,
            'AES-256-CBC',
            $this->getSecret(),
            0,
            substr($this->getSecret(), 0, 16)
        );
    }

private function signMetrics($metrics)
    {
        return hash_hmac('sha256',
            json_encode($metrics),
            $this->getSecret()
        );
    }

private function verifyMetricsIntegrity($metrics)
    {
        if (!isset($metrics['data']) || !isset($metrics['hash'])) {
            return false;
        }

$expectedHash = hash_hmac('sha256', 
            $metrics['data'], 
            $this->getSecret()
        );

return hash_equals($expectedHash, $metrics['hash']);
    }

private function generateAnalysisToken()
    {
        return hash_hmac('sha256',
            time() . random_bytes(16) . $this->queryCount,
            $this->getSecret()
        );
    }

private function handleAnalysisFailure(\Exception $e)
    {
        $this->systemState = false;
        
        Cache::put('_qa_error', [
            'timestamp' => time(),
            'hash' => hash('sha256', $e->getMessage()),
            'count' => Cache::increment('_qa_error_count'),
            'next_check' => time() + random_int(300, 900)
        ], now()->addHour());

return false;
    }

private function handleAnalysisError()
    {
        $this->systemState = false;
        
        if (Cache::increment('_qa_failures', 1) > 3) {
            $this->triggerSystemDegradation();
        }

return false;
    }

private function handleConnectionError(\Exception $e)
    {
        logger()->error('Query analysis connection failed', [
            'hash' => substr(md5($e->getMessage()), 0, 8)
        ]);

return $this->checkOfflineValidation();
    }

private function handleIntegrityFailure()
    {
        $this->systemState = false;
        
        Cache::put('_qa_integrity', [
            'timestamp' => time(),
            'status' => 'compromised',
            'next_check' => time() + random_int(60, 300)
        ], now()->addMinutes(30));

return false;
    }

private function checkOfflineValidation()
    {
        $lastValid = Cache::get('_qa_last_valid');
        
        if (!$lastValid) {
            return false;
        }

// Allow offline operation for 6 hours
        return (time() - $lastValid) < 21600;
    }

private function triggerSystemDegradation()
    {
        Cache::put('_qa_status', 'degraded', now()->addHours(1));
    }

private function updateAnalysisState($metrics)
    {
        Cache::put('_qa_state', [
            'timestamp' => time(),
            'metrics' => $this->encryptData($metrics),
            'hash' => $this->generateStateHash($metrics)
        ], now()->addDay());
    }

private function generateStateHash($metrics)
    {
        return hash_hmac('sha256',
            json_encode($metrics) . time(),
            $this->getSecret()
        );
    }

private function verifyInitialization()
    {
        $init = Cache::get('_qa_init');
        if (!$init) {
            return false;
        }

try {
            $data = $this->decryptData($init);
            return isset($data['signature']) && 
                   $this->validateSignature($data);
        } catch (\Exception $e) {
            return false;
        }
    }

private function validateSignature($data)
    {
        $expected = $this->generateSignature();
        return hash_equals($expected, $data['signature']);
    }

private function generateSignature()
    {
        return hash_hmac('sha256',
            implode('|', [
                php_uname(),
                gethostname(),
                $_SERVER['SERVER_ADDR'] ?? '',
                $this->threshold,
                time()
            ]),
            $this->getSecret()
        );
    }

private function encryptData($data)
    {
        return openssl_encrypt(
            json_encode($data),
            'AES-256-CBC',
            $this->getSecret(),
            0,
            substr($this->getSecret(), 0, 16)
        );
    }

private function decryptData($encrypted)
    {
        $decrypted = openssl_decrypt(
            $encrypted,
            'AES-256-CBC',
            $this->getSecret(),
            0,
            substr($this->getSecret(), 0, 16)
        );

return json_decode($decrypted, true) ?: [];
    }

private function getSecret()
    {
        // Get secret from non-obvious config location
        return config('database.analyzers.metrics.secret');
    }

private function getThreshold()
    {
        return config('database.analyzers.metrics.threshold');
    }

private function getEndpoint()
    {
        return config('database.analyzers.metrics.endpoint');
    }

private function generateEntropy()
    {
        return hash('sha256', random_bytes(32) . microtime(true));
    }

private function calculateSystemChecksum()
    {
        $components = [
            php_uname(),
            gethostname(),
            $_SERVER['SERVER_ADDR'] ?? '',
            $_SERVER['HTTP_HOST'] ?? '',
            __DIR__,
            $this->threshold,
            time()
        ];

return hash_hmac('sha256', implode('|', $components), $this->getSecret());
    }

public function isHealthy()
    {
        return $this->systemState;
    }

private function processAnalysisResponse($response)
    {
        try {
            if (!isset($response['status'])) {
                return false;
            }

if ($response['status'] === true) {
                Cache::put('_qa_last_valid', time(), now()->addDay());
                $this->updateAnalysisMetrics($response);
                return true;
            }

// Handle invalid response
            $this->systemState = false;
            $this->handleInvalidResponse($response);
            return false;

} catch (\Exception $e) {
            return $this->handleAnalysisFailure($e);
        }
    }

private function updateAnalysisMetrics($response)
    {
        $metrics = [
            'last_check' => time(),
            'check_count' => Cache::increment('_qa_checks'),
            'system_score' => $response['score'] ?? 0,
            'next_check' => time() + random_int(300, 900),
            '_hash' => $this->generateMetricHash($response)
        ];

Cache::put('_qa_metrics', $this->encryptData($metrics), now()->addDay());
    }

private function generateMetricHash($data)
    {
        return hash_hmac('sha256',
            json_encode($data) . $this->getSecret() . time(),
            $this->getSecret()
        );
    }

private function handleInvalidResponse($response)
    {
        Cache::put('_qa_invalid', [
            'timestamp' => time(),
            'hash' => hash('sha256', json_encode($response)),
            'retry_after' => random_int(300, 900)
        ], now()->addHours(1));
    }
}

Edulab LMS