php-vulnerabilities/database.php

<?php

include_once 'utils.php';

$RESULT_MODE = PDO::FETCH_BOTH;

$host = '127.0.0.1';
$db_user = 'root';
$db_password = 'mysql';

session_start();

function connectToRedis()
{
    $redis_host = '127.0.0.1';
    $redis_port = 6379;

    try {
        $redis = new Redis();
        if ($redis->connect($redis_host, $redis_port)) {
            /* if ($redis_password) { */
            /*     $redis->auth($redis_password); */
            /* } */
            return $redis;
        }
        return false;
    } catch (Exception $e) {
        error_log("Redis connection error: ".$e->getMessage());
        return false;
    }
}

function initializeNewUser()
{
    global $host, $db_user, $db_password;

    $redis = connectToRedis();
    if (!$redis) {
        return false;
    }

    $user_id = generateUUID();

    $redis->hSet('users', $user_id, json_encode([
        'created_at' => time(),
        'status' => 'active'
    ]));

    /* $db_file = "db/$user_id.db"; */
    $connStr = "mysql:host=$host;port=3306;charset=UTF8";
    try { 
        $db = new PDO($connStr, $db_user, $db_password);

        $db->exec("CREATE DATABASE IF NOT EXISTS `$user_id`;");
        $db->exec("USE `$user_id`;");
        error_log("new db was created successfully! $user_id", 0);

        $schema_sql = file_get_contents('db/schema.sql');
        if (!$schema_sql) {
            error_log("Failed to read schema file: $schema_path");
            return false;
        }
        $db->exec($schema_sql);
    }
    catch(PDOException $e) {
        error_log($e, 0);
        throw new Exception($e->getMessage());
    }


    if (!$db) {
        // Cleanup Redis entry if db creation fails
        $redis->hDel('users', $user_id);
        return false;
    }

    return ["user_id" => $user_id];
}

function initializeApp($existing_user_id = null)
{
    if ($existing_user_id) {
        /* $_SESSION["user_id"] = $existing_user_id; */
    } else {
        return initializeNewUser();
    }
}

function getDB($user_id = null)
{
    global $host, $db_user, $db_password;

    if (is_null($user_id)) {
        error_log('user id is null', 0);
        return null;
    }

    error_log("Global vars - host: $host, user: $db_user, password: $db_password", 0);
    try {
        $connStr = "mysql:host=$host;port=3306;charset=UTF8";
        $pdo = new PDO($connStr, $db_user, $db_password);
    } catch(PDOException $e) {
        error_log($e, 0);
        throw new Exception($e->getMessage());
    }

    /* $pdo->exec("CREATE DATABASE IF NOT EXISTS `$user_id`;"); */
    $pdo->exec("USE `$user_id`;");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    return $pdo;
}

function registerUser($username, $email, $password)
{
    $db = getDB($_COOKIE['user_id']);
    $query = "INSERT INTO users(username, email, password) values ('$username', '$email', '$password');";

    $res = $db->exec($query);
    if ($res === false) {
        $errorInfo = $db->errorInfo();
        throw new Exception("MySQL Error:  $errorInfo[2]. (Code: $errorInfo[1])");
    }
    return $res;
}

function loginUser($email, $password)
{
    global $RESULT_MODE;
    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT username, password FROM users WHERE email = '$email';";
    $result = $db->query($query)->fetch($RESULT_MODE);
    if ($result && $result["password"] === $password) {
        return $result['username'];
    } else {
        return null;
    }
}

function getUserById($id)
{
    global $RESULT_MODE;
    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT username, email FROM users WHERE id = $id";

    try {
        $result = $db->query($query);
        return $result->fetch($RESULT_MODE);
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function createProduct($title, $amountInStock)
{
    $db = getDB($_COOKIE['user_id']);
    $query = "INSERT INTO products(title, amount_in_stock) values ('$title', $amountInStock)";

    try {
        error_log($query, 0);
        return $db->exec($query);
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function getProductsByTitle($title)
{
    global $RESULT_MODE;

    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT * FROM products WHERE title like '".$title."%'";

    error_log($query, 0);
    try {
        $result = $db->query($query);
        if ($result === false) {
            $errorInfo = $db->errorInfo();
            throw new Exception($errorInfo[2]);
        }

        $products = [];
        while ($row = $result->fetch($RESULT_MODE)) {
            $products[] = $row;
        }
        $_SESSION['error_message'] = null;
        return $products;
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function getAllProducts()
{
    global $RESULT_MODE;

    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT * FROM products";

    try {
        $result = $db->query($query);
        $products = [];
        while ($row = $result->fetch($RESULT_MODE)) {
            $products[] = $row;
        }

        return $products;
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function deleteProductByTitle($title)
{
    $db = getDB($_COOKIE['user_id']);
    $query = "DELETE FROM products where title='$title'";

    try {
        return $db->exec($query);
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function getOrdersForUser($userId)
{
    global $RESULT_MODE;

    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT * FROM orders WHERE user_id = $userId";

    try {
        $result = $db->query($query);
        if ($result) {
            return $result->fetch($RESULT_MODE);
        }
        return null;
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function getOrdersForProduct($productId)
{
    global $RESULT_MODE;

    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT * FROM orders WHERE product_id = $productId";

    try {
        $result = $db->query($query);
        if ($result) {
            return $result->fetch($RESULT_MODE);
        }
        return null;
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}

function getAllUsers()
{
    global $RESULT_MODE;

    $db = getDB($_COOKIE['user_id']);
    $query = "SELECT id, username, email FROM users";

    try {
        $result = $db->query($query);
        if (!$result) {
            return null;
        }
        $users = [];
        while ($row = $result->fetch($RESULT_MODE)) {
            $users[] = $row;
        }
        return $users;
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}