Show sourcecode
The following files exists in this folder. Click to view.
config.php
dbconnection.php
footer.php
header.php
dbconnection.php
296 lines UTF-8 Windows (CRLF)
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296
<?php
// show all error reporting
error_reporting(-1); // Report all type of errors
ini_set('display_errors', 1); // Display all errors
ini_set('output_buffering', 0); // Do not buffer outputs, write directly
// Auto-detect environment based on server name
$local = !isset($_SERVER['HTTP_HOST']) || strpos($_SERVER['HTTP_HOST'], 'labb.vgy.se') === false;
if ($local) {
$dbname = 'min_databas';
$hostname = 'localhost';
$/* DB_USER, is removed and hidden for security reasons */ ;
$/* DB_PASSWORD, is removed and hidden for security reasons */ ;
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'");
} else {
$dbname = 'antasp23';
$hostname = 'localhost';
$/* DB_USER, is removed and hidden for security reasons */ ;
$/* DB_PASSWORD, is removed and hidden for security reasons */ ;
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'");
}
try {
$dbconn = new PDO("mysql:host=$hostname;dbname=$dbname;", $/* DB_USER, is removed and hidden for security reasons */ ;
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
// For debug purpose, shows all connection details
echo 'Connection failed: ' . $e->getMessage() . "<br />";
// Hide connection details.
//echo 'Could not connect to database.<br />');
}
class DBManager
{
private PDO $pdo;
static private array $ALLOWED_TABLES = [
"bay_users", "bay_media", "bay_moderation_jobs", "bay_watch_history"
];
private const SAFETY_MAX_ROWS = 5; // Säkerhetsflagga
public function __construct()
{
global $local;
if ($local) {
$dbname = 'min_databas';
$hostname = 'localhost';
$/* DB_USER, is removed and hidden for security reasons */ ;
$/* DB_PASSWORD, is removed and hidden for security reasons */ ;
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'");
} else {
$dbname = 'antasp23';
$hostname = 'localhost';
$/* DB_USER, is removed and hidden for security reasons */ ;
$/* DB_PASSWORD, is removed and hidden for security reasons */ ;
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'");
}
try {
$this->pdo = new PDO("mysql:host=$hostname;dbname=$dbname;", $/* DB_USER, is removed and hidden for security reasons */ ;
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
// For debug purpose, shows all connection details
echo 'Connection failed: ' . $e->getMessage() . "<br />";
}
}
/** Hämtar alla `$cols` från `$table` där `$where` uppfylls och kan lägga till JOINs
*
* @param array $cols Lista av alla kolumner som ska hämtas
* @param string $table Vilken tabell som ska sökas
* @param array $where Villkor för fetch [column => value, ...]
* @param array $joins Lista av JOIN-definitioner:
* [
* [
* 'type' => 'INNER',
* 'table' => 'bay_media',
* 'alias' => 'm',
* 'condition' => 'bay_users.id = m.uploaded_by_user_id'
* ],
* ...
* ]
* @param int $limit Max antal rader som retuneras
* @param str $orderby Vilken kolumn som datan ska sorteras efter (+ ASC eller DESC)
* @return array assoc_array av alla hittade resultat
* */
public function fetch_from_table(array $cols, string $table, array $where, array $joins = [], int $limit = -1, string $orderby = "")
{
try {
if (!in_array($table, $this::$ALLOWED_TABLES)) {
throw new Exception('Otillåtet värde för $table');
}
$conditions = []; // Conditions för WHERE
$params = []; // Parametrar för $conditions
foreach ($where as $col => $val) {
$paramName = str_replace('.', '_', $col); // Sanitize for PDO (dots not allowed in param names)
$conditions[] = "$col = :$paramName";
$params[":$paramName"] = $val;
}
$joinParts = [];
$allowedJoinTypes = [
'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS',
'LEFT OUTER', 'RIGHT OUTER', 'FULL OUTER'
];
foreach ($joins as $join) {
if (!is_array($join) || empty($join['table']) || empty($join['condition'])) {
throw new Exception('Varje join måste vara en array med minst keys "table" och "condition".');
}
$joinType = strtoupper(trim($join['type'] ?? 'INNER'));
if (!in_array($joinType, $allowedJoinTypes, true)) {
throw new Exception('Otillåtet join-typen: ' . $joinType);
}
$joinTable = $join['table'];
if (!in_array($joinTable, $this::$ALLOWED_TABLES)) {
throw new Exception('Otillåtet värde för join-table');
}
$alias = isset($join['alias']) ? trim($join['alias']) : '';
if ($alias !== '' && preg_match('/^[A-Za-z0-9_]+$/', $alias) !== 1) {
throw new Exception('Ogiltigt alias för join-table');
}
$tableExpr = $alias ? "$joinTable AS $alias" : $joinTable;
$joinParts[] = "$joinType JOIN $tableExpr ON " . $join['condition'];
}
$sql = "SELECT " . implode(",", $cols) . " FROM $table";
if (!empty($joinParts)) {
$sql .= " " . implode(" ", $joinParts);
}
if (!empty($conditions)) {
$sql .= " WHERE " . implode(" AND ", $conditions);
}
if ($orderby != "") {
$sql .= " ORDER BY " . strval($orderby);
}
// Lägg till limit om efterfrågat
if ($limit > -1) {
$sql .= " LIMIT " . strval($limit);
}
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
// Spara i associative array format
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $results;
} catch (PDOException $e) {
throw new RuntimeException('Fel vid hämtning av data från $table.', 1, $e);
}
}
/**
* Lägger till värden `["namn" => "värde", ...]` i `$table`
*/
public function insert_into_table(array $values, string $table)
{
$this->pdo->beginTransaction();
try {
if (!in_array($table, $this::$ALLOWED_TABLES)) {
throw new Exception('Otillåtet värde för $table');
}
$columns = array_keys($values);
$params = [];
$placeholders = [];
foreach ($columns as $col) {
$placeholders[] = ":$col"; // PDO-parameter
$params[":$col"] = $values[$col];
}
$sql = "INSERT INTO $table (" . implode(",", $columns) . ")
VALUES (" . implode(",", $placeholders) . ")";
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
$this->pdo->commit();
} catch (PDOException $e) {
$this->pdo->rollBack();
throw new RuntimeException('Fel vid insert av data till $table.', 1, $e);
}
}
/**
* [
* 'column' => value
* ]
*/
public function update_table_values(string $table, array $changes,
array $where, bool $SAFETY_OVERRIDE = false) {
try {
$this->pdo->beginTransaction();
if (!in_array($table, $this::$ALLOWED_TABLES)) {
throw new Exception('Otillåtet värde för $table');
}
if (empty($where)) {
throw new Exception('$where får inte vara tom!');
}
$params = [];
$setParts = [];
foreach ($changes as $col => $val) {
$key = ":update_$col";
$setParts[] = "$col = $key";
$params[$key] = $val;
}
$whereParts = [];
foreach ($where as $col => $val) {
$key = ":where_$col";
$whereParts[] = "$col = $key";
$params[$key] = $val;
}
$sql = "UPDATE $table
SET " . implode(',', $setParts)
. " WHERE " . implode(' AND ', $whereParts);
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
$affectedRows = $stmt->rowCount();
if ($affectedRows > $this::SAFETY_MAX_ROWS && !$SAFETY_OVERRIDE) {
$this->pdo->rollBack();
throw new Exception('För många rader påverkades av $where. Antalet rader påverkade var '
. strval($affectedRows) . ", Max tillåtna är " . strval($this::SAFETY_MAX_ROWS)) . "."
. ' Ändra $SAFETY_OVERRIDE till true för att överrida detta';
}
$this->pdo->commit();
} catch (PDOException $e) {
$this->pdo->rollBack();
throw new RuntimeException('Fel vid updatering av data till $table.', 1, $e);
}
}
/**
* Tar bort rades från `$table` som uppfyller condition `$where`
*
* @param string $table Table att ta bort från
* @param array $where Conditions för att ta bort [column => value, ...]
* @param bool $SAFETY_OVERRIDE Säkerhetsflagga för att inte råka ta bort för mycket
* @throws Exception Om table inte finns ´ALLOWED_TABLES´ eller $where är tom
* @throws RuntimeException Om borttagningen misslyckas
*/
public function remove_from_table(string $table, array $where, bool $SAFETY_OVERRIDE = false)
{
$this->pdo->beginTransaction();
try {
if (!in_array($table, $this::$ALLOWED_TABLES)) {
throw new Exception('Otillåtet värde för $table');
}
if (empty($where)) {
throw new Exception('$where får inte vara tom!');
}
$whereParts = [];
$params = [];
foreach ($where as $col => $val) {
$key = ":delete_$col";
$whereParts[] = "$col = $key";
$params[$key] = $val;
}
$sql = "DELETE FROM $table WHERE " . implode(" AND ", $whereParts);
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
$affectedRows = $stmt->rowCount();
if ($affectedRows > $this::SAFETY_MAX_ROWS && !$SAFETY_OVERRIDE) {
$this->pdo->rollBack();
throw new Exception('För många rader påverkades av $where. Antalet rader påverkade var '
. strval($affectedRows) . ", Max tillåtna är " . strval($this::SAFETY_MAX_ROWS)
. '. Ändra $SAFETY_OVERRIDE till true för att överrida detta');
}
$this->pdo->commit();
} catch (PDOException $e) {
$this->pdo->rollBack();
throw new RuntimeException('Fel vid borttagning av data från $table.', 1, $e);
}
}
}