WHMCS Database Security: Complete Hardening Guide

# /etc/mysql/mysql.conf.d/whmcs-security.cnf
# Enterprise MySQL Security Configuration for WHMCS

[mysqld]
# Network Security
bind-address                    = 127.0.0.1
port                           = 3306
skip-networking                = OFF
skip-name-resolve              = ON

# SSL/TLS Configuration
ssl-ca                         = /etc/mysql/ssl/ca-cert.pem
ssl-cert                       = /etc/mysql/ssl/server-cert.pem
ssl-key                        = /etc/mysql/ssl/server-key.pem
ssl-cipher                     = ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256
require_secure_transport       = ON

# Authentication Security
validate_password.policy       = STRONG
validate_password.length       = 16
validate_password.mixed_case_count = 2
validate_password.number_count = 2
validate_password.special_char_count = 2
default_authentication_plugin = mysql_native_password

# Connection Limits
max_connections                = 100
max_connect_errors            = 10
max_user_connections          = 50
connect_timeout               = 10
wait_timeout                  = 600
interactive_timeout           = 600

# Query Security
sql_mode                      = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
local_infile                  = OFF
show_database                 = OFF

# Logging Security
general_log                   = ON
general_log_file              = /var/log/mysql/general.log
slow_query_log               = ON
slow_query_log_file          = /var/log/mysql/slow.log
long_query_time              = 2
log_queries_not_using_indexes = ON

# Binary Logging (for point-in-time recovery)
log_bin                       = /var/log/mysql/mysql-bin
server_id                     = 1
binlog_format                 = ROW
binlog_row_image             = FULL
expire_logs_days             = 7

# Security Features
symbolic-links                = 0
secure_file_priv             = /var/lib/mysql-files/
automatic_sp_privileges       = OFF

# Memory and Performance
key_buffer_size              = 256M
max_allowed_packet           = 16M
thread_stack                 = 192K
thread_cache_size            = 8
query_cache_size             = 64M
query_cache_limit            = 4M

# InnoDB Security
innodb_file_per_table        = ON
innodb_buffer_pool_size      = 1G
innodb_log_file_size         = 256M
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite           = ON

#!/bin/bash
# WHMCS Database User Security Setup
# Creates secure database users with minimal privileges

# Configuration
DB_NAME="whmcs_secure"
WHMCS_USER="whmcs_app"
BACKUP_USER="whmcs_backup"
AUDIT_USER="whmcs_audit"
ROOT_PASS="your_root_password"
WHMCS_PASS=$(openssl rand -base64 32)
BACKUP_PASS=$(openssl rand -base64 32)
AUDIT_PASS=$(openssl rand -base64 32)

echo "Setting up secure WHMCS database users..."

# Create WHMCS application user with minimal privileges
mysql -u root -p"$ROOT_PASS" << EOF
-- Remove default users and databases
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- Create WHMCS database
CREATE DATABASE IF NOT EXISTS $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create application user with restricted privileges
CREATE USER '$WHMCS_USER'@'localhost' IDENTIFIED BY '$WHMCS_PASS';
GRANT SELECT, INSERT, UPDATE, DELETE ON $DB_NAME.* TO '$WHMCS_USER'@'localhost';

-- Create backup user with read-only access
CREATE USER '$BACKUP_USER'@'localhost' IDENTIFIED BY '$BACKUP_PASS';
GRANT SELECT, LOCK TABLES ON $DB_NAME.* TO '$BACKUP_USER'@'localhost';

-- Create audit user for monitoring
CREATE USER '$AUDIT_USER'@'localhost' IDENTIFIED BY '$AUDIT_PASS';
GRANT SELECT ON mysql.* TO '$AUDIT_USER'@'localhost';
GRANT SELECT ON information_schema.* TO '$AUDIT_USER'@'localhost';
GRANT SELECT ON performance_schema.* TO '$AUDIT_USER'@'localhost';

-- Enable password validation
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Configure password policies
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 16;

-- Flush privileges
FLUSH PRIVILEGES;

-- Show created users
SELECT User, Host, ssl_type FROM mysql.user WHERE User LIKE 'whmcs_%';
EOF

echo "Database users created successfully!"
echo "WHMCS User: $WHMCS_USER"
echo "WHMCS Password: $WHMCS_PASS"
echo "Backup User: $BACKUP_USER"
echo "Backup Password: $BACKUP_PASS"

# Save credentials securely
cat > /etc/whmcs/db-credentials.conf << EOF
[whmcs]
username=$WHMCS_USER
password=$WHMCS_PASS
database=$DB_NAME

[backup]
username=$BACKUP_USER
password=$BACKUP_PASS

[audit]
username=$AUDIT_USER
password=$AUDIT_PASS
EOF

chmod 600 /etc/whmcs/db-credentials.conf
chown root:root /etc/whmcs/db-credentials.conf

echo "Credentials saved to /etc/whmcs/db-credentials.conf"

#!/bin/bash
# WHMCS Database Firewall Implementation
# Blocks malicious SQL patterns and injection attempts

# Install and configure ProxySQL as database firewall
install_proxysql() {
    echo "Installing ProxySQL database firewall..."
    
    # Add ProxySQL repository
    wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -
    echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./ \
        | tee /etc/apt/sources.list.d/proxysql.list
    
    apt-get update
    apt-get install -y proxysql
    
    systemctl start proxysql
    systemctl enable proxysql
}

# Configure ProxySQL for WHMCS
configure_proxysql() {
    cat > /etc/proxysql-admin.cnf << 'EOF'
[client]
user=admin
password=admin
host=127.0.0.1
port=6032
EOF

    # ProxySQL configuration
    mysql --defaults-file=/etc/proxysql-admin.cnf << 'EOF'
-- Configure MySQL servers
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(0, '127.0.0.1', 3306, 1000);

-- Configure users
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES
('whmcs_app', 'your_secure_password', 0);

-- SQL Injection Prevention Rules
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, replace_pattern, apply, comment) VALUES
(1, 1, '^SELECT.*UNION.*SELECT', '', 0, 'Block UNION-based SQL injection'),
(2, 1, '.*\bOR\s+1\s*=\s*1.*', '', 0, 'Block OR 1=1 injection'),
(3, 1, '.*\bAND\s+1\s*=\s*1.*', '', 0, 'Block AND 1=1 injection'),
(4, 1, '.*;\s*(DROP|DELETE|UPDATE|INSERT).*', '', 0, 'Block stacked queries'),
(5, 1, '.*\b(CONCAT|CHAR|ASCII)\s*\(.*', '', 0, 'Block function-based injection'),
(6, 1, '.*\b(SLEEP|BENCHMARK)\s*\(.*', '', 0, 'Block time-based injection'),
(7, 1, '.*\b(LOAD_FILE|INTO\s+OUTFILE).*', '', 0, 'Block file operations'),
(8, 1, '.*\b(INFORMATION_SCHEMA|MYSQL\.|SYS\.).*', '', 0, 'Block schema enumeration'),
(9, 1, '.*--.*', '', 0, 'Block SQL comments'),
(10, 1, '.*\b(EXEC|EXECUTE)\s*[\(\s].*', '', 0, 'Block stored procedure execution');

-- Rate limiting rules
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, delay, apply, comment) VALUES
(100, 1, '^SELECT.*', 100, 1, 'Rate limit SELECT queries');

-- Load configuration
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

-- Save configuration
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
EOF

    echo "ProxySQL configured for WHMCS protection"
}

# Advanced SQL injection detection
setup_advanced_detection() {
    cat > /usr/local/bin/sql-injection-monitor.py << 'EOF'
#!/usr/bin/env python3
"""
Advanced SQL Injection Detection for WHMCS
Monitors MySQL logs for injection attempts and blocks sources
"""

import re
import time
import subprocess
from datetime import datetime
import pymysql
import logging

# Configuration
LOG_FILE = '/var/log/mysql/general.log'
BLOCKED_IPS_FILE = '/etc/mysql/blocked_ips.conf'
MAX_ATTEMPTS = 5
BAN_DURATION = 3600  # 1 hour

# SQL Injection patterns
INJECTION_PATTERNS = [
    r'(\%27)|(\')|(\-\-)|(\%23)|(#)',  # Basic injection
    r'((\%3D)|(=))[^\n]*((\%27)|(\')|(\-\-)|(\%23))',  # Union injection
    r'\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))',  # OR injection
    r'((\%27)|(\'))union',  # Union-based
    r'exec(\s|\+)+(s|x)p\w+',  # Stored procedure
    r'UNION(.|\n)*SELECT',  # Union select
    r'SELECT(.|\n)*FROM(.|\n)*WHERE',  # Select from where
    r'INSERT(.|\n)*INTO(.|\n)*VALUES',  # Insert into
    r'UPDATE(.|\n)*SET(.|\n)*WHERE',  # Update set
    r'DELETE(.|\n)*FROM(.|\n)*WHERE'   # Delete from
]

class SQLInjectionMonitor:
    def __init__(self):
        self.blocked_ips = set()
        self.attempt_counts = {}
        self.setup_logging()
        
    def setup_logging(self):
        logging.basicConfig(
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.FileHandler('/var/log/sql-injection-monitor.log'),
                logging.StreamHandler()
            ]
        )
        self.logger = logging.getLogger(__name__)
        
    def detect_injection(self, query):
        """Detect SQL injection patterns in query"""
        for pattern in INJECTION_PATTERNS:
            if re.search(pattern, query, re.IGNORECASE):
                return True
        return False
        
    def get_client_ip(self, log_line):
        """Extract client IP from MySQL log line"""
        ip_pattern = r'(\d+\.\d+\.\d+\.\d+)'
        match = re.search(ip_pattern, log_line)
        return match.group(1) if match else None
        
    def block_ip(self, ip):
        """Block IP using iptables"""
        try:
            cmd = f"iptables -A INPUT -s {ip} -j DROP"
            subprocess.run(cmd.split(), check=True)
            
            # Add to blocked IPs file
            with open(BLOCKED_IPS_FILE, 'a') as f:
                f.write(f"{ip} {datetime.now().isoformat()}\n")
                
            self.logger.warning(f"Blocked IP {ip} for SQL injection attempt")
            return True
        except subprocess.CalledProcessError:
            self.logger.error(f"Failed to block IP {ip}")
            return False
            
    def monitor_logs(self):
        """Monitor MySQL logs for injection attempts"""
        self.logger.info("Starting SQL injection monitoring")
        
        with open(LOG_FILE, 'r') as f:
            # Move to end of file
            f.seek(0, 2)
            
            while True:
                line = f.readline()
                if not line:
                    time.sleep(1)
                    continue
                    
                # Check for SQL injection patterns
                if self.detect_injection(line):
                    ip = self.get_client_ip(line)
                    if ip:
                        self.attempt_counts[ip] = self.attempt_counts.get(ip, 0) + 1
                        
                        self.logger.warning(f"SQL injection attempt from {ip}: {line.strip()}")
                        
                        if self.attempt_counts[ip] >= MAX_ATTEMPTS:
                            if ip not in self.blocked_ips:
                                self.block_ip(ip)
                                self.blocked_ips.add(ip)

if __name__ == "__main__":
    monitor = SQLInjectionMonitor()
    monitor.monitor_logs()
EOF

    chmod +x /usr/local/bin/sql-injection-monitor.py
    
    # Create systemd service
    cat > /etc/systemd/system/sql-injection-monitor.service << EOF
[Unit]
Description=SQL Injection Monitor for WHMCS
After=mysql.service

[Service]
Type=simple
User=root
ExecStart=/usr/local/bin/sql-injection-monitor.py
Restart=always
RestartSec=10

[Install]
WantedBy=multi-user.target
EOF

    systemctl daemon-reload
    systemctl enable sql-injection-monitor
    systemctl start sql-injection-monitor
}

# Install ProxySQL
install_proxysql
configure_proxysql
setup_advanced_detection

echo "SQL injection prevention system installed successfully!"

#!/bin/bash
# WHMCS Database Encryption at Rest
# Implements full database encryption with key management

# Configuration
MYSQL_DATA_DIR="/var/lib/mysql"
KEYRING_FILE="/var/lib/mysql-keyring/keyring"
MASTER_KEY_FILE="/etc/mysql/encryption/master.key"
WHMCS_DB="whmcs_secure"

# Generate master encryption key
setup_encryption_keys() {
    echo "Setting up database encryption keys..."
    
    # Create directories
    mkdir -p /var/lib/mysql-keyring
    mkdir -p /etc/mysql/encryption
    
    # Generate master key (256-bit)
    openssl rand -out "$MASTER_KEY_FILE" 32
    chmod 600 "$MASTER_KEY_FILE"
    chown mysql:mysql "$MASTER_KEY_FILE"
    
    # Create keyring configuration
    cat >> /etc/mysql/mysql.conf.d/encryption.cnf << EOF
[mysqld]
# Encryption Configuration
early-plugin-load = keyring_file.so
keyring_file_data = $KEYRING_FILE
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
encrypt_tmp_files = ON
encrypt_binlog = ON

# Table encryption
default_table_encryption = ON
table_encryption_privilege_check = ON
EOF

    # Set permissions
    chown -R mysql:mysql /var/lib/mysql-keyring
    chmod 750 /var/lib/mysql-keyring
    
    echo "Encryption keys generated successfully"
}

# Enable table-level encryption for WHMCS
encrypt_whmcs_tables() {
    echo "Encrypting WHMCS database tables..."
    
    # Get list of WHMCS tables
    mysql -u root -p << EOF
USE $WHMCS_DB;

-- Encrypt sensitive tables
ALTER TABLE tblclients ENCRYPTION='Y';
ALTER TABLE tblclientscredit ENCRYPTION='Y';
ALTER TABLE tblcreditcards ENCRYPTION='Y';
ALTER TABLE tblbillableitems ENCRYPTION='Y';
ALTER TABLE tblinvoices ENCRYPTION='Y';
ALTER TABLE tblinvoiceitems ENCRYPTION='Y';
ALTER TABLE tblpayments ENCRYPTION='Y';
ALTER TABLE tblaccounts ENCRYPTION='Y';
ALTER TABLE tblaffiliatespayments ENCRYPTION='Y';
ALTER TABLE tblbankaccounts ENCRYPTION='Y';
ALTER TABLE tblhosting ENCRYPTION='Y';
ALTER TABLE tbldomains ENCRYPTION='Y';
ALTER TABLE tbladmins ENCRYPTION='Y';
ALTER TABLE tblemailtemplates ENCRYPTION='Y';
ALTER TABLE tbltickets ENCRYPTION='Y';
ALTER TABLE tblticketreplies ENCRYPTION='Y';
ALTER TABLE tblactivitylog ENCRYPTION='Y';

-- Show encrypted tables
SELECT TABLE_NAME, CREATE_OPTIONS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '$WHMCS_DB' 
AND CREATE_OPTIONS LIKE '%ENCRYPTION%';
EOF

    echo "WHMCS tables encrypted successfully"
}

# Application-level encryption for sensitive fields
setup_field_encryption() {
    cat > /usr/local/bin/whmcs-field-encryption.php << 'EOF'
<?php
/**
 * WHMCS Field-Level Encryption
 * Encrypts sensitive data fields using AES-256
 */

class WHMCSFieldEncryption {
    private $key;
    private $method = 'aes-256-gcm';
    
    public function __construct($keyFile = '/etc/mysql/encryption/master.key') {
        $this->key = file_get_contents($keyFile);
        if (!$this->key) {
            throw new Exception("Cannot read encryption key");
        }
    }
    
    /**
     * Encrypt sensitive field data
     */
    public function encrypt($data) {
        $iv = random_bytes(16);
        $tag = '';
        
        $encrypted = openssl_encrypt(
            $data, 
            $this->method, 
            $this->key, 
            OPENSSL_RAW_DATA, 
            $iv, 
            $tag
        );
        
        if ($encrypted === false) {
            throw new Exception("Encryption failed");
        }
        
        // Combine IV, tag, and encrypted data
        return base64_encode($iv . $tag . $encrypted);
    }
    
    /**
     * Decrypt sensitive field data
     */
    public function decrypt($encryptedData) {
        $data = base64_decode($encryptedData);
        
        if (strlen($data) < 32) {
            throw new Exception("Invalid encrypted data");
        }
        
        $iv = substr($data, 0, 16);
        $tag = substr($data, 16, 16);
        $encrypted = substr($data, 32);
        
        $decrypted = openssl_decrypt(
            $encrypted, 
            $this->method, 
            $this->key, 
            OPENSSL_RAW_DATA, 
            $iv, 
            $tag
        );
        
        if ($decrypted === false) {
            throw new Exception("Decryption failed");
        }
        
        return $decrypted;
    }
}

/**
 * WHMCS Hook for automatic field encryption
 */
add_hook('ClientAdd', 1, function($vars) {
    $encryption = new WHMCSFieldEncryption();
    
    // Encrypt sensitive fields
    $sensitiveFields = ['password', 'securityqans', 'notes', 'cardnum'];
    
    foreach ($sensitiveFields as $field) {
        if (isset($vars[$field]) && !empty($vars[$field])) {
            // Store original and encrypted version
            $encryptedValue = $encryption->encrypt($vars[$field]);
            
            // Update database with encrypted value
            Capsule::table('tblclients')
                   ->where('id', $vars['userid'])
                   ->update([$field . '_encrypted' => $encryptedValue]);
        }
    }
});

add_hook('ClientEdit', 1, function($vars) {
    $encryption = new WHMCSFieldEncryption();
    
    // Similar encryption for client updates
    $sensitiveFields = ['password', 'securityqans', 'notes'];
    
    foreach ($sensitiveFields as $field) {
        if (isset($vars[$field]) && !empty($vars[$field])) {
            $encryptedValue = $encryption->encrypt($vars[$field]);
            
            Capsule::table('tblclients')
                   ->where('id', $vars['userid'])
                   ->update([$field . '_encrypted' => $encryptedValue]);
        }
    }
});

/**
 * Credit card encryption hook
 */
add_hook('InvoicePaymentReminder', 1, function($vars) {
    $encryption = new WHMCSFieldEncryption();
    
    // Encrypt credit card data
    $cardData = Capsule::table('tblcreditcards')
                       ->where('clientid', $vars['userid'])
                       ->first();
    
    if ($cardData && !empty($cardData->cardnum)) {
        $encryptedCard = $encryption->encrypt($cardData->cardnum);
        
        Capsule::table('tblcreditcards')
               ->where('id', $cardData->id)
               ->update(['cardnum_encrypted' => $encryptedCard]);
    }
});
EOF

    chmod 644 /usr/local/bin/whmcs-field-encryption.php
    echo "Field-level encryption configured"
}

# Setup encryption
setup_encryption_keys
systemctl restart mysql
encrypt_whmcs_tables
setup_field_encryption

echo "Database encryption implemented successfully!"

#!/usr/bin/env python3
"""
WHMCS Database Audit and Monitoring System
Real-time monitoring with threat detection and alerting
"""

import pymysql
import time
import json
import smtplib
from datetime import datetime, timedelta
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import logging
import threading
import requests
import hashlib

class WHMCSDatabaseMonitor:
    def __init__(self):
        self.config = self.load_config()
        self.setup_logging()
        self.suspicious_patterns = []
        self.baseline_metrics = {}
        
    def load_config(self):
        """Load monitoring configuration"""
        return {
            'db_host': '127.0.0.1',
            'db_user': 'whmcs_audit',
            'db_pass': 'your_audit_password',
            'db_name': 'whmcs_secure',
            'alert_email': '[email protected]',
            'slack_webhook': 'https://hooks.slack.com/services/YOUR/WEBHOOK',
            'check_interval': 30,  # seconds
            'alert_threshold': 5   # suspicious events
        }
    
    def setup_logging(self):
        """Configure audit logging"""
        logging.basicConfig(
            level=logging.INFO,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.FileHandler('/var/log/whmcs-db-audit.log'),
                logging.StreamHandler()
            ]
        )
        self.logger = logging.getLogger(__name__)
        
    def get_db_connection(self):
        """Get database connection"""
        return pymysql.connect(
            host=self.config['db_host'],
            user=self.config['db_user'],
            password=self.config['db_pass'],
            database=self.config['db_name'],
            charset='utf8mb4'
        )
    
    def monitor_failed_logins(self):
        """Monitor failed login attempts"""
        try:
            conn = self.get_db_connection()
            cursor = conn.cursor()
            
            # Check for recent failed logins
            query = """
            SELECT COUNT(*) as failed_count, 
                   MAX(datetime) as last_attempt,
                   ipaddress
            FROM tblactivitylog 
            WHERE datetime >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
            AND description LIKE '%failed login%'
            GROUP BY ipaddress
            HAVING failed_count >= 3
            """
            
            cursor.execute(query)
            results = cursor.fetchall()
            
            for row in results:
                failed_count, last_attempt, ip = row
                self.alert_suspicious_activity(
                    'Multiple Failed Logins',
                    f'IP {ip}: {failed_count} failed attempts. Last: {last_attempt}'
                )
                
        except Exception as e:
            self.logger.error(f"Failed login monitoring error: {e}")
        finally:
            conn.close()
    
    def monitor_data_access_patterns(self):
        """Monitor unusual data access patterns"""
        try:
            conn = self.get_db_connection()
            cursor = conn.cursor()
            
            # Check for bulk data access
            query = """
            SELECT COUNT(*) as query_count,
                   USER() as db_user,
                   CONNECTION_ID() as connection_id
            FROM information_schema.PROCESSLIST
            WHERE COMMAND = 'Query'
            AND TIME > 30
            """
            
            cursor.execute(query)
            results = cursor.fetchall()
            
            # Check for unusual table access
            sensitive_tables = [
                'tblclients', 'tblcreditcards', 'tblpayments',
                'tblinvoices', 'tblaccounts'
            ]
            
            for table in sensitive_tables:
                query = f"""
                SELECT COUNT(*) as access_count
                FROM information_schema.TABLES t
                JOIN information_schema.TABLE_PRIVILEGES tp 
                ON t.TABLE_NAME = tp.TABLE_NAME
                WHERE t.TABLE_NAME = '{table}'
                AND tp.GRANTEE LIKE '%whmcs%'
                """
                
                cursor.execute(query)
                count = cursor.fetchone()[0]
                
                if count > self.baseline_metrics.get(table, 0) * 2:
                    self.alert_suspicious_activity(
                        'Unusual Table Access',
                        f'High access to {table}: {count} operations'
                    )
                    
        except Exception as e:
            self.logger.error(f"Data access monitoring error: {e}")
        finally:
            conn.close()
    
    def monitor_privilege_escalation(self):
        """Monitor for privilege escalation attempts"""
        try:
            conn = self.get_db_connection()
            cursor = conn.cursor()
            
            # Check for new user creation
            query = """
            SELECT User, Host, Create_user_priv, Super_priv
            FROM mysql.user
            WHERE Create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
            """
            
            cursor.execute(query)
            new_users = cursor.fetchall()
            
            for user in new_users:
                username, host, create_priv, super_priv = user
                self.alert_suspicious_activity(
                    'New Database User Created',
                    f'User: {username}@{host}, Create: {create_priv}, Super: {super_priv}'
                )
            
            # Check for privilege changes
            query = """
            SELECT User, Host, 
                   Super_priv, Create_priv, Drop_priv, Grant_priv
            FROM mysql.user
            WHERE Super_priv = 'Y' OR Grant_priv = 'Y'
            """
            
            cursor.execute(query)
            privileged_users = cursor.fetchall()
            
            expected_admins = ['root', 'whmcs_admin']
            for user in privileged_users:
                username = user[0]
                if username not in expected_admins:
                    self.alert_suspicious_activity(
                        'Unexpected Privileged User',
                        f'User {username} has elevated privileges'
                    )
                    
        except Exception as e:
            self.logger.error(f"Privilege monitoring error: {e}")
        finally:
            conn.close()
    
    def check_data_integrity(self):
        """Check critical table integrity"""
        try:
            conn = self.get_db_connection()
            cursor = conn.cursor()
            
            critical_tables = [
                'tblclients', 'tblinvoices', 'tblpayments', 
                'tblcreditcards', 'tblaccounts'
            ]
            
            for table in critical_tables:
                # Check table structure
                cursor.execute(f"CHECKSUM TABLE {table}")
                checksum_result = cursor.fetchone()
                
                # Store/compare checksums
                current_checksum = checksum_result[1]
                stored_checksum = self.baseline_metrics.get(f"{table}_checksum")
                
                if stored_checksum and current_checksum != stored_checksum:
                    self.alert_suspicious_activity(
                        'Table Integrity Alert',
                        f'Table {table} checksum changed: {current_checksum}'
                    )
                else:
                    self.baseline_metrics[f"{table}_checksum"] = current_checksum
                    
        except Exception as e:
            self.logger.error(f"Integrity check error: {e}")
        finally:
            conn.close()
    
    def alert_suspicious_activity(self, alert_type, details):
        """Send alert for suspicious activity"""
        alert_message = f"""
        WHMCS Database Security Alert
        
        Alert Type: {alert_type}
        Details: {details}
        Timestamp: {datetime.now()}
        Server: {self.config['db_host']}
        
        Please investigate immediately.
        """
        
        # Email alert
        self.send_email_alert(alert_type, alert_message)
        
        # Slack alert
        self.send_slack_alert(alert_type, details)
        
        # Log alert
        self.logger.warning(f"SECURITY ALERT: {alert_type} - {details}")
    
    def send_email_alert(self, subject, message):
        """Send email alert"""
        try:
            msg = MIMEText(message)
            msg['Subject'] = f"WHMCS DB Alert: {subject}"
            msg['From'] = '[email protected]'
            msg['To'] = self.config['alert_email']
            
            # Configure SMTP (adjust for your setup)
            server = smtplib.SMTP('localhost')
            server.send_message(msg)
            server.quit()
            
        except Exception as e:
            self.logger.error(f"Email alert failed: {e}")
    
    def send_slack_alert(self, alert_type, details):
        """Send Slack alert"""
        try:
            payload = {
                "text": f"🚨 WHMCS Database Alert: {alert_type}",
                "color": "danger",
                "fields": [
                    {"title": "Details", "value": details},
                    {"title": "Time", "value": str(datetime.now())},
                    {"title": "Server", "value": self.config['db_host']}
                ]
            }
            
            requests.post(self.config['slack_webhook'], json=payload)
            
        except Exception as e:
            self.logger.error(f"Slack alert failed: {e}")
    
    def generate_security_report(self):
        """Generate daily security report"""
        try:
            conn = self.get_db_connection()
            cursor = conn.cursor()
            
            # Collect metrics
            report = {
                'date': datetime.now().strftime('%Y-%m-%d'),
                'total_connections': 0,
                'failed_logins': 0,
                'successful_logins': 0,
                'data_queries': 0,
                'admin_actions': 0,
                'suspicious_activities': len(self.suspicious_patterns)
            }
            
            # Get connection statistics
            cursor.execute("SHOW STATUS LIKE 'Connections'")
            report['total_connections'] = cursor.fetchone()[1]
            
            # Get query statistics
            cursor.execute("SHOW STATUS LIKE 'Questions'")
            report['data_queries'] = cursor.fetchone()[1]
            
            # Generate report
            report_text = f"""
WHMCS Database Security Daily Report
Date: {report['date']}

Connection Statistics:
- Total Connections: {report['total_connections']}
- Failed Logins: {report['failed_logins']}
- Successful Logins: {report['successful_logins']}

Query Statistics:
- Total Queries: {report['data_queries']}
- Admin Actions: {report['admin_actions']}

Security Events:
- Suspicious Activities: {report['suspicious_activities']}

System Status: {"SECURE" if report['suspicious_activities'] == 0 else "REVIEW REQUIRED"}
            """
            
            self.send_email_alert("Daily Security Report", report_text)
            
        except Exception as e:
            self.logger.error(f"Report generation error: {e}")
        finally:
            conn.close()
    
    def run_monitoring(self):
        """Main monitoring loop"""
        self.logger.info("Starting WHMCS database monitoring")
        
        while True:
            try:
                # Run all monitoring checks
                self.monitor_failed_logins()
                self.monitor_data_access_patterns()
                self.monitor_privilege_escalation()
                self.check_data_integrity()
                
                # Sleep until next check
                time.sleep(self.config['check_interval'])
                
            except KeyboardInterrupt:
                self.logger.info("Monitoring stopped by user")
                break
            except Exception as e:
                self.logger.error(f"Monitoring error: {e}")
                time.sleep(60)  # Wait a minute before retrying

if __name__ == "__main__":
    monitor = WHMCSDatabaseMonitor()
    
    # Start monitoring in background thread
    monitor_thread = threading.Thread(target=monitor.run_monitoring)
    monitor_thread.daemon = True
    monitor_thread.start()
    
    # Generate daily reports
    while True:
        try:
            # Wait 24 hours for next report
            time.sleep(86400)
            monitor.generate_security_report()
        except KeyboardInterrupt:
            break