|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* Copyright (C) 2016 SURFnet. |
|
4
|
|
|
* |
|
5
|
|
|
* This program is free software: you can redistribute it and/or modify |
|
6
|
|
|
* it under the terms of the GNU Affero General Public License as |
|
7
|
|
|
* published by the Free Software Foundation, either version 3 of the |
|
8
|
|
|
* License, or (at your option) any later version. |
|
9
|
|
|
* |
|
10
|
|
|
* This program is distributed in the hope that it will be useful, |
|
11
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
|
12
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
|
13
|
|
|
* GNU Affero General Public License for more details. |
|
14
|
|
|
* |
|
15
|
|
|
* You should have received a copy of the GNU Affero General Public License |
|
16
|
|
|
* along with this program. If not, see <http://www.gnu.org/licenses/>. |
|
17
|
|
|
*/ |
|
18
|
|
|
|
|
19
|
|
|
namespace SURFnet\VPN\Server\Api; |
|
20
|
|
|
|
|
21
|
|
|
use PDO; |
|
22
|
|
|
use PDOException; |
|
23
|
|
|
|
|
24
|
|
|
/** |
|
25
|
|
|
* Keep track of VPN connections. |
|
26
|
|
|
*/ |
|
27
|
|
|
class ConnectionLog |
|
28
|
|
|
{ |
|
29
|
|
|
/** @var PDO */ |
|
30
|
|
|
private $db; |
|
31
|
|
|
|
|
32
|
|
|
public function __construct(PDO $db) |
|
33
|
|
|
{ |
|
34
|
|
|
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
|
35
|
|
|
$this->db = $db; |
|
36
|
|
|
} |
|
37
|
|
|
|
|
38
|
|
|
public function connect($profileId, $commonName, $ip4, $ip6, $connectedAt) |
|
39
|
|
|
{ |
|
40
|
|
|
$stmt = $this->db->prepare( |
|
41
|
|
|
'INSERT INTO connection_log ( |
|
42
|
|
|
profile_id, |
|
43
|
|
|
common_name, |
|
44
|
|
|
ip4, |
|
45
|
|
|
ip6, |
|
46
|
|
|
connected_at |
|
47
|
|
|
) |
|
48
|
|
|
VALUES( |
|
49
|
|
|
:profile_id, |
|
50
|
|
|
:common_name, |
|
51
|
|
|
:ip4, |
|
52
|
|
|
:ip6, |
|
53
|
|
|
:connected_at |
|
54
|
|
|
)' |
|
55
|
|
|
); |
|
56
|
|
|
|
|
57
|
|
|
$stmt->bindValue(':profile_id', $profileId, PDO::PARAM_STR); |
|
58
|
|
|
$stmt->bindValue(':common_name', $commonName, PDO::PARAM_STR); |
|
59
|
|
|
$stmt->bindValue(':ip4', $ip4, PDO::PARAM_STR); |
|
60
|
|
|
$stmt->bindValue(':ip6', $ip6, PDO::PARAM_STR); |
|
61
|
|
|
$stmt->bindValue(':connected_at', $connectedAt, PDO::PARAM_INT); |
|
62
|
|
|
|
|
63
|
|
|
try { |
|
64
|
|
|
$stmt->execute(); |
|
65
|
|
|
} catch (PDOException $e) { |
|
66
|
|
|
return false; |
|
67
|
|
|
} |
|
68
|
|
|
|
|
69
|
|
|
return true; |
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
|
|
public function disconnect($profileId, $commonName, $ip4, $ip6, $connectedAt, $disconnectedAt, $bytesTransferred) |
|
73
|
|
|
{ |
|
74
|
|
|
$stmt = $this->db->prepare( |
|
75
|
|
|
'UPDATE connection_log |
|
76
|
|
|
SET |
|
77
|
|
|
disconnected_at = :disconnected_at, |
|
78
|
|
|
bytes_transferred = :bytes_transferred |
|
79
|
|
|
WHERE |
|
80
|
|
|
profile_id = :profile_id AND |
|
81
|
|
|
common_name = :common_name AND |
|
82
|
|
|
ip4 = :ip4 AND |
|
83
|
|
|
ip6 = :ip6 AND |
|
84
|
|
|
connected_at = :connected_at |
|
85
|
|
|
' |
|
86
|
|
|
); |
|
87
|
|
|
|
|
88
|
|
|
$stmt->bindValue(':profile_id', $profileId, PDO::PARAM_STR); |
|
89
|
|
|
$stmt->bindValue(':common_name', $commonName, PDO::PARAM_STR); |
|
90
|
|
|
$stmt->bindValue(':ip4', $ip4, PDO::PARAM_STR); |
|
91
|
|
|
$stmt->bindValue(':ip6', $ip6, PDO::PARAM_STR); |
|
92
|
|
|
$stmt->bindValue(':connected_at', $connectedAt, PDO::PARAM_INT); |
|
93
|
|
|
$stmt->bindValue(':disconnected_at', $disconnectedAt, PDO::PARAM_INT); |
|
94
|
|
|
$stmt->bindValue(':bytes_transferred', $bytesTransferred, PDO::PARAM_INT); |
|
95
|
|
|
|
|
96
|
|
|
// XXX number of affected rows should be one! |
|
97
|
|
|
try { |
|
98
|
|
|
$stmt->execute(); |
|
99
|
|
|
} catch (PDOException $e) { |
|
100
|
|
|
return false; |
|
101
|
|
|
} |
|
102
|
|
|
|
|
103
|
|
|
return true; |
|
104
|
|
|
} |
|
105
|
|
|
|
|
106
|
|
View Code Duplication |
public function housekeeping($timeUnix) |
|
|
|
|
|
|
107
|
|
|
{ |
|
108
|
|
|
$stmt = $this->db->prepare( |
|
109
|
|
|
sprintf( |
|
110
|
|
|
'DELETE FROM connection_log |
|
111
|
|
|
WHERE connected_at < :time_unix' |
|
112
|
|
|
) |
|
113
|
|
|
); |
|
114
|
|
|
|
|
115
|
|
|
$stmt->bindValue(':time_unix', $timeUnix, PDO::PARAM_INT); |
|
116
|
|
|
$stmt->execute(); |
|
117
|
|
|
} |
|
118
|
|
|
|
|
119
|
|
|
public function getAll() |
|
120
|
|
|
{ |
|
121
|
|
|
$stmt = $this->db->prepare( |
|
122
|
|
|
'SELECT common_name, connected_at, disconnected_at, bytes_transferred |
|
123
|
|
|
FROM connection_log' |
|
124
|
|
|
); |
|
125
|
|
|
$stmt->execute(); |
|
126
|
|
|
|
|
127
|
|
|
return $stmt->fetchAll(PDO::FETCH_ASSOC); |
|
128
|
|
|
} |
|
129
|
|
|
|
|
130
|
|
|
public function get($dateTimeUnix, $ipAddress) |
|
131
|
|
|
{ |
|
132
|
|
|
$stmt = $this->db->prepare( |
|
133
|
|
|
'SELECT profile_id, common_name, ip4, ip6, connected_at, disconnected_at |
|
134
|
|
|
FROM connection_log |
|
135
|
|
|
WHERE |
|
136
|
|
|
(ip4 = :ip_address OR ip6 = :ip_address) |
|
137
|
|
|
AND connected_at < :date_time_unix |
|
138
|
|
|
AND (disconnected_at > :date_time_unix OR disconnected_at IS NULL)' |
|
139
|
|
|
); |
|
140
|
|
|
$stmt->bindValue(':ip_address', $ipAddress, PDO::PARAM_STR); |
|
141
|
|
|
$stmt->bindValue(':date_time_unix', $dateTimeUnix, PDO::PARAM_STR); |
|
142
|
|
|
$stmt->execute(); |
|
143
|
|
|
|
|
144
|
|
|
return $stmt->fetchAll(PDO::FETCH_ASSOC); |
|
145
|
|
|
} |
|
146
|
|
|
|
|
147
|
|
|
public function init() |
|
148
|
|
|
{ |
|
149
|
|
|
$queryList = [ |
|
150
|
|
|
'CREATE TABLE IF NOT EXISTS connection_log ( |
|
151
|
|
|
profile_id VARCHAR(255) NOT NULL, |
|
152
|
|
|
common_name VARCHAR(255) NOT NULL, |
|
153
|
|
|
ip4 VARCHAR(255) NOT NULL, |
|
154
|
|
|
ip6 VARCHAR(255) NOT NULL, |
|
155
|
|
|
connected_at INTEGER NOT NULL, |
|
156
|
|
|
disconnected_at INTEGER DEFAULT NULL, |
|
157
|
|
|
bytes_transferred INTEGER DEFAULT NULL |
|
158
|
|
|
)', |
|
159
|
|
|
]; |
|
160
|
|
|
|
|
161
|
|
|
foreach ($queryList as $query) { |
|
162
|
|
|
$this->db->query($query); |
|
163
|
|
|
} |
|
164
|
|
|
} |
|
165
|
|
|
} |
|
166
|
|
|
|
eduVPN /
vpn-server-api
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.