· 8 years ago · Apr 19, 2018, 05:36 PM
1<?php
2
3function gen_uuid($use_dashes = false)
4{
5 // Re-seed rand with updated time & unique-ish user info to delay introduction of duplicates in v4uuid
6 // It might be a good idea to salt the client address, but this is not a high-security application
7 mt_srand(crc32(serialize([microtime(true), getClientAddress(), 'ETC'])));
8
9 return sprintf( $use_dashes ? '%04x%04x-%04x-%04x-%04x-%04x%04x%04x' : '%04x%04x%04x%04x%04x%04x%04x%04x',
10/*time_low*/ mt_rand(0, 0xffff), mt_rand(0, 0xffff),
11/*time_mid*/ mt_rand(0, 0xffff),
12/*time_hi_ver*/ mt_rand(0, 0xffff) | 0x4000,
13/*clk_seq_low_hi*/ mt_rand(0, 0xffff) | 0x8000,
14/*node*/ mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
15 );
16}
17
18function getClientAddress()
19{
20 $ip = null;
21 if (!empty($_SERVER['HTTP_CLIENT_IP'])) //check ip from share internet
22 {
23 $ip = $_SERVER['HTTP_CLIENT_IP'];
24 }
25 elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) //to check ip is pass from proxy
26 {
27 $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
28 }
29 else
30 {
31 $ip = $_SERVER['REMOTE_ADDR'];
32 }
33 return $ip;
34}
35
36$db_address = 'localhost';
37$db_username = 'bunbun';
38$db_password = 'tomato';
39$db_conn = null;
40$db_cmd = '';
41$db_stmt = null;
42
43$current_user = null;
44
45
46// Establish MySQL DB
47try
48{
49 $db_conn = new PDO('mysql:host=$db_address;charset=utf8mb4', $db_username, $db_password);
50
51 $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
52
53 $db_cmd = 'CREATE DATABASE IF NOT EXISTS radio';
54 $conn->exec($db_cmd);
55 echo 'Radio database created.<br>';
56
57 $db_cmd = 'USE radio';
58 $conn->exec($db_cmd);
59 echo 'Radio database defaulted.<br>';
60
61 $db_cmd = 'CREATE TABLE IF NOT EXISTS users ('
62 .' user_id CHAR(32) PRIMARY KEY,'
63 .' username VARCHAR(32) NOT NULL,'
64 .' email VARCHAR(1024) NOT NULL,'
65 .' active_ip VARCHAR(45)'
66 .' pass_hash VARCHAR(255),'
67 .' UNIQUE KEY (uuid, username, email)';
68 $conn->exec($db_cmd);
69 echo 'Users table established.<br>';
70
71 $db_cmd = 'CREATE TABLE IF NOT EXISTS user_mgmt ('
72 .' user_id CHAR(32) PRIMARY KEY,'
73 .' token CHAR(32) NOT NULL,';
74 $conn->exec($db_cmd);
75 echo 'User Management table established.<br>';
76
77 $db_cmd = 'CREATE TABLE IF NOT EXISTS rooms ('
78 .' room_id CHAR(32) PRIMARY KEY,'
79 .' name VARCHAR(32) NOT NULL,'
80 .' owner_id CHAR(32) NOT NULL)';
81 $conn->exec($db_cmd);
82 echo 'Rooms table established.<br>';
83
84 $db_cmd = 'CREATE TABLE IF NOT EXISTS queue ('
85 .' queue_id INT AUTO_INCREMENT,'
86 .' room_id CHAR(32),'
87 .' song_name VARCHAR(64) NOT NULL,'
88 .' song_url VARCHAR(64) NOT NULL,'
89 .' song_start TIMESTAMP,'
90 .' song_added TIMESTAMP.'
91 .' requester_id CHAR(32) NOT NULL,'
92 .' PRIMARY KEY (id, room_id))';
93 $conn->exec($db_cmd);
94 echo 'Queue table established.<br>';
95
96 $db_cmd = 'CREATE TABLE IF NOT EXISTS permissions ('
97 .' room_id CHAR(32),'
98 .' user_id CHAR(32),'
99 .' role TINYINT NOT NULL,'
100 .' PRIMARY KEY (room_id, user_id))';
101 $conn->exec($db_cmd);
102 echo 'Permissions table established.<br>';
103
104 $db_cmd = 'SET GLOBAL event_scheduler = ON';
105 $conn->exec($db_cmd);
106 echo 'Scheduled Events enabled.<br>';
107
108 $db_stmt = $db_conn->prepare('SELECT uuid, username FROM users WHERE active_ip = :active_ip LIMIT 1');
109 $db_stmt->bindParam(':active_ip', getClientAddress());
110
111 $db_stmt->execute();
112
113 $user_id = $db_stmt->fetch();
114
115 $current_user = ($user_id ? $user_id : null;
116}
117catch(PDOException $e)
118{
119 echo '<br>Database Init Error:<br>Query: ' . $db_cmd . '<br> ' . $e->getMessage() . '<br>';
120}
121
122function user_register($username, $email, $password, &$user_id)
123{
124 try
125 {
126 $user_id = null;
127
128 $db_stmt = $db_conn->prepare('SELECT 1 FROM users WHERE username = :username OR email = :email LIMIT 1');
129 $db_stmt->bindParam(':username', $username);
130 $db_stmt->bindParam(':email', $email);
131
132 $db_stmt->execute();
133
134 if ($db_stmt->fetch())
135 return 'Username or Email is already registered.';
136
137 do
138 {
139 $user_id = gen_uuid();
140 $db_stmt = $db_conn->prepare('SELECT 1 FROM users WHERE user_id = :user_id LIMIT 1');
141 $db_stmt->bindParam(':user_id', $user_id);
142 $db_stmt->execute();
143 }
144 while ($db_stmt->fetch());
145
146 $db_stmt = $db_conn->prepare('INSERT INTO users (user_id, username, email, pass_hash) VALUES (:user_id, :username, :email, :pass_hash)');
147 $db_stmt->bindParam(':user_id', $user_id);
148 $db_stmt->bindParam(':username', $username);
149 $db_stmt->bindParam(':email', $email);
150 $db_stmt->bindValue(':pass_hash', null, PDO::PARAM_STR); //password_hash($password, PASSWORD_DEFAULT));
151
152 $db_stmt->execute();
153
154 $token = gen_uuid();
155 $db_stmt = $db_conn->prepare('INSERT INTO user_mgmt (user_id, token) VALUES (:user_id, :token)');
156 $db_stmt->bindParam(':user_id', $user_id);
157 $db_stmt->bindParam(':token', $token);
158
159 $db_stmt->execute();
160
161 $headers = 'MIME-Version 1.0' . '\r\n' . 'Content-type:text/html;charset=UTF-8' . '\r\n' . 'From: registration@radiobun.com';
162 mail($email, 'Radio Website Registration Email', sprintf('Username: %s\nToken: %s', $username, $token), $headers);
163
164 return null;
165 }
166 catch(PDOException $e)
167 {
168 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
169 }
170}
171
172function user_setup_password($username, $token, $password)
173{
174 try
175 {
176 $db_stmt = $db_conn->prepare('SELECT user_id FROM users WHERE username = :username LIMIT 1');
177 $db_stmt->bindParam(':username', $username);
178
179 $db_stmt->execute();
180
181 $user_id = $db_stmt->fetch();
182 if (!$user_id)
183 return 'Unknown Username';
184 else
185 $user_id = $user_id['user_id'];
186
187 $db_stmt = $db_conn->prepare('SELECT 1 FROM user_mgmt WHERE user_id = :user_id AND token = :token LIMIT 1');
188 $db_stmt->bindParam(':user_id', $user_id);
189 $db_stmt->bindParam(':token', $token);
190
191 $db_stmt->execute();
192
193 if (!$db_stmt->fetch())
194 return 'Bad Token';
195
196 $db_stmt = $db_conn->prepare('DELETE FROM user_mgmt WHERE user_id = :user_id');
197 $db_stmt->bindParam(':user_id', $user_id);
198
199 $db_stmt->execute();
200
201 $db_stmt = $db_conn->prepare('UPDATE users SET password = :password WHERE user_id = :user_id');
202 $db_stmt->bindParam(':user_id', $user_id);
203 $db_stmt->bindParam(':password', $password);
204
205 $db_stmt->execute();
206
207 user_login($username, $password);
208
209 return null;
210 }
211 catch(PDOException $e)
212 {
213 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
214 }
215}
216
217function user_login($username, $password)
218{
219 try
220 {
221 if (!isset($current_user))
222 {
223 $db_stmt = $db_conn->prepare('SELECT user_id FROM users WHERE username = :username LIMIT 1');
224 $db_stmt->bindParam(':username', $username);
225
226 $db_stmt->execute();
227
228 $user_id = $db_stmt->fetch();
229 if (!$user_id)
230 return 'Unknown Username';
231 else
232 $user_id = $user_id['user_id'];
233
234 $db_stmt = $db_conn->prepare('SELECT 1 FROM users WHERE username = :username AND pass_hash = :pass_hash LIMIT 1');
235 $db_stmt->bindParam(':username', $username);
236 $db_stmt->bindParam(':email', $email);
237
238 $db_stmt->execute();
239
240 if ($db_stmt->fetch())
241 {
242 $current_user = array('user_id'=>$user_id,'username'=>$username);
243
244 $db_stmt = $db_conn->prepare('UPDATE users SET active_ip = :active_ip WHERE username = :username');
245 $db_stmt->bindParam(':username', $username);
246 $db_stmt->bindParam(':active_ip', getClientAddress());
247
248 $db_stmt->execute();
249 }
250 else
251 {
252 $current_user = null;
253 }
254
255 return null;
256 }
257
258 return 'User is already logged in';
259 }
260 catch(PDOException $e)
261 {
262 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
263 }
264}
265
266function user_logout()
267{
268 try
269 {
270 if (isset($current_user))
271 {
272 $db_stmt = $db_conn->prepare('UPDATE users SET active_ip = :active_ip WHERE user_id = :user_id LIMIT 1');
273 $db_stmt->bindParam(':user_id', $current_user['user_id']);
274 $db_stmt->bindValue(':active_ip', null, PDO::PARAM_STR);
275
276 $db_stmt->execute();
277
278 $current_user = null;
279
280 return null;
281 }
282
283 return 'User is not logged in';
284 }
285 catch(PDOException $e)
286 {
287 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
288 }
289}
290
291function user_get_owned_rooms(&$rooms)
292{
293 $rooms = null;
294
295 try
296 {
297 if (!isset($current_user))
298 return 'User is not logged in';
299
300 $db_stmt = $db_conn->prepare('SELECT room_id, name FROM rooms WHERE owner_id = :owner_id');
301 $db_stmt->bindParam(':owner_id', $current_user['user_id']);
302
303 $db_stmt->execute();
304
305 $rooms = $db_stmt->fetchAll(PDO::FETCH_ASSOC);
306
307 return null;
308 }
309 catch(PDOException $e)
310 {
311 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
312 }
313}
314
315function room_destroy($room_id)
316{
317 try
318 {
319 if (!isset($current_user))
320 return 'User is not logged in';
321
322 $db_stmt = $db_conn->prepare('SELECT 1 FROM rooms WHERE room_id = :room_id AND owner_id = :owner_id LIMIT 1');
323 $db_stmt->bindParam(':room_id', $room_id);
324 $db_stmt->bindParam(':owner_id', $current_user['user_id']);
325
326 $db_stmt->execute();
327
328 if (!$db_stmt->fetch())
329 return 'Room not found for user';
330
331 $db_stmt = $db_conn->prepare('DELETE FROM rooms WHERE room_id = :room_id');
332 $db_stmt->bindParam(':room_id', $room_id);
333
334 $db_stmt->execute();
335
336 return null;
337 }
338 catch(PDOException $e)
339 {
340 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
341 }
342}
343
344function room_create($name, &$room_id)
345{
346 $room_id = null;
347
348 try
349 {
350 if (!isset($current_user))
351 return 'User is not logged in';
352
353 do
354 {
355 $room_id = gen_uuid();
356 $db_stmt = $db_conn->prepare('SELECT 1 FROM rooms WHERE room_id = :room_id AND owner_id = :owner_id LIMIT 1');
357 $db_stmt->bindParam(':room_id', $room_id);
358 $db_stmt->bindParam(':owner_id', $current_user['user_id']);
359 $db_stmt->execute();
360 }
361 while ($db_stmt->fetch());
362
363 $db_stmt = $db_conn->prepare('INSERT INTO rooms (room_id, name, owner_id) VALUES (:room_id, :name, :owner_id)');
364 $db_stmt->bindParam(':room_id', $room_id);
365 $db_stmt->bindParam(':name', $name);
366 $db_stmt->bindParam(':owner_id', $current_user['user_id']);
367
368 $db_stmt->execute();
369
370 $db_stmt = $db_conn->prepare('INSERT INTO permissions (room_id, user_id, role) VALUES (:room_id, :user_id, :role)');
371 $db_stmt->bindParam(':room_id', $room_id);
372 $db_stmt->bindParam(':user_id', $current_user['user_id']);
373 $db_stmt->bindValue(':role', 1, PDO::PARAM_INT);
374
375 $db_stmt->execute();
376
377 // make room
378
379 return null;
380 }
381 catch(PDOException $e)
382 {
383 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
384 }
385}
386
387function room_get_songs($room_id, &$songs)
388{
389 $songs = null;
390 try
391 {
392 if (!isset($current_user))
393 return 'User is not logged in';
394
395 $db_stmt = $db_conn->prepare('SELECT q.queue_id AS queue_id, q.song_name AS name, q.song_url AS url, q.song_start AS started, q.song_added AS added, u.username AS requester FROM queue AS q, users AS u WHERE q.room_id = :room_id AND u.user_id = q.requester_id ORDER BY q.song_added DESC');
396 $db_stmt->bindParam(':room_id', $room_id);
397
398 $db_stmt->execute();
399
400 $playlist = $db_stmt->fetchAll(PDO::FETCH_ASSOC);
401
402 $songs = ($playlist ? $playlist : null);
403
404 return null;
405 }
406 catch(PDOException $e)
407 {
408 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
409 }
410}
411
412function room_add_song($room_id, $song_name, $song_url)
413{
414 try
415 {
416 if (!isset($current_user))
417 return 'User is not logged in';
418
419 $db_stmt = $db_conn->prepare('SELECT role FROM permissions WHERE room_id = :room_id AND user_id = :user_id LIMIT 1');
420 $db_stmt->bindParam(':room_id', $room_id);
421 $db_stmt->bindParam(':user_id', $current_user['user_id']);
422
423 $db_stmt->execute();
424
425 $role = $db_stmt->fetch();
426 $role = ($role ? $role['role'] : 0);
427
428 if ($role < 0)
429 return 'User does not have permission for this room';
430
431 .' queue_id INT AUTO_INCREMENT,'
432 .' room_id CHAR(32),'
433 .' song_name VARCHAR(64) NOT NULL,'
434 .' song_url VARCHAR(64) NOT NULL,'
435 .' song_start TIMESTAMP,'
436 .' song_added TIMESTAMP.'
437 .' requester_id CHAR(32) NOT NULL,'
438
439 $db_stmt = $db_conn->prepare('INSERT INTO queue (queue_id, room_id, song_name, song_url, song_start, song_added, requester_id) VALUES (:queue_id, :room_id, :song_name, :song_url, :song_start, UNIX_TIMESTAMP(), :requester_id)');
440 $db_stmt->bindValue(':queue_id', null, PDO::PARAM_INT);
441 $db_stmt->bindParam(':room_id', $room_id);
442 $db_stmt->bindParam(':song_name', $song_name);
443 $db_stmt->bindParam(':song_url', $song_url);
444 $db_stmt->bindValue(':song_start', null, PDO::PARAM_INT);
445 $db_stmt->bindParam(':requester_id', $current_user['user_id']);
446
447 $db_stmt->execute();
448
449 return null;
450 }
451 catch(PDOException $e)
452 {
453 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
454 }
455}
456
457function room_del_song($room_id, $queue_id)
458{
459 try
460 {
461 if (!isset($current_user))
462 return 'User is not logged in';
463
464 $db_stmt = $db_conn->prepare('SELECT role FROM permissions WHERE room_id = :room_id AND user_id = :user_id LIMIT 1');
465 $db_stmt->bindParam(':room_id', $room_id);
466 $db_stmt->bindParam(':user_id', $current_user['user_id']);
467
468 $db_stmt->execute();
469
470 $role = $db_stmt->fetch();
471 $role = ($role ? $role['role'] : 0);
472
473 if ($role < 1)
474 return 'User does not have permission for this room';
475
476 $db_stmt = $db_conn->prepare('SELECT 1 FROM queue WHERE queue_id = :queue_id AND room_id = :room_id LIMIT 1');
477 $db_stmt->bindParam(':queue_id', $queue_id);
478 $db_stmt->bindParam(':room_id', $room_id);
479
480 $db_stmt->execute();
481
482 if (!$db_stmt->fetch())
483 return 'Song not found in playlist';
484
485 $db_stmt = $db_conn->prepare('DELETE FROM queue WHERE queue_id = :queue_id AND room_id = :room_id');
486 $db_stmt->bindParam(':queue_id', $queue_id);
487 $db_stmt->bindParam(':room_id', $room_id);
488
489 $db_stmt->execute();
490
491 return null;
492 }
493 catch(PDOException $e)
494 {
495 return 'DB Error:<br> Query: ' . $db_cmd . '<br> Error: ' . $e->getMessage() . '<br>';
496 }
497}
498
499// Shutdown DB Connection
500$db_stmt = null;
501$db_cmd = null;
502$db_conn = null;
503?>