path: root/mysql-php/code/a2.php



<?php
declare(strict_types=1);
error_reporting(E_ALL);

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"),
            ("user123", "$2y$10$F1gZoOfRUMcMduyokgOKcevNAZ9GXmUHrtjWoZYkgN38NJ6pKgPAC");
        END IF'))
            display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
    return $dbh;
}

function check_credentials($dbh, $username, $password) {
    if (empty($username) || 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 = $new_password = null;
    $successful = [];
    if (!empty($_POST['new_username']))
        $new_username = $_POST['new_username'];
    if (!empty($_POST['new_password']))
        $new_password = $_POST['new_password'];
    if ($new_password !== null) {
        if (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();
}
<?php
declare(strict_types=1);
error_reporting(E_ALL);

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"),
            ("user123", "$2y$10$F1gZoOfRUMcMduyokgOKcevNAZ9GXmUHrtjWoZYkgN38NJ6pKgPAC");
        END IF'))
            display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
    return $dbh;
}

function check_credentials($dbh, $username, $password) {
    if (empty($username) || 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 = $new_password = null;
    $successful = [];
    if (!empty($_POST['new_username']))
        $new_username = $_POST['new_username'];
    if (!empty($_POST['new_password']))
        $new_password = $_POST['new_password'];
    if ($new_password !== null) {
        if (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();
}