function emailcheck()
{
$dbc = connectToDB("leeawg");
$join_name = $_POST['name'];
$join_email = $_POST['email'];
$join_username = $_POST['username'];
$join_password = $_POST['password'];
$join_securepw = sha1($join_password);
$join_age = $_POST['age'];
$q_emailCheck = "SELECT email FROM account WHERE email = '{$join_email}';";
$q_usernameCheck = "SELECT user_id FROM account WHERE user_id = '{$join_username}';";
$emailCheck_result = performQuery($dbc, $q_emailCheck);
$emailCheck_duplicate = mysqli_fetch_array($emailCheck_result, MYSQLI_ASSOC);
$usernameCheck_result = performQuery($dbc, $q_usernameCheck);
$usernameCheck_duplicate = mysqli_fetch_array($usernameCheck_result, MYSQLI_ASSOC);
if (mysqli_num_rows($usernameCheck_result) == 0 && mysqli_num_rows($emailCheck_result) == 0) {
//echo "no duplicate :)";
$query = "INSERT INTO account (user_id,password,name,age,email)\n\t\tVALUES ( '{$join_username}', '{$join_securepw}', '{$join_name}', '{$join_age}', '{$join_email}' )";
insert($dbc, $query);
}
if (mysqli_num_rows($emailCheck_result) > 0) {
errorform('email');
}
if (mysqli_num_rows($usernameCheck_result) > 0) {
errorform('username');
}
}
function changePass($user, $password, $password2)
{
if (empty($user) || empty($password) || empty($password2)) {
//empty username and password
return false;
}
if ($password != $password2) {
echo "the passwords didn't match ";
return false;
}
$conn = connectToDB();
if (!$conn) {
echo "conn failure ";
return false;
}
$stmnt2 = $conn->prepare("SELECT * FROM USERS2 WHERE USER_UID = ?;");
$stmnt2->bind_param('s', $user);
$stmnt2->execute();
$stmnt2->store_result();
$amount = $stmnt2->num_rows;
if ($amount == 0) {
echo "user does not exists ";
return false;
}
$stmnt2->close();
$stmnt = $conn->prepare("UPDATE USERS2 SET USER_PWDHSH=?, USER_PWDSALT= ? WHERE USER_UID = ?;");
$salt = file_get_contents('/dev/urandom', false, null, 0, 64);
$options = array('salt' => $salt);
$phash = crypt($password, $salt);
$stmnt->bind_param('sss', $phash, $salt, $user);
$stmnt->execute();
$stmnt->close();
$conn->close();
return true;
}
function getQuestions($sid, $type)
{
// This file looks up all questions associated with a given session. It's designed
// for use with the student home page, so its only parameter is the session ID,
// and it doesn't support any sorting or filtering of the data.
$db_conn = connectToDB();
// Since we want data for the autocomplete box, we want to get
// all questions and feedback in the database.
$rows = array();
if ($type == 'Q') {
// Query Question and fetch results
$query = sprintf("SELECT * FROM Question WHERE sid = %d", $sid);
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'answered' => $r["answered"], 'type' => 'Q');
}
} elseif ($type == 'F') {
// Query Feedback and fetch results
$query = sprintf("SELECT * FROM Feedback WHERE sid = %d", $sid);
$results = mysql_query($query, $db_conn);
if (!$results) {
die("Error: " . mysql_error($db_conn));
}
while ($r = mysql_fetch_assoc($results)) {
$rows[] = array('text' => $r["text"], 'votes' => $r["numvotes"], 'isread' => $r["isread"], 'type' => 'F');
}
}
mysql_close($db_conn);
return $rows;
}
function newAcqua($username, $fname, $lname)
{
if (empty($username) | empty($fname) | empty($lname)) {
echo "one or more paramters missing ";
return false;
}
$conn = connectToDB();
if (!$conn) {
echo "conn failure ";
return false;
}
$stmnt2 = $conn->prepare("SELECT * FROM ACQUAINTANCE WHERE ACQUAINTANCE_UID = ?;");
$stmnt2->bind_param('s', $username);
$stmnt2->execute();
$stmnt2->store_result();
$amount = $stmnt2->num_rows;
if ($amount >= 1) {
echo "Acquaintance already exists ";
return false;
}
$stmnt2->close();
$stmnt = $conn->prepare("INSERT INTO ACQUAINTANCE(ACQUAINTANCE_UID, ACQUAINTANCE_FNAME, ACQUAINTANCE_LNAME, PICTURE_SET) VALUES(?,?,?,'/SECS/home/s/scnolton/facePics/{$username}')");
$stmnt->bind_param('sss', $username, $fname, $lname);
$stmnt->execute();
$stmnt->close();
$conn->close();
return true;
}
function deleteAcqu($acquId)
{
if (empty($acquId)) {
//empty username and password
echo "Acquaintance Not Found ";
return false;
}
$conn = connectToDB();
if (!$conn) {
echo "conn failure ";
return false;
}
$stmnt3 = $conn->prepare("SELECT * FROM ACQUAINTANCE WHERE ACQUAINTANCE_UID = ?;");
$stmnt3->bind_param('s', $aacquId);
$stmnt3->execute();
$stmnt3->store_result();
$amount = $stmnt3->num_rows;
if ($amount == 0) {
echo "Acquaintance does not exist ";
return false;
}
$stmnt3->close();
$stmnt = $conn->prepare("DELETE FROM RELATIONSHIP WHERE ACQUAINTANCE_UID = ?;");
$stmnt->bind_param('s', $acquId);
$stmnt->execute();
$stmnt->close();
$stmnt2 = $conn->prepare("DELETE FROM ACQUAINTANCE WHERE ACQUAINTANCE_UID = ?;");
$stmnt2->bind_param('s', $acquId);
$stmnt2->execute();
$stmnt2->close();
$conn->close();
$dir = "/var/www/html/facePics/" . $acquId;
exec("rm -r {$dir}");
return true;
}
function printAllFunds()
{
$conn = connectToDB();
$sql = "SELECT * FROM ListOfFunds";
$result = $conn->query($sql);
echo "<table>";
echo "<tr><th>Users</th><th>Fund Name</th><th>Activity</th><th>Fund</th><th>Function</th><th>Cost Center</th> <th>Project Code</th> <th>Balance</th> <th>As of</th> <th> Active </th></tr>";
if ($result->num_rows > 0) {
// Output data of each row
while ($row = $result->fetch_assoc()) {
echo "<tr><td><a href=\".\\funds.php?type=FID&FID=" . $row["FID"] . "\">" . $row["Users"] . "</a></td><td>" . $row["FundName"] . "</td><td>" . $row["Activity"] . "</td>";
echo "<td>" . $row["Fund"] . "</td>";
echo "<td>" . $row["Function"] . "</td>";
echo "<td>" . $row["CostCenter"] . "</td>";
echo "<td>" . $row["ProjectCode"] . "</td>";
echo "<td>" . $row["Balance"] . "</td>";
echo "<td>" . $row["BalanceAsOf"] . "</td>";
if ($row["Active"] == 1) {
echo "<td>" . "Yes" . "</td>";
} else {
echo "<td>" . "No" . "</td>";
}
}
} else {
echo "0 results in Funds";
}
$conn->close();
}
function newUser($username, $password, $password2, $fname, $lname, $email)
{
if (empty($username) || empty($password) || empty($password2) || empty($fname) || empty($lname) || empty($email)) {
echo "one of the fields was blank ";
return false;
}
if ($password != $password2) {
echo "The 2 passwords didn't match ";
return false;
}
$conn = connectToDB();
if (!$conn) {
echo "conn failure ";
return false;
}
$stmnt2 = $conn->prepare("SELECT * FROM USERS2 WHERE USER_UID = ?;");
$stmnt2->bind_param('s', $username);
$stmnt2->execute();
$stmnt2->store_result();
$amount = $stmnt2->num_rows;
if ($amount >= 1) {
echo "user already exists ";
return false;
}
$stmnt2->close();
$stmnt = $conn->prepare("INSERT INTO USERS2(USER_UID,USER_PWDHSH,USER_PWDSALT,USER_FNAME,USER_LNAME, USER_EMAIL, VERIFYED) VALUES(?,?,?,?,?,?,1)");
$salt = file_get_contents('/dev/urandom', false, null, 0, 64);
$options = array('salt' => $salt);
$phash = crypt($password, $salt);
$stmnt->bind_param('ssssss', $username, $phash, $salt, $fname, $lname, $email);
$stmnt->execute();
$stmnt->close();
$conn->close();
return true;
}
function nm_ket($kode)
{
$link = connectToDB();
$data = mysql_query("select ket_unit_kerja from tb_unitkerja where left(kdunit,3)='{$kode}'", $link);
$rdata = mysql_fetch_array($data);
$result = trim($rdata['ket_unit_kerja']);
return $result;
}
function toSafeString($str)
{
$mysqli = connectToDB();
$str = $mysqli->real_escape_string($str);
$mysqli->close();
$str = htmlentities($str, ENT_QUOTES, "utf-8");
$str = trim($str);
return $str;
}
function setTagPrmpt($category, $tag, $prompt)
{
sanitizeIn($category);
sanitizeIn($tag);
sanitizeIn($prompt);
$conn = connectToDB();
$sql = "UPDATE `Tags` SET TEntryAdvice='" . $prompt . "' WHERE CName='" . $cat . "' AND TName='" . $tag . "'";
CheckedQuery($sql, $conn);
$conn->close();
}
function getData()
{
if (!$this->isLoggedIn()) {
return NULL;
}
$conn = connectToDB();
$user = GetSingleDbValue("SELECT * FROM `Users` WHERE `UserID`='" . $this->userID . "'", $conn);
$conn->close();
return $user;
}
function checklogin($name, $passwd)
{
$dbc = connectToDB("jed");
$encodepw = sha1($passwd);
$result = performQuery($dbc, "select * FROM pwdemo where name='{$name}' and pass='******'");
$matches = mysqli_num_rows($result);
mysqli_free_result($result);
disconnectFromDB($dbc);
return $matches == 1;
}
function sanitizeIn(&$data)
{
//Sanitizes a string for safe insertion into a mysqli query
$conn = connectToDB();
$data = mysqli_real_escape_string($conn, $data);
$conn->close();
//The previous function supposedly misses % and _ which do have special meaning for LIKE clauses, so escape those manually
$data = addcslashes($data, '%_');
return $data;
}
function update_page_content_db($page_name, $content, $uid)
{
$con = connectToDB();
if ($con) {
$sql = "UPDATE `pagecontent` SET \n\t\t\t\t`pagecontent`='{$content}',\n\t\t\t\t`adminid`={$uid},\n\t\t\t\t`editeddate` = NOW( )\n\t\t\t\tWHERE `pagename`='{$page_name}';";
$result = desql($sql);
breakCon($con);
}
return $result;
}
function predict($file, $user)
{
$guess = exec("/var/www/facerec/faces predict " . $file . " " . $user, $output);
$guess = $output[0];
$conn = connectToDB();
$sql = "SELECT ACQUAINTANCE_FNAME, ACQUAINTANCE_LNAME, GENDER, RELATION, DESCRIPTION, ACQUAINTANCE_UID FROM RELATIONSHIP NATURAL JOIN ACQUAINTANCE WHERE USER_UID = '" . $user . "' AND REL_ID=" . $guess . ";";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$row["DISTANCE"] = $output[1];
echo json_encode($row);
}
function insertDataOfUser($email, $pass)
{
$db = connectToDB();
$insert = $db->prepare('INSERT INTO users(email, pass) VALUES(?, ?)');
$insert->bindParam(1, $email);
$insert->bindParam(2, $pass);
if ($insert->execute()) {
return;
} else {
header("Location: /404.html");
}
}
function generateMarkers()
{
$dbc = connectToDB();
$query = "SELECT * FROM location";
$result = performQuery($dbc, $query);
$results = array();
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$results[] = $row;
}
header('Content-type: application/json');
echo json_encode(array('results' => $results));
}
public function checkLogin($username, $password)
{
$username = toSafeString($username);
$password = toSafeString($password);
$mysqli = connectToDB();
$result = $mysqli->query("CALL sp_login('{$username}','{$password}');") or die("SELECT query login error");
$mysqli->close();
if ($result->fetch_assoc()) {
return true;
} else {
return false;
}
}
function selectDataOfUser($email, $pass)
{
$db = connectToDB();
$query = $db->prepare('SELECT email, pass
FROM users
WHERE email = ? AND pass = ?');
if ($query->execute(array($email, $pass))) {
$result = $query->fetch(PDO::FETCH_ASSOC);
return $result;
} else {
header("Location: /404.html");
}
}
function executeQuery($sql)
{
$pdo = connectToDB();
try {
$result = $pdo->query($sql);
return $result;
} catch (PDOException $e) {
echo $e->getMessage();
//$error = 'Unable to update to the database server.';
//include 'error.html.php';
exit;
}
}
function DisplayFullText()
{
$conn = connectToDB();
$FeedbackID = $_GET['FeedbackID'];
SanitizeIn($FeedbackID);
$sql = "SELECT `Text` FROM `Feedbacks` WHERE `FeedbackID`='" . $FeedbackID . "'";
$feedback = GetSingleDbValue($sql, $conn);
if (!$feedback) {
echo 'No such feedback found.';
} else {
echo SanitizeOut($feedback['Text']);
}
$conn->close();
}
function get_all_tags_db($class)
{
$con = connectToDB();
if ($con) {
$sql = "SELECT DISTINCT `tag` FROM `techcsondemand`.`PostCollection{$class}` ORDER BY `tag`;";
$rtn = array();
$result = desql($sql);
while ($row = mysql_fetch_row($result)) {
$rtn[] = $row[0];
}
breakCon($con);
}
return $rtn;
}
function checkPw($mailpw)
{
$encodedPw = sha1($mailpw);
$query = "SELECT * FROM `club` where password='******'";
$dbc = connectToDB();
$result = performQuery($dbc, $query);
$numRows = mysqli_num_rows($result);
if ($numRows > 0) {
echo "Password exists in system. <br/>";
} else {
echo "You have entered an incorrect password. Please try again.";
}
return $numRows;
}
function setProfile($id, $FirstName, $MiddleName, $LastName, $Email, $Website, $Address, $Phone)
{
sanitizeIn($FirstName);
sanitizeIn($MiddleName);
sanitizeIn($LastName);
sanitizeIn($Email);
sanitizeIn($Website);
sanitizeIn($Address);
sanitizeIn($Phone);
$conn = connectToDB();
$sql = "UPDATE `Users` SET FirstName='" . $FirstName . "', MiddleName='" . $MiddleName . "', LastName='" . $LastName . "', EmailAddress='" . $Email . "', Website='" . $Website . "', MailingAddress='" . $Address . "', Phone='" . $Phone . "' WHERE UserID=" . $id;
CheckedQuery($sql, $conn);
$conn->close();
}
function getPageData($pageName)
{
$mysqliLink = connectToDB();
$query = $mysqliLink->query("SELECT * FROM page_data WHERE page_name = '{$pageName}'");
$title = "";
$desc = "";
if ($row = $query->fetch_object()) {
$title = $row->page_title;
$desc = $row->page_desc;
}
$html = '<h1>' . $title . '</h1>';
$html .= '<p>' . $desc . '</p>';
echo $html;
}
function checkFirstTime($username, $passwd)
{
$dbc = connectToDB("leeawg");
$encodepw = sha1($passwd);
$query = "select * FROM account where user_id='{$username}' and password='******'";
$result = performQuery($dbc, $query);
$extractedSQL = mysqli_fetch_assoc($result);
$firstTimeStatus = $extractedSQL['first_time'];
disconnectFromDB($dbc, $result);
if ($firstTimeStatus == 1) {
return true;
} else {
return false;
}
}
function GetMySubscriptions()
{
$user = getUser();
$conn = connectToDB();
$sql = "SELECT `TName` FROM `Subscriptions` WHERE `UserID`='" . $user->userID . "'";
$tags = CheckedQuery($sql, $conn);
$retVal = array();
if ($tags) {
while ($tag = $tags->fetch_assoc()) {
$retVal[SanitizeOut($tag['TName'])] = true;
}
}
$conn->close();
return $retVal;
}
function addUser($new_username, $new_user_password, $new_user_email)
{
global $host, $username, $password, $dbName, $user_table, $registered_user_table, $question_table;
global $answer_table, $user_answer, $user_post;
connectToDB($username, $password, $host, $dbName);
$countQuery = "SELECT COUNT(id) FROM {$registered_user_table}";
$count = mysql_fetch_array(mysql_query($countQuery))[0];
//we fetch an array of counts for each column and return the count of column 0
$addQuestionQuery = "INSERT INTO {$user_table} (UID, password, userType, sessionGeo, sessionIP) VALUES ('{$new_username}', '{$new_user_password}', 0, 0, 0)";
$status = mysql_query($addQuestionQuery);
if ($status == false) {
// if the query failed, for whatever reason, let us know.
return false;
}
return true;
}
function postMessage()
{
/* require the message the parameter */
if (isset($_GET['message']) && isset($_GET['location_id'])) {
$message = $_GET['message'];
$location_id = isset($_GET['location_id']) ? $_GET['location_id'] : 1;
//default is 1
/* connect to the database */
$dbc = connectToDB();
/* insert the message into the message table query*/
$query = "INSERT INTO message (comment, location_id) VALUES (\"{$message}\", {$location_id})";
$result = performQuery($dbc, $query);
echo "postMessage works yay";
return header('status: 200');
}
}
function viewMessage()
{
if (isset($_GET['location_id'])) {
$location_id = $_GET['location_id'];
$db = connectToDB();
/* query the list of messages*/
$query = "SELECT * from message where message.location_id = {$location_id}";
$result = performQuery($db, $query);
/* create array of messages */
$messages = array();
while ($message = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$messages = array('comment' => $message);
}
header('Content-type: application/json');
echo json_encode($messages);
}
}
