snuffler-web/inc/database.php

<?php
if(count(get_included_files()) ==1) {
	http_response_code(403);
	die("403: Forbidden");
}
class DataBase extends SQLite3 {
	function __construct() {
		$this->open('database.db');
		$this->exec('PRAGMA foreign_keys=ON;');
		$this->exec('PRAGMA full_column_names=ON;');
		$this->exec('PRAGMA short_column_names=OFF;');
		
		$sql = "
			CREATE TABLE IF NOT EXISTS users (
			id	INTEGER	PRIMARY KEY	UNIQUE,
			pass	TEXT,
			email	TEXT	UNIQUE,
			handle	TEXT	NOT NULL	UNIQUE,
			name	TEXT	NOT NULL,
			about	TEXT
			);

			CREATE TABLE IF NOT EXISTS personas (
			id	INTEGER	PRIMARY KEY	UNIQUE,
			userid	INTEGER	NOT NULL,
			handle	TEXT	NOT NULL,
			name	TEXT	NOT NULL,
			about	TEXT,
			colour	INTEGER,
			FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
			UNIQUE (userid, handle)
			);

			CREATE TABLE IF NOT EXISTS posts (
			id	INTEGER	PRIMARY KEY	UNIQUE,
			time	INTEGER	NOT NULL,
			userid	INTEGER	NOT NULL,
			personaid INTEGER NOT NULL,
			text	TEXT	NOT NULL,
			FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
			FOREIGN KEY (personaid) REFERENCES personas(id) ON UPDATE CASCADE ON DELETE CASCADE
			);

			CREATE TABLE IF NOT EXISTS comments (
			id	INTEGER	PRIMARY KEY	UNIQUE,
			time	INTEGER	NOT NULL,
			userid	INTEGER	NOT NULL,
			personaid INTEGER,
			postid	INTEGER	NOT NULL,
			text	TEXT	NOT NULL,
			FOREIGN KEY (postid) REFERENCES posts(id),
			FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
			FOREIGN KEY (personaid) REFERENCES personas(id) ON UPDATE CASCADE ON DELETE CASCADE
			);

			CREATE TABLE IF NOT EXISTS tokens (
			id	INTEGER	PRIMARY KEY	AUTOINCREMENT	UNIQUE,
			userid	INTEGER	NOT NULL,
			token	TEXT	NOT NULL	UNIQUE,
			lastuse	TEXT	NOT NULL,
			expires	TEXT	NOT NULL,
			FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
			);

			INSERT OR IGNORE INTO users (id, handle, name, about) VALUES ('0', 'SYSTEM', 'SYSTEM', 'SYSTEM');

		";
		
		$ret = $this->exec($sql);
		
	}
	
	function addUser($handle, $name, $about=NULL) {
		$id = hexdec(uniqid());
		$sql = "INSERT INTO users (id, handle, name, about) VALUES ('$id', '$handle', '$name', '$about')";
		$ret = $this->exec($sql);
		if(!$ret) {
			die($this->lastErrorMsg());
		}
	}

	function getUserByHandle($handle) {
		$handle = $this->escapeString($handle);
		$sql = "SELECT * FROM users AS user WHERE handle='$handle';";
		$ret = $this->query($sql)->fetchArray(SQLITE3_ASSOC);
		if(!$ret) {
			return false;
		}
		return $ret;
	}

	function addPost($text, $userid=NULL, $personaid=NULL) {
		$id = hexdec(uniqid());
		$time = time();
		$sql = $this->prepare("INSERT INTO posts (id, time, userid, personaid, text) values ('$id', '$time', '$userid', :personaid, '$text')");
		$sql->bindParam(':personaid', $personaid, SQLITE3_INTEGER);
		$ret = $sql->execute();
		if(!$ret) {
			die($this->lastErrorMsg());
		}
	}

	function addPersona($userid, $handle, $name, $about=NULL, $colour=NULL) {
		$id = hexdec(uniqid());
		$sql = "INSERT INTO personas (id, userid, handle, name, colour) VALUES ('$id', '$userid', '$handle', '$about', '$colour');";
		$ret = $this->exec($sql);
		if(!$ret) {
			die($this->lastErrorMsg());
		}
	}

	function passwordSet($userid, $password=NULL) {
		$hash = empty($password) ? NULL : password_hash($password, PASSWORD_DEFAULT);
		$sql = "UPDATE USERS SET pass='$hash' WHERE id='$userid';";
		$ret = $this->exec($sql);
		if(!$ret) {
			die($this->lastErrorMsg());
		}
	}

	function passwordVerify($userid, $password) {
		$sql = "SELECT pass FROM users WHERE id='$userid';";
		$ret = $this->query($sql)->fetchArray(SQLITE3_NUM);
		if(!$ret) {
			return false;
		}
		$dbhash = $ret[0];
		return password_verify($password, $dbhash);
	}

	function tokenGen() {
		return random_bytes(32);
	}
	function tokenAdd($userid) {
		$token = $this->tokenGen();
		$hashed = hash('sha256', $token);
		$time = time();
		$expires = $time + 2592000; // 30 days
		$sql = "INSERT INTO tokens (userid, token, lastuse, expires) VALUES ('$userid', '$hashed', '$time', '$expires');";
		$ret = $this->exec($sql);
		if(!$ret) {
			die($this->lastErrorMsg());
		}
		return $token;
	}
	function tokenRefresh($tokenid) {
		$time = time();
		$expires = $time + 2592000; // 30 days
		$sql = "UPDATE tokens SET lastuse='$time', expires='$expires' WHERE id='$tokenid';";
		$ret = $this->exec($sql);
		if(!$ret) {
			die($this->lastErrorMsg());
		}
	}
	function tokenRemove($token) {
		$hashed = hash('sha256', $token);
		$sql = "DELETE FROM tokens WHERE token='$hashed';";
		$ret = $this->exec($sql);
		if(!$ret) {
			die($this->lastErrorMsg());
		}
	}

	function getAuthedUserId($token=NULL) {
		if (empty($token)) { 
			if (empty($_COOKIE['token'])) {
				return false;
			}
			$token = base64_decode($_COOKIE['token']);
		}
		$hashed = hash('sha256', $token);
		$sql = "SELECT id AS id, userid AS userid, expires AS expires FROM tokens WHERE token='$hashed';";
		$ret = $this->query($sql)->fetchArray(SQLITE3_ASSOC);
		if(!$ret) {
			return false;
		}
		if ($ret['expires'] < time()) {
			$this->tokenRemove($token);
			return false;
		}
		$this->tokenRefresh($ret['id']);
		return $ret['userid'];
	}

	function getPosts($userid=NULL, $personaid = NULL) {
		$sql = "SELECT * FROM posts AS post LEFT JOIN users AS user ON post.userid=user.id LEFT JOIN personas AS persona ON post.personaid=persona.id;";

		$ret = $this->query($sql);
		$array = array();
		while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
			array_push($array, $row);
		}
		return $array;
	}
}
?>