Initial importHEADmaster

* Registration system's almost done
* Just part way through implementing tasks
So not much done yet, but it's a start.

1 files changed, 310 insertions, 0 deletions

diff --git a/common/db.php b/common/db.php
new file mode 100644
index 0000000..71dbf81
--- /dev/null
+++ b/common/db.php
@@ -0,0 +1,310 @@
+<?php
+namespace mcoop;
+require_once("config_cls.php");
+require_once("vendor/busybee/urljoin/src/urljoin.php");
+require_once("db_classes.php");
+require_once("tables/members.php");
+require_once("tables/dividend_credits.php");
+require_once("tables/tasks.php");
+require_once("tables/task_claims.php");
+require_once("tables/task_dividend_credits.php");
+require_once("tables/tcc_history.php");
+
+// TODO: move this into the config object, or auto add to it in each decl file
+$table_decls = array(
+	"members" => $members_table_decl,
+	"dividend_credits" => $dividend_credits_table_decl,
+	"tasks" => $tasks_table_decl,
+	"task_claims" => $task_claims_table_decl,
+	"task_dividend_credits" => $task_dividend_credits_table_decl,
+	"tcc_history" => $tcc_history_table_decl
+);
+
+class UnknownMember extends \Exception {
+	public $searched = null;
+	public $value = null;
+
+	function __construct($searched, $value) {
+		$this->searched = $searched;
+		$this->value = $value;
+
+		$message = "Unable to find a member with $searched = $value";
+		parent::__construct($message);
+	}
+}
+
+class RegistrationError extends \Exception {
+	public $invalid_part = null;
+	public $reason = null;
+
+	function __construct($invalid_part, $reason) {
+		$this->invalid_part = $invalid_part;
+		$this->reason = $reason;
+
+		$message = "Registration Error ($invalid_part): $reason";
+		parent::__construct($message);
+	}
+}
+
+class LoginError extends \Exception {}
+
+class Database {
+	public $config = null;
+	public $conn = null;
+	public $target_tableversions = array(
+		"unexpected_errors" => 1,
+	);
+	public $statements = array();
+	public $upgraders = array();
+	public $table_decls = array();
+
+	function __construct($config) {
+		$this->config = $config;
+		global $table_decls;
+		$this->table_decls = $table_decls;
+		$this->connect();
+	}
+
+	function connect() {
+		// XXX/TODO: the exceptions from this need to be caught, see the warning on http://php.net/manual/en/pdo.connections.php
+		$this->conn = new \PDO(
+			$this->config->pdo_connstring,
+			$this->config->db_username,
+			$this->config->db_password,
+			array(\PDO::ATTR_PERSISTENT => true)
+		);
+		$this->create_tableversions_table();
+		$this->create_unexpected_errors_table();
+		// TODO: make table creation optional for everything except tableversions and unexpected_errors
+		// (you can change the $create argument to setup_table_decls to false for that now)
+		$this->setup_table_decls();
+		$this->auto_upgrade_all();
+		//$this->create_members_table();
+		//$this->upgraders["members"] = new MembersUpgrader($this->conn);
+		//$this->auto_upgrade_all();
+		//var_dump($this->conn);
+	}
+
+	function setup_table_decls($create=true) {
+		$conn = $this->conn;
+		foreach ($this->table_decls as $tname => $td) {
+			if ($create) {
+				$td->create_table($this);
+			}
+			$td->set_statements($this);
+			$clsname = $td->upgrader_classname;
+			$this->upgraders[$tname] = new $clsname($conn);
+		}
+	}
+
+	function get_cur_table_version($table_name) {
+		$this->statements["get_tableversion"]->execute(array(":name" => $table_name));
+		$x = $this->statements["get_tableversion"]->fetchAll(\PDO::FETCH_COLUMN);
+		$ver = (int)$x[0];
+		return $ver;
+	}
+
+	function auto_upgrade($upgrader) {
+		// This shouldn't be called until at the very least after create_tableversions_table, since it adds set_tableversion to statements
+		$table_name = $upgrader->table_name;
+		$old_ver = $this->get_cur_table_version($table_name);
+		$new_ver = $this->table_decls[$table_name]->newest_tableversion;
+		if ($old_ver < $new_ver) {
+			error_log("upgrading $table_name from $old_ver to $new_ver");
+			// XXX/TODO: this is assuming it doesn't error, going to get really ugly if it does
+			$upgraded_to = $upgrader->upgrade($old_ver, $new_ver);
+			$this->statements["set_tableversion"]->execute(array(":table_name" => $table_name, ":version" => $upgraded_to));
+			//print_r($upgraded_to);
+			return $upgraded_to;
+		}
+	}
+
+	function auto_upgrade_all() {
+		foreach ($this->upgraders as $table_name => $upgrader) {
+			$this->auto_upgrade($upgrader);
+		}
+	}
+
+	function create_tableversions_table() {
+		$conn = $this->conn;
+		$conn->exec("CREATE TABLE IF NOT EXISTS `table_versions` (`name` TEXT CHARACTER SET utf8 NOT NULL, `version` INT, PRIMARY KEY (`name`(1024)));");
+		$this->statements["set_tableversion"] = $conn->prepare("INSERT INTO table_versions VALUES (:table_name, :version) on duplicate key UPDATE version=:version");
+		$this->statements["get_tableversion"] = $conn->prepare("SELECT version FROM table_versions WHERE name = :name");
+	}
+
+	function create_unexpected_errors_table() {
+		// TODO: this table needs a datetime field so you know when the error happened
+		$conn = $this->conn;
+		$conn->exec("CREATE TABLE `unexpected_errors` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `action` TEXT CHARACTER SET utf8 NOT NULL, `error_sql_code` CHAR(5) CHARACTER SET ascii NOT NULL, `error_server_code` INT, `error_server_description` TEXT CHARACTER SET utf8);");
+		$err_info = $conn->errorInfo();
+		//var_dump($err_info);
+		if ($err_info[0] == "00000") {
+			$this->statements["set_tableversion"]->execute(array(
+				":table_name" => "unexpected_errors",
+				":version" => $this->target_tableversions["unexpected_errors"]
+			));
+		}
+		$this->statements["insert_unexpected_error"] = $conn->prepare("INSERT INTO unexpected_errors (action, error_sql_code, error_server_code, error_server_description) VALUES (:action, :sql_code, :server_code, :server_description)");
+	}
+
+	function create_members_table() {
+		$conn = $this->conn;
+		$conn->exec(
+		"CREATE TABLE `members` (
+`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+`username` TEXT CHARACTER SET utf8 NOT NULL,
+`email` TEXT CHARACTER SET utf8 NOT NULL,
+`last_updated_table_version` INT NOT NULL,
+`full_name` TEXT CHARACTER SET utf8,
+`validation_code` CHAR(64) CHARACTER SET ascii,
+`validated` BOOL DEFAULT false,
+`full_member` BOOL DEFAULT false,
+`argon2_password_hash` VARCHAR(256) CHARACTER SET ascii,
+`reset_password_hash` VARCHAR(256) CHARACTER SET ascii,
+`reset_requested` BOOL DEFAULT false,
+UNIQUE KEY (`username`(256)),
+UNIQUE KEY (`email`(256))
+);");
+		$err_info = $conn->errorInfo();
+		// the SQL errorcode for a table already existing seems to be 42S01
+		// but I think we don't need to check for it specifically here,
+		// just make sure there was no error
+		//print_r($err_info);
+		//var_dump($err_info);
+		if ($err_info[0] == "00000") {
+			$this->statements["set_tableversion"]->execute(array(
+				":table_name" => "members",
+				":version" => $this->target_tableversions["members"]
+			));
+		} else if ($err_info[0] != "42S01") {
+			$this->statements["insert_unexpected_error"]->execute(array(
+				":action" => "create_members_table",
+				":sql_code" => $err_info[0],
+				":server_code" => $err_info[1],
+				":server_description" => $err_info[2]
+			));
+		}
+		$this->statements["register_member"] = $conn->prepare("INSERT INTO members (username, email, last_updated_table_version, full_name, argon2_password_hash) VALUES (:username, :email, :version, :full_name, :argon2_phash)");
+		$this->statements["get_member_passhash_by_username"] = $conn->prepare("SELECT argon2_password_hash FROM members WHERE username = :username");
+		$this->statements["get_member_by_username"] = $conn->prepare("SELECT * FROM members WHERE username = ?");
+		$this->statements["get_member_by_email"] = $conn->prepare("SELECT * FROM members WHERE email = ?");
+		$this->statements["get_members_by_uname_or_email"] = $conn->prepare("SELECT * FROM members WHERE username = :username OR email = :email");
+	}
+
+	function validate_username($username) {
+		if (!preg_match("/^[a-zA-Z0-9]{3,}$/", $username)) {
+			throw new RegistrationError("username", "Invalid username (must be at least 3 characters long, only english alphanumeric characters are allowed");
+		}
+		$conn = $this->conn;
+		$username_check = $conn->prepare("SELECT * FROM members WHERE username = :username");
+		$username_check->execute(array(":username" => $username));
+		$x = $username_check->fetchAll(\PDO::FETCH_COLUMN);
+		if ($x) {
+			throw new RegistrationError("username", "Username already registered");
+		}
+		return $username;
+	}
+
+	function validate_email($email) {
+		$filt_email = filter_var($email, FILTER_VALIDATE_EMAIL);
+		if ($filt_email != $email) {
+			throw new RegistrationError("email", "Doesn't validate as a valid email address");
+		}
+		$conn = $this->conn;
+		$email_check = $conn->prepare("SELECT * FROM members where email = :email");
+		$email_check->execute(array(":email" => $filt_email));
+		$x = $email_check->fetchAll(\PDO::FETCH_COLUMN);
+		if ($x) {
+			throw new RegistrationError("email", "An account with that email address already exists, you can <a href=\"/reset_password.php\">Reset your password</a> if you need a new password");
+		}
+		return $filt_email;
+	}
+
+	function validate_fullname($full_name) {
+		$filt_fname = filter_var($full_name, FILTER_SANITIZE_STRING, array(
+			"flags" => FILTER_FLAG_NO_ENCODE_QUOTES | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH | FILTER_FLAG_STRIP_BACKTICK
+		));
+		return $filt_fname;
+	}
+
+	// TODO: reset password functionality that sends a randomly generated password to your email address
+	function register($username, $email, $password, $full_name, $validate_email=false, $email_twig_env=null) {
+		$filt_email = $this->validate_email($email);
+		$filt_username = $this->validate_username($username);
+		$filt_fname = $this->validate_fullname($full_name);
+		$statements = $this->statements;
+		$conn = $this->conn;
+		$pass_hash = password_hash($password, PASSWORD_ARGON2I);
+		$conn->beginTransaction();
+		$success = $statements["register_member"]->execute(array(
+			":username" => $username,
+			":email" => $filt_email,
+			":version" => $this->table_decls["members"]->newest_tableversion,
+			":full_name" => $filt_fname,
+			":argon2_phash" => $pass_hash
+		));
+		// TODO: check that it was actually successful
+		if (!$success) {
+			$einfo = $statements["register_member"]->errorInfo();
+			error_log("mcoop member registration failed inserting into the database: " . var_export($einfo, true) . " ($username, $email, $password, $pass_hash, $full_name)");
+			throw new RegistrationError("database", "registration failed internally, contact the admin");
+		}
+		if ($validate_email) {
+			$this->send_validation_email($username, $filt_email, $email_twig_env);
+		}
+		$conn->commit();
+		// TODO: setup the login session here too, not sure if session_start should be called in the script setup code actually instead of just here (and just set the variable in $_SESSION here)
+		//session_start();
+		$_SESSION["logged_in"] = true;
+		$_SESSION["login_username"] = $username;
+		// TODO: include login time in here and other things so you can invalidate sessions/log people out if a password reset gets requested or anything else
+		//session_write_close();
+	}
+
+	function send_validation_email($username, $email, $email_twig_env) {
+		// generate a validation code, just use some random bytes, hash them and use the hexdigest for now
+		$rbytes = random_bytes(64);
+		$h = hash("sha256", $rbytes, false);
+		$conn = $this->conn;
+		$stmt = $conn->prepare("UPDATE members SET validation_code = :vcode WHERE username = :username AND email = :email");
+		$success = $stmt->execute(array(
+			":vcode" => $h,
+			":username" => $username,
+			":email" => $email
+		));
+		if (!$success) {
+			$einfo = $stmt->errorInfo();
+			error_log("mcoop: send_validation_email failed updating members: " . var_export($einfo, true) . " ($h, $username, $email)");
+			throw new RegistrationError("database", "internal error generating validation code, contact the admin");
+		}
+		$headers = "From: " . $this->config->email_from_address;
+		$sitename = $this->config->website_name;
+		$vurl = urljoin($this->config->webapp_base_uri, "validate.php?un=$username&vcode=$h");
+		$deactivate_url = urljoin($this->config->webapp_base_uri, "deactivate.php?un=$username&vcode=$h");
+		$body = $email_twig_env->render("validation_email.tmpl", array(
+			"email" => $email,
+			"website_name" => $sitename,
+			"vurl" => $vurl,
+			"deactivate_url" => $deactivate_url
+		));
+		mail($email, "Activate your $sitename account", $body, $headers);
+	}
+
+	function login($username, $password, $session_info) {
+		$m = DBMember::load_by($this, "username", $username);
+		// TODO: password reset logic
+		if (password_verify($password, $m->argon2_password_hash)) {
+			$_SESSION["logged_in"] = true;
+			$_SESSION["login_username"] = $username;
+			$session_info->re_init();
+			return true;
+		} else {
+			throw new LoginError("Invalid Password");
+		}
+	}
+
+	function get_members($only_verified=true) {
+		
+	}
+}
+?>