detain / db_abstraction

src/Mysqli/Db.php

<?php
/**
* MySQL Related Functionality
* @author Joe Huss <detain@interserver.net>
* @copyright 2025
* @package MyAdmin
* @category SQL
*/

namespace MyDb\Mysqli;

use MyDb\Generic;
use MyDb\Db_Interface;

/**
* Db
*
* @access public
*/
class Db extends Generic implements Db_Interface
{
    /**
    * @var string
    */
    public $type = 'mysqli';
    public $statement;
    public $statement_query;
    public $statement_vars;

    /**
    * alias function of select_db, changes the database we are working with.
    *
    * @param string $database the name of the database to use
    * @return void
    */
    public function useDb($database)
    {
        $this->selectDb($database);
    }

    /**
    * changes the database we are working with.
    *
    * @param string $database the name of the database to use
    * @return void
    */
    public function selectDb($database)
    {
        $this->connect();
        mysqli_select_db($this->linkId, $database);
    }

    /* public: connection management */

    /**
    * Db::connect()
    * @param string $database
    * @param string $host
    * @param string $user
    * @param string $password
    * @return int|\mysqli
    */
    public function connect($database = '', $host = '', $user = '', $password = '', $port = '')
    {
        /* Handle defaults */
        if ($database == '') {
            $database = $this->database;
        }
        if ($host == '') {
            $host = $this->host;
        }
        if ($user == '') {
            $user = $this->user;
        }
        if ($password == '') {
            $password = $this->password;
        }
        if ($port == '') {
            $port = $this->port;
        }
        /* establish connection, select database */
        if (!is_object($this->linkId)) {
            $this->connectionAttempt++;
            if ($this->connectionAttempt >= $this->maxConnectErrors - 1) {
                error_log("MySQLi Connection Attempt #{$this->connectionAttempt}/{$this->maxConnectErrors}");
                exit;

Using exit here is not recommended.

            }
            if ($this->connectionAttempt >= $this->maxConnectErrors) {
                $this->halt("connect($host, $user, \$password) failed. ".(is_object($this->linkId) && isset( $this->linkId->connect_error) ? $this->linkId->connect_error : ''));

The condition is_object($this->linkId) is always false.

                return 0;
            }
            //error_log("real_connect($host, $user, $password, $database, $port)");

80% of this comment could be valid code. Did you maybe forget this after debugging?

            $this->linkId = mysqli_init();
            $this->linkId->options(MYSQLI_INIT_COMMAND, "SET NAMES {$this->characterSet} COLLATE {$this->collation}, COLLATION_CONNECTION = {$this->collation}, COLLATION_DATABASE = {$this->collation}");
            if (!$this->linkId->real_connect($host, $user, $password, $database, $port != '' ? $port : NULL)) {
                $this->halt("connect($host, $user, \$password) failed. ".(is_object($this->linkId) && isset( $this->linkId->connect_error) ? $this->linkId->connect_error : ''));
                return 0;
            }
            $this->linkId->set_charset($this->characterSet);
            if ($this->linkId->connect_errno) {
                $this->halt("connect($host, $user, \$password) failed. ".(is_object($this->linkId) && isset( $this->linkId->connect_error) ? $this->linkId->connect_error : ''));
                return 0;
            }
        }
        return $this->linkId;
    }

    /**
    * Db::disconnect()
    * @return bool
    */
    public function disconnect()
    {
        $return = !is_int($this->linkId) && method_exists($this->linkId, 'close') ? $this->linkId->close() : false;
        $this->linkId = 0;
        return $return;
    }

    /**
    * @param $string
    * @return string
    */
    public function real_escape($string = '')
    {
        if ((!is_resource($this->linkId) || $this->linkId == 0) && !$this->connect()) {

The condition is_resource($this->linkId) is always false.

            return $this->escape($string);
        }
        return mysqli_real_escape_string($this->linkId, $string);

It seems like $this->linkId can also be of type integer and resource; however, parameter $mysql of mysqli_real_escape_string() does only seem to accept mysqli, maybe add an additional type check?

    }

    /**
    * discard the query result
    * @return void
    */
    public function free()
    {
        if (is_resource($this->queryId)) {

The condition is_resource($this->queryId) is always false.

            @mysqli_free_result($this->queryId);

It seems like you do not handle an error condition for mysqli_free_result(). This can introduce security issues, and is generally not recommended.

        }
        $this->queryId = 0;
    }

    /**
    * Db::queryReturn()
    *
    * Sends an SQL query to the server like the normal query() command but iterates through
    * any rows and returns the row or rows immediately or FALSE on error
    *
    * @param mixed $query SQL Query to be used
    * @param string $line optionally pass __LINE__ calling the query for logging
    * @param string $file optionally pass __FILE__ calling the query for logging
    * @return mixed FALSE if no rows, if a single row it returns that, if multiple it returns an array of rows, associative responses only
    */
    public function queryReturn($query, $line = '', $file = '')
    {
        $this->query($query, $line, $file);

$line of type string is incompatible with the type integer expected by parameter $line of MyDb\Mysqli\Db::query().

        if ($this->num_rows() == 0) {
            return false;
        } elseif ($this->num_rows() == 1) {
            $this->next_record(MYSQLI_ASSOC);
            return $this->Record;
        } else {
            $out = [];
            while ($this->next_record(MYSQLI_ASSOC)) {
                $out[] = $this->Record;
            }
            return $out;
        }
    }

    /**
    * db:qr()
    *
    *  alias of queryReturn()
    *
    * @param mixed $query SQL Query to be used
    * @param string $line optionally pass __LINE__ calling the query for logging
    * @param string $file optionally pass __FILE__ calling the query for logging
    * @return mixed FALSE if no rows, if a single row it returns that, if multiple it returns an array of rows, associative responses only
    */
    public function qr($query, $line = '', $file = '')
    {
        return $this->queryReturn($query, $line, $file);
    }

    /**
    * creates a prepaired statement from query
    *
    * @param string $query sql query like INSERT INTO table (col) VALUES (?)  or  SELECT * from table WHERE col1 = ? and col2 = ?  or  UPDATE table SET col1 = ?, col2 = ? WHERE col3 = ?
    * @return false|mysqli_stmt

The type MyDb\Mysqli\mysqli_stmt was not found. Did you mean mysqli_stmt? If so, make sure to prefix the type with \.

    * @param string $line
    * @param string $file
    */
    public function prepare($query, $line = '', $file = '')
    {
        if (!$this->connect()) {
            return 0;
        }
        $haltPrev = $this->haltOnError;

The assignment to $haltPrev is dead and can be removed.

        $this->haltOnError = 'no';
        $start = microtime(true);
        $this->statement = mysqli_prepare($this->linkId, $query);
        if (!isset($GLOBALS['disable_db_queries'])) {
            $this->statement_query = $query;
            $this->addLog($query, microtime(true) - $start, $line, $file);
        }
        return $this->statement;
    }

    /**
    * Binds variables to a prepared statement as parameters
    * 
    * @param string $types A string that contains one or more characters which specify the types for the corresponding bind variables: (i)nt, (d)ecimal, (s)tring, (b)inary 
    * @param mixed $vars
    * @return bool indicates success
    */
    public function bind_param($types, &...$vars) {
        $params = [$this->statement, $types];
        foreach ($vars as &$var) {
            $params[] = &$var; // Ensure they stay references
        }
        if (!isset($GLOBALS['disable_db_queries'])) {
            $this->statement_vars &= $vars;
        }
        return call_user_func_array('mysqli_stmt_bind_param', $params);
    }

    /**
    * Executes a prepared statement
    * 
    * @return bool success
    */
    public function execute($line = '', $file = '') {
        $start = microtime(true);
        $return = mysqli_stmt_execute($this->statement);
        if (!isset($GLOBALS['disable_db_queries'])) {
            $vars = $this->statement_vars;
            $query = $result = preg_replace_callback('/\?/', function () use (&$vars) {

The assignment to $result is dead and can be removed.

                return array_shift($vars);
                }, $this->statement_query);
            $this->addLog($query, microtime(true) - $start, $line, $file);
        }
        return $return;
    }

    /**
    * Db::query()
    *
    *  Sends an SQL query to the database
    *
    * @param mixed $queryString
    * @param int $line
    * @param string $file
    * @param bool $log
    * @return mixed 0 if no query or query id handler, safe to ignore this return
    */
    public function query($queryString, $line = '', $file = '', $log = false)
    {
        /* No empty queries, please, since PHP4 chokes on them. */
        /* The empty query string is passed on from the constructor,
        * when calling the class without a query, e.g. in situations
        * like these: '$db = new db_Subclass;'
        */
        if ($queryString == '') {
            return 0;
        }
        if (!$this->connect()) {
            return 0;
            /* we already complained in connect() about that. */
        }
        $haltPrev = $this->haltOnError;
        $this->haltOnError = 'no';
        // New query, discard previous result.
        if (is_resource($this->queryId)) {
            $this->free();
        }
        if ($this->Debug) {
            printf("Debug: query = %s<br>\n", $queryString);
        }
        if ($log === true || (isset($GLOBALS['log_queries']) && $GLOBALS['log_queries'] !== false)) {
            $this->log($queryString, $line, $file);
        }
        $tries = 2;
        $try = 0;
        $this->queryId = false;
        while ((null === $this->queryId || $this->queryId === false) && $try <= $tries) {
            $try++;
            if ($try > 1) {
                @mysqli_close($this->linkId);

It seems like you do not handle an error condition for mysqli_close(). This can introduce security issues, and is generally not recommended.

                $this->linkId = 0;
            }
            $start = microtime(true);
            $onlyRollback = true;
            $fails = -1;
            while ($fails < 30 && (null === $this->queryId || $this->queryId === false)) {
                $this->connect();
                $fails++;
                try {
                    $this->queryId = @mysqli_query($this->linkId, $queryString, MYSQLI_STORE_RESULT);
                    if (in_array((int)@mysqli_errno($this->linkId), [1213, 2006, 3101, 1180])) {
                        //error_log("got ".@mysqli_errno($this->linkId)." sql error fails {$fails} on query {$queryString} from {$line}:{$file}");

67% of this comment could be valid code. Did you maybe forget this after debugging?

                        usleep(250000); // 0.25 second
                    } else {
                        $onlyRollback = false;
                        if (in_array((int)@mysqli_errno($this->linkId), [1064])) {
                            $tries = 0;
                        }
                        break;
                    }
                } catch (\mysqli_sql_exception $e) {
                    if (in_array((int)$e->getCode(), [1213, 2006, 3101, 1180])) {
                        //error_log("got ".$e->getCode()." sql error fails {$fails}");

70% of this comment could be valid code. Did you maybe forget this after debugging?

                        usleep(250000); // 0.25 second
                    } else {
                        error_log('Got mysqli_sql_exception code '.$e->getCode().' error '.$e->getMessage().' on query '.$queryString.' from '.$line.':'.$file);
                        $onlyRollback = false;
                        if (in_array((int)@mysqli_errno($this->linkId), [1064])) {
                            $tries = 0;
                        }
                        break;
                    }
                }
            }
            if (!isset($GLOBALS['disable_db_queries'])) {
                $this->addLog($queryString, microtime(true) - $start, $line, $file);
            }
            $this->Row = 0;
            $this->Errno = @mysqli_errno($this->linkId);
            $this->Error = @mysqli_error($this->linkId);
            if ($try == 1 && (null === $this->queryId || $this->queryId === false)) {

This if statement is empty and can be removed.

                //$this->emailError($queryString, 'Error #'.$this->Errno.': '.$this->Error, $line, $file);

66% of this comment could be valid code. Did you maybe forget this after debugging?

            }
        }
        $this->haltOnError = $haltPrev;
        if ($onlyRollback === true && false === $this->queryId) {

The variable $onlyRollback does not seem to be defined for all execution paths leading up to this point.

            error_log('Got MySQLi 3101 Rollback Error '.$fails.' Times, Giving Up on '.$queryString.' from '.$line.':'.$file.' on '.__LINE__.':'.__FILE__);

The variable $fails does not seem to be defined for all execution paths leading up to this point.

        }
        if (null === $this->queryId || $this->queryId === false) {
            $this->emailError($queryString, 'Error #'.$this->Errno.': '.$this->Error, $line, $file);
            $this->halt('', $line, $file);
        }

        // Will return nada if it fails. That's fine.
        return $this->queryId;
    }

    /**
    * @return array|null|object
    */
    public function fetchObject()
    {
        $this->Record = @mysqli_fetch_object($this->queryId);

$this->queryId of type integer is incompatible with the type mysqli_result expected by parameter $result of mysqli_fetch_object().

        return $this->Record;
    }

    /* public: walk result set */

    /**
    * Db::next_record()
    *
    * @param mixed $resultType
    * @return bool
    */
    public function next_record($resultType = MYSQLI_BOTH)
    {
        if ($this->queryId === false) {

The condition $this->queryId === false is always false.

            $this->haltmsg('next_record called with no query pending.');
            return 0;
        }

        $this->Record = @mysqli_fetch_array($this->queryId, $resultType);

$this->queryId of type integer is incompatible with the type mysqli_result expected by parameter $result of mysqli_fetch_array().

        ++$this->Row;
        $this->Errno = mysqli_errno($this->linkId);

It seems like $this->linkId can also be of type integer; however, parameter $mysql of mysqli_errno() does only seem to accept mysqli, maybe add an additional type check?

        $this->Error = mysqli_error($this->linkId);

It seems like $this->linkId can also be of type integer; however, parameter $mysql of mysqli_error() does only seem to accept mysqli, maybe add an additional type check?

        $stat = is_array($this->Record);
        if (!$stat && $this->autoFree && is_resource($this->queryId)) {

The condition is_resource($this->queryId) is always false.

            $this->free();
        }
        return $stat;
    }

    /**
    * switch to position in result set
    *
    * @param integer $pos the row numbe starting at 0 to switch to
    * @return bool whetherit was successfu or not.
    */
    public function seek($pos = 0)
    {
        $status = @mysqli_data_seek($this->queryId, $pos);

$this->queryId of type integer is incompatible with the type mysqli_result expected by parameter $result of mysqli_data_seek().

        if ($status) {
            $this->Row = $pos;
        } else {
            $this->haltmsg("seek({$pos}) failed: result has ".$this->num_rows().' rows', __LINE__, __FILE__);
            /* half assed attempt to save the day, but do not consider this documented or even desirable behaviour. */
            $rows = $this->num_rows();
            @mysqli_data_seek($this->queryId, $rows);

It seems like you do not handle an error condition for mysqli_data_seek(). This can introduce security issues, and is generally not recommended.

            $this->Row = $rows;
            return false;
        }
        return true;
    }

    /**
    * Initiates a transaction
    *
    * @return bool
    */
    public function transactionBegin()
    {
        if (version_compare(PHP_VERSION, '5.5.0') < 0) {
            return true;
        }
        if (!$this->connect()) {
            return 0;
        }
        return mysqli_begin_transaction($this->linkId);
    }

    /**
    * Commits a transaction
    *
    * @return bool
    */
    public function transactionCommit()
    {
        if (version_compare(PHP_VERSION, '5.5.0') < 0 || $this->linkId === 0) {
            return true;
        }
        return mysqli_commit($this->linkId);

It seems like $this->linkId can also be of type integer; however, parameter $mysql of mysqli_commit() does only seem to accept mysqli, maybe add an additional type check?

    }

    /**
    * Rolls back a transaction
    *
    * @return bool
    */
    public function transactionAbort()
    {
        if (version_compare(PHP_VERSION, '5.5.0') < 0 || $this->linkId === 0) {
            return true;
        }
        return mysqli_rollback($this->linkId);

It seems like $this->linkId can also be of type integer; however, parameter $mysql of mysqli_rollback() does only seem to accept mysqli, maybe add an additional type check?

    }

    /**
    * This will get the last insert ID created on the current connection.  Should only be called after an insert query is
    * run on a table that has an auto incrementing field.  $table and $field are required, but unused here since it's
    * unnecessary for mysql.  For compatibility with pgsql, the params must be supplied.
    *
    * @param string $table
    * @param string $field
    * @return int|string
    */
    public function getLastInsertId($table, $field)
    {
        if (!isset($table) || $table == '' || !isset($field) || $field == '') {
            return -1;
        }

        return @mysqli_insert_id($this->linkId);

It seems like $this->linkId can also be of type integer; however, parameter $mysql of mysqli_insert_id() does only seem to accept mysqli, maybe add an additional type check?

    }

    /* public: table locking */

    /**
    * Db::lock()
    * @param mixed  $table
    * @param string $mode
    * @return bool|int|\mysqli_result
    */
    public function lock($table, $mode = 'write')
    {
        $this->connect();
        $query = 'lock tables ';
        if (is_array($table)) {
            foreach ($table as $key => $value) {
                if ($key == 'read' && $key != 0) {
                    $query .= "$value read, ";
                } else {
                    $query .= "$value $mode, ";
                }
            }
            $query = mb_substr($query, 0, -2);
        } else {
            $query .= "$table $mode";
        }
        $res = @mysqli_query($this->linkId, $query);
        if (!$res) {
            $this->halt("lock($table, $mode) failed.");
            return 0;
        }
        return $res;
    }

    /**
    * Db::unlock()
    * @param bool $haltOnError optional, defaults to TRUE, whether or not to halt on error
    * @return bool|int|\mysqli_result
    */
    public function unlock($haltOnError = true)
    {
        $this->connect();

        $res = @mysqli_query($this->linkId, 'unlock tables');
        if ($haltOnError === true && !$res) {
            $this->halt('unlock() failed.');
            return 0;
        }
        return $res;
    }

    /* public: evaluate the result (size, width) */

    /**
    * Db::affectedRows()
    * @return int
    */
    public function affectedRows()
    {
        return @mysqli_affected_rows($this->linkId);

It seems like $this->linkId can also be of type integer; however, parameter $mysql of mysqli_affected_rows() does only seem to accept mysqli, maybe add an additional type check?

    }

    /**
    * Db::num_rows()
    * @return int
    */
    public function num_rows()
    {
        return @mysqli_num_rows($this->queryId);

$this->queryId of type integer is incompatible with the type mysqli_result expected by parameter $result of mysqli_num_rows().

    }

    /**
    * Db::num_fields()
    * @return int
    */
    public function num_fields()
    {
        return @mysqli_num_fields($this->queryId);

$this->queryId of type integer is incompatible with the type mysqli_result expected by parameter $result of mysqli_num_fields().

    }

    /**
    * gets an array of the table names in teh current datase
    *
    * @return array
    */
    public function tableNames()
    {
        $return = [];
        $this->query('SHOW TABLES');
        $i = 0;
        while ($info = $this->queryId->fetch_row()) {

The method fetch_row() does not exist on integer.

            $return[$i]['table_name'] = $info[0];
            $return[$i]['tablespace_name'] = $this->database;
            $return[$i]['database'] = $this->database;
            ++$i;
        }
        return $return;
    }
}

/**
* @param $result
* @param $row
* @param int|string $field
* @return bool
*/
/*

56% of this comment could be valid code. Did you maybe forget this after debugging?

function mysqli_result($result, $row, $field = 0) {
if ($result === false) return false;
if ($row >= mysqli_num_rows($result)) return false;
if (is_string($field) && !(mb_strpos($field, '.') === false)) {
$tField = explode('.', $field);
$field = -1;
$tFields = mysqli_fetch_fields($result);
for ($id = 0, $idMax = mysqli_num_fields($result); $id < $idMax; $id++) {
if ($tFields[$id]->table == $tField[0] && $tFields[$id]->name == $tField[1]) {
$field = $id;
break;
}
}
if ($field == -1) return false;
}
mysqli_data_seek($result, $row);
$line = mysqli_fetch_array($result);
return isset($line[$field]) ? $line[$field] : false;
}
*/