source: trunk/lib/PDO.inc.php @ 747

<?php
/*
* The Strangecode Codebase - a general application development framework for PHP
* For details visit the project site: <http://trac.strangecode.com/>
* Copyright © 2019 Strangecode, LLC
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

/*
* PDO.inc.php
*
*
*
* @author   Quinn Comendant <quinn@strangecode.com>
* @version  1.0
* @since    09 Jul 2019 08:11:03
*
* Example of use:
---------------------------------------------------------------------
$x = new PDO();
$x->setParam(array('foo' => 'bar'));
$x->doIt();
echo $x->getIt();
---------------------------------------------------------------------
*/
namespace Strangecode\Codebase;
use \App;

class PDO
{
    // A place to keep an object instance for the singleton pattern.
    protected static $instance = null;

    // If $db->connect has successfully opened a db connection.
    protected $_connected = false;

    // Database handle.
    public $dbh;

    // Count how many queries run during the whole instance.
    protected $_query_count = 0;

    // Hash of DB parameters.
    protected $_params = array(

        // DB passwords should be set as apache environment variables in httpd.conf, readable only by root.
        'db_server' => 'localhost',
        'db_name' => null,
        'db_user' => null,
        'db_pass' => null,

        // Display all SQL queries. FALSE recommended for production sites.
        'db_always_debug' => false,

        // Display db errors. FALSE recommended for production sites.
        'db_debug' => false,

        // Script stops on db error. TRUE recommended for production sites.
        'db_die_on_failure' => true,

        // Special date settings. These will dynamically changes depending on MySQL version or settings.
        'zero_date' => '0000-00-00',
        'infinity_date' => '9999-12-31',

        // Timezone for MySQL.
        'timezone' => 'UTC',

        // MySQL character set and collation.
        'character_set' => '',
        'collation' => '',

        // Reconnection attempt limit. Set to 0 to disable retries, i.e., only the first connect will be attempted.
        'retry_limit' => 8,
    );

    // Translate between HTML and MySQL character set names.
    public $mysql_character_sets = array(
        'utf-8' => 'utf8mb4',
        'iso-8859-1' => 'latin1',
    );

    // Caches.
    protected static $existing_tables = null;
    protected static $table_columns = [];

    /**
     * PDO constructor.
     *
     * @access public
     * @param string $namespace Namespace for this object, used to avoid collisions in global contexts.
     * @param string $params    Configuration parameters for this object.
     */
    public function __construct($params=null)
    {
        // Set custom parameters.
        $this->setParam($params);
    }

    /**
     * This method enforces the singleton pattern for this class.
     *
     * @return  object  Reference to the global DB object.
     * @access  public
     * @static
     */
    public static function &getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }

        return self::$instance;
    }

    /**
     * Set the params of this object.
     *
     * @access public
     * @param  array $params   Array of param keys and values to set.
     */
    public function setParam($params=null)
    {
        if (isset($params) && is_array($params)) {
            // Merge new parameters with old overriding only those passed.
            $this->_params = array_merge($this->_params, $params);
        }
    }

    /**
     * Return the value of a parameter, if it exists.
     *
     * @access public
     * @param string $param        Which parameter to return.
     * @return mixed               Configured parameter value.
     */
    public function getParam($param)
    {
        $app =& App::getInstance();

        if (isset($this->_params[$param])) {
            return $this->_params[$param];
        } else {
            $app->logMsg(sprintf('Parameter is not set: %s', $param), LOG_DEBUG, __FILE__, __LINE__);
            return null;
        }
    }

    /*
    * Connect to database with credentials in params.
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 08:16:42
    */
    public function connect($retry_num=0)
    {
        $app =& App::getInstance();

        if (!$this->getParam('db_name') || !$this->getParam('db_user') || !$this->getParam('db_pass')) {
            $app->logMsg('Database credentials missing.', LOG_EMERG, __FILE__, __LINE__);
            return false;
        }

        // If db_server not specified, assume localhost.
        if (null === $this->_params['db_server']) {
            $this->setParam(array('db_server' => 'localhost'));
        }

        // If the mysql charset is not defined, try to determine a mysql charset from the app charset.
        if ('' == $this->getParam('character_set') && '' != $app->getParam('character_set') && isset($this->mysql_character_sets[mb_strtolower($app->getParam('character_set'))])) {
            $this->setParam(array('character_set' => $this->mysql_character_sets[mb_strtolower($app->getParam('character_set'))]));
        }

        try {
            // Set charset in DSN and disable emulated prepares as per https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/12202218
            $dsn = sprintf('mysql:host=%s;dbname=%s;charset=%s', $this->getParam('db_server'), $this->getParam('db_name'), $this->getParam('character_set'));
            $options = [
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_EMULATE_PREPARES   => false,
            ];
            $this->dbh = new \PDO($dsn, $this->getParam('db_user'), $this->getParam('db_pass'), $options);
        } catch (\PDOException $e) {
            $mysql_error_msg = sprintf('PDO connect %s: %s (db_server=%s, db_name=%s, db_user=%s, db_pass=%s)%s',
                get_class($e),
                $e->getMessage(),
                $this->getParam('db_server'),
                $this->getParam('db_name'),
                $this->getParam('db_user'),
                ('' == $this->getParam('db_pass') ? 'NO' : 'YES'),
                ($retry_num > 0 ? ' retry ' . $retry_num : '')
            );
            // Use LOG_NOTICE for first connection attempts, and LOG_EMERG for the last one.
            $app->logMsg($mysql_error_msg, ($retry_num >= $this->getParam('retry_limit') ? LOG_EMERG : LOG_NOTICE), __FILE__, __LINE__);

            // These are probably transient errors:
            // SQLSTATE[HY000] [2002] Connection refused
            // SQLSTATE[HY000] [2002] No such file or directory
            // SQLSTATE[HY000] [2006] MySQL server has gone away
            if ($retry_num < $this->getParam('retry_limit') && (strpos($e->getMessage(), '[2002]') !== false || strpos($e->getMessage(), '[2006]') !== false)) {
                // Try again after a delay:
                usleep(500000);
                return $this->connect(++$retry_num);
            }

            // Print helpful or pretty error?
            if ($this->getParam('db_debug') && $app->getParam('display_errors')) {
                if (!$app->isCLI()) {
                    printf('<pre style="padding:1em;background:#ddd;font:10px monospace;">%s</pre>', $mysql_error_msg);
                }
            }

            // Die if db_die_on_failure = true, or just continue without connection.
            return $this->_fail();
        }

        // DB connection success!
        $this->_connected = true;

        // Update config for this version of MySQL.
        if (version_compare($this->dbh->getAttribute(\PDO::ATTR_SERVER_VERSION), '5.7.4', '>=')) {
            $this->setParam(array('zero_date' => '1000-01-01'));
        }

        // Set MySQL session timezone.
        if ($this->getParam('timezone')) {
            // https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
            $this->dbh->query(sprintf("SET time_zone = '%s'", $this->getParam('timezone')));
        }

        return true;
    }

    /**
     * Close db connection.
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 14:32:01
     */
    public function close()
    {
        $this->_connected = false;
        $this->dbh = null;

        return true;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    03 Jul 2013 14:50:23
    */
    public function reconnect()
    {
        $this->close();
        $this->connect();
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 10:05:34
    */
    public function ping()
    {
        $app =& App::getInstance();

        if (!$this->_connected) {
            throw new \Exception(sprintf('No DB connection to run %s', __METHOD__));
        }

        try {
            $this->dbh->query('SELECT 1');
        } catch (\PDOException $e) {
            return false;
        }

        return true;
    }

    /*
    * Die only if db_die_on_failure is true. This will be set to false for some cases
    * when a database is not required for web app functionality.
    *
    * @access   public
    * @param    string  $msg Print $msg when dying.
    * @return   bool    false If we don't die.
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    15 Jan 2007 15:59:00
    */
    protected function _fail()
    {
        $app =& App::getInstance();

        if ($this->getParam('db_die_on_failure')) {
            if (!$app->isCLI()) {
                // For http requests, send a Service Unavailable header.
                header(' ', true, 503);
                echo _("This page is temporarily unavailable. Please try again in a few minutes.");
            }
            die;
        } else {
            return false;
        }
    }

    /**
     * Returns connection status
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 14:58:09
     */
    public function isConnected()
    {
        return (true === $this->_connected);
    }

    /*
    * Return the total number of queries executed thus far.
    *
    * @access   public
    * @return   int Number of queries
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    15 Jun 2006 11:46:05
    */
    public function numQueries()
    {
        return $this->_query_count;
    }

    /**
     * Reset cached items.
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 22:10:50
     */
    public function resetCache()
    {
        self::$existing_tables = null;
        self::$table_columns = [];
    }

    /*
    *
    *
    * @access   public
    * @param    string  $query   The SQL query to execute
    * @param    bool    $debug   If true, prints debugging info
    * @return   resource         PDOStatement
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 10:00:00
    */
    public function query($query, $debug=false)
    {
        $app =& App::getInstance();

        if (!$this->_connected) {
            throw new \Exception(sprintf('No DB connection to run %s', __METHOD__));
        }

        $this->_query_count++;

        $debugqry = preg_replace('/\n[\t ]+/' . $app->getParam('preg_u'), "\n", $query);
        if ($this->getParam('db_always_debug') || $debug) {
            if ($debug > 1) {
                dump($debugqry, true, SC_DUMP_PRINT_R, __FILE__, __LINE__);
            } else {
                echo "<!-- ----------------- PDO query $this->_query_count ---------------------\n$debugqry\n-->\n";
            }
        }

        // Ensure we have an active connection.
        // If we continue on a dead connection we might experience a "MySQL server has gone away" error.
        // http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
        if (!$this->ping()) {
            $app->logMsg(sprintf('MySQL ping failed; reconnecting
 ("%s")', truncate(trim($debugqry), 150)), LOG_DEBUG, __FILE__, __LINE__);
            $this->reconnect();
        }

        // Execute!
        try {
            $stmt = $this->dbh->query($query);
            if (!$stmt) {
                throw new \Exception('PDO::query returned false');
            }
        } catch (\Exception $e) {
            $app->logMsg(sprintf('PDO query %s (%s): %s in query: %s', get_class($e), $e->getCode(), $e->getMessage(), $debugqry), LOG_EMERG, __FILE__, __LINE__);
            if ($this->getParam('db_debug') && $app->getParam('display_errors')) {
                if (!$app->isCLI()) {
                    printf('<pre style="padding:1em;background:#ddd;font:0.9rem monospace;">%s<hr>%s</pre>', wordwrap($e->getMessage()), htmlspecialchars($debugqry));
                }
            }
            // Die if db_die_on_failure = true, or just continue without connection
            return $this->_fail();
        }

        return $stmt;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 19:26:37
    */
    public function prepare($query, ...$params)
    {
        $app =& App::getInstance();

        if (!$this->_connected) {
            throw new \Exception(sprintf('No DB connection to run %s', __METHOD__));
        }

        $this->_query_count++;

        $debugqry = preg_replace('/\n[\t ]+/' . $app->getParam('preg_u'), "\n", $query);
        if ($this->getParam('db_always_debug')) {
            echo "<!-- ----------------- PDO prepare $this->_query_count ---------------------\n$debugqry\n-->\n";
        }

        // Ensure we have an active connection.
        // If we continue on a dead connection we might experience a "MySQL server has gone away" error.
        // http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
        if (!$this->ping()) {
            $app->logMsg(sprintf('MySQL ping failed; reconnecting
 ("%s")', truncate(trim($debugqry), 150)), LOG_DEBUG, __FILE__, __LINE__);
            $this->reconnect();
        }

        // Execute!
        try {
            $stmt = $this->dbh->prepare($query, ...$params);
            if (!$stmt) {
                throw new \Exception('PDO::prepare returned false');
            }
        } catch (\PDOException $e) {
            $app->logMsg(sprintf('PDO prepare %s (%s): %s in query: %s', get_class($e), $e->getCode(), $e->getMessage(), $debugqry), LOG_EMERG, __FILE__, __LINE__);
            if ($this->getParam('db_debug') && $app->getParam('display_errors')) {
                if (!$app->isCLI()) {
                    printf('<pre style="padding:1em;background:#ddd;font:0.9rem monospace;">%s<hr>%s</pre>', wordwrap($e->getMessage()), htmlspecialchars($debugqry));
                }
            }
            // Die if db_die_on_failure = true, or just continue without connection
            return $this->_fail();
        }

        return $stmt;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 19:42:48
    */
    public function lastInsertId($name=null)
    {
        return $this->dbh->lastInsertId($name);
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 18:32:55
    */
    public function quote(...$params)
    {
        return $this->dbh->quote(...$params);
    }

    /*
    * Remove unsafe characters from SQL identifiers (tables, views, indexes, columns, and constraints).
    *
    * @access   public
    * @param    string  $idname     Identifier name.
    * @return   string              Clean string.
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @since    09 Jul 2019 18:32:55
    */
    static function sanitizeIdentifier($idname)
    {
        $app =& App::getInstance();

        return preg_replace('/\W/' . $app->getParam('preg_u'), '', $idname);
    }

    /**
     * Loads a list of tables in the current database into an array, and returns
     * true if the requested table is found. Use this function to enable/disable
     * functionality based upon the current available db tables or to dynamically
     * create tables if missing.
     *
     * @param  string $table                The name of the table to search.
     * @param  bool   $use_cached_results   Get fresh table info (in case DB changed).
     * @return bool                         true if given $table exists.
     */
    public function tableExists($table, $use_cached_results=true)
    {
        $app =& App::getInstance();

        if (!$this->_connected) {
            throw new \Exception(sprintf('No DB connection to run %s', __METHOD__));
        }

        if (null === self::$existing_tables || !$use_cached_results) {
            $stmt = $this->query('SHOW TABLES');
            self::$existing_tables = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        }

        if (in_array($table, self::$existing_tables)) {
            return true;
        } else {
            $app->logMsg(sprintf('Nonexistent DB table: %s.%s', $this->getParam('db_name'), $table), LOG_INFO, __FILE__, __LINE__);
            return false;
        }
    }

    /**
     * Tests if the given array of columns exists in the specified table.
     *
     * @param  string $table                The name of the table to search.
     * @param  array  $columns              An array of column names.
     * @param  bool   $strict               Exact schema match. If TRUE, test if *only* the given columns exist. If FALSE, test if given columns exist (possibly amongst others).
     * @param  bool   $use_cached_results   Get fresh table info (in case DB changed).
     * @return bool                         true if column(s) exist.
     */
    public function columnExists($table, $columns, $strict=true, $use_cached_results=true)
    {
        $app =& App::getInstance();

        if (!$this->_connected) {
            throw new \Exception(sprintf('No DB connection to run %s', __METHOD__));
        }

        // Ensure the table exists.
        if (!$this->tableExists($table, $use_cached_results)) {
            $app->logMsg(sprintf('Table does not exist: %s', $table), LOG_NOTICE, __FILE__, __LINE__);
            return false;
        }

        // For single-value columns.
        if (!is_array($columns)) {
            $columns = array($columns);
        }

        if (!isset(self::$table_columns[$table]) || !$use_cached_results) {
            // Populate and cache array of current columns for this table.
            $stmt = $this->query(sprintf('DESCRIBE `%s`', $this->sanitizeIdentifier($table)));
            self::$table_columns[$table] = $stmt->fetchAll(\PDO::FETCH_COLUMN);
        }

        if ($strict) {
            // Do an exact comparison of table schemas.
            sort($columns);
            sort(self::$table_columns[$table]);
            return self::$table_columns[$table] == $columns;
        } else {
            // Only check that the specified columns are available in the table.
            $match_columns = array_intersect(self::$table_columns[$table], $columns);
            sort($columns);
            sort($match_columns);
            return $match_columns == $columns;
        }
    }

    /**
     * Returns the values of an ENUM or SET column, returning them as an array.
     *
     * @param  string $db_table   database table to lookup
     * @param  string $db_col     database column to lookup
     * @param  bool   $sort          Sort the output.
     * @return array    Array of the set/enum values on success, false on failure.
     */
    public function getEnumValues($db_table, $db_col, $sort=false)
    {
        $app =& App::getInstance();

        $stmt = $this->query(sprintf("SHOW COLUMNS FROM `%s` LIKE %s", $this->sanitizeIdentifier($db_table), $this->dbh->quote($db_col)), false);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (isset($row['Type']) && preg_match('/^(?:enum|set)\((.*)\)$/i', $row['Type'], $matches) && isset($matches[1]) && '' != $matches[1]) {
            $enum = str_getcsv($matches[1], ",", "'");
            if ($sort) {
                natsort($enum);
            }
            return $enum;
        } else {
            $app->logMsg(sprintf('No set or enum fields found in %s.%s', $db_table, $db_col), LOG_ERR, __FILE__, __LINE__);
            return false;
        }
    }
}