Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- class Clipbucket_db
- {
- /** @var mysqli $mysqli */
- var $mysqli = '';
- var $db_name = '';
- var $db_uname = '';
- var $db_pwd = '';
- var $db_host = '';
- var $total_queries_sql = [];
- var $total_queries = 0;
- /**
- * Connect to mysqli Database
- *
- * @param $host
- * @param $name
- * @param $uname
- * @param $pwd
- *
- * @return bool|void
- *
- * @internal param $ : { string } { $host } { your database host e.g localhost }
- * @internal param $ : { string } { $name } { name of database to connect to }
- * @internal param $ : { string } { $uname } { your database username }
- * @internal param $ : { string } { $pwd } { password of database to connect to }
- */
- function connect($host = '', $name = '', $uname = '', $pwd = '')
- {
- try {
- if (!$host) {
- $host = $this->db_host;
- } else {
- $this->db_host = $host;
- }
- if (!$name) {
- $name = $this->db_name;
- } else {
- $this->db_name = $name;
- }
- if (!$uname) {
- $uname = $this->db_uname;
- } else {
- $this->db_uname = $uname;
- }
- if (!$pwd) {
- $pwd = $this->db_pwd;
- } else {
- $this->db_pwd = $pwd;
- }
- $this->mysqli = new mysqli($host, $uname, $pwd, $name);
- if ($this->mysqli->connect_errno) {
- return false;
- }
- $this->execute('SET NAMES "utf8mb4"');
- } catch (\Exception $e) {
- $error = $e->getMessage();
- error_log($error);
- if (in_dev()) {
- die($error);
- }
- }
- }
- /**
- * Select elements from database with query
- *
- * @param : { string } { $query } { mysql query to run }
- *
- * @return array : { array } { $data } { array of selected data }
- * @throws Exception
- */
- function _select($query, $cached_time = -1, $cached_key = ''): array
- {
- try {
- $redis = CacheRedis::getInstance();
- if ($redis->isEnabled() && $cached_time != -1) {
- if (in_dev()) {
- $start = microtime(true);
- $return = $redis->get($cached_key . ':' . $query);
- $end = microtime(true);
- $timetook = $end - $start;
- if (!empty($return)) {
- devWitch($query, 'select', $timetook, true);
- }
- } else {
- $return = $redis->get($cached_key . ':' . $query);
- }
- if (!empty($return)) {
- return $return;
- }
- }
- $result = $this->execute($query, 'select');
- $data = [];
- if ($result) {
- while ($row = $result->fetch_assoc()) {
- $data[] = $row;
- }
- $result->close();
- }
- if ($redis->isEnabled() && $cached_time != -1 && !empty($data)) {
- $redis->set($cached_key.':'.$query, $data, $cached_time);
- }
- } catch (\Exception $e) {
- if ($e->getMessage() == 'lang_not_installed' || $e->getMessage() == 'version_not_installed') {
- throw $e;
- }
- $this->handleError($query);
- }
- return $data;
- }
- /**
- * Select elements from database with numerous conditions
- *
- * @param : { string } { $tbl } { table to select data from }
- * @param string $fields
- * @param bool $cond
- * @param bool $limit
- * @param bool $order
- * @param bool $ep
- *
- * @return array : { array } { $data } { array of selected data }
- * @throws Exception
- */
- function select($tbl, $fields = '*', $cond = false, $limit = false, $order = false, $ep = false, $cached_time = -1, $cached_key = ''): array
- {
- $query_params = '';
- if ($cond) {
- $query_params .= ' WHERE ' . $cond;
- }
- if ($order) {
- $query_params .= ' ORDER BY ' . $order;
- }
- if ($limit) {
- $query_params .= ' LIMIT ' . $limit;
- }
- $query = 'SELECT ' . $fields . ' FROM ' . $tbl . $query_params . ' ' . $ep;
- return $this->_select($query, $cached_time, $cached_key);
- }
- /**
- * Count values in given table using MySQL COUNT
- *
- * @param : { string } { $tbl } { table to count data from }
- * @param string $fields
- * @param bool $cond
- *
- * @return bool|int
- * @throws Exception
- */
- function count($tbl, $fields = '*', $cond = false, $ep = '',$cached_time = -1, $cached_key = '')
- {
- $condition = '';
- if ($cond) {
- $condition = ' WHERE ' . $cond;
- }
- $query = 'SELECT COUNT(' . $fields . ') FROM ' . $tbl . $condition . $ep;
- $result = $this->_select($query, $cached_time, $cached_key);
- if ($result) {
- $fields = $result[0];
- foreach ($fields as $field) {
- return $field;
- }
- }
- return false;
- }
- /**
- * Get row using query
- *
- * @param : { string } { $query } { query to run to get row }
- *
- * @return mixed
- * @throws Exception
- */
- function GetRow($query)
- {
- $result = $this->_select($query);
- if ($result) {
- return $result[0];
- }
- }
- /**
- * Execute a MYSQL query directly without processing
- *
- * @param : { string } { $query } { query that you want to execute }
- *
- * @return bool|mysqli_result
- * @throws Exception
- */
- function execute($query, $type = 'execute')
- {
- $this->ping();
- try {
- if (in_dev()) {
- $start = microtime(true);
- $data = $this->mysqli->query($query);
- $end = microtime(true);
- $timetook = $end - $start;
- devWitch($query, $type, $timetook, false);
- } else {
- $data = $this->mysqli->query($query);
- }
- $this->handleError($query);
- return $data;
- } catch (\Exception $e) {
- if ($e->getMessage() == 'lang_not_installed' || $e->getMessage() == 'version_not_installed') {
- throw $e;
- }
- $this->handleError($query);
- }
- return false;
- }
- /**
- * Update database fields { table, fields, values style }
- *
- * @param $tbl
- * @param $flds
- * @param $vls
- * @param $cond
- * @param null $ep
- *
- * @throws Exception
- * @internal param $ : { string } { $tbl } { table to ujpdate values in }
- * @internal param $ : { array } { $flds } { array of fields you want to update }
- * @internal param $ : { array } { $vls } { array of values to update against fields }
- * @internal param $ : { string } { $cond } { mysql condition for query }
- * @internal param $ : { string } { $ep } { extra parameter after condition }
- */
- function update($tbl, $flds, $vls, $cond, $ep = null)
- {
- $this->ping();
- $total_fields = count($flds);
- $count = 0;
- $fields_query = '';
- for ($i = 0; $i < $total_fields; $i++) {
- $count++;
- $val = ($vls[$i]);
- preg_match('/\|no_mc\|/', $val, $matches);
- if ($matches) {
- $val = preg_replace('/\|no_mc\|/', '', $val);
- } else {
- $val = $this->clean_var($val);
- }
- $needle = substr($val, 0, 3);
- if ($needle != '|f|') {
- $fields_query .= $flds[$i] . "='" . $val . "'";
- } else {
- $val = substr($val, 3, strlen($val));
- $fields_query .= $flds[$i] . '=' . $val;
- }
- if ($total_fields != $count) {
- $fields_query .= ',';
- }
- }
- //Complete Query
- $query = 'UPDATE ' . $tbl . ' SET ' . $fields_query . ' WHERE ' . $cond . ' ' . $ep;
- $this->execute($query, 'update');
- }
- function updateSpecialCase($tbl, $key, $value)
- {
- $this->ping();
- if (is_array($key) && is_array($value) && count($key) === count($value)) {
- $updates = [];
- for ($i = 0; $i < count($key); $i++) {
- $cleanedValue = preg_replace('/\|no_mc\|/', '', $value[$i]);
- $cleanedValue = ($cleanedValue[0] != '|') ? $this->clean_var($cleanedValue) : substr($cleanedValue, 1);
- $updates[] = $key[$i] . "='" . $cleanedValue . "'";
- }
- $fieldsQuery = implode(',', $updates);
- $query = 'UPDATE ' . $tbl . ' SET ' . $fieldsQuery;
- $this->execute($query, 'update');
- } else {
- // Handle the case when $key and $value are not arrays of the same length.
- // You can log an error, throw an exception, or perform other error handling.
- // For example:
- error_log('Error: $key and $value must be arrays!.');
- // You can also throw an exception:
- // throw new Exception('$key and $value must be arrays of the same length');
- }
- }
- function updateUser2FACheckBoxState($usersTable, $inputUserId, $inputUser2FACheckboxState) {
- $QueryFor2FACheckBox = "SELECT user2FAstate, user2FAconfigstate FROM $usersTable WHERE user2FAstate = 0 AND user2FAconfigstate = 0 AND userid = $inputUserId";
- $result = $this->execute($QueryFor2FACheckBox, 'select');
- if ($result !== false) {
- $row_count = mysqli_num_rows($result);
- if ($row_count > 0) {
- $updateQuery = "UPDATE $usersTable SET user2FAstate = $inputUser2FACheckboxState, user2FAconfigstate = $inputUser2FACheckboxState WHERE userid = $inputUserId";
- $this->execute($updateQuery, 'update');
- } else {
- error_log( "No rows found with user2FAstate and user2FAconfigstate both equal to 0 for user $inputUserId.");
- }
- } else {
- error_log( "Error executing SELECT query.");
- }
- }
- function getStatusOfUser2FACheckBox($usersTable, $inputUserId): bool {
- $query = "SELECT user2FAstate, user2FAconfigstate FROM $usersTable WHERE userid = $inputUserId";
- $result = $this->execute($query, 'select');
- if ($result === false) {
- return false; // Error executing query, consider this as false
- }
- $row = $result->fetch_assoc(); // Fetch the first row as an associative array
- if ($row) {
- // Check if both user2FAstate and user2FAconfigstate are equal to 1
- if ($row['user2FAstate'] === '1' && $row['user2FAconfigstate'] === '1') {
- return true;
- }
- }
- return false; // If no rows are returned or conditions not met
- }
- function setUser2FASecretOTP($usersTable, $inputUserID, $inputEncryptedSecureOTP) {
- $CheckIf2FANotSetUp = "SELECT user2FAstate, user2FAconfigstate, user2FAprivatecode FROM $usersTable WHERE userid = $inputUserID AND user2FAstate = 1 AND user2FAconfigstate = 1 AND user2FAprivatecode = 0;";
- $result = $this->execute($CheckIf2FANotSetUp, 'select');
- if ($result !== false) {
- $row_count = mysqli_num_rows($result);
- if ($row_count > 0) {
- $updateQuery = "UPDATE $usersTable SET user2FAprivatecode = '$inputEncryptedSecureOTP' WHERE userid = $inputUserID";
- $this->execute($updateQuery, 'update');
- } else {
- error_log("No rows found with user2FAstate and user2FAconfigstate and user2FAprivatecode all those rows should be equal to 0 for user $inputUserID.");
- }
- } else {
- error_log("Error executing SELECT query.");
- }
- }
- function getGlobal2FAState($usersTable): bool {
- $Global2FAstateQuery = "SELECT enabled2fa FROM $usersTable WHERE enabled2fa = 1;";
- $result = $this->execute($Global2FAstateQuery, 'select');
- if ($result !== false) {
- $row_count = mysqli_num_rows($result);
- return ($row_count > 0); // Returns true if at least one row is found, indicating 2FA is enabled.
- } else {
- error_log("Global 2FA state NOT ENABLED!");
- return false;
- }
- }
- function getUser2FAState($usersTable, $username):bool{
- $Given2FAUserState = "SELECT user2FAstate, user2FAconfigstate, username FROM $usersTable WHERE username = '$username' AND user2FAstate = 1 AND user2FAconfigstate = 1 AND username IS NOT NULL;";
- $result = $this->execute($Given2FAUserState, 'select');
- if ($result !== false) {
- $row_count = mysqli_num_rows($result);
- return ($row_count > 0); // Returns true if at least one row is found, indicating 2FA is enabled.
- } else {
- error_log("User: $username 2FA state is NOT ENABLED!");
- return false;
- }
- }
- function getUser2FASecret($usersTable, $username) {
- $Encrypted2FAUserSecretQuery = "SELECT user2FAprivatecode FROM $usersTable WHERE username = '$username' AND user2FAprivatecode IS NOT NULL;";
- $result = $this->execute($Encrypted2FAUserSecretQuery, 'select');
- if ($result !== false) {
- $row = mysqli_fetch_assoc($result);
- if ($row && isset($row['user2FAprivatecode'])) {
- return $row['user2FAprivatecode']; // Return the 2FA secret code if it exists and is not null.
- } else {
- error_log("User: $username does not have a valid 2FA secret code.");
- return false;
- }
- } else {
- error_log("Error executing query to retrieve 2FA secret code for user: $username");
- return false;
- }
- }
- /**
- * Update database fields { table, associative array style }
- *
- * @param $tbl
- * @param $fields
- * @param $cond
- * @param null $ep
- *
- * @return bool : { boolean }
- *
- * @throws Exception
- * @internal param $ : { array } { $fields } { associative array with fields and values }
- * @internal param $ : { string } { $cond } { mysql condition for query }
- * @internal param $ : { string } { $tbl } { table to update values in }
- */
- function db_update($tbl, $fields, $cond, $ep = null)
- {
- $this->ping();
- $count = 0;
- $fields_query = '';
- foreach ($fields as $field => $val) {
- if ($count > 0) {
- $fields_query .= ',';
- }
- $needle = substr($val, 0, 2);
- if ($needle != '{{') {
- $value = "'" . mysql_clean($val) . "'";
- } else {
- $val = substr($val, 2, strlen($val) - 4);
- $value = mysql_clean($val);
- }
- $fields_query .= $field . "=$value ";
- $count += $count;
- }
- //Complete Query
- $query = 'UPDATE ' . $tbl . ' SET ' . $fields_query . ' WHERE ' . $cond . ' ' . $ep;
- $this->execute($query, 'update');
- return true;
- }
- /**
- * Delete an element from database
- *
- * @param $tbl
- * @param $flds
- * @param $vls
- * @param null $ep
- *
- * @throws Exception
- * @internal param $ : { array } { $flds } { array of fields to update }
- * @internal param $ : { array } { $vlds } { array of values to update against fields }
- * @internal param $ : { string } { $ep } { extra parameters to consider }
- * @internal param $ : { string } { $tbl } { table to delete value from }
- */
- function delete($tbl, $flds, $vls, $ep = null)
- {
- $this->ping();
- $total_fields = count($flds);
- $fields_query = '';
- $count = 0;
- for ($i = 0; $i < $total_fields; $i++) {
- $count++;
- $val = $this->clean_var($vls[$i]);
- $needle = substr($val, 0, 3);
- if ($needle != '|f|') {
- $fields_query .= $flds[$i] . "='" . $val . "'";
- } else {
- $val = substr($val, 3, strlen($val));
- $fields_query .= $flds[$i] . '=' . $val;
- }
- if ($total_fields != $count) {
- $fields_query .= ' AND ';
- }
- }
- //Complete Query
- $query = 'DELETE FROM ' . $tbl . ' WHERE ' . $fields_query . ' ' . $ep;
- if (isset($this->total_queries)) {
- $this->total_queries++;
- }
- $this->total_queries_sql[] = $query;
- $this->execute($query, 'delete');
- }
- /**
- * Function used to insert values in database { table, fields, values style }
- *
- * @param $tbl
- * @param $flds
- * @param $vls
- * @param null $ep
- *
- * @return mixed|void : { integer } { $insert_id } { id of inserted element }
- *
- * @throws Exception
- * @internal param $ : { string } { $tbl } { table to insert values in }
- * @internal param $ : { array } { $flds } { array of fields to update }
- * @internal param $ : { array } { $vlds } { array of values to update against fields }
- * @internal param $ : { string } { $ep } { extra parameters to consider }
- */
- function insert($tbl, $flds, $vls, $ep = null)
- {
- $this->ping();
- $total_fields = count($flds);
- $count = 0;
- $fields_query = '';
- $values_query = '';
- foreach ($flds as $field) {
- $count++;
- $fields_query .= $field;
- if ($total_fields != $count) {
- $fields_query .= ',';
- }
- }
- $total_values = count($vls);
- $count = 0;
- foreach ($vls as $value) {
- $count++;
- preg_match('/\|no_mc\|/', $value, $matches);
- if ($matches) {
- $val = preg_replace('/\|no_mc\|/', '', $value);
- } else {
- $val = $this->clean_var($value);
- }
- if (strtoupper($val) == 'NULL') {
- $values_query .= 'NULL';
- } else {
- $needle = substr($val, 0, 3);
- if ($needle != '|f|') {
- $values_query .= "'" . $val . "'";
- } else {
- $val = substr($val, 3, strlen($val));
- $values_query .= "'" . $val . "'";
- }
- }
- if ($total_values != $count) {
- $values_query .= ',';
- }
- }
- $query = "INSERT INTO $tbl ($fields_query) VALUES ($values_query) $ep";
- $this->total_queries_sql[] = $query;
- if (isset($this->total_queries)) {
- $this->total_queries++;
- }
- try {
- $this->mysqli->query($query);
- $this->handleError($query);
- return $this->insert_id();
- } catch (\Exception $e) {
- $this->handleError($query);
- }
- }
- /**
- * Function used to insert values in database { table, associative array style }
- *
- * @param $tbl
- * @param $fields
- *
- * @return mixed : { integer } { $insert_id } { id of inserted element }
- *
- * @throws Exception
- * @internal param $ : { array } { $flds } { array of fields and values to update (associative array) }
- * @internal param $ : { string } { $tbl } { table to insert values in }
- */
- function db_insert($tbl, $fields)
- {
- $this->ping();
- $count = 0;
- $query_fields = [];
- $query_values = [];
- foreach ($fields as $field => $val) {
- $query_fields[] = $field;
- $needle = substr($val, 0, 2);
- if ($needle != '{{') {
- $query_values[] = "'" . mysql_clean($val) . "'";
- } else {
- $val = substr($val, 2, strlen($val) - 4);
- $query_values[] = mysql_clean($val);
- }
- $count += $count;
- }
- $fields_query = implode(',', $query_fields);
- $values_query = implode(',', $query_values);
- //Complete Query
- $query = "INSERT INTO $tbl ($fields_query) VALUES ($values_query) $ep";
- $this->total_queries++;
- $this->total_queries_sql[] = $query;
- try {
- $this->mysqli->query($query);
- $this->handleError($query);
- return $this->insert_id();
- } catch (\Exception $e) {
- $this->handleError($query);
- }
- }
- /**
- * Returns last insert id.
- *
- * Always use this right after calling insert method or before
- * making another mysqli query.
- *
- * @return mixed
- */
- function insert_id()
- {
- return $this->mysqli->insert_id;
- }
- /**
- * Clean variable for mysql
- *
- * @param $var
- *
- * @return string
- */
- function clean_var($var): string
- {
- $this->ping();
- return $this->mysqli->real_escape_string($var);
- }
- /**
- * @param $query
- * @return void
- * @throws Exception
- */
- private function handleError($query)
- {
- if ($this->getError() != '') {
- //customize exceptions
- if (preg_match('/language.*doesn\'t exist/', $this->getError())) {
- throw new \Exception("lang_not_installed");
- }
- if (preg_match('/version.*doesn\'t exist/', $this->getError())) {
- throw new \Exception("version_not_installed");
- }
- if (preg_match('/doesn\'t exist/', $this->getError())) {
- throw new \Exception("missing_table");
- }
- if (in_dev()) {
- e('SQL : ' . $query);
- e('ERROR : ' . $this->getError());
- error_log('SQL : ' . $query);
- error_log('ERROR : ' . $this->getError());
- error_log(debug_backtrace_string());
- } else {
- e(lang('technical_error'));
- }
- }
- }
- private function ping()
- {
- if (!$this->mysqli->ping()) {
- error_log('SQL ERROR : ' . $this->mysqli->error);
- $this->connect();
- }
- }
- /**
- * Get effect rows
- */
- function Affected_Rows()
- {
- return $this->mysqli->affected_rows;
- }
- function getError()
- {
- return $this->mysqli->error;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement