function getUserProfile($app, $email) { $query = "SELECT name, major, interests FROM accounts INNER JOIN users ON accounts.id=users.account_id WHERE email=?"; $SQLparams = array($email); $link = mysqli_connect(HOST, USER, PASSWORD, DATABASE); $result = mysqli_prepared_query($app, $link, $query, "s", $SQLparams); mysqli_close($link); //var_dump($result); return $result[0]; //protection again extra db matches }
<?php $app->get('/api/recommendation', function ($request, $response, $args) { $this->logger->info("GET /api/recommendation"); $params = $request->getQueryParams(); $token = $params['token']; $this->logger->info($token); $major = get_major_from_key($this, $token); $this->logger->info($major); $query = "SELECT movie, AVG(stars) as average FROM ratings INNER JOIN users ON ratings.user=users.account_id WHERE users.major=? GROUP BY movie ORDER BY average DESC LIMIT 3"; $SQLparams = array($major); $link = mysqli_connect(HOST, USER, PASSWORD, DATABASE); $result = mysqli_prepared_query($this, $link, $query, "s", $SQLparams); mysqli_close($link); $data = $result[0]; return $response->withJson($data); });
<?php $app->post('/api/account', function ($request, $response, $args) { $params = $request->getParsedBody(); $email = $params["email"]; $status = $params["status"]; $this->logger->info("POST /api/account"); $this->logger->info("Setting " . $email . " to " . $status); $query = "UPDATE accounts SET status=? WHERE email=?"; $SQLparams = array($status, $email); $link = mysqli_connect(HOST, USER, PASSWORD, DATABASE); $result = mysqli_prepared_query($this, $link, $query, "ss", $SQLparams); mysqli_close($link); return $response->withHeader('Content-Type', 'application/json')->write(json_encode(array("status" => $status))); }); $app->get('/api/account', function ($request, $response, $args) { $this->logger->info("GET /api/account"); $query = "SELECT email, name, status FROM accounts ORDER BY email"; $link = mysqli_connect(HOST, USER, PASSWORD, DATABASE); $result = mysqli_prepared_query($this, $link, $query); mysqli_close($link); return $response->withHeader('Content-Type', 'application/json')->write(json_encode($result)); });
public function getArtifactCodes($uri, $head, $assertion, $provenance, $pubinfo, $page, $begin_timestamp, $end_timestamp, $order, $debug) { // BUILDS A QUERY LIKE: SELECT artifactCode FROM uris WHERE uri = ? AND sectionID IN (x) LIMIT 1000 $types = ""; $params = $uri; $query = "SELECT uris.artifactCode FROM uris"; if ($begin_timestamp || $end_timestamp || $order == 1 || $order == 2) { $query .= " LEFT JOIN nanopubs ON nanopubs.artifactCode = uris.artifactCode"; } $query .= " WHERE uri IN ("; foreach ($uri as $searchuri) { $query .= "?,"; $types .= "s"; } $query = rtrim($query, ','); $query .= ")"; if ($head == "off" && $assertion == "off" && $provenance == "off" && $pubinfo == "off") { // ALL OFF } else { $query .= " AND sectionID IN ("; if ($head == "on") { $query .= URIs::$SECTION_HEAD . ","; } if ($assertion == "on") { $query .= URIs::$SECTION_ASSERTION . ","; } if ($provenance == "on") { $query .= URIs::$SECTION_PROVENANCE . ","; } if ($pubinfo == "on") { $query .= URIs::$SECTION_PUBINFO . ","; } $query = rtrim($query, ','); $query .= ")"; } if ($begin_timestamp) { $query .= " AND timestamp > ?"; $types .= "d"; $params[] = $begin_timestamp; } if ($end_timestamp) { $query .= " AND timestamp < ?"; $types .= "d"; $params[] = $end_timestamp; } $query .= " GROUP BY artifactCode"; $query .= " HAVING COUNT(*) >= " . count($uri); if ($order == 1) { $query .= " ORDER BY timestamp DESC"; } else { if ($order == 2) { $query .= " ORDER BY timestamp ASC"; } } if ($page != 0) { $query .= " LIMIT " . URIs::$PAGE_SIZE; $query .= " OFFSET " . ($page - 1) * URIs::$PAGE_SIZE; } $data = mysqli_prepared_query($this->_conn, $query, $types, $params); $result = array(); if ($data) { foreach ($data as $item) { $result[] = $item['artifactCode']; } } if ($debug == true) { $result[] = $query; $result = array_merge($result, $params); $result[] = $data; } return json_encode($result); }
public function getIndexes() { $query = "SELECT title, COUNT( title ) AS indexCount, SUM( children ) AS contentCount\n\t\t\t\t\tFROM indexes\n\t\t\t\t\tGROUP BY title"; $result = mysqli_prepared_query($this->_conn, $query); return $result; }
$query = addConjunction($query, $filters); $query = $query . " email=?"; $SQLformat = $SQLformat . "s"; $SQLparams[] = $email; } if (isset($params['major'])) { $major = $params['major']; $filters += 1; $query = addConjunction($query, $filters); $query = $query . " major=?"; $SQLformat = $SQLformat . "s"; $SQLparams[] = $major; } if (isset($params['movie'])) { $movie = $params['movie']; $filters += 1; $query = addConjunction($query, $filters); $query = $query . " movie=?"; $SQLformat = $SQLformat . "s"; $SQLparams[] = $movie; } $this->logger->info("SQL Query: " . $query); $link = mysqli_connect(HOST, USER, PASSWORD, DATABASE); if ($filters == 0) { $result = mysqli_prepared_query($this, $link, $query); } else { $result = mysqli_prepared_query($this, $link, $query, $SQLformat, $SQLparams); } mysqli_close($link); return $response->withHeader('Content-Type', 'application/json')->write(json_encode($result)); });
function register($app, $email, $password, $accountType) { $hash = password_hash($password, PASSWORD_DEFAULT); $query = "INSERT INTO accounts (email, hash) VALUES (?,?)"; $SQLparams = array($email, $hash); $link = mysqli_connect(HOST, USER, PASSWORD, DATABASE); mysqli_prepared_query($app, $link, $query, "ss", $SQLparams); $accountID = mysqli_insert_id($link); if ($accountType == "admin") { $app->logger->info("Registering as admin"); $query = "INSERT INTO admins(account_id) VALUES (" . $accountID . ")"; mysqli_query($link, $query); } else { if ($accountType == "user") { $app->logger->info("Registering as user"); $query = "INSERT INTO users(account_id) VALUES (" . $accountID . ")"; mysqli_query($link, $query); } else { return array("login" => False, "error" => "Registration Failed"); } } mysqli_close($link); $data['token'] = generate_session_key($this, $email); $data['login'] = True; $data['accountType'] = $accountType; return $data; }