<?php
/**
* @package VikWP - Libraries
* @subpackage adapter.database
* @author E4J s.r.l.
* @copyright Copyright (C) 2023 E4J s.r.l. All Rights Reserved.
* @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
* @link https://vikwp.com
*/
// No direct access
defined('ABSPATH') or die('No script kiddies please!');
/**
* This adapter is required to wrap the Wordpress DB
* functions using the Joomla DB interface.
* This is helpful to improve the portability between Joomla and Wordpress.
*
* @since 10.0
*/
class JDatabase
{
/**
* The singleton instance of the database.
*
* @var JDatabase
*/
protected static $instance = null;
/**
* The global $wpdb instance.
*
* @var $wpdb
*/
protected $db;
/**
* The query set for the execution.
*
* @var string
*/
protected $q;
/**
* The last result fetched.
*
* @var mixed
*/
protected $result;
/**
* The query offset (start).
*
* @var integer
* @since 10.1.15
*/
protected $offset;
/**
* The query limit (max number of records).
*
* @var integer
* @since 10.1.15
*/
protected $limit;
/**
* The common database table prefix.
*
* @var string
* @since 10.1.37
*/
protected $tablePrefix;
/**
* Returns the global database adapter object, only creating it if it
* doesn't already exist.
*
* @param $wpdb $db The wordpress database handler.
*
* @return self A new instance of this class.
*/
public static function getInstance($db)
{
if (static::$instance === null)
{
static::$instance = new static($db);
}
return static::$instance;
}
/**
* Class constructor.
*
* @param $wpdb $db The wordpress db handler.
*/
protected function __construct($db)
{
$this->db = $db;
/**
* Hook used to suppress/enable database errors.
*
* @param boolean True to suppress the errors, false otherwise (false by default).
*
* @since 10.1.13
*/
$this->db->suppress_errors(apply_filters('vik_db_suppress_errors', false));
/**
* Hook used to show/hide database errors.
* In case errors are suppressed, this hook would result useless.
*
* @param boolean True to show the errors, false otherwise (true by default).
*
* @since 10.1.13
*/
$this->db->show_errors(apply_filters('vik_db_show_errors', true));
}
/**
* Magic method to proxy the functions in the $wpdb wrapped instance.
*
* @param string $method The called method.
* @param array $args The array of arguments passed to the method.
*
* @return mixed The value returned by the dispatched method.
* Null if the method doesn't exist.
*/
public function __call($name, $args)
{
if (method_exists($this->db, $name))
{
return call_user_func_array(array($this->db, $name), $args);
}
throw new RuntimeException('Call to undefined method ' . __CLASS__ . '::' . $name . '()', 500);
}
/**
* This function replaces a string placeholder with the real database prefix.
*
* @param string $sql The SQL statement to prepare.
* @param string $prefix The common table prefix.
*
* @return string The processed SQL statement.
*/
public function replacePrefix($sql, $prefix = '#__')
{
// generate a random placeholder
$placeholder = md5($prefix . uniqid());
// Replace all prefixes between the single/double quotes
// with the placeholder generated previously.
// This avoids to affect also strings that contains the actual prefix.
$sql = preg_replace_callback(
// "/('.*($prefix).*')|(\".*($prefix).*\")/",
// get all the strings contained between single and double quotes,
// even if they don't contain the prefix
"/('.*?')|(\".*?\")/",
function($match) use ($prefix, $placeholder)
{
// if contained, replace the prefix with the placeholder
return str_replace($prefix, $placeholder, $match[0]);
},
$sql
);
// get the prefix to use
$wp_prefix = $this->getPrefix();
// replace remaining prefixes (e.g. within backticks) with the real db prefix
$sql = str_replace($prefix, $wp_prefix, $sql);
// replace random placeholders with the original escaped prefix
$sql = str_replace($placeholder, $prefix, $sql);
return $sql;
}
/**
* Sets the SQL statement string for later execution.
*
* @param mixed $q The SQL statement to set as string.
* @param integer $offset The affected row offset to set.
* @param integer $limit The maximum affected rows to set.
*
* @return self This object to support chaining.
*/
public function setQuery($q, $offset = 0, $limit = 0)
{
$this->result = null;
/**
* If we are accessing #__users table, we need to route all
* the specified columns that belong to Joomla framework.
*
* @since 10.1.16
*/
$q = static::adjustJoomlaQuery2WP($q);
// save the query with the real db prefix
$this->q = $this->replacePrefix($q);
// register offset and limit (always override previous values)
$this->offset = abs((int) $offset);
$this->limit = abs((int) $limit);
return $this;
}
/**
* Gets the current query object or a new JDatabaseQuery object.
*
* @param boolean $new False to return the current query object, True to return a new JDatabaseQuery object.
*
* @return mixed The JDatabaseQuery object or a SQL plain string.
*/
public function getQuery($new = false)
{
if ($new)
{
JLoader::import('adapter.database.query');
return new JDatabaseQuery($this);
}
return $this->q;
}
/**
* Execute the SQL statement.
*
* @return boolean True on success, otherwise false.
*/
public function execute()
{
$sql = trim((string) $this->q);
// try to limit the query
if ($this->limit > 0 && $this->offset > 0)
{
$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
}
elseif ($this->limit > 0)
{
$sql .= ' LIMIT ' . $this->limit;
}
// if we are executing a SELECT query we need to
// load directly all the results fetched
if (preg_match("/^(SELECT|SHOW)/i", $sql))
{
// result should contain an array
$this->result = $this->db->get_results($sql);
}
// otherwise we can launch a generic query
else
{
// result should contain an integer
$this->result = $this->db->query($sql);
}
return (bool) $this->result;
}
/**
* Get the number of returned rows for the previous executed SQL statement.
* This command is only valid for statements like SELECT or SHOW that return an actual result set.
*
* @return integer The number of returned rows.
*/
public function getNumRows()
{
if (is_array($this->result))
{
return count($this->result);
}
return 0;
}
/**
* Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE
* for the previous executed SQL statement.
*
* @return integer The number of affected rows.
*/
public function getAffectedRows()
{
if (is_numeric($this->result))
{
return $this->result;
}
return 0;
}
/**
* Method to get the auto-incremented value from the last INSERT statement.
*
* @return mixed The value of the auto-increment field from the last inserted row.
*/
public function insertid()
{
return $this->db->insert_id;
}
/**
* Method to get an array of the result set rows from the database query
* where each row is an object.
*
* @return array The object list.
*
* @uses execute()
*/
public function loadObjectList()
{
if (is_null($this->result))
{
$this->execute();
}
if (is_array($this->result))
{
return $this->result;
}
return array();
}
/**
* Method to get an array of the result set rows from the database query
* where each row is an associative array of ['field_name' => 'row_value'].
*
* @return array The associative arrays list.
*
* @uses loadObjectList()
*/
public function loadAssocList()
{
$app = array();
foreach ($this->loadObjectList() as $obj)
{
$app[] = (array) $obj;
}
return $app;
}
/**
* Method to get the first row of the result set from the database query as an object.
*
* @return mixed The return value or null if the query failed.
*
* @uses loadObjectList()
*/
public function loadObject()
{
$list = $this->loadObjectList();
if (count($list))
{
return $list[0];
}
return null;
}
/**
* Method to get the first row of the result set from the database query
* as an associative array of ['field_name' => 'row_value'].
*
* @return mixed The return value or null if the query failed.
*
* @uses loadObject()
*/
public function loadAssoc()
{
$obj = $this->loadObject();
if ($obj !== null)
{
return (array) $obj;
}
return null;
}
/**
* Method to get the first field of the first row of the result set from the database query.
*
* @return mixed The return value or null if the query failed.
*
* @uses loadAssoc()
*/
public function loadResult()
{
$arr = $this->loadAssoc();
if (is_array($arr))
{
$keys = array_keys($arr);
return $arr[$keys[0]];
}
return null;
}
/**
* Method to get the first row of the result set from the database query as an array.
*
* Columns are indexed numerically so the first column in the result set would be accessible via <var>$row[0]</var>, etc.
*
* @return mixed The return value or null if the query failed.
*
* @since 10.1.37
*/
public function loadRow()
{
$arr = $this->loadAssoc();
if (is_array($arr))
{
return array_values($arr);
}
return null;
}
/**
* Method to get an array of values from the <var>$offset</var> field in each row
* of the result set from the database query.
*
* @param integer $offset The row offset to use to build the result array.
*
* @return array A list containing the columns.
*
* @uses loadAssocList()
*/
public function loadColumn($offset = 0)
{
$column = array();
foreach ($this->loadAssocList() as $arr)
{
$keys = array_keys($arr);
$column[] = $arr[$keys[$offset]];
}
return $column;
}
/**
* Quotes and optionally escapes a string to database requirements for use in database queries.
*
* @param mixed $text A string or an array of strings to quote.
* @param boolean $escape True (default) to escape the string, false to leave it unchanged.
*
* @return mixed The quoted input.
*/
public function quote($text, $escape = true)
{
if (is_array($text))
{
return esc_sql($text);
}
return '\'' . ($escape ? esc_sql((string) $text) : $text) . '\'';
}
/**
* Shorten alias for quote() method.
*
* @see quote()
*/
public function q($text, $escape = true)
{
return $this->quote($text, $escape);
}
/**
* Wraps an SQL statement identifier name such as column, table or database names
* in quotes to prevent injection risks and reserved word conflicts.
*
* @param mixed $name The identifier name to wrap in quotes, or an array of identifier
* names to wrap in quotes. Each type supports dot-notation name.
* @param mixed $as The AS query part associated to $name. It can be string or array.
*
* @return string The quote wrapped name.
*
* @uses _quoteName()
*/
public function quoteName($name, $as = null)
{
// define an empty array
$arr = array();
// fill $arr recursively with quoted names
$this->_quoteName($arr, $name, $as);
// concat the list using a comma separator
return implode(', ', $arr);
}
/**
* Shorten alias for quoteName() method.
*
* @see quoteName()
*/
public function qn($str, $as = null)
{
return $this->quoteName($str, $as);
}
/**
* Recursive method to quote a list of names.
*
* @param array &$arr A list containing all the quotes names.
* @param mixed $name The identifier name to wrap in quotes, or an array of identifier
* names to wrap in quotes. Each type supports dot-notation name.
* @param mixed $as The AS query part associated to $name. It can be string or array.
*
* @return void
*/
protected function _quoteName(array &$arr, $name, $as = null)
{
// if the name is (still) an array, quote it recursively
// until we have a scalar value
if (is_array($name))
{
// iterate the names contained in the list
foreach ($name as $i => $inner)
{
// obtain the AS only if it exists
$_as = !is_null($as) && is_array($as) && isset($as[$i]) ? $as[$i] : null;
$this->_quoteName($arr, $inner, $_as);
}
}
// quote the scalar value
else
{
// explode the name for dot-notation
$exp = explode('.', $name);
$name = "`{$exp[0]}`";
if (count($exp) > 1)
{
$name .= ".`{$exp[1]}`";
}
if (!is_null($as))
{
$name .= " AS `$as`";
}
$arr[] = $name;
}
}
/**
* Inserts a row into a table based on an object's properties.
*
* @param string $table The name of the database table to insert into.
* @param object &$object A reference to an object whose public properties match the table fields.
* @param string $key The name of the primary key. If provided the object property is updated.
*
* @return boolean True on success.
*/
public function insertObject($table, &$object, $key = null)
{
$data = array();
foreach (get_object_vars($object) as $k => $v)
{
// exclude primary key, not null values, arrays, objects and
// internal properties (prefixed with an underscore)
if ($k != $key && $v !== null && is_scalar($v) && $k[0] != '_')
{
$data[$k] = $v;
}
}
// insert the new record
if (!$this->db->insert($this->replacePrefix($table), $data))
{
return false;
}
// update the primary key if it exists
$id = $this->db->insert_id;
// store affected row
$this->result = $id;
if ($key && $id && is_string($key))
{
$object->{$key} = $id;
}
return true;
}
/**
* Updates a row in a table based on an object's properties.
*
* @param string $table The name of the database table to update.
* @param object &$object A reference to an object whose public properties match the table fields.
* @param mixed $key The name (or a list of names) of the primary key.
* @param boolean $nulls True to update null fields or false to ignore them.
*
* @return boolean True on success.
*/
public function updateObject($table, &$object, $key, $nulls = false)
{
$set = array();
$where = array();
if (is_string($key))
{
$key = array($key);
}
if (is_object($key))
{
$key = (array) $key;
}
foreach (get_object_vars($object) as $k => $v)
{
// exclude arrays, objects and internal properties (prefixed with an underscore)
if (is_array($v) || is_object($v) || $k[0] == '_')
{
continue;
}
// set the primary key to the WHERE clause instead of a field to update
if (in_array($k, $key))
{
$where[$k] = $v;
continue;
}
// update field only if not null or if nulls values are allowed
if ($v !== null || $nulls)
{
$set[$k] = $v;
}
}
// we don't have any fields to update
if (empty($set))
{
return true;
}
// update the specified record
$affected = $this->db->update($this->replacePrefix($table), $set, $where);
// store affected rows
$this->result = (int) $affected;
return $affected !== false;
}
/**
* Returns the error faced (if any) during the last query execution.
*
* @return string The error message.
*
* @since 10.1.58
*/
public function getLastError()
{
return $this->db->last_error;
}
/**
* Returns a PHP date() function compliant date format for the database driver.
*
* @return string The format string.
*/
public function getDateFormat()
{
return 'Y-m-d H:i:s';
}
/**
* Returns the null date in the format of the database driver.
*
* @return string The null date string.
*
* @since 10.1.5
*/
public function getNullDate()
{
return '0000-00-00 00:00:00';
}
/**
* Get the common table prefix for the database driver.
*
* @return string The common database table prefix.
*
* @since 10.1.37
*/
public function getPrefix()
{
if (is_null($this->tablePrefix))
{
/**
* Hook used to filter the default WP database prefix before it is used.
*
* @param string The database prefix to use for queries.
*
* @since 10.1.1
*/
$this->tablePrefix = apply_filters('vik_get_db_prefix', $this->db->prefix);
}
return $this->tablePrefix;
}
/**
* Retrieves field information about a given table.
*
* @param string $table The name of the database table.
* @param boolean $typeOnly True to only return field types.
*
* @return array An array of fields for the database table.
*
* @since 10.1.19
*/
public function getTableColumns($table, $typeOnly = true)
{
/**
* Do not escape the table name to support SQLite too.
*
* @since 10.1.53
*/
$q = "SHOW FULL COLUMNS FROM " . $table;
// set the query to get the table fields statement
$this->setQuery($q);
$this->execute();
$fields = $this->loadObjectList();
$result = [];
// if we only want the type as the value add just that to the list.
if ($typeOnly)
{
foreach ($fields as $field)
{
$result[$field->Field] = preg_replace('/[(0-9)]/', '', $field->Type);
}
}
// if we want the whole field data object add that to the list.
else
{
foreach ($fields as $field)
{
/**
* With SQLite the Extra column might not be included.
* Simulate the same result by checking whether the column
* is equal to `id` and force the "auto_increment" rule.
*
* @since 10.1.53
*/
if (!isset($field->Extra))
{
$field->Extra = $field->Field === 'id' ? 'auto_increment' : '';
}
$result[$field->Field] = $field;
}
}
return $result;
}
/**
* Method to get an array containing all the database tables.
*
* @return array An array of all the tables in the database.
*
* @since 10.1.37
*/
public function getTableList()
{
// set the query to get the tables statement
$this->setQuery('SHOW TABLES');
$this->execute();
return $this->loadColumn();
}
/**
* Shows the table CREATE statement that creates the given tables.
*
* @param mixed $tables A table name or a list of table names.
*
* @return array A list of the create SQL for the tables.
*
* @since 10.1.37
*/
public function getTableCreate($tables)
{
$result = [];
// sanitize input to an array and iterate over the list
$tables = (array) $tables;
foreach ($tables as $table)
{
// set the query to get the table CREATE statement
$this->setQuery('SHOW CREATE TABLE ' . $this->qn($table));
$this->execute();
$row = $this->loadRow();
// populate the result array based on the create statements
$result[$table] = $row[1];
}
return $result;
}
/**
* Adjusts a query built for Joomla to WordPress needs.
*
* @param mixed $query The SQL query string or a query builder.
*
* @return void
*
* @since 10.1.16
*/
public static function adjustJoomlaQuery2WP($query)
{
// always cast to string
$query = (string) $query;
// check if the query contains `#__users` and an optional alias
if (preg_match("/`#__users`(?:\s+AS\s+`([a-z0-9_]+)`)?/i", $query, $match))
{
$userTable = !empty($match[1]) ? $match[1] : null;
// check whether an alias should be used
$tableAlias = $userTable ? "`{$userTable}`\." : "";
$lookup = array();
// replace all the columns that match the regex
$query = preg_replace_callback("/{$tableAlias}`([a-z0-9_]+)`(?:\s*AS\s*`([a-z0-9_]+)`)?/i", function($match) use ($userTable, $tableAlias, $query, &$lookup)
{
// get current column and alias
$col = $match[1];
$alias = isset($match[2]) ? $match[2] : $match[1];
switch (strtolower($col))
{
case 'name':
$col = 'display_name';
break;
case 'username':
$col = 'user_login';
break;
case 'email':
$col = 'user_email';
break;
}
// rebuild column without using ALIAS
$str = ($userTable ? "`{$userTable}`." : "") . "`{$col}`";
$sign = ($tableAlias ? $tableAlias . '.' : '') . $col;
// check if lookup doesn't contain this column and the query is a select
if (!isset($lookup[$sign]) && preg_match("/^\s*SELECT/i", $query))
{
// obtain position of current column and position of FROM statement and
// make sure the chunk position is displayed before FROM
if (preg_match("/{$match[0]}/i", $query, $token, PREG_OFFSET_CAPTURE)
&& preg_match("/\sFROM\s/i", $query, $from, PREG_OFFSET_CAPTURE)
&& $token[0][1] < $from[0][1])
{
// add alias for column within SELECT
$str .= " AS `{$alias}`";
}
}
// mark column as registered within the lookup in order
// to avoid adding alias again outside the SELECT
$lookup[$sign] = 1;
return $str;
}, $query);
/**
* In case of multi-site, always use the base prefix when
* querying the users database table.
*
* @since 10.1.31
*/
if (is_multisite())
{
global $wpdb;
$query = preg_replace("/`#__users`/", "`{$wpdb->base_prefix}users`", $query);
}
}
return $query;
}
}