public static function createUser($username, $email, $password) { $datbase = new Database(); $datbase->openConnection(); mysqli_query($datbase->getConnection(), "INSERT INTO `Users`(`Username`,`Email`,`Password`) VALUES('" . mysqli_real_escape_string($datbase->getConnection(), $username) . "','" . mysqli_real_escape_string($datbase->getConnection(), $email) . "','" . mysqli_real_escape_string($datbase->getConnection(), hash("sha256", $password)) . "')"); $datbase->closeConnection(); }
public function delete($id) { $database = Database::openConnection(); $database->deleteById("todo", $id); if ($database->countRows() !== 1) { throw new Exception("Couldn't delete todo"); } }
public function __construct($db, $debug = false) { $this->conn = Database::openConnection($db); //$this->initClassfromTable(); $this->isDebug = \System\Entrance::config('IS_DB_DEBUG'); //$this->initTable(); //$this->initSqlStmt(); }
public static function DeleteImage($ownerid, $imageid) { $database = new Database(); $database->openConnection(); $result = mysqli_query($database->getConnection(), "SELECT `ImagePath` FROM `Images` WHERE `id`='" . mysqli_real_escape_string($database->getConnection(), $imageid) . "' AND `OwnerUserID`='" . mysqli_real_escape_string($database->getConnection(), $ownerid) . "' LIMIT 1"); if (mysqli_num_rows($result) == 1) { mysqli_query($database->getConnection(), "DELETE FROM `Images` WHERE `id`='" . mysqli_real_escape_string($database->getConnection(), $imageid) . "' AND `OwnerUserID`='" . mysqli_real_escape_string($database->getConnection(), $ownerid) . "' LIMIT 1"); unlink(__DIR__ . '/../public/' . mysqli_fetch_array($result)['ImagePath']); } $database->closeConnection(); }
/** * get pagination object by executing COUNT(*) query. * * @access public * @param string $table * @param string $options * @param array $values array of data * @param integer $pageNum * @param integer $extraOffset check comment class * @return Pagination */ public static function pagination($table, $options, $values, $pageNum, $extraOffset = 0) { $database = Database::openConnection(); $query = "SELECT COUNT(*) AS count FROM {$table} "; $query .= $options; $database->prepare($query); $database->execute($values); $totalCount = $database->fetchAssociative()["count"]; $extraOffset = (int) $extraOffset > $totalCount ? 0 : (int) $extraOffset; return new Pagination((int) $pageNum, $totalCount - $extraOffset); }
public static function uploadFile($userid, $image) { $type = $image['image']['type']; if ($type == "image/png" || $type == "image/jpg" || $type == "image/jpeg") { if (move_uploaded_file($image['image']['tmp_name'], __DIR__ . "/../public/images/" . $image['image']['name'])) { $database = new Database(); $database->openConnection(); mysqli_query($database->getConnection(), "INSERT INTO `Images`(`OwnerUserID`,`ImagePath`) VALUES('" . $_SESSION['User']->getID() . "','" . mysqli_real_escape_string($database->getConnection(), "images/" . $image['image']['name']) . "')"); $database->closeConnection(); } } }
public static function LoginUser($email, $password) { $datbase = new Database(); $datbase->openConnection(); $results = mysqli_query($datbase->getConnection(), "SELECT `id`,`Email`,`Password`,`IsAdmin` FROM `Users` WHERE `Email`='" . mysqli_real_escape_string($datbase->getConnection(), $email) . "' AND `Password`='" . mysqli_real_escape_string($datbase->getConnection(), hash("sha256", $password)) . "' LIMIT 1"); $resultsarray = mysqli_fetch_array($results); $datbase->closeConnection(); if (mysqli_num_rows($results) == 1) { return new User($resultsarray['id'], $resultsarray['Email'], $resultsarray['Password'], $resultsarray['IsAdmin'] == 1 ? true : false); } else { return null; } }
function dbLogin($username, $password) { $db = new Database(); $mysqli = $db->openConnection(); $sql = "SELECT salt, hash FROM users WHERE email = ?"; $stmt = $mysqli->prepare($sql); $hash_db = NULL; $salt_db = NULL; if ($stmt->bind_param('s', $username)) { if ($stmt->execute()) { $stmt->bind_result($salt_db, $hash_db); if (!$stmt->fetch()) { return false; } else { return existingUsername($salt_db, $hash_db, $password, $username); } $stmt->free_result(); } $db->closeConnection($mysqli); } return false; }
/** * counting the number of comments of a post. * * @access public * @static static method * @param string $postId * @return integer number of comments * */ public static function countComments($postId) { $database = Database::openConnection(); $database->prepare("SELECT COUNT(*) AS count FROM comments WHERE post_id = :post_id"); $database->bindValue(":post_id", $postId); $database->execute(); return (int) $database->fetchAssociative()["count"]; }
<?php session_start(); include_once "testlogin.php"; include_once "database.php"; redirectIfNotLoggedIn("https://127.0.0.1/"); echo "<html>"; echo "<body>"; echo "<h1 style=\"text-align: right; color: red;\">Username = "******"<h1/>"; $database = new Database(); $mysqli = $database->openConnection(); $sql = "SELECT * FROM items WHERE itemName = ?"; $stmt = $mysqli->prepare($sql); $search = $_POST['searchField']; echo "<div>"; echo "<table style='width:20%' id = 'itemTable'>"; if ($stmt->bind_param('s', $search)) { if ($stmt->execute()) { $itemId = NULL; $itemName = NULL; $cost = NULL; $stmt->bind_result($itemId, $itemName, $cost); print "<tr><th>Name</th><th>cost</th></tr>"; if ($stmt->fetch()) { print "</th><th>" . $itemName . "</th><th>" . $cost . "</th></tr>"; } else { print " 0 results for search: " . $search; } $stmt->free_result(); } }
public function __construct($db = 'DB_MASTER', $debug = false) { $this->db = $db; $this->conn = Database::openConnection($db); $this->isDebug = \System\Entrance::config('IS_DB_DEBUG'); }
/** * update Post * * @access public * @static static method * @param string $postId * @param string $title * @param string $content * @return array Array of the updated post * @throws Exception If post couldn't be updated * */ public function update($postId, $title, $content) { $validation = new Validation(); if (!$validation->validate(['Title' => [$title, "required|minLen(2)|maxLen(60)"], 'Content' => [$content, "required|minLen(4)|maxLen(1800)"]])) { $this->errors = $validation->errors(); return false; } $database = Database::openConnection(); $query = "UPDATE posts SET title = :title, content = :content WHERE id = :id LIMIT 1"; $database->prepare($query); $database->bindValue(':title', $title); $database->bindValue(':content', $content); $database->bindValue(':id', $postId); $result = $database->execute(); if (!$result) { throw new Exception("Couldn't update post of ID: " . $postId); } $post = $this->getById($postId); return $post; }
/** * deletes file. * This method overrides the deleteById() method in Model class. * * @access public * @param array $id * @throws Exception If failed to delete the file * */ public function deleteById($id) { $database = Database::openConnection(); $database->getById("files", $id); $file = $database->fetchAssociative(); //start a transaction to guarantee the file will be deleted from both; database and filesystem $database->beginTransaction(); $database->deleteById("files", $id); if ($database->countRows() !== 1) { $database->rollBack(); throw new Exception("Couldn't delete file"); } $basename = $file["hashed_filename"] . "." . $file["extension"]; Uploader::deleteFile(APP . "uploads/" . $basename); $database->commit(); }
/** * Reset the email verification token. * Resetting the token depends on whether the email token was valid or not. * * @access private * @param integer $userId * @param boolean $isValid * @throws Exception If couldn't reset email verification token */ public function resetEmailVerificationToken($userId, $isValid) { $database = Database::openConnection(); if ($isValid) { $query = "UPDATE users SET email_token = NULL, " . "email_last_verification = NULL, is_email_activated = 1 " . "WHERE id = :id LIMIT 1"; } else { $query = "DELETE FROM users WHERE id = :id"; } $database->prepare($query); $database->bindValue(':id', $userId); $result = $database->execute(); if (!$result) { throw new Exception("Couldn't reset email verification token"); } }
} $_SESSION[RegCodes::USED_USERNAME] = 2; return false; } function addUser($mysqli, $email, $pwd) { $crypto = new Crypto(); $salt = $crypto->generateSalt(10); $hash = $crypto->generateHash($pwd, $salt); $sql = "INSERT INTO users(email, hash, salt, nbrAttempts) \n\t\t\tVALUES('" . $email . "', '" . $hash . "', '" . $salt . "', '0')"; $mysqli->multi_query($sql); $_SESSION['isLoggedIn'] = 1; $_SESSION['username'] = $email; redirect("https://127.0.0.1/searchView.php"); } $token = $_POST['token']; if ($token == session_id()) { $email = $_POST['username']; $pwd = $_POST['password']; $db = new Database(); $mysqli = $db->openConnection(); $usernameAvailable = isUsernameFree($mysqli, $email); if ($usernameAvailable) { addUser($mysqli, $email, $pwd); } else { redirect("https://127.0.0.1/registerView.php"); } $db->closeConnection($mysqli); } else { redirect("https://127.0.0.1/index.php"); }
/** * check if email is unique * This will check if email exists and activated. * * @param string $email * @return bool */ private function emailUnique($email) { $database = Database::openConnection(); // email is unique in the database, So, we can't have more than 2 same emails $database->prepare("SELECT * FROM users WHERE email = :email LIMIT 1"); $database->bindValue(':email', $email); $database->execute(); $user = $database->fetchAssociative(); if ($database->countRows() === 1) { if (!empty($user["is_email_activated"])) { return false; } else { $expiry_time = 24 * 60 * 60; $time_elapsed = time() - $user['email_last_verification']; // If time elapsed exceeded the expiry time, it worth to reset the token, and the email as well. // This indicates the email of $user hasn't been verified, and token is expired. if ($time_elapsed >= $expiry_time) { $login = new Login(); $login->resetEmailVerificationToken($user["id"], false); return true; } else { // TODO check if $email is same as current user's email(not-activated), // then ask the user to verify his email return false; } } } return true; }
/** * Reset Cookie, * resetting is done by updating the database, * and resetting the "auth" cookie in the browser * * @static static method * @param string $userId */ public static function reset($userId) { self::$userId = $userId; self::$token = hash('sha256', mt_rand()); $database = Database::openConnection(); $query = "UPDATE users SET cookie_token = :cookie_token WHERE id = :id"; $database->prepare($query); //generate random hash for cookie token (64 char string) $database->bindValue(":cookie_token", self::$token); $database->bindValue(":id", self::$userId); $result = $database->execute(); if (!$result) { Logger::log("COOKIE", "Couldn't remove cookie from the database for user ID: " . $userId, __FILE__, __LINE__); } //generate cookie string(remember me) //Don't expose the original user id in the cookie, Encrypt It! $cookieFirstPart = Encryption::encrypt(self::$userId) . ':' . self::$token; //$hashedCookie generated from the original user Id, NOT from the encrypted one. self::$hashedCookie = hash('sha256', self::$userId . ':' . self::$token . Config::get('COOKIE_SECRET_KEY')); $authCookie = $cookieFirstPart . ':' . self::$hashedCookie; setcookie('auth', $authCookie, time() + Config::get('COOKIE_EXPIRY'), Config::get('COOKIE_PATH'), Config::get('COOKIE_DOMAIN'), Config::get('COOKIE_SECURE'), Config::get('COOKIE_HTTP')); }
/** * open * * @return Database */ private function open() { return $this->dbc->openConnection(); }
public static function query($query) { $conn = Database::openConnection(); $array = array(); $result = mysqli_query($conn, $query); if ($result) { while (true) { $row = mysqli_fetch_assoc($result); if ($row) { $r = array(); foreach ($row as $k => $v) { $r[$k] = $v; } $array[] = $r; } else { break; } } } else { self::logError(mysqli_error($conn)); } return $array; }
/** * Counting the number of a current model's table. * * @return integer */ public function countAll() { $database = Database::openConnection(); return $database->countAll($this->table); }
/** * update session id in database * * @access public * @static static method * @param string $userId * @param string $sessionId * @return string * */ private static function updateSessionId($userId, $sessionId = null) { $database = Database::openConnection(); $database->prepare("UPDATE users SET session_id = :session_id WHERE id = :id"); $database->bindValue(":session_id", $sessionId); $database->bindValue(":id", $userId); $database->execute(); }
/** * checks if user is owner * * @param array $config * @return bool */ private static function owner($config) { $database = Database::openConnection(); $database->prepare('SELECT * FROM ' . $config["table"] . ' WHERE id = :id AND user_id = :user_id LIMIT 1'); $database->bindValue(':id', (int) $config["id"]); $database->bindValue(':user_id', (int) $config["user_id"]); $database->execute(); return $database->countRows() === 1; }
<?php require_once 'database.inc.php'; require_once "mysql_connect_data.inc.php"; $db = new Database($host, $userName, $password, $database); $db->openConnection(); if (!$db->isConnected()) { header("Location: cannotConnect.html"); exit; } $userId = $_REQUEST['userId']; if (!$db->userExists($userId)) { $db->closeConnection(); header("Location: noSuchUser.html"); exit; } $db->closeConnection(); session_start(); $_SESSION['db'] = $db; $_SESSION['userId'] = $userId; header("Location: booking1.php");
/** * get users data. * Use this method to download users info in database as csv file. * * @access public * @return array */ public function getUsersData() { $database = Database::openConnection(); $database->prepare("SELECT name, role, email, is_email_activated FROM users"); $database->execute(); $users = $database->fetchAllAssociative(); $cols = array("User Name", "Role", "Email", "is Email Activated?"); return ["rows" => $users, "cols" => $cols, "filename" => "users"]; }
/** * Returns an overview about the current system: * 1. counts of newsfeed, posts, files, users * 2. latest updates by using "UNION" * * @access public * @return array * */ public function dashboard() { $database = Database::openConnection(); //1. count $tables = ["newsfeed", "posts", "files", "users"]; $stats = []; foreach ($tables as $table) { $stats[$table] = $database->countAll($table); } //2. latest updates //Using UNION to union the data fetched from different tables. //@see http://www.w3schools.com/sql/sql_union.asp //@see (mikeY) http://stackoverflow.com/questions/6849063/selecting-data-from-two-tables-and-ordering-by-date //Sub Query: In SELECT, The outer SELECT must have alias, like "updates" here. //@see http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias $query = "SELECT * FROM ("; $query .= "SELECT 'newsfeed' AS target, content AS title, date, users.name FROM newsfeed, users WHERE user_id = users.id UNION "; $query .= "SELECT 'posts' AS target, title, date, users.name FROM posts, users WHERE user_id = users.id UNION "; $query .= "SELECT 'files' AS target, filename AS title, date, users.name FROM files, users WHERE user_id = users.id "; $query .= ") AS updates ORDER BY date DESC LIMIT 10"; $database->prepare($query); $database->execute(); $updates = $database->fetchAllAssociative(); $data = array("stats" => $stats, "updates" => $updates); return $data; }
/** * update news feed. * * @param string $newsfeedId * @param string $content * @return array feed created * @throws Exception if feed couldn't be updated */ public function update($newsfeedId, $content) { $validation = new Validation(); if (!$validation->validate(['Content' => [$content, "required|minLen(4)|maxLen(300)"]])) { $this->errors = $validation->errors(); return false; } $database = Database::openConnection(); $query = "UPDATE newsfeed SET content = :content WHERE id = :id LIMIT 1"; $database->prepare($query); $database->bindValue(':content', $content); $database->bindValue(':id', $newsfeedId); $result = $database->execute(); if (!$result) { throw new Exception("Couldn't update newsfeed of ID: " . $newsfeedId); } $feed = $this->getById($newsfeedId); return $feed; }