co_security_db.class.php

Go to the documentation of this file.

<?php
/***************************************************************************************************************************/
defined( 'LGV_SD_CATCHER' ) or die ( 'Cannot Execute Directly' );   // Makes sure that this file is in the correct context.
 
if ( !defined('LGV_ADB_CATCHER') ) {
    define('LGV_ADB_CATCHER', 1);
}
 
require_once(CO_Config::db_class_dir().'/a_co_db.class.php');
 
/***************************************************************************************************************************/
class CO_Security_DB extends A_CO_DB {
    /***********************************************************************************************************************/
    /***********************/
    public function __construct(    $in_pdo_object,             
                                    $in_access_object = NULL    
                                ) {
        parent::__construct($in_pdo_object, $in_access_object);
        
        $this->table_name = 'co_security_nodes';    // This is the name of the SQL table we will query.
        
        $this->class_description = 'The security database class.';  // A simple explanation of what this class is.
    }
    
    /***********************/
    public function get_security_ids_for_id(    $in_id,         
                                                $no_personal    
                                            ) {
        $ret = NULL;
        
        $fetch_sql = "ids";
        if (CO_Config::use_personal_tokens() && !$no_personal) {
            $fetch_sql .= ",personal_ids";
        } else {
            $no_personal = false;
        }
        
        $sql = 'SELECT '.$fetch_sql.' FROM '.$this->table_name.' WHERE (access_class LIKE \'%Login%\') AND (login_id IS NOT NULL) AND (id=?)';
        $params = Array(intval($in_id));
        $temp = $this->execute_query($sql, $params);
// Commented out, but useful for debug.
// echo('SQL:<pre>'.htmlspecialchars(print_r($sql, true)).'</pre>');
// echo('PARAMS:<pre>'.htmlspecialchars(print_r($params, true)).'</pre>');
// echo('RESPONSE:<pre>'.htmlspecialchars(print_r($temp, true)).'</pre>');
// echo('<pre>'.($no_personal ? 'NO ' : '').'PERSONAL</pre>');
// echo('ERROR:<pre>'.htmlspecialchars(print_r($this->error, true)).'</pre>');
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = isset($temp[0]['ids']) ? explode(',', $temp[0]['ids']) : [];
            if (!$no_personal && isset($temp[0]['personal_ids'])) {
                $ret_temp = explode(',', $temp[0]['personal_ids']);
                foreach ($ret_temp as $i) {
                    if (0 < $i) {
                        array_push($ret, $i);
                    }
                }
            }
            if (isset($ret) && is_array($ret) && count($ret)) {
                $ret = array_unique(array_map('intval', $ret));
                $ret_temp = Array();
                foreach ($ret as $i) {
                    if (0 < $i) {
                        array_push($ret_temp, $i);
                    }
                }
                sort($ret_temp);
                $ret = $ret_temp;
            }
            array_unshift($ret, $in_id);
        } else {
            $ret = Array($in_id);
        }
        
        return $ret;
    }
    
    /***********************/
    public function add_personal_token_from_current_login(  $in_to_id,  
                                                            $in_id      
                                                            ) {
        $in_to_id = intval($in_to_id);
        $in_id = intval($in_id);
        // If the current login does not own the given ID as a personal token, then we can't proceed.
        if (CO_Config::use_personal_tokens()) {
            $sql = 'SELECT ids FROM '.$this->table_name.' WHERE (id=?)';
            $params = [$in_to_id];
            $temp = $this->execute_query($sql, $params);
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                $ret = explode(',', $temp[0]['ids']);
                
                if (isset($ret) && is_array($ret)) {
                    $ret = array_map('intval', $ret);
                    $ret[] = $in_id;
                    sort($ret);
                    $ret = array_unique($ret);
                    $new_ids = implode(',', array_unique($ret));
 
                    $sql = 'UPDATE '.$this->table_name.' SET ids=? WHERE id=?';
                    $params = [$new_ids, $in_to_id];
                    $this->execute_query($sql, $params, true);
                    if ($this->error == NULL) {
                        return true;
                    };
                }
            } else {
                $ret = Array();
            }
        }
        
        return false;
    }
    
    /***********************/
    public function remove_personal_token_from_this_login(  $in_to_id,  
                                                            $in_id      
                                                            ) {
        $in_to_id = intval($in_to_id);
        $in_id = intval($in_id);
        // If the current login does not own the given ID as a personal token, then we can't proceed.
        if (CO_Config::use_personal_tokens()) {
            $sql = 'SELECT ids FROM '.$this->table_name.' WHERE (id=?)';
            $params = [$in_to_id];
            $temp = $this->execute_query($sql, $params);
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                $ret = explode(',', $temp[0]['ids']);
                
                if (isset($ret) && is_array($ret)) {
                    $return = false;
                    $ret = array_unique(array_map('intval', $ret));
                    sort($ret);
                    
                    if (($key = array_search($in_id, $ret)) !== false) {
                        $return = true;
                        unset($ret[$key]);
                    }
                    
                    $new_ids = implode(',', $ret);
 
                    $sql = 'UPDATE '.$this->table_name.' SET ids=? WHERE id=?';
                    $params = [$new_ids, $in_to_id];
                    $this->execute_query($sql, $params, true);
                    if ($this->error == NULL) {
                        return $return;
                    };
                }
            } else {
                $ret = Array();
            }
        }
        
        return false;
    }
    
    /***********************/
    public function get_personal_ids_for_id(    $in_id  
                                            ) {
        $ret = NULL;
        
        if (!CO_Config::use_personal_tokens()) {
            return $ret;
        }
        
        $sql = 'SELECT personal_ids FROM '.$this->table_name.' WHERE (access_class LIKE \'%Login%\') AND (login_id IS NOT NULL) AND (id=?)';
 
        $temp = $this->execute_query($sql, Array(intval($in_id)));
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = explode(',', $temp[0]['personal_ids']);
            if (isset($ret) && is_array($ret) && count($ret)) {
                $ret = array_unique(array_map('intval', $ret));
                $ret_temp = Array();
                foreach ($ret as $i) {
                    if (0 < $i) {
                        array_push($ret_temp, $i);
                    }
                }
                sort($ret_temp);
                $ret = $ret_temp;
            }
        } else {
            $ret = Array();
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_all_personal_ids_except_for_id( $in_id = 0  
                                                        ) {
        $ret = NULL;
        
        if (!CO_Config::use_personal_tokens()) {
            return $ret;
        }
        
        $sql = 'SELECT personal_ids FROM '.$this->table_name.' WHERE (access_class LIKE \'%Login%\') AND (login_id IS NOT NULL) AND (id<>?)';
 
        $temp = $this->execute_query($sql, Array(intval($in_id)));
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = "";
            foreach ($temp as $i) {
                if ($i['personal_ids']) {
                    if ($ret) {
                        $ret .= ",";
                    }
                    $ret .= $i['personal_ids'];
                }
            }
            $ret = explode(",", $ret);
            if (isset($ret) && is_array($ret) && count($ret)) {
                $ret = array_unique(array_map('intval', $ret));
                $ret_temp = Array();
                foreach ($ret as $i) {
                    if (0 < $i) {
                        array_push($ret_temp, $i);
                    }
                }
                sort($ret_temp);
                $ret = $ret_temp;
            }
        } else {
            $ret = Array();
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_logins_that_have_any_of_my_ids() {
        $ret = NULL;
        
        // Will not work for God Mode, as God doesn't have personal IDs.
        if (!CO_Config::use_personal_tokens() || $this->access_object->god_mode()) {
            return $ret;
        }
        
        // We can only check personal IDs relevant to our login. 
        $in_ids = $this->access_object->get_personal_security_ids();
        $in_id = $this->access_object->get_login_id();
        
        $sql = 'SELECT id,ids FROM '.$this->table_name.' WHERE (access_class LIKE \'%Login%\') AND (login_id IS NOT NULL) AND (id<>?)';
 
        $temp = $this->execute_query($sql, Array(intval($in_id)));
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = [];
            foreach ($temp as $i) {
                $tmp = [];
                if (isset($i['id']) && $i['id'] && isset($i['ids']) && $i['ids']) {
                    $key = intval($i['id']);
                    $ids = array_unique(array_map('intval', explode(',', $i['ids'])));
                    if (1 < $key && count($ids)) {
                        sort($ids);
                        $tmp_ids = [];
                        foreach($ids as $tmp_id) {
                            if (in_array($tmp_id, $in_ids)) {
                                $tmp_ids[] = $tmp_id;
                            }
                        }
                        if (count($tmp_ids)) {
                            $ret[$key] = $tmp_ids;
                        }
                    }
                }
            }
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_logins_with_personal_ids() {
        $ret = NULL;
        
        // Will only work for God Mode.
        if (!CO_Config::use_personal_tokens() || !$this->access_object->god_mode()) {
            return $ret;
        }
        
        $sql = 'SELECT id,personal_ids FROM '.$this->table_name.' WHERE (access_class LIKE \'%Login%\') AND (login_id IS NOT NULL) AND (personal_ids IS NOT NULL) AND (personal_ids<>\'\')';
 
        $temp = $this->execute_query($sql, Array());
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = [];
            foreach ($temp as $i) {
                $tmp = [];
                if (isset($i['id']) && $i['id'] && isset($i['personal_ids']) && $i['personal_ids']) {
                    $key = intval($i['id']);
                    $ids = array_unique(array_map('intval', explode(',', $i['personal_ids'])));
                    $ret[$key] = $ids;
                }
            }
        }
        return $ret;
    }
    
    /***********************/
    public function get_all_login_ids() {
        $ret = NULL;
        
        $sql = 'SELECT id FROM '.$this->table_name.' WHERE (access_class LIKE \'%Login%\') AND (login_id IS NOT NULL) AND (login_id<>\'\')';
 
        $temp = $this->execute_query($sql, Array());
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = "";
            foreach ($temp as $i) {
                if ($i['id']) {
                    if ($ret) {
                        $ret .= ",";
                    }
                    $ret .= $i['id'];
                }
            }
            $ret = explode(",", $ret);
            if (isset($ret) && is_array($ret) && count($ret)) {
                $ret = array_unique(array_map('intval', $ret));
                $ret_temp = Array();
                foreach ($ret as $i) {
                    if (0 < $i) {
                        array_push($ret_temp, $i);
                    }
                }
                sort($ret_temp);
                $ret = $ret_temp;
            }
        } else {
            $ret = Array();
        }
        
        return $ret;
    }
    
    /***********************/
    public function is_this_a_personal_id(  $in_id  
                                            ) {
        $in_id = intval($in_id);
        
        if (CO_Config::use_personal_tokens() && (1 < $in_id)) {
            $ret = $this->get_all_personal_ids_except_for_id();
    
            if (!$this->error) {
                return in_array($in_id, $ret);
            }
        }
 
        return false;
    }
    
    /***********************/
    public function is_this_a_login_id( $in_id  
                                        ) {
        $in_id = intval($in_id);
        
        if (1 < $in_id) {
            $ret = $this->get_all_login_ids();
    
            if (!$this->error) {
                return in_array($in_id, $ret);
            }
        }
 
        return false;
    }
    
    /***********************/
    public function i_have_all_ids(  $in_id  
                                    ) {
        $ret = false;
        
        if (intval($in_id)) {
            $sql = 'SELECT write_security_id, ids FROM '.$this->table_name.' WHERE id=?';
 
            $result = $this->execute_query($sql, Array(intval($in_id)));
        
            if (isset($result) && is_array($result) && (1 == count($result))) {
                $access_ids = $this->access_object->get_security_ids();
                $write_security_id = intval($result[0]['write_security_id']);
                $ids = (isset($result[0]['ids']) && trim($result[0]['ids'])) ? array_map('intval', explode(',', trim($result[0]['ids']))) : [];
                $ids[] = $write_security_id;
                sort($ids);
                sort($access_ids);
                $diff = array_diff($ids, $access_ids);
 
                if (is_array($diff) && !count($diff)) { // We only go true if there are no outliers. We have to have ALL of them.
                    $ret = true;
                }
            }
        }
        return $ret;
    }
    
    /***********************/
    public function get_all_tokens() {
        $ret = NULL;
        
        if ($this->access_object->god_mode()) {
            $sql = 'SELECT id FROM '.$this->table_name.' WHERE true ORDER BY id';
            $temp = $this->execute_query($sql, Array());
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                $ret = [];
                foreach($temp as $row) {
                    $ret[] = intval($row['id']);
                }
            }
        }
        return $ret;
    }
    
    /***********************/
    public function get_all_visible_logins() {
        $ret = array();
        $sql = 'SELECT id,object_name,login_id FROM '.$this->table_name.' WHERE';
    
        $predicate = $this->_create_read_security_predicate();
        
        if ($predicate) {
            $sql = "$sql $predicate AND";
        }
        
        $sql = "$sql login_id<>''";
        
        $temp = $this->execute_query($sql, Array());
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            foreach($temp as $value) {
                $ret[$value["id"]] = [$value["object_name"], $value["login_id"]];
            }
        }
 
        return $ret;
    }
    
    /***********************/
    public function get_initial_record_by_login_id( $in_login_id    
                                                    ) {
        $ret = NULL;
        
        $sql = 'SELECT * FROM '.$this->table_name.' WHERE login_id=?';
        $params = Array(trim($in_login_id));
        
        $temp = $this->execute_query($sql, $params);
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $result = $this->_instantiate_record($temp[0]);
            if ($result) {
                $ret = $result;
            }
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_credentials_by_api_key( $in_api_key 
                                                ) {
        $ret = NULL;
        
        $sql = 'SELECT * FROM '.$this->table_name.' WHERE api_key LIKE ?';
        $params = Array(trim($in_api_key).' - %');
        
        $temp = $this->execute_query($sql, $params);
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $result = $this->_instantiate_record($temp[0]);
            if (isset($result) && ($result instanceof CO_Security_Login) && $result && $result->is_api_key_valid($in_api_key)) {
                $ret = Array('login_id' => $result->login_id, 'hashed_password' => $result->get_crypted_password());
                if ($result->id() == CO_Config::god_mode_id()) {
                    $ret['hashed_password'] = $in_api_key;
                }
            } elseif (isset($result) && ($result instanceof CO_Security_Login)) {
                $this->error = $result->error;
            }
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_initial_record_by_id( $in_id    
                                            ) {
        $ret = NULL;
        
        $sql = 'SELECT * FROM '.$this->table_name.' WHERE id=?';
        
        $temp = $this->execute_query($sql, Array(intval($in_id)));
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $result = $this->_instantiate_record($temp[0]);
            if ($result) {
                $ret = $result;
            }
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_single_record_by_login_id(  $in_login_id,       
                                                    $and_write = false  
                                                    ) {
        $ret = NULL;
        
        $temp = $this->get_multiple_records_by_login_id(Array($in_login_id), $and_write);
        
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            $ret = $temp[0];
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_multiple_records_by_login_id(   $in_login_id_array,
                                                        $and_write = false
                                                    ) {
        $ret = NULL;
        
        $predicate = $this->_create_security_predicate($and_write);
        
        if ($predicate) {   // If we got a predicate, then we AND it with the rest of the statement.
            $predicate .= ' AND ';
        }
        
        $sql = 'SELECT * FROM '.$this->table_name.' WHERE '.$predicate. '(';
        
        $params = Array();
        
        foreach ($in_login_id_array as $id) {
            if (trim($id)) {                
                if (0 < count($params)) {
                    $sql .= ') OR (';
                }
                $sql.= 'login_id=?';
                array_push($params, $id);
            }
        }
        
        $sql  .= ')';
        
        if (0 < count($params)) {
            $temp = $this->execute_query($sql, $params);
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                $ret = Array();
                foreach ($temp as $result) {
                    $result = $this->_instantiate_record($result);
                    if ($result) {
                        array_push($ret, $result);
                    }
                }
                usort($ret, function($a, $b){return ($a->id() > $b->id());});
            }
        }
        
        // At this point, only managers (or the login object, itself) can see logins. God, too, of course.
        $my_id = $this->access_object->get_login_id();
        $my_login_instance_is_manager = $this->access_object->god_mode() || ($this->access_object->get_login_item() instanceof CO_Login_Manager);
        $temp_ret = Array();
        
        if (isset($ret) && is_array($ret) && count($ret)) {
            foreach ($ret as $instance) {
                if (($instance->id() == $my_id) || $my_login_instance_is_manager) {
                    array_push($temp_ret, $instance);
                }
            }
        }
        
        return $temp_ret;
    }
        
    /***********************/
    public function get_all_login_objects ( $and_write = false  
                                            ) {
        $ret = Array();
        
        // Can only look for tokens we can see.
        
        $predicate = $this->_create_security_predicate($and_write);
        
        if ($predicate) {   // If we got a predicate, then we AND it with the rest of the statement.
            $predicate .= ' AND ';
        }
        
        $sql = 'SELECT * FROM '.$this->table_name.' WHERE '.$predicate. '(login_id IS NOT NULL) AND (login_id<>\'\')';
        $temp = $this->execute_query($sql, Array());    // We just get everything.
        if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
            foreach ($temp as $result) {
                $result = $this->_instantiate_record($result);
                if ($result) {
                    array_push($ret, $result);
                }
            }
        }
        
        // At this point, only managers (or the login object, itself) can see logins. God, too, of course.
        $my_id = $this->access_object->get_login_id();
        $my_login_instance_is_manager = $this->access_object->god_mode() || ($this->access_object->get_login_item() instanceof CO_Login_Manager);
        $temp_ret = Array();
        
        foreach ($ret as $instance) {
            if (($instance->id() == $my_id) || $my_login_instance_is_manager) {
                array_push($temp_ret, $instance);
            }
        }
        
        return $temp_ret;
    }
    
    /***********************/
    public function get_all_login_objects_with_access(  $in_security_token, 
                                                        $and_write = false  
                                                        ) {
        $ret = Array();
        
        $in_security_token = intval($in_security_token);
        
        $access_ids = $this->access_object->get_security_ids();
        
        // Can only look for tokens we can see.
        if (($this->access_object->god_mode() && $in_security_token) || in_array($in_security_token, $access_ids)) {
            $predicate = $this->_create_security_predicate($and_write);
        
            if ($predicate) {   // If we got a predicate, then we AND it with the rest of the statement.
                $predicate .= ' AND ';
            }
            
            $sql = 'SELECT * FROM '.$this->table_name.' WHERE '.$predicate.'(login_id IS NOT NULL) AND (login_id<>\'\')';
            $temp = $this->execute_query($sql, Array());    // We just get everything.
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                $ret = Array();
                
                foreach ($temp as $result) {
                    $id = intval($result['id']);
                    $id_array = Array($id);
                    $ids = explode(',', $result['ids']);
                    if (isset($ids) && is_array($ids) && count($ids)) {
                        $ids = array_map('trim', $ids);
                        $ids = array_map('intval', $ids);
                        
                        foreach ($ids as $single_id) {
                            array_push($id_array, $single_id);
                        }
                    }
                    
                    $found = (2 > $in_security_token);
                    
                    if (!$found) {
                        foreach ($id_array as $test_id) {
                            if ($test_id == $in_security_token) {
                                $found = true;
                                break;
                            }
                        }
                    }
                    
                    if ($found) {
                        $result = $this->_instantiate_record($result);
                        if ($result) {
                            array_push($ret, $result);
                        }
                    }
                }
            }
        }
        
        // At this point, only managers (or the login object, itself) can see logins. God, too, of course.
        $my_id = $this->access_object->get_login_id();
        $my_login_instance_is_manager = $this->access_object->god_mode() || ($this->access_object->get_login_item() instanceof CO_Login_Manager);
        $temp_ret = Array();
        
        foreach ($ret as $instance) {
            if (($instance->id() == $my_id) || $my_login_instance_is_manager) {
                array_push($temp_ret, $instance);
            }
        }
        
        return $temp_ret;
    }
    
    /***********************/
    public function count_all_login_objects_with_access($in_security_token  
                                                        ) {
        $ret = -1;
        
        $in_security_token = intval($in_security_token);
        
        // No security predicate, but we do have to "own" the given token.
        if (($this->access_object->god_mode() && $in_security_token) || in_array($in_security_token, $this->access_object->get_security_ids())) {
            $sql = 'SELECT * FROM '.$this->table_name.' WHERE (login_id IS NOT NULL) AND (login_id<>\'\') AND (id<>?)';
            $temp = $this->execute_query($sql, Array(intval(CO_Config::god_mode_id())));    // We just get everything.
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                $ret = 0;
                
                foreach ($temp as $result) {
                    $id = intval($result['id']);
                    $ids = explode(',', $result['ids']);
                    if (isset($ids) && is_array($ids) && count($ids)) {
                        $ids = array_map('trim', $ids);
                        $ids = array_map('intval', $ids);
                        array_push($ids, $id);
                        $ids = array_unique($ids);
                        foreach ($ids as $single_id) {
                            if ($single_id == $in_security_token) {
                                $ret += 1;
                                break;
                            }
                        }
                    }
                }
            }
        }
        
        return $ret;
    }
    
    /***********************/
    public function get_all_user_objects_with_access($in_security_token  
                                                        ) {
        $ret = array();
        
        $in_security_token = intval($in_security_token);
        $access_instance = $this->access_object;
        
        // No security predicate, but we do have to "own" the given token.
        if (($access_instance->god_mode() && $in_security_token) || in_array($in_security_token, $access_instance->get_security_ids())) {
            $sql = 'SELECT * FROM '.$this->table_name.' WHERE (login_id IS NOT NULL) AND (login_id<>\'\') AND (id<>?)';
            $temp = $this->execute_query($sql, Array(intval(CO_Config::god_mode_id())));    // We just get everything.
            if (isset($temp) && $temp && is_array($temp) && count($temp) ) {
                foreach ($temp as $result) {
                    $id = intval($result['id']);
                    $ids = explode(',', $result['ids']);
                    if (isset($ids) && is_array($ids) && count($ids)) {
                        $ids = array_map('trim', $ids);
                        $ids = array_map('intval', $ids);
                        array_push($ids, $id);
                        $ids = array_unique($ids);
                        foreach ($ids as $single_id) {
                            if ($single_id == $in_security_token) {
                                array_push($ret, $id);
                                break;
                            }
                        }
                    }
                }
            }
        }
 
        $returnVar = array();
        foreach ($ret as $id) {
            $temp2 = $access_instance->get_user_from_login($id);
            if (isset($temp2) && $temp2) {
                array_push($returnVar, $temp2);
            }
        }
        
        return $returnVar;
    }
};