dev-chiefworks
309 lines
11 KiB
PHP
309 lines
11 KiB
PHP
<?php
|
|
|
|
class OAuth2 {
|
|
|
|
public function getAllTokens($db, $member_id) {
|
|
$result = array();
|
|
$total = 0;
|
|
$q = "SELECT * FROM oauth2_tokens WHERE member_id=${member_id} ORDER BY id DESC";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$total = pg_num_rows($r);
|
|
while($f=pg_fetch_assoc($r)) {
|
|
list($decrypted,$googleKMS) = self::decrypt($f["refresh_token_encrypted"]);
|
|
$f["refresh_token"] = $decrypted;
|
|
list($decrypted,$googleKMS) = self::decrypt($f["access_token_encrypted"], $googleKMS);
|
|
$f["access_token"] = $decrypted;
|
|
$result[] = $f;
|
|
}
|
|
}
|
|
return array($total, $result);
|
|
}
|
|
|
|
public function getLatestToken($db, $member_id, $oauth_provider_id) {
|
|
$result = array();
|
|
$total = 0;
|
|
$q = "SELECT * FROM oauth2_tokens WHERE member_id=${member_id} AND oauth_provider_id=${oauth_provider_id} ORDER BY id DESC LIMIT 1";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
list($decrypted,$googleKMS) = self::decrypt($f["refresh_token_encrypted"]);
|
|
$f["refresh_token"] = $decrypted;
|
|
list($decrypted,$googleKMS) = self::decrypt($f["access_token_encrypted"], $googleKMS);
|
|
$f["access_token"] = $decrypted;
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public function getTokenById($db, $id) {
|
|
$result = array();
|
|
$q = "SELECT * FROM oauth2_tokens WHERE id=${id}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
list($decrypted,$googleKMS) = self::decrypt($f["refresh_token_encrypted"]);
|
|
$f["refresh_token"] = $decrypted;
|
|
list($decrypted,$googleKMS) = self::decrypt($f["access_token_encrypted"], $googleKMS);
|
|
$f["access_token"] = $decrypted;
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
private function encrypt($plainText, $googleKMS=NULL) {
|
|
global $savvyext;
|
|
$cipherText = NULL;
|
|
try {
|
|
if ($googleKMS==NULL) {
|
|
$projectId = $savvyext->cfgReadChar('google.kms_project_id');
|
|
$authFile = $savvyext->cfgReadChar('google.kms_auth_file');
|
|
$keyRingId = $savvyext->cfgReadChar('google.kms_keyring_id');
|
|
$keyId = $savvyext->cfgReadChar('google.kms_key_id');
|
|
/*error_log("projectId=$projectId");
|
|
error_log("authFile=$authFile");
|
|
error_log("keyRingId=$keyRingId");
|
|
error_log("keyId=$keyId");*/
|
|
$googleKMS = new GoogleKMS(
|
|
$projectId,
|
|
$authFile,
|
|
$keyRingId,
|
|
$keyId
|
|
);
|
|
}
|
|
$cipherText = bin2hex($googleKMS->encrypt($plainText));
|
|
} catch (Exception $e) {
|
|
error_log($e->getMessage());
|
|
}
|
|
return [$cipherText, $googleKMS];
|
|
}
|
|
|
|
private function decrypt($cipherText, $googleKMS=NULL) {
|
|
global $savvyext;
|
|
$plainText = NULL;
|
|
try {
|
|
if ($googleKMS==NULL) {
|
|
$projectId = $savvyext->cfgReadChar('google.kms_project_id');
|
|
$authFile = $savvyext->cfgReadChar('google.kms_auth_file');
|
|
$keyRingId = $savvyext->cfgReadChar('google.kms_keyring_id');
|
|
$keyId = $savvyext->cfgReadChar('google.kms_key_id');
|
|
|
|
$googleKMS = new GoogleKMS(
|
|
$projectId,
|
|
$authFile,
|
|
$keyRingId,
|
|
$keyId
|
|
);
|
|
}
|
|
$plainText = $googleKMS->decrypt(hex2bin($cipherText));
|
|
} catch (Exception $e) {
|
|
error_log($e->getMessage());
|
|
}
|
|
return [$plainText, $googleKMS];
|
|
}
|
|
|
|
public function saveToken($db, $oauth2_provider_id, $member_id, $refresh_token, $access_token, $email, $name, $user_id="") {
|
|
|
|
$result = array();
|
|
$db_oauth2_provider_id = (int)$oauth2_provider_id;
|
|
$db_member_id = (int)$member_id;
|
|
// TODO: clear
|
|
$db_refresh_token = substr(pg_escape_string($refresh_token),0,500);
|
|
// TODO: clear
|
|
$db_access_token = substr(pg_escape_string($access_token),0,500);
|
|
list($refresh_token_encrypted,$googleKMS) = self::encrypt($refresh_token);
|
|
$db_refresh_token_encrypted = pg_escape_string($refresh_token_encrypted);
|
|
list($access_token_encrypted,$googleKMS) = self::encrypt($access_token, $googleKMS);
|
|
$db_access_token_encrypted = pg_escape_string($access_token_encrypted);
|
|
if ($refresh_token_encrypted==NULL || $access_token_encrypted==NULL) {
|
|
return NULL;
|
|
}
|
|
$db_email = pg_escape_string($email);
|
|
$db_name = pg_escape_string($name);
|
|
$db_user_id = pg_escape_string($user_id);
|
|
$q = "INSERT INTO oauth2_tokens (oauth2_provider_id, member_id, refresh_token, access_token, ";
|
|
$q.= "email, name, expires_in, refresh_token_encrypted, access_token_encrypted, user_id) VALUES (";
|
|
$q.= "${db_oauth2_provider_id},${db_member_id},'${db_refresh_token}','${db_access_token}',";
|
|
$q.= "'${db_email}','${db_name}',now() + interval '3600','${db_refresh_token_encrypted}',";
|
|
$q.= "'${db_access_token_encrypted}','${db_user_id}'";
|
|
$q.= ") RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
OAuth2::updateMemberEmailStatus($db, $db_member_id, 1);
|
|
return OAuth2::getTokenById($db, $f[0]);
|
|
}
|
|
return NULL;
|
|
}
|
|
|
|
public function getMemberByEmail($db, $email) {
|
|
$result = array();
|
|
$db_email = pg_escape_string(strtolower($email));
|
|
$q = "SELECT * FROM members WHERE lower(email)='${db_email}' OR lower(username)='${db_email}'";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$result = $f;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
public static function getMemberEmailByUserId($db, $oauth2_provider_id, $user_id) {
|
|
$db_oauth2_provider_id = (int)$oauth2_provider_id;
|
|
$db_user_id = pg_escape_string($user_id);
|
|
$q = "SELECT email FROM oauth2_tokens WHERE oauth2_provider_id=${db_oauth2_provider_id} ";
|
|
$q.= " AND user_id='${db_user_id}' AND (email<>'' OR email IS NOT NULL) ORDER BY created DESC LIMIT 1";
|
|
// error_log($q);
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
|
|
return $f[0];
|
|
}
|
|
return NULL;
|
|
}
|
|
|
|
public function remove($db, $member_id) {
|
|
$res = NULL;
|
|
$err = NULL;
|
|
$mid = (int)$member_id;
|
|
try {
|
|
$q = "SELECT md5(username) AS username,md5(firstname) AS firstname,md5(lastname) AS lastname,md5(email) AS email,md5(phone) AS phone,password FROM members WHERE id=${mid}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$nmid = 0;
|
|
$vals = []; $keys = [];
|
|
foreach ($f as $key=>$val) {
|
|
$keys[] = $key;
|
|
$vals[] = $val;
|
|
}
|
|
$q = "INSERT INTO members (id,".implode(",",$keys).") VALUES(-${mid},'".implode("','",$vals)."') RETURNING id";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$nmid = $f['id'];
|
|
} else {
|
|
$q = "SELECT * FROM members WHERE id=-${mid}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
$nmid = $f['id'];
|
|
} else {
|
|
throw new Exception("Failed to hash member data");
|
|
}
|
|
}
|
|
if ($nmid<0) {
|
|
$needs_delete = false;
|
|
$q = "SELECT * FROM trackedemail_item WHERE member_id=${mid}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r)) {
|
|
$needs_delete = true;
|
|
}
|
|
if ($needs_delete) {
|
|
// TODO: clear e-mail data...
|
|
//$q = "UPDATE trackedemail_item SET member_id=-${mid},subject=NULL,message=NULL,message_from=NULL,hash=NULL,oauth2_pull_job_id=NULL,message_date=NULL WHERE member_id=${mid}";
|
|
// DEBUG: testing
|
|
$q = "UPDATE trackedemail_item SET member_id=-${mid} WHERE member_id=${mid}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_affected_rows($r)) {
|
|
$res["mails_deleted"] = pg_affected_rows($r);
|
|
$res["message"] = "E-mails deleted";
|
|
} else {
|
|
throw new Exception("Failed to delete messages!");
|
|
}
|
|
} else {
|
|
$res["mails_deleted"] = 0;
|
|
$res["message"] = "No e-mails found to delete";
|
|
}
|
|
// TODO: delete...
|
|
$r = null;
|
|
// Delete OAuth2 pull job threads
|
|
$q = "delete from oauth2_pull_job_threads where oauth2_token_id in (select id from oauth2_tokens where member_id=${mid})";
|
|
//$r = pg_query($db, $q);
|
|
$res["pull_threads_deleted"] = (int)pg_affected_rows($r);
|
|
// Delete OAuth2 pull jobs
|
|
$q = "delete from oauth2_pull_jobs where oauth2_token_id in (select id from oauth2_tokens where member_id=${mid})";
|
|
//$r = pg_query($db, $q);
|
|
$res["pull_jobs_deleted"] = (int)pg_affected_rows($r);
|
|
// Delete OAuth2 tokens
|
|
$q = "delete from oauth2_tokens where member_id=${mid}";
|
|
//$r = pg_query($db, $q);
|
|
$res["oauth2_tokens_deleted"] = (int)pg_affected_rows($r);
|
|
} else {
|
|
throw new Exception("Failed to hash member's data");
|
|
}
|
|
} else {
|
|
throw new Exception("Failed to load member record");
|
|
}
|
|
} catch (Exception $e) {
|
|
$err = $e->getMessage();
|
|
if (pg_last_error()!="") {
|
|
$err.= ": ".pg_last_error();
|
|
}
|
|
}
|
|
return [$res, $err];
|
|
}
|
|
|
|
function updateMemberEmailStatus($db, $member_id, $status=0){
|
|
try {
|
|
$q = "UPDATE members SET email_connected=${status} WHERE id=${member_id}";
|
|
error_log('update email connected: '.$q);
|
|
$r = pg_query($db, $q);
|
|
} catch (Exception $e) {
|
|
$err = $e->getMessage();
|
|
if (pg_last_error()!="") {
|
|
$err.= ": ".pg_last_error();
|
|
error_log('update email connected error: '.$err);
|
|
}
|
|
}
|
|
}
|
|
|
|
function removeTokens($db, $member_id){
|
|
$res = NULL;
|
|
$err = NULL;
|
|
$mid = (int)$member_id;
|
|
try {
|
|
$q = "SELECT member_id FROM oauth2_tokens WHERE member_id=${mid}";
|
|
$r = pg_query($db, $q);
|
|
if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
|
|
OAuth2::updateMemberEmailStatus($db, $mid, 0);
|
|
$q = "UPDATE oauth2_pull_job_threads SET started=NOW(), completed=NOW() WHERE oauth2_token_id IN (SELECT id FROM oauth2_tokens WHERE member_id=${mid})";
|
|
error_log('update oauth2_pull_job_threads: '.$q);
|
|
$r = pg_query($db, $q);
|
|
$q = "UPDATE oauth2_pull_jobs SET started=NOW(), completed=NOW() WHERE oauth2_token_id IN (SELECT id FROM oauth2_tokens WHERE member_id=${mid})";
|
|
error_log('update oauth2_pull_jobs: '.$q);
|
|
$r = pg_query($db, $q);
|
|
$q = "UPDATE oauth2_tokens SET expires_in=now()-interval '4 hours' WHERE member_id=${mid}";
|
|
error_log('update oauth2_tokens: '.$q);
|
|
$r = pg_query($db, $q);
|
|
$res["message"] = "Email is disconnected";
|
|
$res["oauth2_tokens_deleted"] = (int)pg_affected_rows($r);
|
|
} else {
|
|
throw new Exception("Failed to load tokens");
|
|
}
|
|
} catch (Exception $e) {
|
|
$err = $e->getMessage();
|
|
if (pg_last_error()!="") {
|
|
$err.= ": ".pg_last_error();
|
|
}
|
|
}
|
|
error_log('removeTokens: '.json_encode([$res, $err]));
|
|
return [$res, $err];
|
|
}
|
|
|
|
|
|
function saveMembersSurvey($db, $surveyData,$out) {
|
|
if (empty($out["added"])) {//only save first time
|
|
$member_id = $out['member_id'];
|
|
foreach($surveyData as $group_key=>$survey){
|
|
$answers = isset($survey['answers'])?$survey['answers']:[];
|
|
foreach($answers as $answer_key => $value){
|
|
if($value==true){
|
|
$q = "INSERT INTO members_onboarding_survey ( member_id, answers_key, answers,status, added) VALUES (" . $member_id . ",'" . $answer_key . "','" . $value . "', 1, now())";
|
|
$res1 = pg_query($db, $q);
|
|
if ($res1 and pg_num_rows($res1) > 0) {
|
|
//logger
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
// vi:ts=2
|
|
|