food-inventory/assets/php/utils.php

<?php

$SESSION_COOKIE_NAME = "connection_id";
$MAX_COOKIE_LIFE = 86400 * 30; // 30 days max
$MAX_RESET_TOKEN_LIFE = 1200; // 20 minutes max
$MAX_TOKENS = 3;
$MINIMAL_PASSWORD_LENGTH = 8;

$config_array = json_decode(file_get_contents("config.json", true));

$dsn = $config_array->{"kind"} . ":dbname="
    . $config_array->{"dbname"} . ";host=" . $config_array->{"host"};
$PDO = new PDO($dsn, $config_array->{"user"}, $config_array->{"password"});
$PDO->query('SET CHARSET UTF8');

function is_https()
{
    return (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off');
}

function generate_random_string()
{
    return substr(str_shuffle(MD5(microtime())), 0, 32);
}

function connect_user($user_id, $long_expiration = true)
{
    global $PDO, $SESSION_COOKIE_NAME, $MAX_COOKIE_LIFE;

    // Set the session max lifespan
    $delay = $long_expiration === true ? $MAX_COOKIE_LIFE : 86400;

    // Set the cookie lifespan
    $cookie_life = $long_expiration === true ? time() + $MAX_COOKIE_LIFE : 0;

    // The session id is a 32-chars random string
    $session_id = generate_random_string();

    $sql = "INSERT INTO sessions(user_id, connection_eol, session_id) 
        VALUES (:user_id, :connection_eol, :session_id);";
    $query = $PDO->prepare($sql);

    $query->bindValue(":user_id", $user_id);
    $query->bindValue(
        ":connection_eol",
        date('Y-m-d H:i:s', strtotime("now + $delay seconds")),
        PDO::PARAM_STR
    );
    $query->bindValue(":session_id", $session_id);

    if ($query->execute()) {
        return setcookie(
            $SESSION_COOKIE_NAME,
            $session_id,
            $cookie_life,
            "",
            "",
            true,
            false
        );
    }
    return false;
}

function clean_old_sessions()
{
    global $PDO;

    $sql = "DELETE FROM sessions WHERE connection_eol < CURRENT_TIMESTAMP();";
    $query = $PDO->prepare($sql);
    return $query->execute();
}

function disconnect()
{
    global $PDO, $SESSION_COOKIE_NAME;

    if (isset($_COOKIE[$SESSION_COOKIE_NAME])) {
        $sql = "DELETE FROM sessions WHERE session_id = :session_id;";
        $query = $PDO->prepare($sql);
        $query->bindValue(":session_id", $_COOKIE[$SESSION_COOKIE_NAME]);
        $query->execute();
        setcookie($SESSION_COOKIE_NAME, "", time() - 3600);
    }
}

function is_connected()
{
    global $PDO, $SESSION_COOKIE_NAME;

    if (isset($_COOKIE[$SESSION_COOKIE_NAME])) {
        if (!clean_old_sessions()) {
            return false;
        }

        $sql = "SELECT * FROM sessions INNER JOIN accounts ON sessions.user_id = accounts.id WHERE session_id = :session_id;";
        $query = $PDO->prepare($sql);

        $query->bindValue(":session_id", $_COOKIE[$SESSION_COOKIE_NAME]);
        if ($query->execute()) {
            if ($query->rowCount() === 1) {
                return true;
            } else {
                disconnect();
                return false;
            }
        } else {
            return false;
        }
    }
    return false;
}

function get_username_count($email)
{
    global $PDO;

    $sql = "SELECT email FROM accounts WHERE email = :email;";
    $query = $PDO->prepare($sql);
    $query->bindValue(":email", $email);

    if ($query->execute()) {
        return $query->rowCount();
    }
    return false;
}

function correct_email_password($email, $password)
{
    global $PDO;

    $sql = "SELECT email, password_hash FROM accounts WHERE email = :email;";
    $query = $PDO->prepare($sql);
    $query->bindValue(":email", $email);

    if ($query->execute()) {
        foreach ($query as $row) {
            return password_verify($password, $row["password_hash"]);
        }
    }
    return false;
}

function get_user_id_from_email($email)
{
    global $PDO;

    $sql = "SELECT id FROM accounts WHERE email = :email;";
    $query = $PDO->prepare($sql);
    $query->bindValue(":email", $email);

    if ($query->execute()) {
        foreach ($query as $row) {
            return $row["id"];
        }
    }
    return false;
}

function get_user_info_from_session_id($info)
{
    global $PDO, $SESSION_COOKIE_NAME;

    if (isset($_COOKIE[$SESSION_COOKIE_NAME])) {
        $sql = "SELECT accounts.id AS id, email,
            first_name, first_name, 
            last_name, public_id FROM accounts 
            INNER JOIN sessions 
            ON sessions.user_id = accounts.id 
            WHERE session_id = :session_id;";
        $query = $PDO->prepare($sql);
        $query->bindValue(":session_id", $_COOKIE[$SESSION_COOKIE_NAME]);
        if ($query->execute())
            foreach ($query as $row) {
                switch ($info) {
                    case "id":
                    case "email":
                    case "first_name":
                    case "last_name":
                    case "public_id":
                        return $row[$info];
                    default;
                        break;
                }
            }
    }
    return false;
}

function add_user($email, $first_name, $last_name, $clear_password)
{
    global $PDO;

    $password_hash = password_hash($clear_password, PASSWORD_DEFAULT);

    $sql = "INSERT INTO accounts(email, first_name, last_name, password_hash, public_id)
    VALUES (:email, :first_name, :last_name, :password, :public_id);";
    $query = $PDO->prepare($sql);
    $query->bindValue(":email", $email);
    $query->bindValue(":first_name", $first_name);
    $query->bindValue(":last_name", $last_name);
    $query->bindValue(":password", $password_hash);
    $query->bindValue(":public_id", generate_random_string());
    return $query->execute();
}

function change_user_password($user_id, $new_clear_password)
{
    global $PDO;

    $password_hash = password_hash($new_clear_password, PASSWORD_DEFAULT);

    $sql = "UPDATE accounts SET password_hash = :password_hash WHERE accounts.id = :id";
    $query = $PDO->prepare($sql);
    $query->bindValue(":password_hash", $password_hash);
    $query->bindValue(":id", $user_id, PDO::PARAM_INT);
    return $query->execute();
}

function add_cupboard($name, $description)
{
    global $PDO;

    $sql = "INSERT INTO cupboards (name, description, owner_id, public_id)
        VALUES (:name, :description, :owner_id, :public_id);";
    $query = $PDO->prepare($sql);
    $query->bindValue(":name", $name);
    $query->bindValue(":description", $description);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));
    $query->bindValue(":public_id", generate_random_string());
    return $query->execute();
}

function is_users_cupboard($cupboard_public_id)
{
    global $PDO;

    $sql = "SELECT cupboards.public_id FROM cupboards 
        INNER JOIN accounts ON cupboards.owner_id = accounts.id
        WHERE cupboards.public_id = :public_id AND accounts.id = :accounts_id;";
    $query = $PDO->prepare($sql);
    $query->bindValue(":public_id", $cupboard_public_id);
    $query->bindValue(":accounts_id", get_user_info_from_session_id("id"));

    if ($query->execute()) {
        return ($query->rowCount() === 1);
    }
    return false;
}

function add_product($name, $description, $expiration_date = NULL, $cupboard_id = NULL)
{
    global $PDO;

    $sql = "INSERT INTO products 
        (name, description, expiration_date, owner_id, cupboard_id, public_id)
        VALUES 
        (:name, :description, :expiration_date, :owner_id, :cupboard_id, :public_id);";
    $query = $PDO->prepare($sql);
    $query->bindValue(":name", $name);
    $query->bindValue(":description", $description);

    if ($expiration_date === NULL) {
        $query->bindValue(":expiration_date", NULL, PDO::PARAM_INT);
    } else {
        $query->bindValue(":expiration_date", $expiration_date);
    }

    if ($cupboard_id === NULL) {
        $query->bindValue(":cupboard_id", NULL, PDO::PARAM_INT);
    } else {
        $query->bindValue(":cupboard_id", $cupboard_id);
    }

    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));
    $query->bindValue(":public_id", generate_random_string());
    return $query->execute();
}

function get_users_products_array()
{
    global $PDO;

    $user_products = array();

    $sql = "SELECT 
        products.id AS id, products.name AS name, products.description AS description,
        cupboards.id AS cupboard_id, cupboards.name AS cupboard_name,
        cupboards.description AS cupboard_description, expiration_date,
        added_date, products.public_id AS public_id, cupboards.public_id AS cupboard_public_id
        FROM products 
        LEFT JOIN cupboards ON products.cupboard_id = cupboards.id 
        WHERE products.owner_id = :owner_id;";

    $query = $PDO->prepare($sql);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));
    if ($query->execute()) {
        foreach ($query as $row) {
            array_push($user_products, $row);
        }
    }
    return $user_products;
}

function get_users_cupboards_array()
{
    global $PDO;

    $user_cupboards = array();

    $sql = "SELECT 
        id, name, description, cupboards.public_id AS public_id
        FROM cupboards WHERE owner_id = :owner_id;";

    $query = $PDO->prepare($sql);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));
    if ($query->execute()) {
        foreach ($query as $row) {
            array_push($user_cupboards, $row);
        }
    }
    return $user_cupboards;
}

function delete_cupboard($cupboard_public_id)
{
    global $PDO;

    $sql = "DELETE cupboards 
        FROM cupboards 
        INNER JOIN accounts 
        ON cupboards.owner_id = accounts.id 
        WHERE cupboards.public_id = :id 
        AND cupboards.owner_id = :owner_id;";
    $query = $PDO->prepare($sql);

    $query->bindValue(":id", $cupboard_public_id);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));

    return $query->execute();
}

function delete_product($product_public_id)
{
    global $PDO;

    $sql = "DELETE products 
        FROM products 
        INNER JOIN accounts 
        ON products.owner_id = accounts.id 
        WHERE products.public_id = :id 
        AND products.owner_id = :owner_id;";
    $query = $PDO->prepare($sql);

    $query->bindValue(":id", $product_public_id);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));

    return $query->execute();
}

function update_product(
    $product_public_id,
    $new_name,
    $new_description,
    $new_expiration_date,
    $new_cupboard_id
) {
    global $PDO;

    $sql = "UPDATE products 
        INNER JOIN accounts 
        ON products.owner_id = accounts.id 
        SET products.name = :new_name,
            products.description = :new_description,
            products.expiration_date = :new_expiration_date,
            products.cupboard_id = :new_cupboard_id
        WHERE products.public_id = :id 
        AND products.owner_id = :owner_id;";
    $query = $PDO->prepare($sql);

    $query->bindValue(":new_name", $new_name);
    $query->bindValue(":new_description", $new_description);
    if ($new_expiration_date === null) {
        $query->bindValue(":new_expiration_date", $new_expiration_date, PDO::PARAM_INT);
    } else {
        $query->bindValue(":new_expiration_date", $new_expiration_date);
    }
    if ($new_cupboard_id === null) {
        $query->bindValue(":new_cupboard_id", $new_cupboard_id, PDO::PARAM_INT);
    } else {
        $query->bindValue(":new_cupboard_id", $new_cupboard_id);
    }
    $query->bindValue(":id", $product_public_id);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));

    return $query->execute();
}

function update_cupboard(
    $cupboard_public_id,
    $new_name,
    $new_description
) {
    global $PDO;

    $sql = "UPDATE cupboards 
        INNER JOIN accounts 
        ON cupboards.owner_id = accounts.id 
        SET cupboards.name = :new_name,
            cupboards.description = :new_description
        WHERE cupboards.public_id = :id 
        AND cupboards.owner_id = :owner_id;";
    $query = $PDO->prepare($sql);

    $query->bindValue(":new_name", $new_name);
    $query->bindValue(":new_description", $new_description);
    $query->bindValue(":id", $cupboard_public_id);
    $query->bindValue(":owner_id", get_user_info_from_session_id("id"));

    return $query->execute();
}

function clean_old_reset_tokens()
{
    global $PDO;

    $sql = "DELETE FROM reset_password WHERE token_eol < CURRENT_TIMESTAMP();";
    $query = $PDO->prepare($sql);
    return $query->execute();
}

function is_reset_token_valid($token)
{
    global $PDO;
    $sql = "SELECT * FROM reset_password WHERE token = :token;";
    $query = $PDO->prepare($sql);

    $query->bindValue(":token", $token);

    if ($query->execute()) {
        foreach ($query as $row) {
            return $row;
        }
    }

    return false;
}

function delete_reset_token($token)
{
    global $PDO;
    $sql = "DELETE FROM reset_password WHERE token = :token;";

    $query = $PDO->prepare($sql);
    $query->bindValue(":token", $token);

    return $query->execute();
}

function get_user_id_from_reset_token($token)
{
    global $PDO;

    $sql = "SELECT accounts.id AS id FROM accounts INNER JOIN reset_password
        ON accounts.id = reset_password.user_id WHERE token = :token;";

    $query = $PDO->prepare($sql);
    $query->bindValue(":token", $token);
    if ($query->execute()) {
        if ($row = $query->fetch()) {
            return $row["id"];
        }
    }
    return false;
}

function get_token_count_from_email($email)
{
    global $PDO;

    $sql = "SELECT token FROM reset_password 
        INNER JOIN accounts ON reset_password.user_id = accounts.id 
        WHERE accounts.email = :email;";

    $query = $PDO->prepare($sql);
    $query->bindValue(":email", $email);
    if ($query->execute()) {
        return $query->rowCount();
    }
    return false;
}

function generate_reset_password_token($email)
{
    global $PDO, $MAX_RESET_TOKEN_LIFE, $MAX_TOKENS;
    $user_id = get_user_id_from_email($email);
    if (
        is_numeric($user_id)
        && get_token_count_from_email($email) < $MAX_TOKENS
    ) {
        $random_token = generate_random_string();
        $sql = "INSERT INTO reset_password(token, token_eol, user_id) 
            VALUES(:token, :token_eol, :user_id);";
        $query = $PDO->prepare($sql);
        $query->bindValue(":token", $random_token);
        $query->bindValue(
            ":token_eol",
            date('Y-m-d H:i:s', strtotime("now + $MAX_RESET_TOKEN_LIFE seconds")),
            PDO::PARAM_STR
        );
        $query->bindValue(":user_id", $user_id);
        if ($query->execute()) {
            return $random_token;
        }
    }
    return false;
}

function get_email_message($link, $first_name)
{
    global $MAX_RESET_TOKEN_LIFE, $MAX_TOKENS;
    $email = "Hey $first_name,<br>";
    $email .= "<br>";
    $email .= "Looks like you asked for a password reset, if so please click the link below (under " . $MAX_RESET_TOKEN_LIFE / 60 . " minutes from now) and reset you password on our website:<br>";
    $email .= $link . "<br>";
    $email .= "<br>";
    $email .= "If it wasn't you who requested this, you can just ignore this message, and nothing will be done.<br>";
    $email .= "<br>";
    $email .= "Please remember that each link expires after " . $MAX_RESET_TOKEN_LIFE / 60 . " minutes, so this one will expire exactly at " . date('Y-m-d H:i:s', strtotime("now + $MAX_RESET_TOKEN_LIFE seconds")) . ".<br>";
    $email .= "You can generate $MAX_TOKENS at maximum. So if you ever want to generate one more after this limit, you'd have to wait for the previous links to be deactivated.<br>";
    $email .= "<br>";
    $email .= "Thanks,<br>";
    $email .= "The Food Inventory system";
    return ["html" => $email, "alt" => str_replace("<br>", "\n", $email)];
}

function get_user_info_from_email($email)
{
    global $PDO;

    $sql = "SELECT * FROM accounts WHERE email = :email;";

    $query = $PDO->prepare($sql);
    $query->bindValue(":email", $email);

    if ($query->execute()) {
        foreach ($query as $row) {
            return $row;
        }
    }
    return false;
}