public function getLastId()
{
$qb = new QueryBuilder();
$qb->select($qb->expr()->max(new Field('id'), 'lastId'))->from(Tbl::get('TBL_CHAT_MESSAGES'));
$lastId = $this->query->exec($qb->getSQL())->fetchField('lastId');
return empty($lastId) ? 0 : $lastId;
}
/**
* Does login operation
* @param string $username
* @param string $password
* @param bool $writeCookie
* @param bool $isPasswordEncrypted
*
* @throws RuntimeException (Codes: 1 - Incorrect login/password combination, 2 - Account is disabled)
*/
public function doLogin($username, $password, $writeCookie = false, $isPasswordEncrypted = false)
{
if ($this->um->checkCredentials($username, $password, $isPasswordEncrypted)) {
$this->usr = $this->um->getObjectByLogin($username);
$this->authorize($this->usr);
$this->saveUserId($this->usr->getId());
if ($writeCookie) {
$secs = getdate();
$exp_time = $secs[0] + 60 * 60 * 24 * $this->config->rememberDaysCount;
$cookie_value = $this->usr->getId() . ":" . hash('sha256', $username . ":" . md5($password));
setcookie($this->config->loginCookieName, $cookie_value, $exp_time, '/');
}
if (Reg::get('packageMgr')->isPluginLoaded("Security", "RequestLimiter") and $this->config->bruteForceProtectionEnabled) {
$this->query->exec("DELETE FROM `" . Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG') . "` WHERE `ip`='" . $_SERVER['REMOTE_ADDR'] . "'");
}
} else {
if (Reg::get('packageMgr')->isPluginLoaded("Security", "RequestLimiter") and $this->config->bruteForceProtectionEnabled) {
$this->query->exec("SELECT `count` \n\t\t\t\t\t\t\t\t\t\t\tFROM `" . Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG') . "` \n\t\t\t\t\t\t\t\t\t\t\tWHERE `ip`='" . $_SERVER['REMOTE_ADDR'] . "'");
$failedAuthCount = $this->query->fetchField('count');
$newFailedAuthCount = $failedAuthCount + 1;
if ($newFailedAuthCount >= $this->config->failedAuthLimit) {
Reg::get(ConfigManager::getConfig("Security", "RequestLimiter")->Objects->RequestLimiter)->blockIP();
$this->query->exec("DELETE FROM `" . Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG') . "` WHERE `ip`='" . $_SERVER['REMOTE_ADDR'] . "'");
throw new RequestLimiterTooManyAuthTriesException("Too many unsucessful authorization tries.");
}
$this->query->exec("INSERT INTO `" . Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG') . "` (`ip`) \n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $_SERVER['REMOTE_ADDR'] . "')\n\t\t\t\t\t\t\t\t\t\tON DUPLICATE KEY UPDATE `count` = `count` + 1");
}
throw new RuntimeException("Incorrect login/password combination", static::EXCEPTION_INCORRECT_LOGIN_PASSWORD);
}
}
/**
* Check validity of username, password and other auth factors
*
* @param string $username
* @param string $password
* @param array $additionalCredentials
* @param boolean $writeCookie
* @throws UserAuthFailedException
* @return User
*/
public function checkCredentials($username, $password, $additionalCredentials = array(), $writeCookie = false)
{
$qb = new QueryBuilder();
$qb->select(new Field('id'), new Field('password'), new Field('salt'))->from(Tbl::get('TBL_USERS', 'UserManager'))->where($qb->expr()->equal(new Field('login'), $username));
$this->query->exec($qb->getSQL());
if ($this->query->countRecords() == 1) {
$userData = $this->query->fetchRecord();
$hashToCheck = static::getUserPasswordHash($password, $userData['salt']);
if ($userData['password'] === $hashToCheck) {
$usr = $this->doLogin($userData['id'], $additionalCredentials, $writeCookie);
try {
$hookParams = array("user" => $usr, "additionalCredentials" => $additionalCredentials);
HookManager::callHook("UserAuthSuccess", $hookParams);
} catch (UserAuthFailedException $e) {
$this->doLogout();
throw $e;
}
return $usr;
}
}
// Failed login nothing returned from above code
$hookParams = array("username" => $username, "password" => $password, "additionalCredentials" => $additionalCredentials);
HookManager::callHook("UserAuthFail", $hookParams);
throw new UserAuthFailedException("Incorrect login/password combination");
}
/**
* Unblock blocked IP
* @param string $ip
*/
public function unblockIP($ip = null)
{
if ($ip === null) {
$ip = $_SERVER['REMOTE_ADDR'];
}
$this->query->exec("DELETE FROM `" . Tbl::get('TBL_SECURITY_FLOODER_IPS') . "` WHERE `ip` = '{$ip}'");
}
protected static function queryString($lang_id = null, $host_id = null, $module = null, $page = null, $cacheMinutes = null)
{
$qb = new QueryBuilder();
$qb->select(new Field('title'), new Field('meta_keywords'), new Field('meta_description'))->from(Tbl::get('TBL_PAGE_INFO'));
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));
}
return $qb->getSQL();
}
public static function logCustom($name, $value)
{
$remoteIP = "";
if (isset($_SERVER['REMOTE_ADDR'])) {
$remoteIP = $_SERVER['REMOTE_ADDR'];
}
Reg::get('sql')->exec("INSERT DELAYED INTO `" . Tbl::get("TBL_MIXED_LOG") . "` \n\t\t\t\t\t\t\t\t\t\t(`session_id`,`name`,`value`,`ip`)\n\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . session_id() . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string($name) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . mysql_real_escape_string($value) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'{$remoteIP}'\n\t\t\t\t\t\t\t\t\t\t\t\t)");
}
public function fillUsersGps($userId, $leafId)
{
$this->query->exec("delete from `" . Tbl::get('TBL_USERS_GPS') . "` where `user_id`='{$userId}'");
$gpsTree = $this->getNodeTree($leafId);
foreach ($gpsTree as $treeNode) {
$this->query->exec("INSERT INTO `" . Tbl::get('TBL_USERS_GPS') . "` (`user_id`,`node_id`) VALUES('{$userId}','{$treeNode["node_id"]}')");
}
}
/**
* Check if given country code is valid
*
* @param string $countryCode
* @param int $cacheMinutes
*/
public function isValidCountryCode($countryCode = null, $cacheMinutes = null)
{
$this->query->exec("SELECT count(*) as `count` FROM " . Tbl::get('TBL_LOCATIONS') . "\n\t\t\t\t\t\t\t\tWHERE `country`='{$countryCode}'", $cacheMinutes);
$count = $this->query->fetchField('count');
if ($count > 0) {
return true;
}
return false;
}
protected static function queryString($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 . "'");
$query = "SELECT `title`,\t`meta_keywords`, `meta_description` FROM `" . Tbl::get('TBL_PAGE_INFO') . "` \n\t\t\t\t\tWHERE " . $lang_where . "\n\t\t\t\t\tAND " . $host_where . "\n\t\t\t\t\tAND " . $module_where . "\n\t\t\t\t\tAND " . $page_where;
return $query;
}
public function __construct($headersOnly = true)
{
parent::__construct();
if ($headersOnly) {
$this->qb->select(new Field("*"));
} else {
$this->qb->select(array(new Field('id', 'main'), new Field('subject', 'main'), new Field('date', 'main'), new Field('sender', 'extra'), new Field('read', 'extra'), new Field('trashed', 'extra'), new Field('deleted', 'extra')));
}
$this->qb->from(Tbl::get('TBL_MESSAGES', 'MessageManagement'), "main")->leftJoin(Tbl::get('TBL_EXTRA', 'MessageManagement'), "extra", $this->qb->expr()->equal(new Field('id', 'main'), new Field('message_id', 'extra')));
}
public static function logCustom($name, $value)
{
$remoteIP = "";
if (isset($_SERVER['REMOTE_ADDR'])) {
$remoteIP = $_SERVER['REMOTE_ADDR'];
}
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_MIXED_LOG'))->values(array("session_id" => session_id(), "name" => $name, "value" => $value, "ip" => $remoteIP));
Reg::get('sql')->exec($qb->getSQL());
}
/**
* Check if given country code is valid
*
* @param string $countryCode
* @param int $cacheMinutes
*/
public function isValidCountryCode($countryCode = null, $cacheMinutes = null)
{
$qb = new QueryBuilder();
$qb->select($qb->expr()->count("*", "count"))->from(Tbl::get('TBL_LOCATIONS'))->where($qb->expr(new Field('country'), $countryCode));
$this->query->exec($qb->getSQL(), $cacheMinutes);
$count = $this->query->fetchField('count');
if ($count > 0) {
return true;
}
return false;
}
public function deleteAllAliasesForTextValue(TextValue $textValue)
{
if (empty($textValue->id)) {
throw new InvalidArgumentException("Text Value ID have to be specified");
}
if (!is_numeric($textValue->id)) {
throw new InvalidArgumentException("Text Value ID have to be integer");
}
$this->query->exec("DELETE FROM `" . Tbl::get('TBL_TEXTS_ALIASES') . "` WHERE `value_id`='{$textValue->id}'");
return $this->query->affected();
}
public function deleteGroup(TextsGroup $group)
{
if (empty($group->id)) {
throw new InvalidArgumentException("Group ID have to be specified");
}
if (!is_numeric($group->id)) {
throw new InvalidArgumentException("Group ID have to be integer");
}
$this->query->exec("DELETE FROM `" . Tbl::get('TBL_TEXTS_GROUPS') . "` WHERE `id`='{$group->id}'");
return $this->query->affected();
}
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;
}
/**
* 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 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();
}
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)");
}
public function fillUsersGps($userId, $leafId)
{
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_USERS_GPS'))->where($qb->expr()->equal(new Field('user_id'), $userId));
$this->query->exec($qb->getSQL());
$gpsTree = $this->getNodeTree($leafId);
foreach ($gpsTree as $treeNode) {
$qb = new QueryBuilder();
$qb->insert(Tbl::get('TBL_USERS_GPS'))->values(array('user_id' => $userId, 'node_id' => $treeNode["node_id"]));
$this->query->exec($qb->getSQL());
}
}
public function deleteGroup(TextsGroup $group)
{
if (empty($group->id)) {
throw new InvalidArgumentException("Group ID have to be specified");
}
if (!is_numeric($group->id)) {
throw new InvalidArgumentException("Group ID have to be integer");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_TEXTS_GROUPS'))->where($qb->expr()->equal(new Field("id"), $group->id));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
public function deleteAllAliasesForTextValue(TextValue $textValue)
{
if (empty($textValue->id)) {
throw new InvalidArgumentException("Text Value ID have to be specified");
}
if (!is_numeric($textValue->id)) {
throw new InvalidArgumentException("Text Value ID have to be integer");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_TEXTS_ALIASES'))->where($qb->expr()->equal(new Field("value_id"), $textValue->id));
$this->query->exec($qb->getSQL());
return $this->query->affected();
}
/**
* Set user answers by their ids
*
* @param array $answers an array containing user's answers
*/
public function setAnswersByIds($answers)
{
if (is_array($answers)) {
$this->query->exec("DELETE FROM `" . Tbl::get('TBL_PROFILE_SAVE') . "` WHERE `user_id`='{$this->userId}'");
foreach ($answers as $answer) {
if (is_numeric($answer)) {
$this->query->exec("INSERT INTO `" . Tbl::get('TBL_PROFILE_SAVE') . "` (`user_id`,`profile_id`) VALUES('{$this->userId}','{$answer}')");
}
}
$this->initUserAnswers();
} else {
throw new UnexpectedValueException("\$answers have to array");
}
}
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'));
}
}
}
/**
* Is remote IP blocked by country
*
* @return boolean
*/
private function isBlockedByCountry($cacheMinutes = null)
{
$myLocation = Reg::get(ConfigManager::getConfig('GeoIP', 'GeoIP')->Objects->GeoIP)->getLocation();
if (empty($myLocation)) {
return false;
}
$countryCode = $myLocation->country;
if (empty($countryCode)) {
return false;
}
$this->query->exec("SELECT count(*) as `count` \n\t\t\t\t\t\t\t\tFROM `" . Tbl::get('TBL_SECURITY_BLACKLISTED_COUNTRIES') . "` \n\t\t\t\t\t\t\t\tWHERE `country` = '{$countryCode}'", $cacheMinutes);
$count = $this->query->fetchField('count');
if ($count > 0) {
return true;
}
return false;
}
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 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;
}
/**
* Is remote IP blocked by country
*
* @return boolean
*/
private function isBlockedByCountry($cacheMinutes = null)
{
$myLocation = Reg::get(ConfigManager::getConfig('GeoIP', 'GeoIP')->Objects->GeoIP)->getLocation();
if (empty($myLocation)) {
return false;
}
$countryCode = $myLocation->country;
if (empty($countryCode)) {
return false;
}
$qb = new QueryBuilder();
$qb->select($qb->expr()->count('*', 'count'))->from(Tbl::get('TBL_SECURITY_BLACKLISTED_COUNTRIES'))->where($qb->expr()->equal(new Field('country'), $countryCode));
$this->query->exec($qb->getSQL(), $cacheMinutes);
$count = $this->query->fetchField('count');
if ($count > 0) {
return true;
}
return false;
}
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());
}
}
}
public function addEvent($name, $selfUserId, $userId = null, $data = array())
{
if (empty($name)) {
throw new InvalidArgumentException("\$name have to be non empty string");
}
if (empty($selfUserId) or !is_numeric($selfUserId)) {
throw new InvalidArgumentException("\$selfUserId have to be non zero integer");
}
if ($userId !== null and (empty($userId) or !is_numeric($userId))) {
throw new InvalidArgumentException("\$userId have to be non zero integer");
}
if (!is_array($data)) {
throw new InvalidArgumentException("\$data have to be array");
}
$qb = new QueryBuilder();
$values = array('name' => $name, 'self_user_id' => $selfUserId, 'data' => serialize($data));
if ($userId !== null) {
$values['user_id'] = $userId;
}
$qb->insert(Tbl::get('TBL_COMET_EVENTS'))->values($values);
return $this->query->exec($qb->getSQL())->affected();
}
