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
|
<?php
declare(strict_types=1);
error_reporting(E_ALL);
// student: name roll city email date_of_birth
function connect_to_database() {
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
if (!($dbh = mysqli_connect('localhost', 'root', '')))
display_failure('Could not connect to the database: ' . mysqli_connect_error($dbh));
mysqli_set_charset($dbh, 'utf8mb4');
if (!mysqli_query($dbh, 'CREATE DATABASE IF NOT EXISTS STUDENTS_DB'))
display_failure('Could not create database: ' . mysqli_error($dbh));
mysqli_select_db($dbh, 'STUDENTS_DB');
if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS STUDENT (
ROLL INT(20) PRIMARY KEY,
NAME VARCHAR(255),
CITY VARCHAR(255),
EMAIL VARCHAR(255),
DATE_OF_BIRTH DATE
)'))
display_failure('Could not create `STUDENT` table: ' . mysqli_error($dbh));
if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS `LOGIN` (
`USERNAME` VARCHAR(255),
`PASSWORD` VARCHAR(255),
UNIQUE (`USERNAME`)
)'))
display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
if (!mysqli_query($dbh, 'IF NOT EXISTS (SELECT * FROM `LOGIN` WHERE `USERNAME` = "admin") THEN
INSERT INTO `LOGIN` (`USERNAME`, `PASSWORD`) VALUES ("admin", "$2y$10$3cq2joFu6kEYccaTxDkRXexrsd3GAnq4rGTip9erOucM9H9E8q5ly");
END IF'))
display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
return $dbh;
}
function check_credentials($dbh, $username, $password) {
if (!isset($username) || empty($username) || !isset($password) || empty($password))
return false;
$stmt = mysqli_prepare($dbh, 'SELECT `PASSWORD` FROM `LOGIN` WHERE `USERNAME` = ?');
mysqli_stmt_bind_param($stmt, 's', $username);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($result) === 0) return false;
$record = mysqli_fetch_array($result);
return password_verify($password, $record['PASSWORD']);
}
function update_credentials($dbh) {
if (!check_credentials($dbh, $_POST['previous_username'], $_POST['previous_password']))
display_failure('Can not update credentials, both previous usernames and passwords need to be provided and they need to be valid.');
$new_username = null;
$new_password = null;
$successful = [];
if (isset($_POST['new_username']) && !empty($_POST['new_username']))
$new_username = $_POST['new_username'];
if (isset($_POST['new_password']) && !empty($_POST['new_password']))
$new_password = $_POST['new_password'];
if ($new_password !== null) {
if (!isset($_POST['new_password2']) || empty($_POST['new_password2']))
display_failure('Need to provide new password twice');
if ($new_password !== $_POST['new_password2'])
display_failure('New password provided twice need to match');
$stmt = mysqli_prepare($dbh, 'UPDATE `LOGIN` SET `PASSWORD` = ? WHERE `USERNAME` = ?');
mysqli_stmt_bind_param($stmt, 'ss', password_hash($new_password, PASSWORD_DEFAULT), $_POST['previous_username']);
$successful['password'] = mysqli_stmt_execute($stmt);
}
if ($new_username !== NULL) {
$stmt = mysqli_prepare($dbh, 'UPDATE `LOGIN` SET `USERNAME` = ? WHERE `USERNAME` = ?');
mysqli_stmt_bind_param($stmt, 'ss', $_POST['new_username'], $_POST['previous_username']);
$successful['username'] = mysqli_stmt_execute($stmt);
}
html_prologue('Credential update');
echo '<p>';
if ($new_username !== null)
echo isset($successful['username']) ? 'Username update successful.' : 'Username update failed'; echo '<br>';
if ($new_password !== null)
echo isset($successful['username']) ? 'Password update successful.' : 'Password update failed'; echo '<br>';
if ($new_username === null && $new_password === null)
echo 'There was nothing to update.';
}
function html_prologue($title) {
?><!doctype html>
<meta charset="utf-8">
<title><?php echo $title; ?></title>
<style>
body { font-family: sans-serif; font-size: 1.3rem; }
h1 { font-size: 2rem; font-weight: 500; }
table, tr, th, td { border-collapse: collapse; border: 1px solid black; }
th, td { padding: 5px; }
</style>
<?php
}
function display_failure($reason) {
html_prologue('Operation failure');
?>
<h2>Operation failed</h2>
<p>Reason: <?php echo $reason; ?></p>
<?php
die();
}
function display_success() {
html_prologue('Operation successful');
?>
<h2>Updation successful.</h2>
<?php
}
function show_table($dbh) {
if (!($result = mysqli_query($dbh, 'SELECT * FROM STUDENT')))
display_failure('Could not perform query: ' . mysqli_error($dbh));
html_prologue('Students\' details');
?>
<h2>Students' details</h2>
<p><?php echo mysqli_num_rows($result); ?> record(s) found.</p>
<p><a href="<?php echo $_SERVER['PHP_SELF']; ?>?change">Change credentials</a></p>
<table>
<tr>
<th>Roll No.</th>
<th>Name</th>
<th>E-mail</th>
<th>City</th>
<th>Date of birth</th>
</tr><?php
while ($row = mysqli_fetch_assoc($result)) { ?>
<tr><td>
<?php echo implode('</td><td>', array_map('htmlspecialchars', [
$row['ROLL'], $row['NAME'], $row['EMAIL'], $row['CITY'],
$row['DATE_OF_BIRTH']
])); ?>
</td></tr>
<?php
} ?>
</table>
<?php
}
function display_login_form() {
html_prologue('Authorization required');
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<h1>Provide credentials</h1>
<p><label>Username: <input type="text" name="username"></label></p>
<p><label>Password: <input type="password" name="password"></label></p>
<input type="submit" value="Log in">
</form>
<?php
}
function display_credential_change_form() {
html_prologue('Change credentails');
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>?change">
<h1>Change credentials</h1>
<p>Fields for new value can be left empty to keep the value unchanged.</p>
<p><label>Previous Username: <input type="text" name="previous_username"></label></p>
<p><label>New Username: <input type="text" name="new_username"></label></p>
<p><label>Previous Password: <input type="password" name="previous_password"></label></p>
<p><label>New Password: <input type="password" name="new_password"></label></p>
<p><label>New Password again: <input type="password" name="new_password2"></label></p>
<input type="submit" value="Change">
</form>
<?php
}
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$dbh = connect_to_database();
if ($_SERVER['QUERY_STRING'] === 'change') {
update_credentials($dbh);
} else if (check_credentials($dbh, $_POST['username'], $_POST['password'])) {
show_table($dbh);
} else {
display_failure('Invalid credentials, try again');
}
mysqli_close($dbh);
} else if ($_SERVER['QUERY_STRING'] === 'change') {
display_credential_change_form();
} else {
display_login_form();
}
|