3 * Automatic database updates
5 * @author Roland Haeder <webmaster@shipsimu.org>
7 * @copyright Copyright (c) 2009 - 2017 Cracker Tracker Team
8 * @license GNU GPL 3.0 or any newer version
9 * @link http://www.shipsimu.org
11 * This program is free software: you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation, either version 3 of the License, or
14 * (at your option) any later version.
16 * This program is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
19 * GNU General Public License for more details.
21 * You should have received a copy of the GNU General Public License
22 * along with this program. If not, see <http://www.gnu.org/licenses/>.
26 function crackerTrackerInitUpdates () {
28 $GLOBALS['ctracker_updates'] = [
31 'CREATE TABLE IF NOT EXISTS `ctracker_ticket` (
32 `ctracker_ticket` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
33 `ctracker_ticket_remote_addr` varchar(15) NOT NULL,
34 `ctracker_ticket_user_agent` tinytext NOT NULL,
35 `ctracker_ticket_name` varchar(255) NOT NULL,
36 `ctracker_ticket_email` varchar(255) NOT NULL,
37 `ctracker_ticket_comment` tinytext NOT NULL,
38 PRIMARY KEY ( `ctracker_ticket` ),
39 UNIQUE KEY (`ctracker_ticket_remote_addr`)
41 'CREATE TABLE IF NOT EXISTS `ctracker_ticket_data` (
42 `ctracker_ticket_data_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
43 `ctracker_ticket_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
44 `ctracker_data_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
45 PRIMARY KEY ( `ctracker_ticket_data_id` ),
46 UNIQUE KEY `ctracker_ticket_data` ( `ctracker_ticket_id`, `ctracker_data_id` ),
47 INDEX ( `ctracker_data_id` ),
48 FOREIGN KEY ( `ctracker_ticket_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . '`.`ctracker_ticket` (
51 FOREIGN KEY ( `ctracker_data_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . '`.`ctracker_data` (
57 // NULL for missing values
59 "ALTER TABLE `ctracker_data` CHANGE `script_name` `script_name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Full script name'",
60 "UPDATE `ctracker_data` SET `script_name`=NULL WHERE `script_name`=''",
61 "ALTER TABLE `ctracker_data` CHANGE `server_name` `server_name` TINYTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Server''s host name'",
62 "UPDATE `ctracker_data` SET `server_name`=NULL WHERE `server_name`=''",
65 // Also store proxy address
67 "ALTER TABLE `ctracker_data` ADD `proxy_addr` VARCHAR(15) NULL DEFAULT NULL AFTER `remote_addr`",
68 "ALTER TABLE `ctracker_ticket` ADD `ctracker_ticket_proxy_addr` VARCHAR(15) NULL DEFAULT NULL",
71 // Better key for always repeating query
73 "ALTER TABLE `ctracker_data` ADD INDEX `remote_proxy_last` ( `remote_addr`, `proxy_addr`, `last_attempt` DESC )",
76 // HTTP request method
78 "ALTER TABLE `ctracker_data` ADD `request_method` VARCHAR( 255 ) NULL DEFAULT NULL COMMENT 'HTTP request method' AFTER `referer`, ADD INDEX ( `request_method` )",
83 "ALTER TABLE `ctracker_data` ADD INDEX `count` (`count`)",
86 // Log also UA after check
88 "ALTER TABLE `ctracker_data` ADD `check_ua` tinytext COLLATE 'utf8_general_ci' NULL COMMENT 'User agent after check' AFTER `post_data`",
89 "ALTER TABLE `ctracker_data` CHANGE `check_worm` `check_get` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'GET data after check' AFTER `check_ua`",
90 "ALTER TABLE `ctracker_data` CHANGE `check_post` `check_post` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'POST data after check' AFTER `check_get`",
93 // View for request methods
95 "CREATE VIEW `view_most_request_methods` AS
96 SELECT SQL_CALC_FOUND_ROWS COUNT(`id`) AS `total_rows`, `request_method`
98 GROUP BY `request_method`"
101 // Anti-spambot field
103 "ALTER TABLE `ctracker_data` CHANGE `proxy_addr` `proxy_addr` varchar(15) COLLATE 'latin1_swedish_ci' NULL COMMENT 'Detected proxy address' AFTER `remote_addr`",
104 "ALTER TABLE `ctracker_data` ADD `spam_bot_dectections` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'Spam-bot attempts'",
109 "ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_remote_addr` `ctracker_ticket_remote_addr` varchar(15) COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket`",
110 "ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_proxy_addr` `ctracker_ticket_proxy_addr` varchar(15) COLLATE 'utf8_general_ci' NULL AFTER `ctracker_ticket_remote_addr`",
111 "ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_user_agent` `ctracker_ticket_user_agent` tinytext COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_proxy_addr`",
112 "ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_name` `ctracker_ticket_name` varchar(255) COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_user_agent`",
113 "ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_email` `ctracker_ticket_email` varchar(255) COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_name`",
114 "ALTER TABLE `ctracker_ticket` CHANGE `ctracker_ticket_comment` `ctracker_ticket_comment` tinytext COLLATE 'utf8_general_ci' NOT NULL AFTER `ctracker_ticket_email`",
115 "ALTER TABLE `ctracker_ticket` COLLATE 'utf8_general_ci'",
120 "ALTER TABLE `ctracker_data` CHANGE `remote_addr` `remote_addr` varchar(15) COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Remote address' AFTER `id`",
121 "ALTER TABLE `ctracker_data` CHANGE `user_agent` `user_agent` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'User agent string' AFTER `remote_addr`",
122 "ALTER TABLE `ctracker_data` CHANGE `get_data` `get_data` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Serialized GET data' AFTER `user_agent`",
123 "ALTER TABLE `ctracker_data` CHANGE `post_data` `post_data` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Serialized POST data' AFTER `get_data`",
124 "ALTER TABLE `ctracker_data` CHANGE `check_get` `check_get` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Manipulated GET data' AFTER `check_ua`",
125 "ALTER TABLE `ctracker_data` CHANGE `check_post` `check_post` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Manipulated GET data' AFTER `check_get`",
126 "ALTER TABLE `ctracker_data` CHANGE `referer` `referer` varchar(255) COLLATE 'utf8_general_ci' NOT NULL COMMENT 'Referer' AFTER `script_name`",
127 "ALTER TABLE `ctracker_data` CHANGE `request_method` `request_method` varchar(255) COLLATE 'utf8_general_ci' NULL COMMENT 'HTTP request method' AFTER `referer`",
128 "ALTER TABLE `ctracker_data` CHANGE `proxy_used` `proxy_used` enum('Y','N') COLLATE 'utf8_general_ci' NOT NULL DEFAULT 'N' COMMENT 'Proxy used?' AFTER `request_method`",
129 "ALTER TABLE `ctracker_data` CHANGE `last_attempt` `last_attempt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last attempt' AFTER `first_attempt`",
130 "ALTER TABLE `ctracker_data` CHANGE `proxy_addr` `proxy_addr` varchar(15) COLLATE 'utf8_general_ci' NULL COMMENT 'Detected proxy address' AFTER `count`",
131 "ALTER TABLE `ctracker_data` COLLATE 'utf8_general_ci'",
136 "ALTER TABLE `ctracker_data` CHANGE `spam_bot_dectections` `spam_bot_detections` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'Spam-bot attempts'",
141 // Runs the given updates at number X
142 function runCrackerTrackerUpdates ($update) {
143 // We assume it is set
144 foreach ($GLOBALS['ctracker_updates'][$update] as $sql) {
145 // Run the SQL command
146 runCrackerTrackerSql($sql, __FUNCTION__, __LINE__);