1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
<?php
final class ITSEC_Schema {
const TABLES = [
'itsec_logs',
'itsec_lockouts',
'itsec_temp',
'itsec_distributed_storage',
'itsec_geolocation_cache',
'itsec_fingerprints',
'itsec_opaque_tokens',
'itsec_user_groups',
'itsec_mutexes',
'itsec_bans',
];
/**
* Creates appropriate database tables.
*
* Uses dbdelta to create database tables either on activation or in the event that one is missing.
*
* @since 3.9.0
*
* @return true|WP_Error
*/
public static function create_database_tables() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$tables = "
CREATE TABLE {$wpdb->base_prefix}itsec_logs (
id bigint(20) unsigned NOT NULL auto_increment,
parent_id bigint(20) unsigned NOT NULL default '0',
module varchar(50) NOT NULL default '',
code varchar(100) NOT NULL default '',
data longtext NOT NULL,
type varchar(20) NOT NULL default 'notice',
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
init_timestamp datetime NOT NULL default '0000-00-00 00:00:00',
memory_current bigint(20) unsigned NOT NULL default '0',
memory_peak bigint(20) unsigned NOT NULL default '0',
url varchar(500) NOT NULL default '',
blog_id bigint(20) NOT NULL default '0',
user_id bigint(20) unsigned NOT NULL default '0',
remote_ip varchar(50) NOT NULL default '',
PRIMARY KEY (id),
KEY module (module),
KEY code (code),
KEY type (type),
KEY timestamp (timestamp),
KEY init_timestamp (init_timestamp),
KEY user_id (user_id),
KEY blog_id (blog_id)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_lockouts (
lockout_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
lockout_type varchar(25) NOT NULL,
lockout_start datetime NOT NULL,
lockout_start_gmt datetime NOT NULL,
lockout_expire datetime NOT NULL,
lockout_expire_gmt datetime NOT NULL,
lockout_host varchar(40),
lockout_user bigint(20) UNSIGNED,
lockout_username varchar(60),
lockout_active int(1) NOT NULL DEFAULT 1,
lockout_context TEXT,
PRIMARY KEY (lockout_id),
KEY lockout_expire_gmt (lockout_expire_gmt),
KEY lockout_host (lockout_host),
KEY lockout_user (lockout_user),
KEY lockout_username (lockout_username),
KEY lockout_active (lockout_active)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_temp (
temp_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
temp_type varchar(25) NOT NULL,
temp_date datetime NOT NULL,
temp_date_gmt datetime NOT NULL,
temp_host varchar(40),
temp_user bigint(20) UNSIGNED,
temp_username varchar(60),
PRIMARY KEY (temp_id),
KEY temp_date_gmt (temp_date_gmt),
KEY temp_host (temp_host),
KEY temp_user (temp_user),
KEY temp_username (temp_username)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_distributed_storage (
storage_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
storage_group varchar(40) NOT NULL,
storage_key varchar(40) NOT NULL default '',
storage_chunk int NOT NULL default 0,
storage_data longtext NOT NULL,
storage_updated datetime NOT NULL,
PRIMARY KEY (storage_id),
UNIQUE KEY storage_group__key__chunk (storage_group,storage_key,storage_chunk)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_geolocation_cache (
location_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
location_host varchar(40) NOT NULL,
location_lat decimal(10,8) NOT NULL,
location_long decimal(11,8) NOT NULL,
location_label varchar(255) NOT NULL,
location_credit varchar (255) NOT NULL,
location_time datetime NOT NULL,
PRIMARY KEY (location_id),
UNIQUE KEY location_host (location_host),
KEY location_time (location_time)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_fingerprints (
fingerprint_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
fingerprint_user bigint(20) UNSIGNED NOT NULL,
fingerprint_hash char(32) NOT NULL,
fingerprint_created_at datetime NOT NULL,
fingerprint_approved_at datetime NOT NULL,
fingerprint_data longtext NOT NULL,
fingerprint_snapshot longtext NOT NULL,
fingerprint_last_seen datetime NOT NULL,
fingerprint_uses int NOT NULL default 0,
fingerprint_status varchar(20) NOT NULL,
fingerprint_uuid char(36) NOT NULL,
PRIMARY KEY (fingerprint_id),
UNIQUE KEY fingerprint_user__hash (fingerprint_user,fingerprint_hash),
UNIQUE KEY fingerprint_uuid (fingerprint_uuid)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_opaque_tokens (
token_id char(64) NOT NULL,
token_hashed char(64) NOT NULL,
token_type VARCHAR(32) NOT NULL,
token_data TEXT NOT NULL,
token_created_at DATETIME NOT NULL,
PRIMARY KEY (token_id),
UNIQUE KEY token_hashed (token_hashed)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_user_groups (
group_id char(36) NOT NULL,
group_label varchar(255) NOT NULL default '',
group_roles TEXT,
group_canonical TEXT,
group_users TEXT,
group_min_role varchar(255),
group_created_at DATETIME,
PRIMARY KEY (group_id)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_mutexes (
mutex_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
mutex_name varchar(100) NOT NULL,
mutex_expires int(11) UNSIGNED NOT NULL,
PRIMARY KEY (mutex_id),
UNIQUE KEY mutex_name (mutex_name)
) $charset_collate;
CREATE TABLE {$wpdb->base_prefix}itsec_bans (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
host varchar(64) NOT NULL,
type varchar(20) NOT NULL default 'ip',
created_at datetime NOT NULL,
actor_type varchar(20),
actor_id varchar(128),
comment varchar(255) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY host (host),
KEY actor (actor_type,actor_id)
) $charset_collate;
";
$wp_error = new WP_Error();
ITSEC_Lib::add_to_wp_error( $wp_error, ITSEC_Lib::db_delta_with_error_handling( $tables ) );
foreach ( self::TABLES as $table ) {
if ( ! count( $wpdb->get_results( "SHOW TABLES LIKE '{$wpdb->base_prefix}{$table}'" ) ) ) {
$wp_error->add(
'missing_table',
sprintf( __( 'The %s table is not installed.', 'better-wp-security' ), $table )
);
}
}
/**
* Fires when the DB schema is installed or updated.
*
* @param WP_Error $wp_error
*/
do_action( 'itsec_install_schema', $wp_error );
if ( $wp_error->has_errors() ) {
return $wp_error;
}
return true;
}
public static function remove_database_tables() {
global $wpdb;
foreach ( self::TABLES as $table ) {
$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->base_prefix}{$table};" );
}
$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->base_prefix}itsec_log;" );
}
}
|