string $database Database name * @param bool $connect If false, don't connect in constructor (since 1.5.0.1) */ public function __construct($server, $user, $password, $database, $connect = true) { $this->server = $server; $this->user = $user; $this->password = $password; $this->database = $database; $this->is_cache_enabled = (defined('_PS_CACHE_ENABLED_')) ? _PS_CACHE_ENABLED_ : false; if (!defined('_PS_DEBUG_SQL_')) { define('_PS_DEBUG_SQL_', false); } if ($connect) { $this->connect(); } } /** * Disable the use of the cache. */ public function disableCache() { $this->is_cache_enabled = false; } /** * Enable & flush the cache. */ public function enableCache() { $this->is_cache_enabled = true; Cache::getInstance()->flush(); } /** * Closes connection to database. */ public function __destruct() { if ($this->link) { $this->disconnect(); } } /** * Execute a query and get result resource. * * @param string|DbQuery $sql * * @return bool|mysqli_result|PDOStatement|resource * * @throws PrestaShopDatabaseException */ public function query($sql) { if ($sql instanceof DbQuery) { $sql = $sql->build(); } $this->result = $this->_query($sql); if (!$this->result && $this->getNumberError() == 2006) { if ($this->connect()) { $this->result = $this->_query($sql); } } if (_PS_DEBUG_SQL_) { $this->displayError($sql); } return $this->result; } /** * Executes an INSERT query. * * @param string $table Table name without prefix * @param array $data Data to insert as associative array. If $data is a list of arrays, multiple insert will be done * @param bool $null_values If we want to use NULL values instead of empty quotes * @param bool $use_cache * @param int $type Must be Db::INSERT or Db::INSERT_IGNORE or Db::REPLACE * @param bool $add_prefix Add or not _DB_PREFIX_ before table name * * @return bool * * @throws PrestaShopDatabaseException */ public function insert($table, $data, $null_values = false, $use_cache = true, $type = Db::INSERT, $add_prefix = true) { if (!$data && !$null_values) { return true; } if ($add_prefix) { $table = _DB_PREFIX_ . $table; } if ($type == Db::INSERT) { $insert_keyword = 'INSERT'; } elseif ($type == Db::INSERT_IGNORE) { $insert_keyword = 'INSERT IGNORE'; } elseif ($type == Db::REPLACE) { $insert_keyword = 'REPLACE'; } elseif ($type == Db::ON_DUPLICATE_KEY) { $insert_keyword = 'INSERT'; } else { throw new PrestaShopDatabaseException('Bad keyword, must be Db::INSERT or Db::INSERT_IGNORE or Db::REPLACE'); } // Check if $data is a list of row $current = current($data); if (!is_array($current) || isset($current['type'])) { $data = [$data]; } $keys = []; $values_stringified = []; $first_loop = true; $duplicate_key_stringified = ''; foreach ($data as $row_data) { $values = []; foreach ($row_data as $key => $value) { if (!$first_loop) { // Check if row array mapping are the same if (!in_array("`$key`", $keys)) { throw new PrestaShopDatabaseException('Keys form $data subarray don\'t match'); } if ($duplicate_key_stringified != '') { throw new PrestaShopDatabaseException('On duplicate key cannot be used on insert with more than 1 VALUE group'); } } else { $keys[] = '`' . bqSQL($key) . '`'; } if (!is_array($value)) { $value = ['type' => 'text', 'value' => $value]; } if ($value['type'] == 'sql') { $values[] = $string_value = $value['value']; } else { $values[] = $string_value = $null_values && ($value['value'] === '' || null === $value['value']) ? 'NULL' : "'{$value['value']}'"; } if ($type == Db::ON_DUPLICATE_KEY) { $duplicate_key_stringified .= '`' . bqSQL($key) . '` = ' . $string_value . ','; } } $first_loop = false; $values_stringified[] = '(' . implode(', ', $values) . ')'; } $keys_stringified = implode(', ', $keys); $sql = $insert_keyword . ' INTO `' . $table . '` (' . $keys_stringified . ') VALUES ' . implode(', ', $values_stringified); if ($type == Db::ON_DUPLICATE_KEY) { $sql .= ' ON DUPLICATE KEY UPDATE ' . substr($duplicate_key_stringified, 0, -1); } return (bool) $this->q($sql, $use_cache); } /** * Executes an UPDATE query. * * @param string $table Table name without prefix * @param array $data Data to insert as associative array. If $data is a list of arrays, multiple insert will be done * @param string $where WHERE condition * @param int $limit * @param bool $null_values If we want to use NULL values instead of empty quotes * @param bool $use_cache * @param bool $add_prefix Add or not _DB_PREFIX_ before table name * * @return bool */ public function update($table, $data, $where = '', $limit = 0, $null_values = false, $use_cache = true, $add_prefix = true) { if (!$data) { return true; } if ($add_prefix) { $table = _DB_PREFIX_ . $table; } $sql = 'UPDATE `' . bqSQL($table) . '` SET '; foreach ($data as $key => $value) { if (!is_array($value)) { $value = ['type' => 'text', 'value' => $value]; } if ($value['type'] == 'sql') { $sql .= '`' . bqSQL($key) . "` = {$value['value']},"; } else { $sql .= ($null_values && ($value['value'] === '' || null === $value['value'])) ? '`' . bqSQL($key) . '` = NULL,' : '`' . bqSQL($key) . "` = '{$value['value']}',"; } } $sql = rtrim($sql, ','); if ($where) { $sql .= ' WHERE ' . $where; } if ($limit) { $sql .= ' LIMIT ' . (int) $limit; } return (bool) $this->q($sql, $use_cache); } /** * Executes a DELETE query. * * @param string $table Name of the table to delete * @param string $where WHERE clause on query * @param int $limit Number max of rows to delete * @param bool $use_cache Use cache or not * @param bool $add_prefix Add or not _DB_PREFIX_ before table name * * @return bool */ public function delete($table, $where = '', $limit = 0, $use_cache = true, $add_prefix = true) { if ($add_prefix) { $table = _DB_PREFIX_ . $table; } $this->result = false; $sql = 'DELETE FROM `' . bqSQL($table) . '`' . ($where ? ' WHERE ' . $where : '') . ($limit ? ' LIMIT ' . (int) $limit : ''); $res = $this->query($sql); if ($use_cache && $this->is_cache_enabled) { Cache::getInstance()->deleteQuery($sql); } return (bool) $res; } /** * Executes a query. * * @param string|DbQuery $sql * @param bool $use_cache * * @return bool */ public function execute($sql, $use_cache = true) { if ($sql instanceof DbQuery) { $sql = $sql->build(); } $this->result = $this->query($sql); if ($use_cache && $this->is_cache_enabled) { Cache::getInstance()->deleteQuery($sql); } return (bool) $this->result; } /** * Executes return the result of $sql as array. * * @param string|DbQuery $sql Query to execute * @param bool $array Return an array instead of a result object (deprecated since 1.5.0.1, use query method instead) * @param bool $use_cache * * @return array|false|mysqli_result|PDOStatement|resource|null * * @throws PrestaShopDatabaseException */ public function executeS($sql, $array = true, $use_cache = true) { if ($sql instanceof DbQuery) { $sql = $sql->build(); } $this->result = false; $this->last_query = $sql; if ($use_cache && $this->is_cache_enabled && $array) { $this->last_query_hash = Cache::getInstance()->getQueryHash($sql); if (($result = Cache::getInstance()->get($this->last_query_hash)) !== false) { Cache::getInstance()->incrementQueryCounter($sql); $this->last_cached = true; return $result; } } // This method must be used only with queries which display results if (!preg_match('#^\s*\(?\s*(select|show|explain|describe|desc)\s#i', $sql)) { if (defined('_PS_MODE_DEV_') && _PS_MODE_DEV_) { throw new PrestaShopDatabaseException('Db->executeS() must be used only with select, show, explain or describe queries'); } return $this->execute($sql, $use_cache); } $this->result = $this->query($sql); if (!$this->result) { $result = false; } else { if (!$array) { $use_cache = false; $result = $this->result; } else { $result = $this->getAll($this->result); } } $this->last_cached = false; if ($use_cache && $this->is_cache_enabled && $array) { Cache::getInstance()->setQuery($sql, $result); } return $result; } /** * Returns an associative array containing the first row of the query * This function automatically adds "LIMIT 1" to the query. * * @param string|DbQuery $sql the select query (without "LIMIT 1") * @param bool $use_cache Find it in cache first * * @return array|bool|object|null */ public function getRow($sql, $use_cache = true) { if ($sql instanceof DbQuery) { $sql = $sql->build(); } $sql = rtrim($sql, " \t\n\r\0\x0B;") . ' LIMIT 1'; $this->result = false; $this->last_query = $sql; if ($use_cache && $this->is_cache_enabled) { $this->last_query_hash = Cache::getInstance()->getQueryHash($sql); if (($result = Cache::getInstance()->get($this->last_query_hash)) !== false) { Cache::getInstance()->incrementQueryCounter($sql); $this->last_cached = true; return $result; } } $this->result = $this->query($sql); if (!$this->result) { $result = false; } else { $result = $this->nextRow($this->result); } $this->last_cached = false; if (null === $result) { $result = false; } if ($use_cache && $this->is_cache_enabled) { Cache::getInstance()->setQuery($sql, $result); } return $result; } /** * Returns a value from the first row, first column of a SELECT query. * * @param string|DbQuery $sql * @param bool $use_cache * * @return string|false|null */ public function getValue($sql, $use_cache = true) { if ($sql instanceof DbQuery) { $sql = $sql->build(); } if (!$result = $this->getRow($sql, $use_cache)) { return false; } return array_shift($result); } /** * Get number of rows for last result. * * @return int */ public function numRows() { if (!$this->last_cached && $this->result) { $nrows = $this->_numRows($this->result); if ($this->is_cache_enabled) { Cache::getInstance()->set($this->last_query_hash . '_nrows', $nrows); } return $nrows; } elseif ($this->is_cache_enabled && $this->last_cached) { return Cache::getInstance()->get($this->last_query_hash . '_nrows'); } } /** * Executes a query. * * @param string|DbQuery $sql * @param bool $use_cache * * @return bool|mysqli_result|PDOStatement|resource * * @throws PrestaShopDatabaseException */ protected function q($sql, $use_cache = true) { if ($sql instanceof DbQuery) { $sql = $sql->build(); } $this->result = false; $result = $this->query($sql); if ($use_cache && $this->is_cache_enabled) { Cache::getInstance()->deleteQuery($sql); } if (_PS_DEBUG_SQL_) { $this->displayError($sql); } return $result; } /** * Displays last SQL error. * * @param string|bool $sql * * @throws PrestaShopDatabaseException */ public function displayError($sql = false) { global $webservice_call; $errno = $this->getNumberError(); if ($webservice_call && $errno) { $dbg = debug_backtrace(); WebserviceRequest::getInstance()->setError(500, '[SQL Error] ' . $this->getMsgError() . '. From ' . (isset($dbg[3]['class']) ? $dbg[3]['class'] : '') . '->' . $dbg[3]['function'] . '() Query was : ' . $sql, 97); } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) { if ($sql) { throw new PrestaShopDatabaseException($this->getMsgError() . '

' . $sql . '
'); } throw new PrestaShopDatabaseException($this->getMsgError()); } } /** * Sanitize data which will be injected into SQL query. * * @param string $string SQL data which will be injected into SQL query * @param bool $html_ok Does data contain HTML code ? (optional) * * @return string Sanitized data */ public function escape($string, $html_ok = false, $bq_sql = false) { if (_PS_MAGIC_QUOTES_GPC_) { $string = stripslashes($string); } if (!is_numeric($string)) { $string = $this->_escape($string); if (!$html_ok) { $string = strip_tags(Tools::nl2br($string)); } if ($bq_sql === true) { $string = str_replace('`', '\`', $string); } } return $string; } /** * Try a connection to the database. * * @param string $server Server address * @param string $user Login for database connection * @param string $pwd Password for database connection * @param string $db Database name * @param bool $new_db_link * @param string|bool $engine * @param int $timeout * * @return int Error code or 0 if connection was successful */ public static function checkConnection($server, $user, $pwd, $db, $new_db_link = true, $engine = null, $timeout = 5) { return call_user_func_array([Db::getClass(), 'tryToConnect'], [$server, $user, $pwd, $db, $new_db_link, $engine, $timeout]); } /** * Try a connection to the database and set names to UTF-8. * * @param string $server Server address * @param string $user Login for database connection * @param string $pwd Password for database connection * * @return bool */ public static function checkEncoding($server, $user, $pwd) { return call_user_func_array([Db::getClass(), 'tryUTF8'], [$server, $user, $pwd]); } /** * Try a connection to the database and check if at least one table with same prefix exists. * * @param string $server Server address * @param string $user Login for database connection * @param string $pwd Password for database connection * @param string $db Database name * @param string $prefix Tables prefix * * @return bool */ public static function hasTableWithSamePrefix($server, $user, $pwd, $db, $prefix) { return call_user_func_array([Db::getClass(), 'hasTableWithSamePrefix'], [$server, $user, $pwd, $db, $prefix]); } /** * Tries to connect to the database and create a table (checking creation privileges). * * @param string $server * @param string $user * @param string $pwd * @param string $db * @param string $prefix * @param string|null $engine Table engine * * @return bool|string True, false or error */ public static function checkCreatePrivilege($server, $user, $pwd, $db, $prefix, $engine = null) { return call_user_func_array([Db::getClass(), 'checkCreatePrivilege'], [$server, $user, $pwd, $db, $prefix, $engine]); } /** * Checks if auto increment value and offset is 1. * * @param string $server * @param string $user * @param string $pwd * * @return bool */ public static function checkAutoIncrement($server, $user, $pwd) { return call_user_func_array([Db::getClass(), 'checkAutoIncrement'], [$server, $user, $pwd]); } /** * Get used link instance. * * @return PDO|mysqli|resource Resource */ public function getLink() { return $this->link; } }