public function select($sql)
{
$result = $this->pdo->query($sql);
$result->setFetchMode(\PDO::FETCH_ASSOC);
$data = $result->fetchAll();
return $data;
}
/**
* Connect to database
* @param string $databaseName
*/
public function connect($databaseName = "")
{
$this->bdLink = @mysqli_connect($this->hostname, $this->username, $this->password, '', $this->port);
$this->gestionErreur(!$this->bdLink, 'Connect - ' . self::ERROR_CONNECT . ' ' . $this->hostname);
if ($databaseName != "") {
$this->selectBd($databaseName);
}
//request with UTF-8 character set according to http://se.php.net/manual/en/function.mysqli-query.php
$this->bdLink->query("SET NAMES 'utf8'");
}
/**
* Creates a connection to the database.
*/
protected function _connect()
{
if ($this->connection) {
return;
}
$this->connection = new PDO($this->_dsn(), $this->cfg->user, $this->cfg->pass, $this->cfg->driverOptions);
foreach ($this->cfg->conQuery as $q) {
$this->connection->query($q);
}
$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
/**
* Executes an SQL query, returning an SQLiteResult object if the query
* returns results.
*
* @param string $sql The SQL query to execute.
* @param int $resultType Controls how the row(s) will be returned.
*
* @return mixed Returns a SQLiteResult object, or false on failure.
*
* @throws SQLiteException on failure.
*/
public function query($sql, $resultType = SQLiteResult::RESULT_OBJ)
{
try {
$result = $this->link->query($sql);
if ($result !== false) {
return new SQLiteResult($result, $resultType);
}
} catch (PDOException $e) {
throw new SQLiteException($e->getMessage());
}
return false;
}
/**
* データベースの一覧を配列で取得する
* @return array
*/
function _getTables()
{
$tables = array();
switch ($this->_dbType) {
case 'mysql':
$rs = mysql_query("SHOW TABLES;", $this->_dbLink);
while ($table = mysql_fetch_row($rs)) {
$tables[] = $table[0];
}
break;
case 'postgres':
$tables = $this->pg_list_tables($this->_dbLink);
break;
case 'sqlite':
// TODO 未実装
break;
case 'sqlite3':
$sth = $this->_dbLink->query("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
$result = $sth->fetchAll();
foreach ($result as $table) {
$tables[] = $table[0];
}
break;
}
return $tables;
}
/**
* Send Query to the database
* @param string $sql
* @return resource
*/
public function query($sql = null)
{
$sql = str_replace("{PREFIX}", $this->configs['mysql']['prefix'], $sql);
if (empty($sql)) {
return false;
}
if ($this->auto_free) {
$this->freeResult();
}
if ($this->db_type == "mysqli") {
$this->query_id = $this->link_id->query($sql);
if ($this->link_id->error != "") {
$this->halt($this->link_id->error);
}
} else {
if ($this->db_type == "mysql") {
$this->query_id = mysql_query($sql, $this->link_id) or $this->halt(mysql_error());
}
}
if (!$this->query_id) {
if ($this->db_type == "mysqli") {
$this->errno = $this->link_id->errno;
$this->error = $this->link_id->error;
} else {
if ($this->db_type == "mysql") {
$this->errno = mysql_errno($this->link_id);
$this->error = mysql_error($this->link_id);
}
}
$this->halt("Invalid SQL: " . $sql);
}
return $this->query_id;
}
/**
*
* @param type $sql
* @param bool $useExceptions Optional - If set to TRUE will throw an _Exception() | If set to FALSE will return FALSE on error
* @return boolean|string
* @throws _Exception with code _DB_QUERY_ERROR only if _DB_USE_EXCEPTIONS == TRUE or $useExceptions (override) == TRUE
*/
public function query($sql, $useExceptions = _DbConfig::USE_EXCEPTIONS)
{
if (!$this->isConnected) {
$rc = $this->createConnection();
if ($rc === FALSE) {
return FALSE;
}
}
if (_DbConfig::LOG_SQL) {
_Log::debug($sql);
}
try {
$this->lastResult = $this->mysqli->query($sql);
} catch (Exception $e) {
if ($useExceptions) {
throw new _Exception('Error executing query | ' . $e->getMessage(), _DbErrors::QUERY_ERROR, $e);
} else {
return FALSE;
}
}
if ($this->lastResult === FALSE) {
// Error occurred
_Log::crit('Error occurred while executing query');
if ($useExceptions) {
throw new _Exception('Error executing query', _DbErrors::QUERY_ERROR);
} else {
return FALSE;
}
}
$this->lastCount = $this->mysqli->affected_rows;
return $this->lastResult;
}
/**
* Executes an SQL script on the connection
* @param string $sql SQL script
* @return zibo\library\database\mysql\MysqlResult Result object
* @throws zibo\library\database\mysql\exception\MysqlException when the provided SQL is empty
* @throws zibo\library\database\mysql\exception\MysqlException when not connected to the database
* @throws zibo\library\database\mysql\exception\MysqlErrorException when the SQL could not be executed
*/
public function execute($sql)
{
if (String::isEmpty($sql)) {
throw new SqliteException('Provided SQL is empty');
}
if (!$this->isConnected()) {
$exception = new SqliteException('Not connected to the database');
Zibo::getInstance()->runEvent('log', 'Execute ' . $sql, $exception->getMessage(), 1, DatabaseManager::LOG_NAME);
throw $exception;
}
try {
$resultResource = $this->connection->query($sql);
if ($resultResource === false) {
$errorCode = $this->connection->lastErrorCode();
$errorMessage = $this->connection->lastErrorMessage();
$exception = new SqliteErrorException($errorCode, $errorMessage);
Zibo::getInstance()->runEvent('log', 'Execute ' . $sql, $exception->getMessage(), 1, DatabaseManager::LOG_NAME);
throw $exception;
}
} catch (Exception $exception) {
Zibo::getInstance()->runEvent('log', 'Execute ' . $sql, $exception->getMessage(), 1, DatabaseManager::LOG_NAME);
throw $exception;
}
Zibo::getInstance()->runEvent('log', 'Execute ' . $sql, '', 0, DatabaseManager::LOG_NAME);
$result = new SqliteResult($sql, $resultResource);
if ($resultResource !== true) {
$resultResource->finalize();
}
return $result;
}
/**
* Add items on the database
*
* @param Integer $feed_id ToDo desc
* @param Array $items ToDo desc
*
* @return void
*/
public function addItems($feed_id, $items)
{
if (empty($items)) {
return;
}
$items = array_slice($items, 0, intval($this->getConfig('FeedTicker.itemsLimit', 5)));
$dli = intval($this->getConfig('FeedTicker.dateLimit', 60 * 60 * 24 * 7));
$dateLimit = time() - $dli;
$q = $this->db->prepare('INSERT INTO ft_items (
feed_id, updated, title, link, author, read
) VALUES (
:feed_id, :updated, :title, :link, :author, :read
)');
foreach ($items as $i) {
if (!empty($i['updated']) and $i['updated'] < $dateLimit) {
continue;
}
// Check if this item already exists
$sql = 'SELECT COUNT(*) FROM ft_items WHERE feed_id = ' . $this->db->quote($feed_id) . ' AND link = ' . $this->db->quote(trim($i['link']));
$opa = $this->db->query($sql)->fetchColumn();
if ((bool) $this->db->query($sql)->fetchColumn()) {
continue;
}
$q->execute(array(':feed_id' => $feed_id, ':updated' => trim($i['updated']), ':title' => trim($i['title']), ':link' => trim($i['link']), ':author' => trim($i['author']), ':read' => 0));
}
}
/**
* perform a query on the database
*
* @param string $sql a valid MySQL query
* @param int $limit number of records to return
* @param int $start offset of first record to return
*
* @return bool|resource query result or FALSE if successful
* or TRUE if successful and no result
* @deprecated since version 2.6.0 - alpha 3. Switch to doctrine connector.
*/
public function queryF($sql, $limit = 0, $start = 0)
{
$this->deprecated();
if (!empty($limit)) {
if (empty($start)) {
$start = 0;
}
$sql = $sql . ' LIMIT ' . (int) $start . ', ' . (int) $limit;
}
$xoopsPreload = XoopsPreload::getInstance();
$xoopsPreload->triggerEvent('core.database.query.start');
try {
$result = $this->conn->query($sql);
} catch (Exception $e) {
$result = false;
}
$this->lastResult = $result;
$xoopsPreload->triggerEvent('core.database.query.end');
if ($result) {
$xoopsPreload->triggerEvent('core.database.query.success', array($sql));
return $result;
} else {
$xoopsPreload->triggerEvent('core.database.query.failure', array($sql, $this));
return false;
}
}
/**
* {@inheritDoc}
*
* @param string $sql SQL statement to execute
* @param array $params bind_name => value values to interpolate into
* the $sql to be executes
* @return mixed false if query fails, resource or true otherwise
*/
function exec($sql, $params = array())
{
static $last_sql = NULL;
static $statement = NULL;
$is_select = strtoupper(substr(ltrim($sql), 0, 6)) == "SELECT";
if ($last_sql != $sql) {
$statement = NULL;
//garbage collect so don't sqlite lock
}
if ($params) {
if (!$statement) {
$statement = $this->pdo->prepare($sql);
}
$result = $statement->execute($params);
$this->num_affected = $statement->rowCount();
if ($result) {
if ($is_select) {
$result = $statement;
} else {
$result = $this->num_affected;
}
}
} else {
if ($is_select) {
$result = $this->pdo->query($sql);
$this->num_affected = 0;
} else {
$this->num_affected = $this->pdo->exec($sql);
$result = $this->num_affected + 1;
}
}
$last_sql = $sql;
return $result;
}
/**
* Execute a MySQL-Query
*
* @throws Exception
* @param string $query The query to execute
* @param const $kind Type of result set
*
* @return array
*/
public function query($query, $kind = self::ARRAY_OBJECT)
{
if (!$this->_mysqli instanceof mysqli) {
$this->dbConnect($this->dbSelected);
}
//echo "<br>Mysqli: executing query: " . $query;
$result = $this->_mysqli->query($query);
if (false === $result) {
$this->sqlError($this->_mysqli->error, $this->_mysqli->errno);
}
if (!$result instanceof mysqli_result || $kind === self::SIMPLE) {
return $result;
}
$ret = array();
if ($kind === self::ARRAY_OBJECT) {
while ($row = $result->fetch_object()) {
$ret[] = $row;
}
} elseif ($kind === self::ARRAY_NUMERIC) {
while ($row = $result->fetch_array(MYSQLI_NUM)) {
$ret[] = $row;
}
} elseif ($kind === self::ARRAY_ASSOC) {
while ($row = $result->fetch_assoc()) {
$ret[] = $row;
}
}
if ($result instanceof mysqli) {
$result->close();
}
return $ret;
}
/**
* Construct
*
* @param Registry $registry
* @param Request $request
* @param int $language_id Default language id
*/
public function __construct(Registry $registry, Request $request, $language_id)
{
$_translation = array();
$this->_db = $registry->get('db');
try {
$statement = $this->_db->query('SELECT * FROM `language`');
} catch (PDOException $e) {
if ($this->_db->inTransaction()) {
$this->_db->rollBack();
}
trigger_error($e->getMessage());
}
if ($statement->rowCount()) {
foreach ($statement->fetchAll() as $language) {
// Add languages registry
$this->_languages[$language->language_id] = array('language_id' => $language->language_id, 'language_code' => $language->code, 'language_locale' => $language->locale, 'language_name' => $language->name);
// Set default language
if ($language->language_id == $language_id) {
$this->_language_id = $language->language_id;
$this->_language_code = $language->code;
$this->_language_locale = $language->locale;
$this->_language_name = $language->name;
}
// Get active language
if (isset($request->get['language_id'])) {
$_language_id = (int) $request->get['language_id'];
} else {
if (isset($request->cookie['language_id'])) {
$_language_id = (int) $request->cookie['language_id'];
} else {
$_language_id = (int) DEFAULT_LANGUAGE_ID;
}
}
// Set current language
$language_file = DIR_BASE . 'language' . DIR_SEPARATOR . $language->code . '.php';
if ($_language_id == $language->language_id && file_exists($language_file) && is_readable($language_file)) {
$this->_language_id = $language->language_id;
$this->_language_code = $language->code;
$this->_language_locale = $language->locale;
$this->_language_name = $language->name;
// Load language package if exist
require_once $language_file;
$this->_translation = $_translation;
}
}
}
}
/**
* Ejecuta la consulta SQL expresada en la variable _sqlQuery.
* @method execQuery()
* @access public
* @return resorce|boolean|null
* @throws Exception
* @see self::_throwModelException(), self::isValidConnResource(), self::_parseResults()
*/
public function execQuery()
{
if (!empty($this->_sqlQuery) && !is_string($this->_sqlQuery)) {
exit('$sql no es una consulta valida.');
}
$this->_resource = $this->_PDOmySQLConn->query($this->_sqlQuery);
$this->_parseResults();
}
/**
* query
* @param string $sql
* @param resource $connResource
* @return array
*/
public function query($sql, $connResource)
{
$rows = array();
$result = $connResource->query($sql);
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
/**
* Execute the query string
*
* @param string $sql[optional] The query string. If not given then the object's sql property will be used.
* @return boolean True if the query was successful, false otherwise.
*/
function query( $sql = '') {
if( $sql != '' ) $this->sql = $sql;
$this->rs = $this->conn->query( $this->sql );
if( ! $this->rs ) return false;
return true;
}
/**
* Garbage Collector
* @param int life time (sec.)
* @return bool
* @see session.gc_divisor 100
* @see session.gc_maxlifetime 1440
* @see session.gc_probability 1
* @usage execution rate 1/100
* (session.gc_probability/session.gc_divisor)
*/
public function gc($max)
{
//Delete single use sessions (search-bots etc.)
$limit = time() - 3600 * 5;
$sql = sprintf("DELETE FROM %s WHERE hits=1 AND timestamp < %s", $this->dbTable, $limit);
$this->dbConnection->query($sql);
//Delete according to GC $max age setting
$sql = sprintf("DELETE FROM %s WHERE `timestamp` < '%s'", $this->dbTable, time() - intval($max));
return $this->dbConnection->query($sql);
}
/**
* Execute query
*
* @abstract
* @access protected
* @param string $query The query to execute
* @return resource The resulting query resource
*/
function &query($query)
{
$this->foowd->debug('sql', $query);
$result = $this->conn->query($query);
// Always check that $result is not an error
if (DB::isError($result)) {
$this->foowd->debug('msg', $result->getMessage());
return FALSE;
}
return $result;
}
/**
* @ticket 32279
*/
function test_strip_invalid_text_from_query_cp1251_is_safe()
{
$tablename = 'test_cp1251_query_' . rand_str(5);
if (!self::$_wpdb->query("CREATE TABLE {$tablename} ( a VARCHAR(50) ) DEFAULT CHARSET 'cp1251'")) {
$this->markTestSkipped("Test requires the 'cp1251' charset");
}
$safe_query = "INSERT INTO {$tablename}( `a` ) VALUES( 'safe data' )";
$stripped_query = self::$_wpdb->strip_invalid_text_from_query($safe_query);
self::$_wpdb->query("DROP TABLE {$tablename}");
$this->assertEquals($safe_query, $stripped_query);
}
/**
* @dataProvider data_table_collation_check
* @ticket 21212
*/
function test_table_collation_check($create, $expected, $query, $drop, $always_true)
{
self::$_wpdb->query($drop);
self::$_wpdb->query($create);
$return = self::$_wpdb->check_safe_collation($query);
$this->assertEquals($expected, $return);
foreach ($always_true as $true_query) {
$return = self::$_wpdb->check_safe_collation($true_query);
$this->assertTrue($return);
}
self::$_wpdb->query($drop);
}
/**
* Run the query
*
* @param string $query The query
*/
public function query($query)
{
$this->clear();
$result = $this->mysqli->query($query);
if (!$result) {
$this->last_error = $this->mysqli->error;
} else {
$this->last_error = false;
}
$this->last_query = $query;
return $result;
}
/**
* Get a list of all cell addresses currently held in cache
*
* @return array of string
*/
public function getCellList()
{
$query = "SELECT id FROM kvp_" . $this->_TableName;
$cellIdsResult = $this->_DBHandle->query($query);
if ($cellIdsResult === false) {
throw new Exception($this->_DBHandle->lastErrorMsg());
}
$cellKeys = array();
while ($row = $cellIdsResult->fetchArray(SQLITE3_ASSOC)) {
$cellKeys[] = $row['id'];
}
return $cellKeys;
}
/**
* @param string $sql
* @param bool $noexcept
* @return bool|stdClass
* @throws AException
*/
public function query($sql, $noexcept = false)
{
//echo $this->database_name;
$time_start = microtime(true);
$result = $this->connection->query($sql);
$time_exec = microtime(true) - $time_start;
// to avoid debug class init while setting was not yet loaded
if ($this->registry->get('config')) {
if ($this->registry->get('config')->has('config_debug')) {
$backtrace = debug_backtrace();
ADebug::set_query($sql, $time_exec, $backtrace[2]);
}
}
if ($result) {
if (!is_bool($result)) {
$i = 0;
$data = array();
while ($row = $result->fetch_object()) {
$data[$i] = (array) $row;
$i++;
}
$query = new stdClass();
$query->row = isset($data[0]) ? $data[0] : array();
$query->rows = $data;
$query->num_rows = (int) $result->num_rows;
unset($data);
return $query;
} else {
return TRUE;
}
} else {
if ($noexcept) {
$this->error = 'AbanteCart Error: ' . $result->error . '<br />' . $sql;
return FALSE;
} else {
throw new AException(AC_ERR_MYSQL, 'Error: ' . $result->error . '<br />' . $sql);
}
}
}
/**
* Retrieve a Token based on the passed ID (Token hash).
* @access public
* @param string $token_id Token ID
* @return mixed Token object, or null if it could not be retrieved.
*/
public function retrieveToken($token_id)
{
$tokens = $this->connection->query(sprintf("SELECT token_hash, token_valid_until, username FROM tokens INNER JOIN users ON (token_user_id = user_id) WHERE token_hash = '%s' LIMIT 1;", sqlite_escape_string($token_id)));
if ($tokens === false) {
return null;
}
$t = $tokens->fetch(PDO::FETCH_ASSOC);
if ($t === false) {
return null;
}
$token = new Token($t['username'], null, $t['token_valid_until'], $t['token_hash']);
return $token;
}
public function getConnection()
{
try {
if ($this->dbh == null) {
$this->dbh = new PDO($this->connectionString, $this->username, $this->password);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->dbh->query("SET NAMES 'utf8'");
}
return $this->dbh;
} catch (Exception $e) {
Logger::getLogger('system.services.Storage.GenericDAO')->error('Cannot connect to the database: ' . $e->getMessage());
throw $e;
}
}
/**
* Check availabe InnoDB on database.
*
* @return Magento_Downloader_Validator
*/
protected function _checkDbInnoDb()
{
if (!$this->_connection) {
return $this;
}
$result = $this->_connection->query('show variables like \'have_innodb\';');
$innoDb = $result->fetchColumn(1);
if ($innoDb != 'YES') {
$this->addError('Database server does not support InnoDB storage engine');
} else {
$this->addMessage('Database server supports InnoDB storage engine');
}
return $this;
}
/**
* Is a value set for an indexed cell?
*
* @param string $pCoord Coordinate address of the cell to check
* @return boolean
*/
public function isDataSet($pCoord)
{
if ($pCoord === $this->_currentObjectID) {
return true;
}
// Check if the requested entry exists in the cache
$query = "SELECT id FROM kvp_" . $this->_TableName . " WHERE id='" . $pCoord . "'";
$cellResultSet = $this->_DBHandle->query($query, SQLITE_ASSOC);
if ($cellResultSet === false) {
throw new Exception(sqlite_error_string($this->_DBHandle->lastError()));
} elseif ($cellResultSet->numRows() == 0) {
// Return null if requested entry doesn't exist in cache
return false;
}
return true;
}
/**
* Get a list of all cell addresses currently held in cache
*
* @return string[]
* @throws \PHPExcel\Exception
*/
public function getCellList()
{
if ($this->currentObjectID !== null) {
$this->storeData();
}
$query = "SELECT id FROM kvp_" . $this->TableName;
$cellIdsResult = $this->DBHandle->query($query);
if ($cellIdsResult === false) {
throw new \PHPExcel\Exception($this->DBHandle->lastErrorMsg());
}
$cellKeys = array();
while ($row = $cellIdsResult->fetchArray(SQLITE3_ASSOC)) {
$cellKeys[] = $row['id'];
}
return $cellKeys;
}
/**
* Return an array with the data to send
*
* @access private
* @return array
*/
function _getRecord()
{
if (isset($GLOBALS['TableListImpact'])) {
$tableList = explode(',', $GLOBALS['TableListImpact']);
if (count($tableList) > 1) {
$withTableName = true;
} else {
$withTableName = false;
}
foreach ($tableList as $tableImpact) {
if (!empty($tableImpact) && !preg_match('#\\.#', $tableImpact)) {
$tempInfoTable = $this->SQLiteConnId->array_query('PRAGMA table_info(' . brackets(trim($tableImpact)) . ');');
if (is_array($tempInfoTable)) {
foreach ($tempInfoTable as $infoTable) {
if ($withTableName) {
$this->NullInfo[trim($tableImpact) . '.' . $infoTable['name']] = $infoTable['notnull'];
} else {
$this->NullInfo[$infoTable['name']] = $infoTable['notnull'];
}
}
}
}
}
}
if (strpos(trim($this->order), ' ')) {
$order = '"' . $this->order . '"';
} else {
$order = $this->order;
}
$query = $this->query . ($this->order ? ' ORDER BY ' . $order . ' ' . $this->orderSens : '');
if (!preg_match('#pragma#i', $this->query) && !preg_match('#limit#i', $this->query)) {
$query .= ' LIMIT ' . $this->indexStart . ', ' . $this->recordPerPage;
}
if ($this->SQLiteConnId->query($query)) {
unset($tabRecord);
$tabRecord = array();
while ($ligne = $this->SQLiteConnId->fetch_array(null, SQLITE_NUM)) {
$tabRecord[] = $ligne;
}
}
$this->realQuery = $query;
return $tabRecord;
}
public function query($sql, $errorLevel = E_USER_ERROR)
{
if (isset($_REQUEST['previewwrite']) && in_array(strtolower(substr($sql, 0, strpos($sql, ' '))), array('insert', 'update', 'delete', 'replace'))) {
Debug::message("Will execute: {$sql}");
return;
}
if (isset($_REQUEST['showqueries']) && Director::isDev(true)) {
$starttime = microtime(true);
}
$handle = $this->dbConn->query($sql);
if (isset($_REQUEST['showqueries']) && Director::isDev(true)) {
$endtime = round(microtime(true) - $starttime, 4);
Debug::message("\n{$sql}\n{$endtime}ms\n", false);
}
if (!$handle && $errorLevel) {
$this->databaseError("Couldn't run query: {$sql} | " . $this->dbConn->error, $errorLevel);
}
return new MySQLQuery($this, $handle);
}
