public static function logRequest($dbInstanceKey = null)
{
$sql = MySqlDbManager::getQueryObject($dbInstanceKey);
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_REQUEST_LOG'))->values(array("session_id" => session_id(), "get" => serialize($_GET), "post" => serialize($_POST), "server" => serialize($_SERVER), "cookies" => serialize($_COOKIE), "session" => serialize($_SESSION), "response" => ob_get_contents()));
$sql->exec($qb->getSQL());
}
/**
* Parse requests log and blacklist flooding IPs.
* Should be called by cron job every minute.
*/
public function parseLogForFloodingIps()
{
$tablesToLock = array(Tbl::get('TBL_SECURITY_REQUESTS_LOG'), Tbl::get('TBL_SECURITY_FLOODER_IPS'));
MySqlDbManager::getDbObject()->lockTables($tablesToLock, "w");
$this->query->exec("INSERT IGNORE INTO `" . Tbl::get('TBL_SECURITY_FLOODER_IPS') . "` (`ip`) \n\t\t\t\t\t\t\tSELECT `ip` \n\t\t\t\t\t\t\t\tFROM `" . Tbl::get('TBL_SECURITY_REQUESTS_LOG') . "` \n\t\t\t\t\t\t\t\tWHERE `count` >= " . $this->config->requestsLimit);
$this->query->exec("TRUNCATE TABLE `" . Tbl::get('TBL_SECURITY_REQUESTS_LOG') . "`");
MySqlDbManager::getDbObject()->unlockTables();
}
/**
* Get Mysql's current datetime by selecting NOW()
*
* @return string
*/
function getDBCurrentDateTime($isTimestamp = false)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
if ($isTimestamp) {
$qb->select(new Func("UNIX_TIMESTAMP", new Func("NOW"), 'now'));
} else {
$qb->select(new Func("NOW", null, 'now'));
}
return $sql->exec($qb->getSQL())->fetchField('now');
}
public static function setControllerTemplateByHost(Host $host, $controller, $template)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
if (!empty($controller) or !empty($template)) {
$qb->insert(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->values(array('host_id' => $host->id, 'controller' => $controller, 'template' => $template))->onDuplicateKeyUpdate()->set(new Field('controller'), $controller)->set(new Field('template'), $template);
} else {
$qb->delete(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->where($qb->expr()->equal(new Field('host_id'), $host->id));
}
$sql->exec($qb->getSQL());
return $sql->affected();
}
/**
* Get all hosts
*@return array Set of Host objects
*/
public static function getAllHosts($cacheMinutes = null)
{
$hosts = array();
$sql = MySqlDbManager::getQueryObject();
$sql->exec("SELECT * FROM `" . Tbl::get('TBL_HOSTS', 'Host') . "`", $cacheMinutes);
while (($host_data = $sql->fetchRecord()) != false) {
$h = new Host();
Host::setData($host_data, $h);
$hosts[] = $h;
}
return $hosts;
}
public static function getAllLanguages($cacheMinutes = null)
{
$languages = array();
$sql = MySqlDbManager::getQueryObject();
$sql->exec("SELECT * FROM `" . Tbl::get('TBL_LANGUAGES') . "`", $cacheMinutes);
while (($lang_data = $sql->fetchRecord()) != false) {
$l = new Language();
static::setData($lang_data, $l);
$languages[] = $l;
}
return $languages;
}
public static function logRequest($dbInstanceKey = null)
{
$sql = MySqlDbManager::getQueryObject($dbInstanceKey);
$userId = "NULL";
$userObjectSerialized = "''";
$userObj = Reg::get(ConfigManager::getConfig("Users", "Users")->ObjectsIgnored->User);
if ($userObj->isAuthorized()) {
$userId = $userObj->getId();
$userObjectSerialized = "'" . mysql_real_escape_string(serialize($userObj)) . "'";
}
$sql->exec("INSERT DELAYED INTO `" . Tbl::get("TBL_REQUEST_LOG") . "` \n\t\t\t\t\t\t(`user_id`, `user_obj`,`session_id`, `get`, `post`, `server`, `cookies`, `session`, `response`)\n\t\t\t\t\t\tVALUES\t(\n\t\t\t\t\t\t\t\t\t{$userId},\n\t\t\t\t\t\t\t\t\t{$userObjectSerialized},\n\t\t\t\t\t\t\t\t\t'" . session_id() . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_GET)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_POST)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_SERVER)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_COOKIE)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_SESSION)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(ob_get_contents()) . "'\n\t\t\t\t\t\t\t\t)");
}
/**
* Parse requests log and blacklist flooding IPs.
* Should be called by cron job every minute.
*/
public function parseLogForFloodingIps()
{
$tablesToLock = array(Tbl::get('TBL_SECURITY_REQUESTS_LOG'), Tbl::get('TBL_SECURITY_FLOODER_IPS'));
MySqlDbManager::getDbObject()->startTransaction();
$qb = new QueryBuilder();
$qbSelect = new QueryBuilder();
$qbSelect->select(new Field('ip'))->from(Tbl::get('TBL_SECURITY_REQUESTS_LOG'))->where($qbSelect->expr()->greaterEqual(new Field('count'), $this->config->requestsLimit));
$qb->insertIgnore(Tbl::get('TBL_SECURITY_FLOODER_IPS'))->fields('ip')->values($qbSelect);
$this->query->exec($qb->getSQL());
$this->query->exec("TRUNCATE TABLE `" . Tbl::get('TBL_SECURITY_REQUESTS_LOG') . "`");
if (!MySqlDbManager::getDbObject()->commit()) {
MySqlDbManager::getDbObject()->rollBack();
}
}
public static function logRequest($dbInstanceKey = null)
{
$sql = MySqlDbManager::getQueryObject($dbInstanceKey);
$userId = "NULL";
$userObjectSerialized = "''";
$userObj = Reg::get(ConfigManager::getConfig("Users", "Users")->ObjectsIgnored->User);
if ($userObj->isAuthorized()) {
$userId = $userObj->id;
$userObjectSerialized = "'" . mysql_real_escape_string(serialize($userObj)) . "'";
}
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_REQUEST_LOG'))->values(array("user_id" => $userId, "user_obj" => $userObjectSerialized, "session_id" => session_id(), "get" => serialize($_GET), "post" => serialize($_POST), "server" => serialize($_SERVER), "cookies" => serialize($_COOKIE), "session" => serialize($_SESSION), "response" => ob_get_contents()));
$sql->exec($qb->getSQL());
}
function __construct($host_id = null, $cacheMinutes = null, $dbInstanceKey = null)
{
if ($host_id !== null) {
if (!is_numeric($host_id)) {
throw new InvalidIntegerArgumentException("host_id argument should be an integer.");
}
$sql = MySqlDbManager::getQueryObject($dbInstanceKey);
$sql->exec("SELECT * FROM `" . Tbl::get('TBL_HOSTS') . "` WHERE `id` = '{$host_id}'", $cacheMinutes);
if ($sql->countRecords()) {
$res = $sql->fetchRecord();
static::setData($res, $this);
} else {
throw new InvalidArgumentException("Wrong host id is given. No record with id: {$host_id} in table " . Tbl::get('TBL_HOSTS'));
}
}
}
public static function getAllLanguages(MysqlPager $pager = null, $cacheMinutes = null)
{
$languages = array();
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_LANGUAGES'));
if ($pager !== null) {
$sql = $pager->executePagedSQL($qb->getSQL(), $cacheMinutes);
} else {
$sql->exec($qb->getSQL(), $cacheMinutes);
}
while (($lang_data = $sql->fetchRecord()) != false) {
$l = new Language();
static::setData($lang_data, $l);
$languages[] = $l;
}
return $languages;
}
function __construct($host_id = null, $cacheMinutes = null, $dbInstanceKey = null)
{
if ($host_id !== null) {
if (!is_numeric($host_id)) {
throw new InvalidIntegerArgumentException("host_id argument should be an integer.");
}
$sql = MySqlDbManager::getQueryObject($dbInstanceKey);
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS'))->where($qb->expr()->equal(new Field('id'), $host_id));
$sql->exec($qb->getSQL(), $cacheMinutes);
if ($sql->countRecords()) {
$res = $sql->fetchRecord();
static::setData($res, $this);
} else {
throw new InvalidArgumentException("Wrong host id is given. No record with id: {$host_id} in table " . Tbl::get('TBL_HOSTS'));
}
}
}
public function hookInvalidLoginAttempt($params)
{
if ($this->config->AuxConfig->loginBruteForceProtectionEnabled) {
if (isset($_SERVER['REMOTE_ADDR'])) {
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$sql->exec($qb->select(new Field('count'))->from(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->where($qb->expr()->equal(new Field('ip'), $_SERVER['REMOTE_ADDR']))->getSQL());
$failedAuthCount = $sql->fetchField('count');
$newFailedAuthCount = $failedAuthCount + 1;
if ($newFailedAuthCount >= $this->config->AuxConfig->failedLoginLimit) {
Reg::get(ConfigManager::getConfig("Security", "RequestLimiter")->Objects->RequestLimiter)->blockIP();
$qb = new QueryBuilder();
$sql->exec($qb->delete(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->where($qb->expr()->equal(new Field('ip'), $_SERVER['REMOTE_ADDR']))->getSQL());
throw new RequestLimiterTooManyAuthTriesException("Too many unsucessful authorization tries.");
}
$qb = new QueryBuilder();
$sql->exec($qb->insert(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->values(array('ip' => $_SERVER['REMOTE_ADDR']))->onDuplicateKeyUpdate()->set(new Field('count'), $qb->expr()->sum(new Field('count'), 1))->getSQL());
}
}
}
/**
* Get Users list according to filter and pager
*
* @param UsersFilter $filter
* @param MysqlPager $pager
* @param integer $initObjects For ex. INIT_PROPERTIES | INIT_PERMISSIONS
* @param integer $cacheMinutes
*/
public function getUsersList(UsersFilter $filter = null, MysqlPager $pager = null, $initObjects = self::INIT_ALL, $cacheMinutes = 0, $cacheTag = null)
{
if ($filter == null) {
$filter = new UsersFilter();
}
$sqlQuery = $filter->getSQL();
$sql = MySqlDbManager::getQueryObject();
if ($pager !== null) {
$sql = $pager->executePagedSQL($sqlQuery, $cacheMinutes, null, $cacheTag);
} else {
$sql->exec($sqlQuery, $cacheMinutes, $cacheTag);
}
$users = array();
if ($sql->countRecords()) {
while (($userId = $sql->fetchField('id')) != false) {
array_push($users, $this->getUserById($userId, $initObjects, $cacheMinutes, $cacheTag));
}
}
return $users;
}
public static function getTemplateByHost(Host $host)
{
$sql = MySqlDbManager::getQueryObject();
$sql->exec("SELECT `template` FROM `" . Tbl::get("TBL_HOST_TEMPLATES") . "` WHERE `host_id`='{$host->id}'");
return $sql->fetchField("template");
}
public function clearGarbage()
{
$db = MySqlDbManager::getDbObject();
$db->lockTables(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS'), "w");
$qb = new QueryBuilder();
$qb->select(new Field("system_filename"))->from(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS', 'ConversationAttachmentManager'))->where($qb->expr()->isNull(new Field('message_id')))->andWhere($qb->expr()->greater($qb->expr()->diff(new Func("NOW"), new Field('date')), 60 * 60 * 24 * $this->config->attachmentsClearTimeout));
$this->query->exec($qb->getSQL());
while (($row = $this->query->fetchRecord()) != null) {
try {
@unlink($this->config->uploadDir . $row['system_filename']);
} catch (ErrorException $e) {
}
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS', 'ConversationAttachmentManager'))->where($qb->expr()->isNull(new Field('message_id')))->andWhere($qb->expr()->greater($qb->expr()->diff(new Func("NOW"), new Field('date')), 60 * 60 * 24 * $this->config->attachmentsClearTimeout));
$deletedCount = $this->query->exec($qb->getSQL())->affected();
$db->unlockTables();
return $deletedCount;
}
public function init()
{
$this->db = MySqlDbManager::getDbObject($this->dbInstanceKey);
$this->query = MySqlDbManager::getQueryObject($this->dbInstanceKey);
}
protected function loadQuery()
{
$query = MySqlDbManager::getQueryObject();
$this->register($query);
}
private static function getCallerDbInstanceKey()
{
$backtrace = debug_backtrace();
if (!empty($backtrace[2]['object']) and method_exists($backtrace[2]['object'], 'getDbInstanceKey')) {
return $backtrace[2]['object']->getDbInstanceKey();
} else {
return MySqlDbManager::getDefaultInstanceKey();
}
}
public static function deleteHostsLanguage(Host $host, Language $language)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_HOST_LANGUAGE'))->where($qb->expr()->equal(new Field('host_id'), $host->id))->andWhere($qb->expr()->equal(new Field('lang_id'), $language->id));
$sql->exec($qb->getSQL());
}
/**
* Get all possible pairs of Host Language
*
* @return array 2D array key is host_language_id with "host" and "language" keys with values as corresponding objects
*/
public static function getAllPairs($cacheMinutes = null)
{
$pairs = array();
$sql = MySqlDbManager::getQueryObject();
$sql->exec("SELECT *, hl.id host_lang_id FROM " . Tbl::get('TBL_HOST_LANGUAGE') . " hl\n\t\t\t\t\tLEFT JOIN `" . Tbl::get("TBL_LANGUAGES", "Language") . "` l ON hl.lang_id=l.id\n\t\t\t\t\tLEFT JOIN `" . Tbl::get("TBL_HOSTS", "Host") . "` h ON hl.host_id=h.id", $cacheMinutes);
while (($row = $sql->fetchRecord()) != false) {
$host = new Host();
$language = new Language();
$row["id"] = $row["lang_id"];
Language::setData($row, $language);
$row["id"] = $row["host_id"];
Host::setData($row, $host);
$pairs[$row["host_lang_id"]] = array("host" => $host, "language" => $language);
}
return $pairs;
}
public static function logRequest($dbInstanceKey = null)
{
$sql = MySqlDbManager::getQueryObject($dbInstanceKey);
$sql->exec("INSERT DELAYED INTO `" . Tbl::get("TBL_REQUEST_LOG") . "` \n\t\t\t\t\t\t(`session_id`, `get`, `post`, `server`, `cookies`, `session`, `response`)\n\t\t\t\t\t\tVALUES\t(\n\t\t\t\t\t\t\t\t\t'" . session_id() . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_GET)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_POST)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_SERVER)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_COOKIE)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(serialize($_SESSION)) . "',\n\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string(ob_get_contents()) . "'\n\t\t\t\t\t\t\t\t)");
}
/**
* Delete current host and Lang id alias value for config DB
* @param ConfigDB $configDB
* @param unknown_type $aliasHostLangId
* @throws InvalidArgumentException
*/
public static function deleteDBConfigAlias(ConfigDB $configDB, $aliasHostLangId)
{
if (empty($configDB)) {
throw new InvalidArgumentException("ConfigDB object is empty!");
}
if (!is_numeric($configDB->id)) {
throw new InvalidArgumentException("ConfigDB object's id is not numeric!");
}
if (!is_numeric($aliasHostLangId)) {
throw new InvalidArgumentException("Alias Host Language id is not numeric!");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get("TBL_CONFIGS"))->where($qb->expr()->equal(new Field('alias_of'), $configDB->id))->andWhere($qb->expr()->equal(new Field('host_lang_id'), $aliasHostLangId));
$sql = MySqlDbManager::getQueryObject();
$sql->exec($qb->getSQL());
}
protected function loadQuery()
{
MySqlDbManager::setQueryClassName("MySqlQueryMemcache");
$query = new MySqlQueryMemcache(Reg::get(ConfigManager::getConfig("Db", "Db")->Objects->Db));
$this->register($query);
}
/**
* Check if record exists
*
* @param array $pageInfo
* @param Language $lang
* @param Host $host
* @param string $module
* @param string $page
* @return bool
*/
private static function exists($lang_id = null, $host_id = null, $module = null, $page = null)
{
$lang_where = "lang_id " . ($lang_id === null ? "IS NULL " : "=" . $lang_id);
$host_where = "host_id " . ($host_id === null ? "IS NULL " : "=" . $host_id);
$module_where = "module " . ($module === null ? "IS NULL " : "='" . $module . "'");
$page_where = "page " . ($page === null ? "IS NULL " : "='" . $page . "'");
$sql = MySqlDbManager::getQueryObject();
$query = "SELECT id FROM `" . Tbl::get('TBL_PAGE_INFO', 'PageInfo') . "` \n\t\tWHERE " . $lang_where . "\n\t\tAND " . $host_where . "\n\t\tAND " . $module_where . "\n\t\tAND " . $page_where;
$sql->exec($query);
if ($sql->countRecords()) {
return $sql->fetchField("id");
}
return false;
}
/**
* Get given set of Configs from DB
* Location is optional.
*
* @param array $location
* @throws InvalidArgumentException
*/
public static function getDBConfig($location = null)
{
if ($location !== null and (empty($location) or !is_array($location))) {
throw new InvalidArgumentException("Location of new config should be non empty array");
}
$sql = MySqlDbManager::getQueryObject();
$additionalSQL = "";
if ($location !== null) {
$additionalSQL = " WHERE `location` LIKE '" . implode(":", $location) . "%'";
}
$sql->exec("SELECT * FROM `" . Tbl::get("TBL_CONFIGS") . "`" . $additionalSQL);
return static::parseDBRowsToConfig($sql->fetchRecords());
}
protected function openConversation($userId1, $userId2, $systemMessage = false)
{
if (empty($userId1) or !is_numeric($userId1)) {
throw new InvalidIntegerArgumentException("\$userId1 have to be non zero integer.");
}
if (empty($userId2) or !is_numeric($userId2)) {
throw new InvalidIntegerArgumentException("\$userId2 have to be non zero integer.");
}
$db = MySqlDbManager::getDbObject();
$db->startTransaction(true);
$newUUID = $this->getMaxUUID() + 1;
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_CONVERSATIONS'))->values(array('uuid' => $newUUID, 'user_id' => $userId1, 'interlocutor_id' => $userId2, 'system' => $systemMessage ? '1' : '0'));
$this->query->exec($qb->getSQL());
$qb->insert(Tbl::get('TBL_CONVERSATIONS'))->values(array('uuid' => $newUUID, 'user_id' => $userId2, 'interlocutor_id' => $userId1, 'system' => $systemMessage ? '1' : '0'));
$this->query->exec($qb->getSQL());
if (!$db->commit()) {
$db->rollBack();
return false;
}
return $newUUID;
}
/**
* Get all hosts
*@return array Set of Host objects
*/
public static function getAllHosts(MysqlPager $pager = null, $cacheMinutes = null)
{
$hosts = array();
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS', 'Host'));
if ($pager !== null) {
$sql = $pager->executePagedSQL($qb->getSQL(), $cacheMinutes);
} else {
$sql->exec($qb->getSQL(), $cacheMinutes);
}
while (($host_data = $sql->fetchRecord()) != false) {
$h = new Host();
Host::setData($host_data, $h);
$hosts[] = $h;
}
return $hosts;
}
/**
* Constructor. It defines MysqlQuery object
* to be able to use Mysql queries.
* @param integer $resultsPerPage
* @param string $id
*/
public function __construct($resultsPerPage, $id = null)
{
parent::__construct($resultsPerPage, $id);
$this->query = MySqlDbManager::getQueryObject();
}
/**
* Check if record exists
*
* @param array $pageInfo
* @param Language $lang
* @param Host $host
* @param string $module
* @param string $page
* @return bool
*/
private static function exists($lang_id = null, $host_id = null, $module = null, $page = null, $cacheMinutes = null)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->select(new Field('id'))->from(Tbl::get('TBL_PAGE_INFO', 'PageInfo'));
if ($lang_id === null) {
$qb->andWhere($qb->expr()->isNull(new Field('lang_id')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('lang_id'), $lang_id));
}
if ($host_id === null) {
$qb->andWhere($qb->expr()->isNull(new Field('host_id')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('host_id'), $host_id));
}
if ($module === null) {
$qb->andWhere($qb->expr()->isNull(new Field('module')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('module'), $module));
}
if ($page === null) {
$qb->andWhere($qb->expr()->isNull(new Field('page')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('page'), $page));
}
$sql->exec($qb->getSQL(), $cacheMinutes);
if ($sql->countRecords()) {
return $sql->fetchField("id");
}
return false;
}
