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 deleteAction()
{
$database = new Database();
$stmt = $database->getConnection()->prepare("DELETE FROM Accounts WHERE ID = ?");
$stmt->bind_param("i", $ID);
$ID = $this->_params['accountID'];
$stmt->execute();
$stmt->close();
$database->getConnection()->close();
}
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;
}
}
/**
* Initializes framework object. Sets up Database, Session, and Templates if applicable
*/
public function init($cli = false)
{
$this->isError = false;
$this->smarty = false;
if (Config::get('useDatabase') !== false) {
/**
* Load DB
*/
$this->loadLibrary('Database');
$connectionParams = array('dbname' => Config::get('database.database'), 'user' => Config::get('database.username'), 'password' => Config::get('database.password'), 'host' => Config::get('database.host'));
$this->db = Database::getConnection($connectionParams);
}
if (Config::get('useSession') !== false && !$cli) {
$this->loadLibrary('Session');
$this->session = new Session();
}
if (Config::get('useTemplate') !== false && !$cli) {
$this->loadLibrary('Smarty/Smarty');
$this->smarty = new Smarty();
$this->smarty->template_dir = APP_PATH . '/pages';
$this->smarty->compile_dir = STORAGE_PATH . 'cache/templates';
$this->smarty->plugins_dir[] = Config::get('SMARTY_PLUGIN_DIRECTORY');
$this->smarty->caching = false;
}
}
function delete() {
Category::requirePermission("DELETE");
$link = Database::getConnection();
$query = "DELETE FROM category WHERE id=".Database::sqlValue($this->id);
mysql_query($query) or die(Database::formatError($query, Text::getText("QueryFailed")));
Database::returnConnection($link);
}
/**
* Gets the database time.
*
* @return DateTime (database time)
* @throws Exception (if we can't get the db time)
* @throws DatabaseException (custom exception for if we can't get the time)
*/
public static function getTime()
{
try {
$conn = Database::getConnection();
$commString = 'SELECT now()';
$stmt = $conn->prepare($commString);
if (!$stmt->execute()) {
throw new DatabaseException('Unknown error during statement execution while getting the database time.', 1);
} else {
$stmt->bind_result($time);
if ($stmt->fetch()) {
return $time;
} else {
throw new DatabaseException('Unknown error during statement execution while getting the database time.', 1);
}
}
} catch (Exception $ex) {
throw $ex;
} finally {
if (isset($conn)) {
$conn->kill($conn->thread_id);
$conn->close();
}
}
}
/**
* Gets all chat messages.
*
* @param int $limit the max amount of messages to return
* @return array Browser
* @throws Exception generic error for if something goes wrong while talking to the database
* @throws BrowserDBException error for if something goes wrong while getting the browsers
*/
public static function getChatMessages($limit)
{
try {
$conn = Database::getConnection();
$commString = 'SELECT message_id, first_name, last_name, user, text, DATE_FORMAT(CONVERT_TZ(message_time, @@global.time_zone, ?), "%d/%m/%Y %H:%i") message_time FROM stippers_chat_messages JOIN stippers_users ON user = user_id ORDER BY message_id DESC LIMIT ?';
$stmt = $conn->prepare($commString);
//Check if statement could be prepared
if ($stmt) {
$timezone = GlobalConfig::MYSQL_TIME_ZONE;
$stmt->bind_param('si', $timezone, $limit);
if (!$stmt->execute()) {
throw new ChatDBException('Unknown error during statement execution while getting chat messages.', ChatDBException::UNKNOWNERROR);
} else {
$stmt->bind_result($messageId, $firstName, $lastName, $user, $text, $messageTime);
$messagesUserNames = array();
while ($stmt->fetch()) {
array_push($messagesUserNames, new ChatMessage($messageId, $firstName, $lastName, $user, $text, $messageTime));
}
return $messagesUserNames;
}
} else {
throw new ChatDBException('Cannot prepare statement.', ChatDBException::CANNOTPREPARESTMT);
}
} catch (Exception $ex) {
throw $ex;
} finally {
if (isset($conn)) {
$conn->kill($conn->thread_id);
$conn->close();
}
}
}
public static function find(array $fields = null)
{
$data = [];
$pdo = Database::getConnection();
$columns = "ARTYPE.\"ARB-Type\",\n ARTYPE.\"ARB-CodeType\",\n ARTYPE.\"ARB-Descript\"";
$from = "from PUB.ARTYPE";
$where = "where ARTYPE.\"ARB-WebShow\"='yes'";
if ($fields) {
if (!empty($fields['CodeType'])) {
$t = $fields['CodeType'] === 'C' ? 'C' : 'T';
$where .= " and ARTYPE.\"ARB-CodeType\"='{$t}'";
} elseif (!empty($fields['category'])) {
$c = preg_replace('[^A-Z]', '', $fields['category']);
if ($c) {
$columns = "distinct {$columns}";
$from = "from PUB.ARSECTION join PUB.ARTYPE on ARSECTION.\"ARS-Type\"=ARTYPE.\"ARB-Type\"";
$where = "where ARSECTION.\"ARS-WebShow\"='yes' and ARSECTION.\"ARS-Category\"='{$c}'";
}
}
}
$sql = "select {$columns} {$from} {$where}";
$result = $pdo->query($sql);
if (!$result) {
print_r($pdo->errorInfo());
}
return $result;
}
function getAppointmentPatientList($patientName, $hosiptal, $appdate)
{
$dbConnection = new Database();
$sql = "SELECT * from appointment where patientName LIKE :patientName and hosiptalid = :hosiptalid and appointementdate = :appdate and status = 'N'";
// echo $sql;
// echo $patientName;
try {
$db = $dbConnection->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue("patientName", "%" . $patientName . "%", PDO::PARAM_STR);
$stmt->bindParam("hosiptalid", $hosiptal);
$stmt->bindParam("appdate", $appdate);
$stmt->execute();
$appointmentDetails = $stmt->fetchAll(PDO::FETCH_OBJ);
$db = null;
//$_SESSION['userDetails'] = $userDetails;
// echo $stmt->debugDumpParams();
// print_r($userDetails);
return $appointmentDetails;
} catch (PDOException $e) {
echo '{"error":{"text":' . $e->getMessage() . '}}';
} catch (Exception $e1) {
echo '{"error11":{"text11":' . $e1->getMessage() . '}}';
}
}
/**
* Define the behaviour of the database driver during the scope of the
* life of this instance.
*
* @param Connection $connection
*
* Instance of the connection to be configured. Leave null to use the
* current default connection.
*
* @param mixed $bypass_queries
*
* Do not preprocess the query before execution.
*
* @param mixed $direct_query
*
* Prepare statements with SQLSRV_ATTR_DIRECT_QUERY = TRUE.
*
* @param mixed $statement_caching
*
* Enable prepared statement caching. Cached statements are reused even
* after the context has expired.
*
*/
public function __construct(Connection $connection = NULL,
$bypass_queries = NULL,
$direct_query = NULL,
$statement_caching = NULL) {
if ($connection == NULL) {
$connection = Database::getConnection();
}
$this->connection = $connection;
$this->state_bypass = $this->connection->bypassQueryPreprocess;
$this->state_direct = $this->connection->directQuery;
$this->statement_caching = $this->connection->statementCaching;
if ($bypass_queries !== NULL) {
$this->connection->bypassQueryPreprocess = $bypass_queries;
}
if ($direct_query !== NULL) {
$this->connection->directQuery = $direct_query;
}
if ($statement_caching !== NULL) {
$this->connection->statementCaching = $statement_caching;
}
}
function content()
{
if (isset($_POST['mode'])) {
$db = Database::getConnection();
$stmt = $db->prepare("SELECT SUM(dc.qty) AS q, d.id, d.name, n.player, n.event, n.medal \n\t\t FROM decks d, entries n, deckcontents dc, events e \n WHERE d.name LIKE ? AND n.deck=d.id \n AND dc.deck=d.id AND dc.issideboard=0\n AND n.event=e.name\n GROUP BY dc.deck\n HAVING q>=60\n ORDER BY e.start DESC, n.medal");
$decknamesearch = "%" . $_POST['deck'] . "%";
$stmt->bind_param("s", $decknamesearch);
$stmt->execute();
$stmt->bind_result($qty, $id, $name, $player, $event, $medal);
echo "<table align=\"center\" style=\"border-width: 0px;\" cellpadding=3>";
while ($stmt->fetch()) {
echo "<tr><td><a href=\"deck.php?mode=view&id={$id}\">";
echo "{$name}</a></td>";
echo "<td><img src=\"/images/{$medal}.gif\"></td>\n";
echo "<td>{$player}</td>";
echo "<td>{$event}";
echo "</td></tr>\n";
}
$stmt->close();
echo "</table>";
} else {
echo "<form method=\"post\" action=\"{$_SERVER['REQUEST_URI']}\">";
echo "Enter a deck name. You may use % as a wildcard.<br><br>";
echo "<input type=\"text\" name=\"deck\">";
echo "<input type=\"submit\" name=\"mode\" value=\"Gimme some decks!\">";
echo "</form>";
}
}
/**
* Tests aborting of traditional SQL database systems with invalid data.
*/
function testInsertDuplicateData()
{
// Try to insert multiple records where at least one has bad data.
try {
db_insert('test')->fields(array('name', 'age', 'job'))->values(array('name' => 'Elvis', 'age' => 63, 'job' => 'Singer'))->values(array('name' => 'John', 'age' => 17, 'job' => 'Consultant'))->values(array('name' => 'Frank', 'age' => 75, 'job' => 'Singer'))->execute();
$this->fail('Insert succeedded when it should not have.');
} catch (IntegrityConstraintViolationException $e) {
// Check if the first record was inserted.
$name = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 63))->fetchField();
if ($name == 'Elvis') {
if (!Database::getConnection()->supportsTransactions()) {
// This is an expected fail.
// Database engines that don't support transactions can leave partial
// inserts in place when an error occurs. This is the case for MySQL
// when running on a MyISAM table.
$this->pass("The whole transaction has not been rolled-back when a duplicate key insert occurs, this is expected because the database doesn't support transactions");
} else {
$this->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
}
} else {
$this->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
}
// Ensure the other values were not inserted.
$record = db_select('test')->fields('test', array('name', 'age'))->condition('age', array(17, 75), 'IN')->execute()->fetchObject();
$this->assertFalse($record, 'The rest of the insert aborted as expected.');
}
}
static function getLessonsBySubjectId($subject_id)
{
//Clear the result
$lessons = array();
//get connection
$connection = Database::getConnection();
$query = 'SELECT * FROM lesson WHERE subject_id=' . $subject_id . ' AND deleted=false ORDER BY date DESC';
//Run the query
$result_obj = $connection->query($query);
try {
//I COULD USE A FOR AND IT WOULD BE BETTER
//BUT IT DOESN'T WORK AND I HAVE NO TIME TO
//FIND THE PROBLEM :)
$i = 0;
while ($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
$lessons[$i] = new Lesson($result);
$i++;
}
//Pass back the result
return $lessons;
} catch (Exception $e) {
$_SESSION['message'] = $e->getMessage();
//Not properly good for safety
}
}
function recentTable()
{
$db = Database::getConnection();
$result = $db->query("SELECT b.event, b.player, d.name \n FROM entries b, decks d, events e \n WHERE b.medal='1st' AND d.id=b.deck AND e.name=b.event\n ORDER BY e.start DESC LIMIT 3");
$result or die($db->error);
echo "<table align=\"center\" width=\"90%\">\n";
while ($row = $result->fetch_assoc()) {
$query = "SELECT COUNT(*) AS c FROM trophies \n WHERE event=\"{$row['event']}\"";
$res2 = $db->query($query) or die($db->error);
$row2 = $res2->fetch_assoc();
if ($row2['c'] > 0) {
echo "<tr><td colspan=\"3\" align=\"center\">";
echo "<a href=\"/gatherling/deck.php?mode=view&";
echo "event={$row['event']}\">";
echo "<img src=\"/gatherling/displayTrophy.php?";
echo "event={$row['event']}\" style=\"border-width: 0px;\"></a>";
echo "</td></tr>\n";
}
echo "<tr><td><b><a href=\"/gatherling/profile.php?player=";
echo "{$row['player']}\">{$row['player']}</a></td>";
echo "<td><i><a href=\"/gatherling/deck.php?";
echo "mode=view&event={$row['event']}\">{$row['name']}</a></td>";
echo "<td><a href=\"/gatherling/eventreport.php?event={$row['event']}\">{$row['event']}</a></td></tr>\n";
}
echo "</table>";
$result->close();
}
static function getCodeBybullet($bullet_id)
{
//clear all the results
$codes = array();
$connection = Database::getConnection();
$query = "SELECT * FROM code WHERE deleted=false AND bullet_id=" . $bullet_id . " ORDER BY date ASC";
//print the query
//echo $query;
//run the query
$result_obj = $connection->query($query);
try {
//I COULD USE A FOR AND IT WOULD BE BETTER
//BUT IT DOESN'T WORK AND I HAVE NO TIME TO
//FIND THE PROBLEM :)
$i = 0;
while ($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
$codes[$i] = new Code($result);
$i++;
}
//Pass back the result
return $codes;
} catch (Exception $e) {
$_SESSION['message'] = $e->getMessage();
//Not properly good for safety
}
}
/**
* Constructs a new PDOTokenBackend, and connects to the database. Throws an Exception on error.
* a database connection could not be established.
* @access public
*/
public function __construct()
{
try {
$this->connection = Database::getConnection();
} catch (Exception $e) {
throw new Exception(sprintf("Failed to open database connection: %s", $e->getMessage()));
}
}
function save()
{
$db = Database::getConnection();
$stmt = $db->prepare("UPDATE subevents SET parent = ?, rounds = ?, \n timing = ?, type = ? WHERE id = ?");
$stmt->bind_param("sddss", $this->parent, $this->rounds, $this->timing, $this->type, $this->id);
$stmt->execute() or die($stmt->error);
$stmt->close();
}
public function getArrayDados()
{
$con = Database::getConnection();
$stmt = $con->prepare("\n SELECT *\n FROM vs_estudantes\n WHERE cd_info_estudos = {$this->dto->getCdInfoEstudos()}\n ");
$stmt->execute();
$return = $stmt->fetch(PDO::FETCH_ASSOC);
return $return;
}
/**
* Takes an INSERT query and returns the inserted ID,
* or 0 if query does not generate an AUTO_INCREMENT
* value.
*/
function insert($sql) {
$con = Database::getConnection();
$result = mysql_query($sql) or die(Database::formatError($sql, Text::getText("QueryFailed")));
$record = new DatabaseRecord($result);
$id = mysql_insert_id($con);
Database::returnConnection($con);
return $id;
}
/**
* @param $idLocation
* @return bool|resource
*/
public function getImages(int $idLocation)
{
$query = 'SELECT id_image from image where fk_location = ?';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($idLocation));
if (sqlsrv_errors()) {
http_response_code(500);
}
return $stmt;
}
/**
* Lädt die Orte, welche den mitgegebenen String im Namen enthalten.
* Offset: Beim wievielten Datensatz das Laden beginnt
* Rows: Wie viele Datensätze geladen werden
* @param int $offset
* @param int $rows
* @param string $location
* @return bool|resource
*/
public function loadLocationsByIdAndName(int $offset, int $rows, string $location)
{
$query = "SELECT \n id_location AS id_location,\n name AS name, \n description AS description\n FROM location\n WHERE location.name LIKE ?\n ORDER BY id_location\n OFFSET {$offset} ROWS \n FETCH NEXT {$rows} ROWS ONLY";
$stmt = sqlsrv_query(Database::getConnection(), $query, ['%' . $location . '%']);
if (sqlsrv_errors()) {
http_response_code(500);
}
return $stmt;
}
public function load(string $username)
{
$query = 'SELECT * FROM person WHERE username = ?';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($username));
if (sqlsrv_errors()) {
http_response_code(500);
}
return sqlsrv_fetch_array($stmt);
}
public function __construct($action)
{
// Get a connectio to the database
$this->_DB = \Database::getConnection();
// Start the session manager
$this->_session = new Session();
$this->_action = strtolower($action);
//$this->_user = new User();
}
/**
* send a query to database
* @param string $query
* @param array $values
*/
public function __construct($query = null, $values = null)
{
$this->database = Database::getConnection();
if (empty($query)) {
return;
}
$this->setQuery($query);
$this->execute($values);
}
public static function createFromSystem()
{
$role_permissions = array();
$db = \Database::getConnection();
foreach ($db->query('SELECT rid, permission, module FROM {role_permission}') as $row) {
$role_permissions[$row->rid][$row->module][$row->permission] = TRUE;
}
return self::create(user_roles(), $role_permissions);
}
function testExecutingSQLWithUnSetTablePrefixShouldFail()
{
global $TWITALYTIC_CFG;
$TWITALYTIC_CFG['table_prefix'] = 'tw_';
$this->expectException();
$db = new Database($TWITALYTIC_CFG);
$conn = $db->getConnection();
$sql_result = $db->exec("SELECT \n\t\t\t\tuser_id \n\t\t\tFROM \n\t\t\t\t%prefix%users \n\t\t\tWHERE \n\t\t\t\tuser_id = 930061");
$db->closeConnection($conn);
}
static function single_result($sql)
{
$db = @Database::getConnection();
$stmt = $db->prepare($sql);
$stmt->execute();
$stmt->bind_result($result);
$stmt->fetch();
$stmt->close();
return $result;
}
function getByResetKey($key) {
$link = Database::getConnection();
$query = "SELECT * FROM security_user WHERE reset_key=".Database::sqlValue($key);
$result = Database::query($query);
$u = NULL;
if ($line = $result->next()) {
$u = SecurityUser::build($line);
}
return $u;
}
public function __construct($sql, $params, Connection $connection, Database $db)
{
try {
$stmt = $connection->prepare($sql);
$stmt->execute($params);
$this->statement = $stmt;
$this->connection = $connection;
} catch (\PDOException $e) {
// 服务端断开时重连一次
if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) {
$master_or_slave = $connection->getMasterOrSlave();
$db->closeConnection($master_or_slave, $connection->getConnectionIndex());
$connection = $db->getConnection($master_or_slave == Connection::MASTER_CONNECTION);
try {
$stmt = $connection->prepare($sql);
$stmt->execute($params);
$this->statement = $stmt;
$this->connection = $connection;
} catch (\PDOException $ex) {
$db->rollback();
throw $ex;
}
} else {
$db->rollback();
throw $e;
}
}
}