]>
Commit | Line | Data |
---|---|---|
ea27475b VP |
1 | <?php |
2 | ||
3 | require_once "SQL/sql.php"; | |
ce9cf366 | 4 | require_once "SQL/settings.php"; |
4d634d0a | 5 | |
ea27475b VP |
6 | class sql_auth |
7 | { | |
b44a2e97 | 8 | public $name = "SQLAuth"; |
ea27475b VP |
9 | public $author = "Valware"; |
10 | public $version = "1.0"; | |
11 | public $description = "Provides a User Auth and Management Panel with an SQL backend"; | |
b65f0496 | 12 | public $email = "v.a.pond@outlook.com"; |
ea27475b VP |
13 | |
14 | function __construct() | |
15 | { | |
6930484c VP |
16 | Hook::func(HOOKTYPE_USER_LOOKUP, 'sql_auth::get_user'); |
17 | Hook::func(HOOKTYPE_USERMETA_ADD, 'sql_auth::add_usermeta'); | |
18 | Hook::func(HOOKTYPE_USERMETA_DEL, 'sql_auth::del_usermeta'); | |
19 | Hook::func(HOOKTYPE_USERMETA_GET, 'sql_auth::get_usermeta'); | |
180b8ec1 VP |
20 | Hook::func(HOOKTYPE_USER_CREATE, 'sql_auth::user_create'); |
21 | Hook::func(HOOKTYPE_GET_USER_LIST, 'sql_auth::get_user_list'); | |
22 | Hook::func(HOOKTYPE_USER_DELETE, 'sql_auth::user_delete'); | |
5a7f0cde | 23 | Hook::func(HOOKTYPE_EDIT_USER, 'sql_auth::edit_core'); |
b6a7129b | 24 | Hook::func(HOOKTYPE_PRE_OVERVIEW_CARD, 'sql_auth::add_pre_overview_card'); |
e1461204 | 25 | Hook::func(HOOKTYPE_UPGRADE, 'sql_auth::create_tables'); // handles upgrades too ;) |
088733d4 | 26 | Hook::func(HOOKTYPE_USER_ROLE_LIST, 'sql_auth::roles_list'); |
c00c34d2 | 27 | AuthModLoaded::$status = 1; |
ea27475b VP |
28 | } |
29 | ||
088733d4 VP |
30 | public static function roles_list(&$list) |
31 | { | |
32 | $settings = DbSettings::get(); | |
33 | if (isset($settings['user_roles'])) | |
34 | foreach($settings['user_roles'] as $r => $role) | |
35 | $list[$r] = $role; | |
36 | } | |
37 | ||
b6a7129b BM |
38 | public static function add_pre_overview_card($empty) |
39 | { | |
40 | if (defined('SQL_DEFAULT_USER')) | |
41 | Message::Fail("Warning: SQL_DEFAULT_USER is set in config.php. You should remove that item now, as it is only used during installation."); | |
5611f966 BM |
42 | if (defined('DEFAULT_USER')) |
43 | Message::Fail("Warning: DEFAULT_USER is set in config.php. You should remove that item now, as it is only used during installation."); | |
b6a7129b BM |
44 | } |
45 | ||
1bbc51fb BM |
46 | /** |
47 | * Delete all the tables (of us) in the SQLdb | |
48 | * @return void | |
49 | */ | |
50 | public static function delete_tables() | |
51 | { | |
52 | $conn = sqlnew(); | |
53 | foreach(["users","user_meta", "settings", "fail2ban"] as $table_name) | |
54 | $conn->query("DROP TABLE IF EXISTS " . get_config("mysql::table_prefix") . $table_name); | |
55 | } | |
56 | ||
ce9cf366 VP |
57 | /** |
58 | * Create the tables we'll be using in the SQLdb | |
59 | * @return void | |
60 | */ | |
5015c85c VP |
61 | public static function create_tables() |
62 | { | |
63 | $conn = sqlnew(); | |
2dbe2544 VP |
64 | $conn->query("CREATE TABLE IF NOT EXISTS " . get_config("mysql::table_prefix") . "users ( |
65 | user_id int AUTO_INCREMENT NOT NULL, | |
66 | user_name VARCHAR(255) NOT NULL, | |
67 | user_pass VARCHAR(255) NOT NULL, | |
68 | user_email VARCHAR(255), | |
69 | user_fname VARCHAR(255), | |
70 | user_lname VARCHAR(255), | |
71 | user_bio VARCHAR(255), | |
72 | created VARCHAR(255), | |
73 | PRIMARY KEY (user_id) | |
74 | )"); | |
75 | ||
76 | /** | |
77 | * Patch for beta users | |
78 | * This adds the email column to existing tables without it | |
79 | */ | |
80 | $columns = $conn->query("SHOW COLUMNS FROM " . get_config("mysql::table_prefix") . "users"); | |
81 | $column_names = array(); | |
82 | $c = $columns->fetchAll(); | |
83 | ||
84 | foreach($c as $column) { | |
85 | $column_names[] = $column['Field']; | |
86 | } | |
87 | $column_exists = in_array("user_email", $column_names); | |
88 | if (!$column_exists) { | |
89 | $conn->query("ALTER TABLE " . get_config("mysql::table_prefix") . "users ADD COLUMN user_email varchar(255)"); | |
90 | } | |
91 | ||
92 | /** | |
93 | * Another patch for beta users | |
94 | * This changes the size of the meta_value so we can store more | |
95 | */ | |
96 | ||
97 | $conn->query("CREATE TABLE IF NOT EXISTS " . get_config("mysql::table_prefix") . "user_meta ( | |
98 | meta_id int AUTO_INCREMENT NOT NULL, | |
99 | user_id int NOT NULL, | |
100 | meta_key VARCHAR(255) NOT NULL, | |
101 | meta_value VARCHAR(255), | |
7058d6d1 BM |
102 | PRIMARY KEY (meta_id), |
103 | CONSTRAINT meta_key_user_id UNIQUE(meta_key,user_id) | |
2dbe2544 | 104 | )"); |
b41fa16f | 105 | $conn->query("CREATE TABLE IF NOT EXISTS " . get_config("mysql::table_prefix") . "settings ( |
2dbe2544 VP |
106 | id int AUTO_INCREMENT NOT NULL, |
107 | setting_key VARCHAR(255) NOT NULL, | |
088733d4 | 108 | setting_value VARCHAR(5000), |
a20d0d39 BM |
109 | PRIMARY KEY (id), |
110 | UNIQUE(setting_key) | |
2dbe2544 VP |
111 | )"); |
112 | $conn->query("CREATE TABLE IF NOT EXISTS " . get_config("mysql::table_prefix") . "fail2ban ( | |
113 | id int AUTO_INCREMENT NOT NULL, | |
114 | ip VARCHAR(255) NOT NULL, | |
115 | count VARCHAR(255), | |
116 | PRIMARY KEY (id) | |
117 | )"); | |
e1461204 BM |
118 | |
119 | /* Upgrades: */ | |
7058d6d1 | 120 | /* - user_meta: set charset and size */ |
2dbe2544 | 121 | $c = []; |
088733d4 | 122 | if (($columns = $conn->query("SHOW COLUMNS FROM ".get_config("mysql::table_prefix")."user_meta"))) |
2dbe2544 VP |
123 | $c = $columns->fetchAll(); |
124 | if (!empty($c)) | |
088733d4 | 125 | $conn->query("ALTER TABLE ".get_config("mysql::table_prefix")."user_meta CHANGE `meta_value` `meta_value` VARCHAR(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NULL DEFAULT NULL"); |
2dbe2544 | 126 | |
7058d6d1 | 127 | /* - settings: add UNIQUE(setting_key) */ |
e1461204 | 128 | $c = []; |
088733d4 | 129 | if (($columns = $conn->query("SHOW INDEXES FROM ".get_config("mysql::table_prefix")."settings WHERE Key_name='setting_key'"))) |
e1461204 BM |
130 | $c = $columns->fetchAll(); |
131 | if (empty($c)) | |
088733d4 | 132 | { |
7058d6d1 | 133 | $conn->query("ALTER TABLE " . get_config("mysql::table_prefix") . "settings ADD CONSTRAINT setting_key UNIQUE(setting_key)"); |
088733d4 VP |
134 | } |
135 | else | |
136 | $conn->query("ALTER TABLE ".get_config("mysql::table_prefix")."settings CHANGE setting_value setting_value VARCHAR(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NULL DEFAULT NULL"); | |
137 | ||
7058d6d1 BM |
138 | /* - user_meta: add UNIQUE(meta_key,user_id) */ |
139 | $c = []; | |
088733d4 | 140 | if (($columns = $conn->query("SHOW INDEXES FROM ".get_config("mysql::table_prefix")."user_meta WHERE Key_name='meta_key_user_id'"))) |
7058d6d1 BM |
141 | $c = $columns->fetchAll(); |
142 | if (empty($c)) | |
143 | $conn->query("ALTER TABLE " . get_config("mysql::table_prefix") . "user_meta ADD CONSTRAINT meta_key_user_id UNIQUE(meta_key,user_id)"); | |
144 | ||
2dbe2544 | 145 | /* make sure everything went well */ |
b41fa16f | 146 | $tables = ["users", "user_meta", "fail2ban", "settings"]; |
2dbe2544 VP |
147 | $errors = 0; // counter |
148 | $error_messages = ""; | |
149 | foreach($tables as $table) | |
f2c7ac01 | 150 | { |
ca3c76f7 | 151 | $prefix = get_config("sql::table_prefix"); |
2dbe2544 VP |
152 | $sql = "SHOW TABLES LIKE '$prefix%'"; // SQL query to check if table exists |
153 | ||
154 | $result = $conn->query($sql); | |
155 | if ($result->rowCount()) | |
156 | { /* great! */ } | |
157 | ||
158 | else { | |
159 | $errors++; | |
160 | strcat($error_messages,"Table '$prefix$table' was not created successfully.<br>"); | |
161 | } | |
f2c7ac01 | 162 | } |
2dbe2544 VP |
163 | if (!$errors) |
164 | { | |
165 | if (defined('DEFAULT_USER')) // we've got a default account | |
166 | { | |
167 | $lkup = new PanelUser(DEFAULT_USER['username']); | |
168 | ||
169 | if (!$lkup->id) // doesn't exist, add it with full privileges | |
170 | { | |
171 | $user = []; | |
172 | $user['user_name'] = DEFAULT_USER['username']; | |
173 | $user['user_pass'] = DEFAULT_USER['password']; | |
174 | $user['err'] = ""; | |
175 | create_new_user($user); | |
176 | } | |
177 | $lkup = new PanelUser(DEFAULT_USER['username']); | |
178 | if (!user_can($lkup, PERMISSION_MANAGE_USERS)) | |
179 | $lkup->add_permission(PERMISSION_MANAGE_USERS); | |
180 | } | |
181 | return true; | |
182 | } | |
183 | else | |
184 | return false; | |
185 | ||
5015c85c VP |
186 | } |
187 | ||
6930484c VP |
188 | /* We convert $u with a full user as an object ;D*/ |
189 | public static function get_user(&$u) | |
190 | { | |
191 | $id = $u['id']; | |
192 | $name = $u['name']; | |
193 | $conn = sqlnew(); | |
194 | ||
195 | if ($id) | |
196 | { | |
ea90b321 | 197 | $prep = $conn->prepare("SELECT * FROM " . get_config("mysql::table_prefix") . "users WHERE user_id = :id LIMIT 1"); |
6930484c VP |
198 | $prep->execute(["id" => strtolower($id)]); |
199 | } | |
200 | elseif ($name) | |
201 | { | |
ea90b321 | 202 | $prep = $conn->prepare("SELECT * FROM " . get_config("mysql::table_prefix") . "users WHERE LOWER(user_name) = :name LIMIT 1"); |
6930484c VP |
203 | $prep->execute(["name" => strtolower($name)]); |
204 | } | |
205 | $data = NULL; | |
206 | $obj = (object) []; | |
207 | if ($prep) | |
208 | $data = $prep->fetchAll(); | |
209 | if (isset($data[0]) && $data = $data[0]) | |
210 | { | |
211 | $obj->id = $data['user_id']; | |
212 | $obj->username = $data['user_name']; | |
213 | $obj->passhash = $data['user_pass']; | |
214 | $obj->first_name = $data['user_fname'] ?? NULL; | |
215 | $obj->last_name = $data['user_lname'] ?? NULL; | |
216 | $obj->created = $data['created']; | |
217 | $obj->bio = $data['user_bio']; | |
9a674833 | 218 | $obj->email = $data['user_email']; |
6930484c VP |
219 | $obj->user_meta = (new PanelUser_Meta($obj->id))->list; |
220 | } | |
221 | $u['object'] = $obj; | |
222 | } | |
223 | ||
224 | public static function get_usermeta(&$u) | |
225 | { | |
6930484c VP |
226 | $list = &$u['meta']; |
227 | $id = $u['id']; | |
228 | $conn = sqlnew(); | |
229 | if (isset($id)) | |
230 | { | |
ea90b321 | 231 | $prep = $conn->prepare("SELECT * FROM " . get_config("mysql::table_prefix") . "user_meta WHERE user_id = :id"); |
6930484c VP |
232 | $prep->execute(["id" => $id]); |
233 | } | |
234 | foreach ($prep->fetchAll() as $row) | |
235 | { | |
236 | $list[$row['meta_key']] = $row['meta_value']; | |
237 | } | |
238 | } | |
239 | ||
240 | public static function add_usermeta(&$meta) | |
241 | { | |
da6fa2d1 | 242 | $meta = $meta['meta']; |
6930484c VP |
243 | $conn = sqlnew(); |
244 | /* check if it exists first, update it if it does */ | |
ea90b321 | 245 | $query = "SELECT * FROM " . get_config("mysql::table_prefix") . "user_meta WHERE user_id = :id AND meta_key = :key"; |
6930484c VP |
246 | $stmt = $conn->prepare($query); |
247 | $stmt->execute(["id" => $meta['id'], "key" => $meta['key']]); | |
248 | if ($stmt->rowCount()) // it exists, update instead of insert | |
249 | { | |
ea90b321 | 250 | $query = "UPDATE " . get_config("mysql::table_prefix") . "user_meta SET meta_value = :value WHERE user_id = :id AND meta_key = :key"; |
6930484c VP |
251 | $stmt = $conn->prepare($query); |
252 | $stmt->execute($meta); | |
253 | if ($stmt->rowCount()) | |
254 | return true; | |
255 | return false; | |
256 | } | |
257 | ||
258 | else | |
259 | { | |
ea90b321 | 260 | $query = "INSERT INTO " . get_config("mysql::table_prefix") . "user_meta (user_id, meta_key, meta_value) VALUES (:id, :key, :value)"; |
6930484c VP |
261 | $stmt = $conn->prepare($query); |
262 | $stmt->execute($meta); | |
263 | if ($stmt->rowCount()) | |
264 | return true; | |
265 | return false; | |
266 | } | |
267 | } | |
268 | public static function del_usermeta(&$u) | |
269 | { | |
270 | $conn = sqlnew(); | |
ea90b321 | 271 | $query = "DELETE FROM " . get_config("mysql::table_prefix") . "user_meta WHERE user_id = :id AND meta_key = :key"; |
6930484c VP |
272 | $stmt = $conn->prepare($query); |
273 | $stmt->execute($u['meta']); | |
274 | if ($stmt->rowCount()) | |
275 | return true; | |
276 | return false; | |
277 | } | |
180b8ec1 VP |
278 | public static function user_create(&$u) |
279 | { | |
280 | $username = $u['user_name']; | |
9f9d16d5 VP |
281 | $first_name = $u['fname'] ?? NULL; |
282 | $last_name = $u['lname'] ?? NULL; | |
283 | $password = $u['user_pass'] ?? NULL; | |
284 | $user_bio = $u['user_bio'] ?? NULL; | |
285 | $user_email = $u['user_email'] ?? NULL; | |
180b8ec1 | 286 | $conn = sqlnew(); |
ea90b321 | 287 | $prep = $conn->prepare("INSERT INTO " . get_config("mysql::table_prefix") . "users (user_name, user_pass, user_fname, user_lname, user_bio, user_email, created) VALUES (:name, :pass, :fname, :lname, :user_bio, :user_email, :created)"); |
9a674833 | 288 | $prep->execute(["name" => $username, "pass" => $password, "fname" => $first_name, "lname" => $last_name, "user_bio" => $user_bio, "user_email" => $user_email, "created" => date("Y-m-d H:i:s")]); |
180b8ec1 VP |
289 | if ($prep->rowCount()) |
290 | $u['success'] = true; | |
291 | else | |
292 | $u['errmsg'][] = "Could not add user"; | |
293 | } | |
294 | ||
295 | public static function get_user_list(&$list) | |
296 | { | |
297 | $conn = sqlnew(); | |
ea90b321 | 298 | $result = $conn->query("SELECT user_id FROM " . get_config("mysql::table_prefix") . "users"); |
180b8ec1 VP |
299 | if (!$result) // impossible |
300 | { | |
301 | die("Something went wrong."); | |
302 | } | |
303 | $userlist = []; | |
304 | while($row = $result->fetch()) | |
305 | { | |
306 | $userlist[] = new PanelUser(NULL, $row['user_id']); | |
307 | } | |
308 | if (!empty($userlist)) | |
309 | $list = $userlist; | |
310 | ||
311 | } | |
312 | public static function user_delete(&$u) | |
313 | { | |
314 | $user = $u['user']; | |
ea90b321 | 315 | $query = "DELETE FROM " . get_config("mysql::table_prefix") . "users WHERE user_id = :id"; |
180b8ec1 VP |
316 | $conn = sqlnew(); |
317 | $stmt = $conn->prepare($query); | |
318 | $stmt->execute(["id" => $user->id]); | |
319 | $deleted = $stmt->rowCount(); | |
320 | if ($deleted) | |
321 | { | |
322 | $u['info'][] = "Successfully deleted user \"$user->username\""; | |
323 | $u['boolint'] = 1; | |
324 | } else { | |
325 | $u['info'][] = "Unknown error"; | |
326 | $u['boolint'] = 0; | |
327 | } | |
328 | } | |
5a7f0cde VP |
329 | |
330 | public static function edit_core($arr) | |
331 | { | |
332 | $conn = sqlnew(); | |
333 | $user = $arr['user']; | |
334 | $info = $arr['info']; | |
335 | foreach($info as $key => $val) | |
336 | { | |
e9c858fd | 337 | $value = NULL; |
8a73256b | 338 | if (!$val || !strlen($val) || BadPtr($val)) |
5a7f0cde | 339 | continue; |
e9c858fd VP |
340 | if (!strcmp($key,"update_fname") && $val != $user->first_name) |
341 | { | |
5a7f0cde | 342 | $value = "user_fname"; |
e9c858fd VP |
343 | $valuestr = "first name"; |
344 | } | |
345 | elseif (!strcmp($key,"update_lname") && $val != $user->last_name) | |
346 | { | |
5a7f0cde | 347 | $value = "user_lname"; |
e9c858fd VP |
348 | $valuestr = "last name"; |
349 | } | |
350 | elseif (!strcmp($key,"update_bio") && $val != $user->bio) | |
351 | { | |
5a7f0cde | 352 | $value = "user_bio"; |
e9c858fd VP |
353 | $valuestr = "bio"; |
354 | } | |
5a7f0cde | 355 | elseif (!strcmp($key,"update_pass") || !strcmp($key,"update_pass_conf")) |
e9c858fd | 356 | { |
5a7f0cde | 357 | $value = "user_pass"; |
e9c858fd VP |
358 | $valuestr = "password"; |
359 | } | |
360 | elseif(!strcmp($key,"update_email") && $val != $user->email) | |
361 | { | |
5a7f0cde | 362 | $value = "user_email"; |
e9c858fd VP |
363 | $valuestr = "email address"; |
364 | } | |
365 | ||
366 | if (!$value) | |
367 | continue; | |
ea90b321 | 368 | $query = "UPDATE " . get_config("mysql::table_prefix") . "users SET $value=:value WHERE user_id = :id"; |
5a7f0cde VP |
369 | $stmt = $conn->prepare($query); |
370 | $stmt->execute(["value" => $val, "id" => $user->id]); | |
e9c858fd VP |
371 | |
372 | if (!$stmt->rowCount() && $stmt->errorInfo()[0] != "00000") | |
373 | Message::Fail("Could not update $valuestr for $user->username: ".$stmt->errorInfo()[0]." (CODE: ".$stmt->errorCode().")"); | |
374 | ||
375 | else | |
376 | Message::Success("Successfully updated the $valuestr for $user->username"); | |
5a7f0cde VP |
377 | } |
378 | } | |
ce9cf366 VP |
379 | } |
380 | ||
381 | ||
382 | function security_check() | |
383 | { | |
384 | $ip = $_SERVER['REMOTE_ADDR']; | |
385 | if (dnsbl_check($ip)) | |
386 | return true; | |
387 | ||
388 | else if (fail2ban_check($ip)) | |
389 | { | |
390 | ||
391 | } | |
392 | } | |
393 | ||
394 | function dnsbl_check($ip) | |
395 | { | |
bb2ee92b | 396 | $dnsbl_lookup = get_config("dnsbl"); |
c51d17f4 | 397 | if (!$dnsbl_lookup) |
5a7f0cde | 398 | return; |
ce9cf366 VP |
399 | |
400 | // clear variable just in case | |
401 | $listed = NULL; | |
402 | ||
403 | // if the IP was not given because you're an idiot, stop processing | |
404 | if (!$ip) { return; } | |
405 | ||
406 | // get the first two segments of the IPv4 | |
407 | $because = split($ip, "."); // why you | |
408 | $you = $because[1]; // gotta play | |
409 | $want = $because[2]; // that song | |
410 | $to = $you.".".$want."."; // so loud? | |
411 | ||
412 | // exempt local connections because sometimes they get a false positive | |
413 | if ($to == "192.168." || $to == "127.0.") { return NULL; } | |
414 | ||
415 | // you spin my IP right round, right round, to check the records baby, right round-round-round | |
416 | $reverse_ip = glue(array_reverse(split($ip, ".")), "."); | |
417 | ||
418 | // checkem | |
419 | foreach ($dnsbl_lookup as $host) { | |
420 | ||
421 | //if it was listed | |
422 | if (checkdnsrr($reverse_ip . "." . $host . ".", "A")) { | |
423 | ||
424 | //take note | |
425 | $listed = $host; | |
426 | } | |
427 | } | |
428 | ||
429 | // if it was safe, return NOTHING | |
430 | if (!$listed) { | |
431 | return NULL; | |
432 | } | |
433 | ||
434 | // else, you guessed it, return where it was listed | |
435 | else { | |
436 | return $listed; | |
437 | } | |
438 | } | |
439 | ||
440 | function fail2ban_check($ip) | |
33f512fa VP |
441 | { |
442 | ||
443 | } |