diff options
Diffstat (limited to 'common/db.php')
| -rw-r--r-- | common/db.php | 310 |
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) { + + } +} +?> |