function test_6_or()
{
$where = new WhereClause('or');
$where->add('username=%s', 'Bart');
$where->add('username=%s', 'Abe');
$result = DB::query("SELECT * FROM accounts WHERE %l", $where);
$this->assert(count($result) === 2);
}
/**
* Used to vaidate a user's credentials. (uname, pass)
* @param array $creds the uname and password passed in as an array.
* @return bool
*/
function validateCredentials($creds)
{
$where_clause = new WhereClause('name', $creds['uname']);
$where_clause->w_and('password', $creds['password']);
$this->where_clause($where_clause);
$user_info = $this->getOneUsingWhere();
if ($user_info != NULL) {
if (is_object($user_info)) {
if ($user_info->uid > 0) {
$_SESSION['authed_user'] = $user_info->uid;
$this->authed_user = $user_info->uid;
return TRUE;
}
}
}
return FALSE;
}
/**
* @test
*/
public function shouldAcceptSingleNullValueAsParam()
{
// when
$result = WhereClause::create('name = ?', null);
// then
$this->assertEquals(array(null), $result->getParameters());
$this->assertEquals('name = ?', $result->toSql());
}
public function getFacilityById($registryId)
{
try {
$selectStmt = new SelectStmt();
$selectStmt->setTableName(self::TABLE_NAME);
$whereClauses = array();
$wc = new WhereClause();
$wc->setColumn("registry_id");
$wc->setOperator($wc::OP_EQUAL);
$wc->setValue($registryId);
array_push($whereClauses, $wc);
$selectStmt->setWhereClauses($whereClauses);
$result = $this->dbService->executeQuery($selectStmt);
$this->setResponse($result->code, $result->msg, $result->pages, $result->rowCount, $result->data);
} catch (Exception $e) {
$this->setResponse(static::SYSTEM_FAILURE_CODE, "System error occurred, unable retrieve facility with ID: " . $registryId, $result->pages, $result->rowCount, array());
} finally {
$this->outputResponse();
}
}
function setParams($params)
{
foreach ($params as $key => $value) {
if ($key == "pageSize") {
$this->pageSize = $value;
} elseif ($key == "page") {
$this->page = $value;
} elseif ($key == "q") {
//$val = str_replace("|","','",$value);
$val = "'" . strtoupper($value) . "'";
$this->where = " WHERE UPPER(registry_id) REGEXP {$val}\n or UPPER(primary_name) REGEXP {$val} \n or UPPER(city_name) REGEXP {$val} \n or UPPER(county_name) REGEXP {$val} \n or UPPER(state_code) REGEXP {$val} \n or UPPER(state_name) REGEXP {$val} \n or UPPER(country_name) REGEXP {$val} \n or UPPER(postal_code) REGEXP {$val} \n or UPPER(site_type_name) REGEXP {$val} ";
} else {
$wc = new WhereClause();
$wc->setColumn($this->camelCaseToUnderScore($key));
$wc->setValue("%{$value}%");
array_push($this->whereClauses, $wc);
}
}
if (sizeof($this->whereClauses) > 0) {
$this->where = $this->createWhere();
}
}
include $_SESSION['settings']['cpassman_dir'] . '/includes/settings.php';
header("Content-type: text/html; charset==utf-8");
//Connect to DB
require_once $_SESSION['settings']['cpassman_dir'] . '/includes/libraries/Database/Meekrodb/db.class.php';
DB::$host = $server;
DB::$user = $user;
DB::$password = $pass;
DB::$dbName = $database;
DB::$port = $port;
DB::$error_handler = 'db_error_handler';
$link = mysqli_connect($server, $user, $pass, $database, $port);
//Columns name
$aColumns = array('id', 'label', 'description', 'tags', 'id_tree', 'folder', 'login');
//init SQL variables
$sOrder = $sLimit = "";
$where = new WhereClause('and');
$where->add('id_tree IN %ls', $_SESSION['groupes_visibles']);
//limit search to the visible folders
//get list of personal folders
$array_pf = array();
$list_pf = "";
$rows = DB::query("SELECT id FROM " . $pre . "nested_tree WHERE personal_folder=%i AND NOT title = %s", 1, $_SESSION['user_id']);
foreach ($rows as $reccord) {
if (!in_array($reccord['id'], $array_pf)) {
//build an array of personal folders ids
array_push($array_pf, $reccord['id']);
//build also a string with those ids
if (empty($list_pf)) {
$list_pf = $reccord['id'];
} else {
$list_pf .= ',' . $reccord['id'];
public static function timeInInterval($field, $start, $end)
{
$wc = new WhereClause('and');
if (!empty($start)) {
//Assume goes off to neg infinity
$wc->add('%b > %l', $field, self::timeRelativeToSQL($start));
}
if (!empty($end)) {
//Assume goes off to pos infinity
$wc->add('%b < %l', $field, self::timeRelativeToSQL($end));
}
return $wc;
}
private function getWhereClause($where, $combinedBy)
{
$whereClause = new WhereClause($where, $combinedBy);
return [$whereClause->getClause(), $whereClause->getParams()];
}
}
$logs .= '<tr><td>' . date($_SESSION['settings']['date_format'] . " " . $_SESSION['settings']['time_format'], $reccord['date']) . '</td><td align=\\"left\\">' . $label[0] . '</td><td align=\\"center\\">' . $reccord['login'] . '</td></tr>';
}
echo '[{"tbody_logs": "' . $logs . '" , "log_pages" : "' . $pages . '"}]';
break;
/**
* CASE admin want to see COPIES logs
*/
/**
* CASE admin want to see COPIES logs
*/
case "admin_logs":
$logs = $sqlFilter = "";
$nbPages = 1;
$pages = '<table style=\'border-top:1px solid #969696;\'><tr><td>' . $LANG['pages'] . ' : </td>';
$where = new WhereClause('and');
$where->add('l.type=%s', "admin_action");
if (isset($_POST['filter']) && !empty($_POST['filter'])) {
$where->add('i.label=%ss', $_POST['filter']);
}
if (isset($_POST['filter_user']) && !empty($_POST['filter_user'])) {
$where->add('i.id_user=%ss', $_POST['filter_user']);
}
//get number of pages
DB::query("SELECT * FROM " . prefix_table("log_system") . " as l\n INNER JOIN " . prefix_table("users") . " as u ON (l.qui=u.id)\n WHERE %l", $where);
if ($data[0] != 0) {
$nbPages = ceil($data[0] / $nbElements);
for ($i = 1; $i <= $nbPages; $i++) {
$pages .= '<td onclick=\'displayLogs(\\"copy_logs\\", ' . $i . ', \'\')\'><span style=\'cursor:pointer;' . ($_POST['page'] == $i ? 'font-weight:bold;font-size:18px;\'>' . $i : '\'>' . $i) . '</span></td>';
}
}
/**
* check to see if this user has a prefs entry, and optionally create
* one if they don't
*
*@access public
*@param int $uid the uid to look for
*@param bool $create automagically create prefs entry? (default:false)
*@param string $auth_mod the auth mod they should be found under
*@param array initial_data the initial stuff to populate prefs with.
*@return bool
*/
public function checkUID($uid, $create = false, $auth_mod = NULL, $initial_data = NULL)
{
if ($auth_mod == NULL) {
$auth_mod = self::$config->auth_class;
}
$data = array('fname' => '', 'lname' => '', 'perms' => 0, 'auth_mod' => $auth_mod, 'uid' => $uid);
// for now, only set fname and lname, perms should remain 0 until set by an admin.
if (is_array($initial_data)) {
$data['fname'] = $initial_data['fname'];
$data['lname'] = $initial_data['lname'];
}
API::DEBUG("[Prefs::checkUID()] " . print_r($data, true), 1);
$where_tmp = new WhereClause('uid', $uid);
$where_tmp->w_and('auth_mod', $auth_mod);
$this->where_clause($where_tmp);
$results = $this->getUsingWhere();
if (count($results) > 1) {
API::DEBUG("[Prefs::checkUID()] Multiple results returned for '{$uid}' and '{$auth_mod}'.");
API::DEBUG("[Prefs::checkUID()] This is bad because I am using the first one.");
}
if (count($results) < 1) {
if ($create === TRUE) {
// create the entry.
$this->set_data($data, true);
return TRUE;
}
return FALSE;
}
return TRUE;
}
public static function create(array $predicated)
{
$clause = new static();
$clause->setPredicates(WhereClause::buildPredicates($predicated));
return $clause;
}
// *** db_Functions.php ***
// THis is the The Main Page SQL Only. see post.php, etc for other individual page routines
// Using meekrodb for connecting to mySQL server for better security.
// You can specify string, integer, and decimal parameters
// by using %s, %i, and %d placeholders in the query,
// and attaching the parameters as shown below.
// The library will run all needed safety checks,
// such as escaping strings and making sure integers are really integers
if (empty($db_Where)) {
$db_Where = '';
}
if ($db_Page == 'db_List') {
if ($db_Where) {
$arrWhere = explode(',', $db_Where);
$where = new WhereClause('and');
// create a WHERE statement of pieces joined by ANDs
$where->add($arrWhere[0], $arrWhere[1]);
//$results = DB::query("SELECT * FROM $db_Table WHERE %l ", $where->text());
$results = DB::query("SELECT * FROM {$db_Table} WHERE {$arrWhere['0']}", $arrWhere[1]);
} else {
if (!empty($db_OrderBy)) {
$results = DB::query("SELECT * FROM {$db_Table} ORDER BY {$db_OrderBy}");
} else {
$results = DB::query("SELECT * FROM {$db_Table}");
}
}
}
// SETTING DEFAULTS IF NOT SET IN CONFIG
//Lets get the FieldNames, FieldCount and RcordCount
$arrFieldNames = DB::columnList($db_Table);
/**
* @param bool $not
*
* @return WhereCondition
*/
public function addNullCondition($not)
{
$this->whereClause->addNullCondition($this->column, $this->separator, $not);
return $this->condition;
}
/**
* identifyUserRights()
*
* @return
*/
function identifyUserRights($groupesVisiblesUser, $groupesInterditsUser, $isAdmin, $idFonctions, $refresh)
{
global $server, $user, $pass, $database, $pre, $port, $encoding;
//load ClassLoader
require_once $_SESSION['settings']['cpassman_dir'] . '/sources/SplClassLoader.php';
//Connect to DB
require_once $_SESSION['settings']['cpassman_dir'] . '/includes/libraries/Database/Meekrodb/db.class.php';
DB::$host = $server;
DB::$user = $user;
DB::$password = $pass;
DB::$dbName = $database;
DB::$port = $port;
DB::$encoding = $encoding;
DB::$error_handler = 'db_error_handler';
$link = mysqli_connect($server, $user, $pass, $database, $port);
$link->set_charset($encoding);
//Build tree
$tree = new SplClassLoader('Tree\\NestedTree', $_SESSION['settings']['cpassman_dir'] . '/includes/libraries');
$tree->register();
$tree = new Tree\NestedTree\NestedTree(prefix_table("nested_tree"), 'id', 'parent_id', 'title');
// Check if user is ADMINISTRATOR
if ($isAdmin == 1) {
$groupesVisibles = array();
$_SESSION['groupes_visibles'] = array();
$_SESSION['groupes_interdits'] = array();
$_SESSION['personal_visible_groups'] = array();
$_SESSION['list_restricted_folders_for_items'] = array();
$_SESSION['groupes_visibles_list'] = "";
$rows = DB::query("SELECT id FROM " . prefix_table("nested_tree") . " WHERE personal_folder = %i", 0);
foreach ($rows as $record) {
array_push($groupesVisibles, $record['id']);
}
$_SESSION['groupes_visibles'] = $groupesVisibles;
$_SESSION['all_non_personal_folders'] = $groupesVisibles;
// Exclude all PF
$_SESSION['forbiden_pfs'] = array();
//$sql = "SELECT id FROM ".prefix_table("nested_tree")." WHERE personal_folder = 1";
$where = new WhereClause('and');
// create a WHERE statement of pieces joined by ANDs
$where->add('personal_folder=%i', 1);
if (isset($_SESSION['settings']['enable_pf_feature']) && $_SESSION['settings']['enable_pf_feature'] == 1) {
//$sql .= " AND title != '".$_SESSION['user_id']."'";
$where->add('title=%s', $_SESSION['user_id']);
$where->negateLast();
}
// Get ID of personal folder
$pf = DB::queryfirstrow("SELECT id FROM " . prefix_table("nested_tree") . " WHERE title = %s", $_SESSION['user_id']);
if (!empty($pf['id'])) {
if (!in_array($pf['id'], $_SESSION['groupes_visibles'])) {
array_push($_SESSION['groupes_visibles'], $pf['id']);
array_push($_SESSION['personal_visible_groups'], $pf['id']);
// get all descendants
$tree = new Tree\NestedTree\NestedTree(prefix_table("nested_tree"), 'id', 'parent_id', 'title', 'personal_folder');
$tree->rebuild();
$tst = $tree->getDescendants($pf['id']);
foreach ($tst as $t) {
array_push($_SESSION['groupes_visibles'], $t->id);
array_push($_SESSION['personal_visible_groups'], $t->id);
}
}
}
$_SESSION['groupes_visibles_list'] = implode(',', $_SESSION['groupes_visibles']);
$_SESSION['is_admin'] = $isAdmin;
// Check if admin has created Folders and Roles
DB::query("SELECT * FROM " . prefix_table("nested_tree") . "");
$_SESSION['nb_folders'] = DB::count();
DB::query("SELECT * FROM " . prefix_table("roles_title"));
$_SESSION['nb_roles'] = DB::count();
} else {
// init
$_SESSION['groupes_visibles'] = array();
$_SESSION['groupes_interdits'] = array();
$_SESSION['personal_visible_groups'] = array();
$_SESSION['read_only_folders'] = array();
$groupesVisibles = array();
$groupesInterdits = array();
$groupesInterditsUser = explode(';', trimElement($groupesInterditsUser, ";"));
if (!empty($groupesInterditsUser) && count($groupesInterditsUser) > 0) {
$groupesInterdits = $groupesInterditsUser;
}
$_SESSION['is_admin'] = $isAdmin;
$fonctionsAssociees = explode(';', trimElement($idFonctions, ";"));
$newListeGpVisibles = array();
$listeGpInterdits = array();
$listAllowedFolders = $listForbidenFolders = $listFoldersLimited = $listFoldersEditableByRole = $listRestrictedFoldersForItems = $listReadOnlyFolders = $listNoAccessFolders = array();
// rechercher tous les groupes visibles en fonction des roles de l'utilisateur
foreach ($fonctionsAssociees as $roleId) {
if (!empty($roleId)) {
// Get allowed folders for each Role
$rows = DB::query("SELECT folder_id FROM " . prefix_table("roles_values") . " WHERE role_id=%i", $roleId);
if (DB::count() > 0) {
$tmp = DB::queryfirstrow("SELECT allow_pw_change FROM " . prefix_table("roles_title") . " WHERE id = %i", $roleId);
foreach ($rows as $record) {
if (isset($record['folder_id']) && !in_array($record['folder_id'], $listAllowedFolders)) {
array_push($listAllowedFolders, $record['folder_id']);
//echo $record['folder_id'].";";
}
// Check if this group is allowed to modify any pw in allowed folders
if ($tmp['allow_pw_change'] == 1 && !in_array($record['folder_id'], $listFoldersEditableByRole)) {
array_push($listFoldersEditableByRole, $record['folder_id']);
}
}
// Check for the users roles if some specific rights exist on items
$rows = DB::query("SELECT i.id_tree, r.item_id\n FROM " . prefix_table("items") . " as i\n INNER JOIN " . prefix_table("restriction_to_roles") . " as r ON (r.item_id=i.id)\n WHERE r.role_id=%i\n ORDER BY i.id_tree ASC", $roleId);
$x = 0;
foreach ($rows as $record) {
if (isset($record['id_tree'])) {
$listFoldersLimited[$record['id_tree']][$x] = $record['item_id'];
$x++;
}
}
}
}
}
// Does this user is allowed to see other items
$x = 0;
$rows = DB::query("SELECT id, id_tree FROM " . prefix_table("items") . "\n WHERE restricted_to=%ss AND inactif=%s", $_SESSION['user_id'], '0');
foreach ($rows as $record) {
$listRestrictedFoldersForItems[$record['id_tree']][$x] = $record['id'];
$x++;
// array_push($listRestrictedFoldersForItems, $record['id_tree']);
}
// => Build final lists
// Clean arrays
$allowedFoldersTmp = array();
$listAllowedFolders = array_unique($listAllowedFolders);
// Add user allowed folders
$allowedFoldersTmp = array_unique(array_merge($listAllowedFolders, explode(';', trimElement($groupesVisiblesUser, ";"))));
// Exclude from allowed folders all the specific user forbidden folders
$allowedFolders = array();
foreach ($allowedFoldersTmp as $id) {
if (!in_array($id, $groupesInterditsUser) && !empty($id)) {
array_push($allowedFolders, $id);
}
}
// Clean array
$listAllowedFolders = array_filter(array_unique($allowedFolders));
// Exclude all PF
$_SESSION['forbiden_pfs'] = array();
$where = new WhereClause('and');
$where->add('personal_folder=%i', 1);
if (isset($_SESSION['settings']['enable_pf_feature']) && $_SESSION['settings']['enable_pf_feature'] == 1 && isset($_SESSION['personal_folder']) && $_SESSION['personal_folder'] == 1) {
$where->add('title=%s', $_SESSION['user_id']);
$where->negateLast();
}
$pfs = DB::query("SELECT id FROM " . prefix_table("nested_tree") . " WHERE %l", $where);
foreach ($pfs as $pfId) {
array_push($_SESSION['forbiden_pfs'], $pfId['id']);
}
// Get ID of personal folder
if (isset($_SESSION['settings']['enable_pf_feature']) && $_SESSION['settings']['enable_pf_feature'] == 1 && isset($_SESSION['personal_folder']) && $_SESSION['personal_folder'] == 1) {
$pf = DB::queryfirstrow("SELECT id FROM " . prefix_table("nested_tree") . " WHERE title = %s", $_SESSION['user_id']);
if (!empty($pf['id'])) {
if (!in_array($pf['id'], $listAllowedFolders)) {
// get all descendants
$ids = $tree->getDescendants($pf['id'], true);
foreach ($ids as $id) {
array_push($listAllowedFolders, $id->id);
array_push($_SESSION['personal_visible_groups'], $id->id);
}
}
}
}
// get list of readonly folders
// rule - if one folder is set as W in one of the Role, then User has access as W
foreach ($listAllowedFolders as $folderId) {
if (!in_array($folderId, $listReadOnlyFolders) || isset($pf) && $folderId != $pf['id']) {
DB::query("SELECT *\n FROM " . prefix_table("roles_values") . "\n WHERE folder_id = %i AND role_id IN %li AND type = %s", $folderId, $fonctionsAssociees, "W");
if (DB::count() == 0) {
array_push($listReadOnlyFolders, $folderId);
}
}
}
$_SESSION['all_non_personal_folders'] = $listAllowedFolders;
$_SESSION['groupes_visibles'] = $listAllowedFolders;
$_SESSION['groupes_visibles_list'] = implode(',', $listAllowedFolders);
$_SESSION['read_only_folders'] = $listReadOnlyFolders;
$_SESSION['list_folders_limited'] = $listFoldersLimited;
$_SESSION['list_folders_editable_by_role'] = $listFoldersEditableByRole;
$_SESSION['list_restricted_folders_for_items'] = $listRestrictedFoldersForItems;
// Folders and Roles numbers
DB::queryfirstrow("SELECT id FROM " . prefix_table("nested_tree") . "");
$_SESSION['nb_folders'] = DB::count();
DB::queryfirstrow("SELECT id FROM " . prefix_table("roles_title"));
$_SESSION['nb_roles'] = DB::count();
}
// update user's timestamp
DB::update(prefix_table('users'), array('timestamp' => time()), "id=%i", $_SESSION['user_id']);
}
DB::query("SELECT label\n FROM " . prefix_table("items") . "\n WHERE id_tree = %i AND label = %s", $idFolder, $label);
} else {
// case complete database
//get list of personal folders
$arrayPf = array();
$listPf = "";
if (!empty($row['id'])) {
$rows = DB::query("SELECT id FROM " . prefix_table("nested_tree") . " WHERE personal_folder = %i", "1");
foreach ($rows as $record) {
if (!in_array($record['id'], $arrayPf)) {
array_push($arrayPf, $record['id']);
}
}
}
// build WHERE condition
$where = new WhereClause('and');
$where->add('id_tree = %i', $idFolder);
$where->add('label = %s', $label);
if (!empty($arrayPf)) {
$where->add("id_tree NOT IN (" . implode(',', $arrayPf) . ")");
}
DB::query("SELECT label\n FROM " . prefix_table("items") . "\n WHERE %l", $where);
}
// count results
if (DB::count() > 0) {
$duplicate = true;
}
// send data
echo '[{"duplicate" : "' . $duplicate . '" , "error" : ""}]';
}
break;
/**
* Updates fields in a table for rows that match the provided criteria
*
* $newFields can be a complete row or it can be a sparsely populated
* hashtable of values (where the keys are integers which are the column
* indexes to update)
*
* @param string $tablename The table to update
* @param array $newFields A hashtable (with integer keys) of fields to update
* @param WhereClause $whereClause The criteria or NULL to update all rows
*/
function updateSetWhere($tablename, $newFields, $whereClause)
{
$schema = $this->getSchema($tablename);
$lockfp = $this->getLock($tablename);
for ($i = 0; $i < count($this->tables[$tablename]); ++$i) {
if ($whereClause === NULL || $whereClause->testRow($this->tables[$tablename][$i], $schema)) {
foreach ($newFields as $k => $v) {
$this->tables[$tablename][$i][$k] = $v;
}
}
}
$this->writeTable($tablename);
$this->releaseLock($lockfp);
$this->loadTable($tablename);
}
public function __construct($field, $isNull)
{
parent::__construct($field, $isNull, Comparison::EQ());
}
private function getWhereAssignments($combinedBy)
{
$whereAssignments = new WhereClause($this->where, $combinedBy);
return [$whereAssignments->getClause(), $whereAssignments->getParams()];
}
function getUserPerms($uid, $auth_mod = NULL)
{
if ($auth_mod == NULL) {
$auth_mod = self::$config->auth_class;
}
$prefs = new Prefs($uid);
API::DEBUG("[Perms::getUserPerms()] uid = {$uid}, auth_mod = {$auth_mod}");
$tmp_where = new WhereClause('uid', $uid);
$tmp_where->w_and('auth_mod', $auth_mod);
$prefs->where_clause($tmp_where);
$perms = $prefs->getUsingWhere();
return $perms;
}
/** @test */
public function it_cannot_be_instantiated_with_invalid_keyword()
{
$where = new WhereClause([['id', 'foo', 15]]);
$this->assertEquals('', $where->getClause());
$this->assertEquals([], $where->getParams());
}
private function updateIs($is, $setstr)
{
if (empty($this->Questions) || count($this->Questions) == 0) {
return;
}
//$setstr is risky.
$where = new WhereClause('or');
foreach ($is as $i) {
$where->add("QID=%i", $this->Questions[$i][0]);
}
DB::query("UPDATE questions SET {$setstr} WHERE (%l) LIMIT %i", $where, count($this->Questions));
}
