public function fire() { $driver = config('database.default'); $db = new Db($driver, config("database.connections.{$driver}.database"), config("database.connections.{$driver}.username"), config("database.connections.{$driver}.password"), config("database.connections.{$driver}.host") ?: 'localhost'); $args = $this->input->getArguments(); $emailOrLogin = strtolower(trim($args['email_or_login'])); $authField = $this->input->getOption('login') ? 'login' : 'email'; $password = \Hash::make($args['password']); $table = "`{$args['schema']}`.`{$args['table']}`"; $exists = $db::processRecords($db->query(DbExpr::create("SELECT 1 FROM {$table} WHERE `{$authField}`=``{$emailOrLogin}``")), $db::FETCH_VALUE); if ($exists > 0) { $query = "UPDATE {$table} SET `password`=``{$password}``, `role`=``{$args['role']}`` WHERE `{$authField}`=``{$emailOrLogin}``"; } else { $query = "INSERT INTO {$table} (`{$authField}`, `password`, `role`) VALUES (``{$emailOrLogin}``,``{$password}``, ``{$args['role']}``)"; } try { $result = $db->exec(DbExpr::create($query)); if ($result > 0) { $this->line($exists ? 'Admin updated' : 'Admin created'); } else { $this->line('Fail. DB returned "0 rows updated"'); } } catch (\Exception $exc) { $this->line('Fail. DB Exception:'); $this->line($exc->getMessage()); $this->line($exc->getTraceAsString()); } }
public function fire() { $db = new Db(Db::PGSQL, env('DB_DATABASE'), env('DB_USERNAME'), env('DB_PASSWORD'), env('DB_HOST', 'localhost')); $args = $this->input->getArguments(); $email = strtolower(trim($args['email'])); $password = \Hash::make($args['password']); $table = "`{$args['schema']}`.`{$args['table']}`"; $exists = $db->processRecords($db->query(DbExpr::create("SELECT 1 FROM {$table} WHERE `email`=``{$email}``")), $db::FETCH_VALUE); if ($exists > 0) { $query = "UPDATE {$table} SET `password`=``{$password}``, `role`=``{$args['role']}`` WHERE `email`=``{$email}``"; } else { $query = "INSERT INTO {$table} (`email`, `password`, `role`) VALUES (``{$email}``,``{$password}``, ``{$args['role']}``)"; } try { $result = $db->exec(DbExpr::create($query)); if ($result > 0) { $this->line($exists ? 'Admin updated' : 'Admin created'); } else { $this->line('Fail. DB returned "0 rows updated"'); } } catch (\Exception $exc) { $this->line('Fail. DB Exception:'); $this->line($exc->getMessage()); $this->line($exc->getTraceAsString()); } }
/** * Vlidate access key and find user * @param string $accessKey * @return CmfDbObject|bool - false = failed to parse access key, validate data or load user */ public static function loadFromPasswordRecoveryAccessKey($accessKey) { try { $data = \Crypt::decrypt($accessKey); } catch (DecryptException $exc) { return false; } if (empty($data)) { return false; } $data = json_decode($data, true); if (empty($data) || !is_array($data) || empty($data['account_id']) || empty($data['expires_at']) || $data['expires_at'] < time()) { return false; } /** @var CmfDbObject|ResetsPasswordsViaAccessKey $user */ $user = static::create(); $conditions = [$user->_getPkFieldName() => $data['account_id']]; foreach ($user->getAdditionalFieldsForPasswordRecoveryAccessKey() as $fieldName) { if (empty($data[$fieldName])) { return false; } $fieldType = $user->_getField($fieldName)->getType(); switch ($fieldType) { case DbColumnConfig::TYPE_DATE: $conditions[$fieldName . '::date'] = DbExpr::create("``{$data[$fieldName]}``::date"); break; case DbColumnConfig::TYPE_TIME: $conditions[$fieldName . '::time'] = DbExpr::create("``{$data[$fieldName]}``::time"); break; case DbColumnConfig::TYPE_TIMESTAMP: $conditions[] = DbExpr::create("`{$fieldName}`::timestamp(0) = ``{$data[$fieldName]}``::timestamp(0)"); break; default: $conditions[$fieldName] = $data[$fieldName]; } } if (!$user->find($conditions)->exists()) { return false; } return $user; }
public static function getTimezonesList($asOptions = false) { if (self::$timeZonesList === null) { $ds = self::_getDataSource('default'); $query = $ds->replaceQuotes(DbExpr::create('SELECT * from `pg_timezone_names` ORDER BY `utc_offset` ASC')->get()); self::$timeZonesList = Db::processRecords($ds->query($query), Db::FETCH_ALL); } if ($asOptions) { if (self::$timeZonesOptions === null) { self::$timeZonesOptions = []; foreach (self::$timeZonesList as $tzInfo) { $offset = preg_replace('%:\\d\\d$%', '', $tzInfo['utc_offset']); $offsetPrefix = $offset[0] === '-' ? '' : '+'; self::$timeZonesOptions[$tzInfo['name']] = "({$offsetPrefix}{$offset}) {$tzInfo['name']}"; } } return self::$timeZonesOptions; } else { return self::$timeZonesList; } }
public function doLogin(Request $request) { $userLoginColumn = CmfConfig::getInstance()->user_login_column(); $this->validate($request->data(), [$userLoginColumn => 'required' . ($userLoginColumn === 'email' ? '|email' : ''), 'password' => 'required']); $credentials = [DbExpr::create("LOWER(`{$userLoginColumn}`) = LOWER(``" . trim($request->data($userLoginColumn)) . '``)'), 'password' => $request->data('password')]; if (!Auth::guard()->attempt($credentials)) { return cmfServiceJsonResponse(HttpCode::INVALID)->setMessage(CmfConfig::transCustom('.login_form.login_failed')); } else { return cmfServiceJsonResponse()->setRedirect($this->getIntendedUrl()); } }
private function getDataGridItems(Request $request) { $dataGridConfig = $this->getScaffoldConfig()->getDataGridConfig(); $dataGridFilterConfig = $this->getScaffoldConfig()->getDataGridFilterConfig(); $conditions = ['LIMIT' => $request->query('length', $dataGridConfig->getLimit()), 'OFFSET' => (int) $request->query('start', 0), 'ORDER' => []]; if ($dataGridConfig->hasContains()) { $conditions['CONTAIN'] = $dataGridConfig->getContains(); } $conditions = array_merge($dataGridConfig->getSpecialConditions(), $conditions); $searchInfo = $request->query('search'); if (!empty($searchInfo) && !empty($searchInfo['value'])) { $search = json_decode($searchInfo['value'], true); if (!empty($search) && is_array($search) && !empty($search['r'])) { $conditions = array_replace($dataGridFilterConfig->buildConditionsFromSearchRules($search), $conditions); } } $order = $request->query('order', [['column' => $dataGridConfig->getOrderBy(), 'dir' => $dataGridConfig->getOrderDirection()]]); $columns = $request->query('columns', array()); /** @var array $order */ foreach ($order as $config) { if (is_numeric($config['column']) && !empty($columns[$config['column']])) { $config['column'] = $columns[$config['column']]['name']; } if (!empty($config['column']) && !is_numeric($config['column'])) { if ($config['column'] instanceof DbExpr) { $conditions['ORDER'][] = DbExpr::create($config['column']->get() . ' ' . $config['dir']); } else { $conditions['ORDER'][$config['column']] = $config['dir']; } } } $result = $this->getModel()->selectWithCount(array_keys($dataGridConfig->getDbFields()), $conditions); if ($result['count'] > 0) { $result['records'] = $dataGridConfig->prepareRecords($result['records']); } return ['draw' => $request->query('draw'), 'recordsTotal' => $result['count'], 'recordsFiltered' => $result['count'], 'data' => $result['records']]; }
/** * @param string $schema * @param string $queryTpl * @param array $tables - empty value: just execute $queryTpl * @param null|string $testQueryTpl * @throws \PeskyORM\Exception\DbException */ public function executeQueryOnSchema($schema, $queryTpl, array $tables = [], $testQueryTpl = null) { $this->out('DB Schema: ' . $schema); $ds = $this->getConnection(); if (!empty($tables)) { foreach ($tables as $tableName) { $this->out('Update table: ' . $tableName); $query = StringUtils::insert($queryTpl, ['table' => $tableName, 'schema' => $schema]); if (!empty($testQueryTpl)) { $test = StringUtils::insert($testQueryTpl, ['table' => $tableName, 'schema' => $schema]); $stmnt = $ds->query(DbExpr::create($test)); if ($stmnt) { $exists = Db::processRecords($stmnt, DB::FETCH_VALUE); if (!empty($exists)) { $this->out('- Object already exists'); } else { $ds->exec(DbExpr::create($query)); $this->out('+ Done'); } } else { $this->out('- Failed to test if Object already exists'); } } else { $ds->exec(DbExpr::create($query)); $this->out('+ Done'); } } } else { $query = $queryTpl; if (!empty($testQueryTpl)) { $test = StringUtils::insert($testQueryTpl, ['schema' => $schema]); $stmnt = $ds->query(DbExpr::create($test)); if ($stmnt) { $exists = Db::processRecords($stmnt, DB::FETCH_VALUE); if (!empty($exists)) { $this->out('- Object already exists'); } else { $ds->exec(DbExpr::create($query)); $this->out('+ Done'); } } else { $this->out('- Failed to test if Object already exists'); } } else { $ds->exec(DbExpr::create($query)); $this->out('+ Done'); } } $ds->disconnect(); }
/** * @param string $operator * @param string $value * @return array * @throws ScaffoldException */ public function buildConditionFromSearchRule($operator, $value) { if (!in_array($operator, $this->getOperators())) { throw new ScaffoldException("Operator [{$operator}] is forbidden for filter [{$this->getColumnName()}]"); } if (!is_array($value)) { $value = trim($value); } // resolve multivalues switch ($operator) { case self::OPERATOR_IN_ARRAY: case self::OPERATOR_NOT_IN_ARRAY: $value = preg_split('%\\s*,\\s*%s', $value); break; } $this->validateValue($value, $operator); $value = $this->convertRuleValueToConditionValue($value, $operator); $dbOperator = $this->convertRuleOperatorToDbOperator($operator); $dataTypeConverter = $this->getValueDataTypeConverterForDb(); // resolve column name replacement (it could be a DbExpr that concatenates many columns) if ($this->hasColumnNameReplacementForCondition()) { $colReplacement = $this->getColumnNameReplacementForCondition(); if ($colReplacement instanceof DbExpr) { switch ($operator) { case self::OPERATOR_IN_ARRAY: case self::OPERATOR_NOT_IN_ARRAY: $value = '(``' . implode('``,``', $value) . '``)'; break; case self::OPERATOR_BETWEEN: case self::OPERATOR_NOT_BETWEEN: $value = "``{$value[0]}`` AND ``{$value[1]}``"; break; case self::OPERATOR_IS_NULL: case self::OPERATOR_IS_NOT_NULL: $value = 'NULL'; break; default: $value = "``{$value}``"; } return DbExpr::create($colReplacement->get() . " {$dbOperator} {$value}"); } else { $columnName = $colReplacement; } } else { $columnName = $this->getColumnName(); } return [trim($columnName . $dataTypeConverter . ' ' . $dbOperator) => $value]; }
protected function getColumns($tableName, $tableSchema = 'public') { $dataSource = call_user_func([$this->modelParentClass, '_getDataSource'], 'default'); $query = "SELECT * FROM `information_schema`.`columns` WHERE `table_name` = ``{$tableName}`` AND `table_schema` = ``{$tableSchema}``"; $columns = $dataSource->processRecords($dataSource->query(DbExpr::create($query))); if (empty($columns)) { $this->line("Table [{$tableName}] possibly not exists"); return false; } $columns = Set::combine($columns, '/column_name', '/.'); return $columns; }