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)); }