 Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory : /home/gositeme/domains/soundstudiopro.com/private_html/ |
<?php
require_once 'config/database.php';
$pdo = getDBConnection();
echo "<h1>Creating Security Intelligence Tables</h1>";
try {
// 1. Security Events Table - Track all security-related events
$pdo->exec("
CREATE TABLE IF NOT EXISTS security_events (
id INT AUTO_INCREMENT PRIMARY KEY,
event_type ENUM('login_success', 'login_failed', 'login_failed_nonexistent', 'registration_success', 'registration_failed', 'password_reset', 'password_reset_failed', 'csrf_violation', 'user_blocked', 'user_unblocked', 'suspicious_activity', 'admin_action', 'file_access', 'api_call', 'page_visit', 'error_occurred') NOT NULL,
user_id INT NULL,
ip_address VARCHAR(45) NOT NULL,
user_agent TEXT,
request_url TEXT,
request_method VARCHAR(10),
request_data JSON,
response_code INT,
error_message TEXT,
session_id VARCHAR(255),
country VARCHAR(100),
city VARCHAR(100),
timezone VARCHAR(100),
device_type VARCHAR(50),
browser VARCHAR(100),
os VARCHAR(100),
referrer TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_event_type (event_type),
INDEX idx_ip_address (ip_address),
INDEX idx_user_id (user_id),
INDEX idx_created_at (created_at),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
echo "✅ Security Events table created<br>";
// 2. User Login History Table - Track all login attempts
$pdo->exec("
CREATE TABLE IF NOT EXISTS user_login_history (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NULL,
email VARCHAR(255),
ip_address VARCHAR(45) NOT NULL,
user_agent TEXT,
success BOOLEAN NOT NULL,
failure_reason VARCHAR(255),
session_id VARCHAR(255),
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
logout_time TIMESTAMP NULL,
session_duration INT,
country VARCHAR(100),
city VARCHAR(100),
device_info JSON,
INDEX idx_user_id (user_id),
INDEX idx_email (email),
INDEX idx_ip_address (ip_address),
INDEX idx_success (success),
INDEX idx_login_time (login_time),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
echo "✅ User Login History table created<br>";
// 3. Page Visits Table - Track all page visits
$pdo->exec("
CREATE TABLE IF NOT EXISTS page_visits (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NULL,
session_id VARCHAR(255),
ip_address VARCHAR(45) NOT NULL,
page_url VARCHAR(500) NOT NULL,
page_title VARCHAR(255),
referrer_url VARCHAR(500),
user_agent TEXT,
country VARCHAR(100),
city VARCHAR(100),
timezone VARCHAR(100),
device_type VARCHAR(50),
browser VARCHAR(100),
os VARCHAR(100),
screen_resolution VARCHAR(20),
language VARCHAR(10),
visit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
time_on_page INT,
INDEX idx_user_id (user_id),
INDEX idx_session_id (session_id),
INDEX idx_ip_address (ip_address),
INDEX idx_page_url (page_url),
INDEX idx_visit_time (visit_time),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
echo "✅ Page Visits table created<br>";
// 4. Registration Events Table - Track all registration attempts
$pdo->exec("
CREATE TABLE IF NOT EXISTS registration_events (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(255),
ip_address VARCHAR(45) NOT NULL,
user_agent TEXT,
success BOOLEAN NOT NULL,
failure_reason VARCHAR(255),
validation_errors JSON,
country VARCHAR(100),
city VARCHAR(100),
device_info JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_ip_address (ip_address),
INDEX idx_success (success),
INDEX idx_created_at (created_at)
)
");
echo "✅ Registration Events table created<br>";
// 5. API Calls Table - Track all API requests
$pdo->exec("
CREATE TABLE IF NOT EXISTS api_calls (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NULL,
endpoint VARCHAR(255) NOT NULL,
method VARCHAR(10) NOT NULL,
ip_address VARCHAR(45) NOT NULL,
user_agent TEXT,
request_data JSON,
response_code INT,
response_time FLOAT,
error_message TEXT,
api_key_used VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_endpoint (endpoint),
INDEX idx_ip_address (ip_address),
INDEX idx_response_code (response_code),
INDEX idx_created_at (created_at),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
echo "✅ API Calls table created<br>";
// 6. Suspicious Activity Table - Track suspicious behavior
$pdo->exec("
CREATE TABLE IF NOT EXISTS suspicious_activity (
id INT AUTO_INCREMENT PRIMARY KEY,
activity_type ENUM('multiple_failed_logins', 'rapid_requests', 'unusual_location', 'bot_behavior', 'sql_injection_attempt', 'xss_attempt', 'file_inclusion_attempt', 'admin_access_attempt', 'suspicious_user_agent', 'rate_limit_exceeded') NOT NULL,
ip_address VARCHAR(45) NOT NULL,
user_id INT NULL,
user_agent TEXT,
request_url TEXT,
request_data JSON,
threat_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
action_taken ENUM('none', 'blocked', 'flagged', 'notified') DEFAULT 'none',
details TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_activity_type (activity_type),
INDEX idx_ip_address (ip_address),
INDEX idx_user_id (user_id),
INDEX idx_threat_level (threat_level),
INDEX idx_created_at (created_at),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)
");
echo "✅ Suspicious Activity table created<br>";
// 7. IP Blacklist Table - Track blocked IPs
$pdo->exec("
CREATE TABLE IF NOT EXISTS ip_blacklist (
id INT AUTO_INCREMENT PRIMARY KEY,
ip_address VARCHAR(45) NOT NULL UNIQUE,
reason VARCHAR(255) NOT NULL,
blocked_by INT NULL,
threat_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
blocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NULL,
is_active BOOLEAN DEFAULT TRUE,
INDEX idx_ip_address (ip_address),
INDEX idx_is_active (is_active),
INDEX idx_expires_at (expires_at),
FOREIGN KEY (blocked_by) REFERENCES users(id) ON DELETE SET NULL
)
");
echo "✅ IP Blacklist table created<br>";
// 8. Security Settings Table - Store security configuration
$pdo->exec("
CREATE TABLE IF NOT EXISTS security_settings (
id INT AUTO_INCREMENT PRIMARY KEY,
setting_key VARCHAR(100) NOT NULL UNIQUE,
setting_value TEXT,
description TEXT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
");
echo "✅ Security Settings table created<br>";
// Insert default security settings
$defaultSettings = [
['max_login_attempts', '5', 'Maximum failed login attempts before blocking'],
['block_duration_minutes', '30', 'Duration to block IP after max attempts'],
['session_timeout_minutes', '120', 'Session timeout in minutes'],
['enable_captcha', 'true', 'Enable CAPTCHA for failed attempts'],
['log_all_visits', 'true', 'Log all page visits'],
['enable_geo_tracking', 'true', 'Enable geographic location tracking'],
['suspicious_activity_threshold', '10', 'Number of events to trigger suspicious activity alert'],
['admin_notification_email', '', 'Email for security notifications']
];
$stmt = $pdo->prepare("INSERT IGNORE INTO security_settings (setting_key, setting_value, description) VALUES (?, ?, ?)");
foreach ($defaultSettings as $setting) {
$stmt->execute($setting);
}
echo "✅ Default security settings inserted<br>";
echo "<h2>🎉 All Security Tables Created Successfully!</h2>";
echo "<p>Your security intelligence system is now ready to track:</p>";
echo "<ul>";
echo "<li>✅ All login attempts (successful and failed)</li>";
echo "<li>✅ All registration attempts</li>";
echo "<li>✅ All page visits and user behavior</li>";
echo "<li>✅ All API calls and responses</li>";
echo "<li>✅ Suspicious activity and threats</li>";
echo "<li>✅ IP blocking and blacklisting</li>";
echo "<li>✅ Geographic location tracking</li>";
echo "<li>✅ Device and browser information</li>";
echo "</ul>";
} catch (Exception $e) {
echo "<p style='color: red;'>❌ Error creating tables: " . $e->getMessage() . "</p>";
}
?>