install/install.sql

   1 SET FOREIGN_KEY_CHECKS=0;
   2 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
   3 SET time_zone = "+00:00";
   4
   5 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   6 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   7 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   8 /*!40101 SET NAMES utf8 */;
   9
  10
  11 DROP TABLE IF EXISTS `ctracker_config`;
  12 CREATE TABLE IF NOT EXISTS `ctracker_config` (
  13   `ctracker_config` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  14   `ctracker_db_version` bigint(20) unsigned NOT NULL DEFAULT '0',
  15   `ctracker_min_sleep` smallint(5) unsigned NOT NULL DEFAULT '10',
  16   `ctracker_max_sleep` smallint(5) unsigned NOT NULL DEFAULT '30',
  17   `ctracker_alert_user` enum('Y','N') NOT NULL DEFAULT 'Y',
  18   `ctracker_language` char(2) NOT NULL DEFAULT 'en',
  19   PRIMARY KEY (`ctracker_config`)
  20 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  21
  22 INSERT INTO `ctracker_config` (`ctracker_config`, `ctracker_db_version`, `ctracker_min_sleep`, `ctracker_max_sleep`, `ctracker_alert_user`, `ctracker_language`) VALUES
  23 (1, 8, 10, 30, 'Y', 'en');
  24
  25 DROP TABLE IF EXISTS `ctracker_data`;
  26 CREATE TABLE IF NOT EXISTS `ctracker_data` (
  27   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Running id number',
  28   `remote_addr` varchar(15) NOT NULL COMMENT 'Remote address',
  29   `proxy_addr` varchar(15) DEFAULT NULL COMMENT 'Detected IP behind proxy',
  30   `user_agent` tinytext NOT NULL COMMENT 'User agent string',
  31   `get_data` tinytext NOT NULL COMMENT 'Serialized GET data',
  32   `post_data` tinytext NOT NULL COMMENT 'Serialized POST data',
  33   `check_ua` tinytext NOT NULL COMMENT 'User-agent string after check',
  34   `check_get` tinytext NOT NULL COMMENT 'GET data after check',
  35   `check_post` tinytext NOT NULL COMMENT 'POST data after check',
  36   `server_name` tinytext COMMENT 'Server''s host name',
  37   `script_name` varchar(255) DEFAULT NULL COMMENT 'Full script name',
  38   `referer` varchar(255) NOT NULL COMMENT 'Referer',
  39   `request_method` varchar(255) DEFAULT NULL COMMENT 'HTTP request method',
  40   `proxy_used` enum('Y','N') NOT NULL DEFAULT 'N' COMMENT 'Proxy used?',
  41   `first_attempt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'First attempt',
  42   `last_attempt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last attempt',
  43   `count` bigint(20) unsigned NOT NULL DEFAULT '1' COMMENT 'Counter',
  44   PRIMARY KEY (`id`),
  45   KEY `remote_addr` (`remote_addr`),
  46   KEY `remote_proxy_last` (`remote_addr`,`proxy_addr`),
  47   KEY `request_method` (`request_method`),
  48   KEY `count` (`count`)
  49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Gathered data of attack';
  50
  51 DROP TABLE IF EXISTS `ctracker_ticket`;
  52 CREATE TABLE IF NOT EXISTS `ctracker_ticket` (
  53   `ctracker_ticket` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  54   `ctracker_ticket_remote_addr` varchar(15) NOT NULL,
  55   `ctracker_ticket_user_agent` tinytext NOT NULL,
  56   `ctracker_ticket_name` varchar(255) NOT NULL,
  57   `ctracker_ticket_email` varchar(255) NOT NULL,
  58   `ctracker_ticket_comment` tinytext NOT NULL,
  59   `ctracker_ticket_proxy_addr` varchar(15) DEFAULT NULL,
  60   PRIMARY KEY (`ctracker_ticket`),
  61   UNIQUE KEY `ctracker_ticket_remote_addr` (`ctracker_ticket_remote_addr`)
  62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  63
  64 DROP TABLE IF EXISTS `ctracker_ticket_data`;
  65 CREATE TABLE IF NOT EXISTS `ctracker_ticket_data` (
  66   `ctracker_ticket_data_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  67   `ctracker_ticket_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  68   `ctracker_data_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  69   PRIMARY KEY (`ctracker_ticket_data_id`),
  70   UNIQUE KEY `ctracker_ticket_data` (`ctracker_ticket_id`,`ctracker_data_id`),
  71   KEY `ctracker_data_id` (`ctracker_data_id`)
  72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  73 DROP VIEW IF EXISTS `view_attack_distribution`;
  74 CREATE TABLE IF NOT EXISTS `view_attack_distribution` (
  75 `Rows` bigint(21)
  76 ,`remote_addr` varchar(15)
  77 );DROP VIEW IF EXISTS `view_most_attacked_domains`;
  78 CREATE TABLE IF NOT EXISTS `view_most_attacked_domains` (
  79 `Rows` bigint(21)
  80 ,`server_name` tinytext
  81 );DROP VIEW IF EXISTS `view_most_attacked_scripts`;
  82 CREATE TABLE IF NOT EXISTS `view_most_attacked_scripts` (
  83 `Rows` bigint(21)
  84 ,`script_name` varchar(255)
  85 );DROP VIEW IF EXISTS `view_most_attacking_agents`;
  86 CREATE TABLE IF NOT EXISTS `view_most_attacking_agents` (
  87 `Rows` bigint(21)
  88 ,`user_agent` tinytext
  89 );DROP VIEW IF EXISTS `view_proxy_addresses`;
  90 CREATE TABLE IF NOT EXISTS `view_proxy_addresses` (
  91 `remote_addr` varchar(15)
  92 ,`proxy_addr` varchar(15)
  93 );DROP VIEW IF EXISTS `view_proxy_attackers`;
  94 CREATE TABLE IF NOT EXISTS `view_proxy_attackers` (
  95 `Rows` bigint(21)
  96 ,`has_used_proxy` enum('Y','N')
  97 );DROP VIEW IF EXISTS `view_proxy_ip_usage`;
  98 CREATE TABLE IF NOT EXISTS `view_proxy_ip_usage` (
  99 `total_proxy_ips` decimal(42,0)
 100 ,`distinct_proxy_ips` bigint(21)
 101 );DROP VIEW IF EXISTS `view_spreaded_attacks`;
 102 CREATE TABLE IF NOT EXISTS `view_spreaded_attacks` (
 103 `time_diff` bigint(11)
 104 ,`id` bigint(20) unsigned
 105 ,`remote_addr` varchar(15)
 106 ,`user_agent` tinytext
 107 ,`server_name` tinytext
 108 ,`get_data` tinytext
 109 ,`check_get` tinytext
 110 ,`proxy_used` enum('Y','N')
 111 ,`first_attempt` timestamp
 112 ,`last_attempt` timestamp
 113 ,`count` bigint(20) unsigned
 114 );DROP VIEW IF EXISTS `view_total_attacks`;
 115 CREATE TABLE IF NOT EXISTS `view_total_attacks` (
 116 `total_attacks` decimal(42,0)
 117 ,`distinct_attacks` bigint(21)
 118 ,`max_attack_repeat` bigint(20) unsigned
 119 );DROP TABLE IF EXISTS `view_attack_distribution`;
 120
 121 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_attack_distribution` AS select count(0) AS `Rows`,`ctracker_data`.`remote_addr` AS `remote_addr` from `ctracker_data` group by `ctracker_data`.`remote_addr` order by count(0) desc;
 122 DROP TABLE IF EXISTS `view_most_attacked_domains`;
 123
 124 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_most_attacked_domains` AS select count(0) AS `Rows`,`ctracker_data`.`server_name` AS `server_name` from `ctracker_data` group by `ctracker_data`.`server_name` order by count(0) desc;
 125 DROP TABLE IF EXISTS `view_most_attacked_scripts`;
 126
 127 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_most_attacked_scripts` AS select count(0) AS `Rows`,`ctracker_data`.`script_name` AS `script_name` from `ctracker_data` group by `ctracker_data`.`script_name` order by count(0) desc;
 128 DROP TABLE IF EXISTS `view_most_attacking_agents`;
 129
 130 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_most_attacking_agents` AS select count(0) AS `Rows`,`ctracker_data`.`user_agent` AS `user_agent` from `ctracker_data` group by `ctracker_data`.`user_agent` order by count(0) desc;
 131 DROP TABLE IF EXISTS `view_proxy_addresses`;
 132
 133 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_proxy_addresses` AS select distinct `ctracker_data`.`remote_addr` AS `remote_addr`,`ctracker_data`.`proxy_addr` AS `proxy_addr` from `ctracker_data` where (`ctracker_data`.`remote_addr` <> `ctracker_data`.`proxy_addr`) order by `ctracker_data`.`remote_addr` limit 1;
 134 DROP TABLE IF EXISTS `view_proxy_attackers`;
 135
 136 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_proxy_attackers` AS select count(0) AS `Rows`,`ctracker_data`.`proxy_used` AS `has_used_proxy` from `ctracker_data` group by `ctracker_data`.`proxy_used` order by `ctracker_data`.`proxy_used`;
 137 DROP TABLE IF EXISTS `view_proxy_ip_usage`;
 138
 139 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_proxy_ip_usage` AS select sum(`ctracker_data`.`count`) AS `total_proxy_ips`,count(`ctracker_data`.`id`) AS `distinct_proxy_ips` from `ctracker_data` where (`ctracker_data`.`remote_addr` <> `ctracker_data`.`proxy_addr`);
 140 DROP TABLE IF EXISTS `view_spreaded_attacks`;
 141
 142 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_spreaded_attacks` AS select (unix_timestamp(`ctracker_data`.`last_attempt`) - unix_timestamp(`ctracker_data`.`first_attempt`)) AS `time_diff`,`ctracker_data`.`id` AS `id`,`ctracker_data`.`remote_addr` AS `remote_addr`,`ctracker_data`.`user_agent` AS `user_agent`,`ctracker_data`.`server_name` AS `server_name`,`ctracker_data`.`get_data` AS `get_data`,`ctracker_data`.`check_get` AS `check_get`,`ctracker_data`.`proxy_used` AS `proxy_used`,`ctracker_data`.`first_attempt` AS `first_attempt`,`ctracker_data`.`last_attempt` AS `last_attempt`,`ctracker_data`.`count` AS `count` from `ctracker_data` group by `ctracker_data`.`remote_addr` order by (unix_timestamp(`ctracker_data`.`last_attempt`) - unix_timestamp(`ctracker_data`.`first_attempt`)) desc;
 143 DROP TABLE IF EXISTS `view_total_attacks`;
 144
 145 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_attacks` AS select sum(`ctracker_data`.`count`) AS `total_attacks`,count(`ctracker_data`.`id`) AS `distinct_attacks`,max(`ctracker_data`.`count`) AS `max_attack_repeat` from `ctracker_data` limit 1;
 146
 147
 148 ALTER TABLE `ctracker_ticket_data`
 149   ADD CONSTRAINT `ctracker_ticket_data_ibfk_1` FOREIGN KEY (`ctracker_ticket_id`) REFERENCES `ctracker_ticket` (`ctracker_ticket`) ON DELETE CASCADE,
 150   ADD CONSTRAINT `ctracker_ticket_data_ibfk_2` FOREIGN KEY (`ctracker_data_id`) REFERENCES `ctracker_data` (`id`) ON DELETE CASCADE;
 151 SET FOREIGN_KEY_CHECKS=1;
 152
 153 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 154 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 155 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;