detain / db_abstraction

src/Pgsql/Db.php

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

namespace MyDb\Pgsql;

use MyDb\Generic;
use MyDb\Db_Interface;

/**
 * Db
 *
 * @access public
 */
class Db extends Generic implements Db_Interface
{
    /* public: this is an api revision, not a CVS revision. */
    public $type = 'pgsql';
    public $port = '';
    public $defaultPort = '5432';


    /**
     * adds if not blank
     *
     * @param string $add the value to set
     * @param string $me the key/field to set the value for
     * @param false|string $quote optional indicate the value needs quoted
     * @return string
     */
    public function ifadd($add, $me, $quote = false)
    {
        if ('' != $add) {
            return ' '.$me.($quote === false ? '' : $quote).$add.($quote === false ? '' : $quote);
        }
        return '';
    }

    /**
     * @param $string
     * @return string
     */
    public function real_escape($string = '')
    {
        return $this->escape($string);
    }

    /**
     * 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)

The parameter $database is not used and could be removed.

    {
        /*if ($database != $this->database) {

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

            $this->database = $database;
            $this->linkId = null;
            $this->connect();
        }*/
    }

    /**
     * Db::connect()
     * @return void
     */
    public function connect()
    {
        if (0 == $this->linkId) {
            $connectString = trim($this->ifadd($this->host, 'host=').
                             $this->ifadd($this->port, 'port=').
                             $this->ifadd($this->database, 'dbname=').
                             $this->ifadd($this->user, 'user=').
                             $this->ifadd($this->password, 'password=', "'"));
            $this->linkId = pg_connect($connectString);

It seems like pg_connect($connectString) of type resource is incompatible with the declared type integer|object of property $linkId.

            if (!$this->linkId) {
                $this->halt('Link-ID == FALSE, connect failed');
            }
        }
    }

    /* This only affects systems not using persistent connections */

    /**
     * Db::disconnect()
     * @return bool
     */
    public function disconnect()
    {
        return @pg_close($this->linkId);

$this->linkId of type integer|object is incompatible with the type resource expected by parameter $connection of pg_close().

    }

    /**
     * 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);
        if ($this->num_rows() == 0) {
            return false;
        } elseif ($this->num_rows() == 1) {
            $this->next_record(MYSQL_ASSOC);

The constant MYSQL_ASSOC has been deprecated: 5.5

            return $this->Record;
        } else {
            $out = [];
            while ($this->next_record(MYSQL_ASSOC)) {

The constant MYSQL_ASSOC has been deprecated: 5.5

                $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);
    }

    /**
     * Db::query()
     *
     *  Sends an SQL query to the database
     *
     * @param mixed $queryString
     * @param string $line
     * @param string $file
     * @return mixed 0 if no query or query id handler, safe to ignore this return
     */
    public function query($queryString, $line = '', $file = '')
    {
        /* 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;
        }

        $this->connect();

        /* printf("<br>Debug: query = %s<br>\n", $queryString); */

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

        $this->queryId = @pg_exec($this->linkId, $queryString);
        $this->Row = 0;

        $this->Error = pg_errormessage($this->linkId);
        $this->Errno = ($this->Error == '') ? 0 : 1;
        if (!$this->queryId) {
            $this->halt('Invalid SQL: '.$queryString, $line, $file);
        }

        return $this->queryId;
    }

    /**
     * Db::free()
     *
     * @return void
     */
    public function free()
    {
        @pg_freeresult($this->queryId);

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

        $this->queryId = 0;
    }

    /**
     * Db::next_record()
     * @param mixed $resultType
     * @return bool
     */
    public function next_record($resultType = PGSQL_BOTH)
    {
        $this->Record = @pg_fetch_array($this->queryId, $this->Row++, $resultType);

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

        $this->Error = pg_errormessage($this->linkId);
        $this->Errno = ($this->Error == '') ? 0 : 1;

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

The condition $stat is always true.

            pg_freeresult($this->queryId);
            $this->queryId = 0;
        }
        return $stat;
    }

    /**
     * Db::seek()
     *
     * @param mixed $pos
     * @return void
     */
    public function seek($pos)
    {
        $this->Row = $pos;
    }

    /**
     * Db::transactionBegin()
     *
     * @return mixed
     */
    public function transactionBegin()
    {
        return $this->query('begin');
    }

    /**
     * Db::transactionCommit()
     * @return bool|mixed
     */
    public function transactionCommit()
    {
        if (!$this->Errno) {
            return pg_exec($this->linkId, 'commit');
        } else {
            return false;
        }
    }

    /**
     * Db::transactionAbort()
     * @return mixed
     */
    public function transactionAbort()
    {
        return pg_exec($this->linkId, 'rollback');
    }

    /**
     * Db::getLastInsertId()
     * @param mixed $table
     * @param mixed $field
     * @return int
     */
    public function getLastInsertId($table, $field)
    {
        /* 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.  Of note, table
        * and field are required because pgsql returns the last inserted OID, which is unique across
        * an entire installation.  These params allow us to retrieve the sequenced field without adding
        * conditional code to the apps.
        */
        if (!isset($table) || $table == '' || !isset($field) || $field == '') {
            return -1;
        }

        $oid = pg_getlastoid($this->queryId);
        if ($oid == -1) {
            return -1;
        }

        $result = @pg_exec($this->linkId, "select $field from $table where oid=$oid");
        if (!$result) {
            return -1;
        }

        $Record = @pg_fetch_array($result, 0);
        @pg_freeresult($result);

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

        if (!is_array($Record)) /* OID not found? */

The condition is_array($Record) is always true.

        {
            return -1;
        }

        return $Record[0];
    }

    /**
     * Db::lock()
     * @param mixed  $table
     * @param string $mode
     * @return int|mixed
     */
    public function lock($table, $mode = 'write')
    {
        $result = $this->transactionBegin();

        if ($mode == 'write') {
            if (is_array($table)) {
                foreach ($table as $t) {
                    $result = pg_exec($this->linkId, 'lock table '.$t[1].' in share mode');
                }
            } else {
                $result = pg_exec($this->linkId, 'lock table '.$table.' in share mode');
            }
        } else {
            $result = 1;
        }

        return $result;
    }

    /**
     * Db::unlock()
     * @return bool|mixed
     */
    public function unlock()
    {
        return $this->transactionCommit();
    }

    /**
     * Db::affectedRows()
     * @return void
     */
    public function affectedRows()
    {
        return pg_cmdtuples($this->queryId);
    }

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

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

    /**
     * @param mixed $msg
     * @param string $line
     * @param string $file
     * @return mixed|void
     */
    public function haltmsg($msg, $line = '', $file = '')
    {
        $this->log("Database error: $msg", $line, $file, 'error');
        if ($this->Errno != '0' || $this->Error != '()') {
            $this->log('PostgreSQL Error: '.pg_last_error($this->linkId), $line, $file, 'error');

$this->linkId of type integer|object is incompatible with the type resource expected by parameter $connection of pg_last_error().

        }
        $this->logBackTrace($msg, $line, $file);
    }

    /**
     * Db::tableNames()
     *
     * @return array
     */
    public function tableNames()
    {
        $return = [];
        $this->query("select relname from pg_class where relkind = 'r' and not relname like 'pg_%'");
        $i = 0;
        while ($this->next_record()) {
            $return[$i]['table_name'] = $this->f(0);
            $return[$i]['tablespace_name'] = $this->database;
            $return[$i]['database'] = $this->database;
            ++$i;
        }
        return $return;
    }

    /**
     * Db::indexNames()
     *
     * @return array
     */
    public function indexNames()
    {
        $return = [];
        $this->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relkind ='i' ORDER BY relname");
        $i = 0;
        while ($this->next_record()) {
            $return[$i]['index_name'] = $this->f(0);
            $return[$i]['tablespace_name'] = $this->database;
            $return[$i]['database'] = $this->database;
            ++$i;
        }
        return $return;
    }
}