php-vulnerabilities/database.php

<?php

include_once 'utils.php';

$RESULT_MODE = SQLITE3_BOTH;

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()
{
    $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";
    $db = new SQLite3($db_file);

    $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);

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

    return ["user_id" => $user_id, "db_file" => "db/$user_id.db"];
}

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

function getDB($user_id = null)
{
    if (!is_null($user_id)) {
        $db_file = "db/$user_id.db";
        if (!file_exists($db_file)) {
            return createUserDatabase($user_id);
        }
        return new SQLite3($db_file);
    }
}

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

    return $db->exec($query);
}

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)->fetchArray($RESULT_MODE);
    if ($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->fetchArray($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 {
        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) {
            error_log("{$db->lastErrorCode()}", 0);
            throw new Exception($db->lastErrorMsg());
        }

        $products = [];
        while ($row = $result->fetchArray($RESULT_MODE)) {
            $products[] = $row;
        }
        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->fetchArray($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);
        return $result->fetchArray($RESULT_MODE);
    } 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);
        return $result->fetchArray($RESULT_MODE);
    } catch (Exception $e) {
        $_SESSION['error_message'] = $e->getMessage()."\n";
    }
}