public static function key()
{
$id = DBMaintenance::dbNextNumber(DB::DEF, 'tblCrossSiteProtection');
$key = uniqid('', true);
$sql = 'INSERT INTO tblCrossSiteProtection VALUES(?,?,?)';
DB::exec(DB::DEF, $sql, [$id, $key, time() + self::EXPIRY]);
return $key;
}
public static function migrate()
{
$maxRun = 0;
$runItems = [];
foreach (DBTable::factory(DB::DEF, 'SELECT * FROM tblMigration') as $row) {
if ((int) $row['fldRun'] > $maxRun) {
$maxRun = (int) $row['fldRun'];
}
if (!isset($runItems[$row['fldClass']])) {
$runItems[$row['fldClass']] = [];
}
$runItems[$row['fldClass']][] = $row['fldMethod'];
}
$maxRun += 1;
$html = '';
// Go through all the migration classes
foreach (Cfg::get('migration', []) as $migrationClass) {
$clazz = new \ReflectionClass($migrationClass);
// If new class then just add empty list
if (!isset($runItems[$migrationClass])) {
$runItems[$migrationClass] = [];
}
// get a list of methods to run
$methodList = [];
foreach ($clazz->getMethods() as $method) {
if (in_array($method->name, $runItems[$migrationClass])) {
continue;
}
if (strpos($method->name, 'migrate') !== 0) {
continue;
}
// Add the name to the list
$methodList[] = $method->name;
}
// Sort so that it will be date ordered
sort($methodList);
foreach ($methodList as $method) {
if (($result = call_user_func([$migrationClass, $method])) === false) {
$html .= "There is a problem running {$migrationClass}::{$method}<br/>\n";
} else {
$html .= $result;
DB::exec(DB::DEF, 'INSERT INTO tblMigration (fldMigrationID,fldRun,fldClass,fldMethod) VALUES (?,?,?,?)', [DBMaintenance::dbNextNumber(DB::DEF, 'tblMigration'), $maxRun, $migrationClass, $method]);
}
}
}
return $html;
}
public static function initialize()
{
$dbType = Cfg::get('local-driver');
switch ($dbType) {
case DB::SQLITE:
$dbFileName = Cfg::get('local-host');
echo "Checking that the file {$dbFileName} exists\n";
if (file_exists($dbFileName)) {
echo "Database exists ({$dbFileName})\n";
} else {
echo "Creating empty database\n";
touch($dbFileName);
}
break;
case DB::MYSQL:
$fldHostName = Cfg::get('local-host');
$fldDBName = Cfg::get('local-db');
$fldUsername = Cfg::get('local-user');
$fldPassword = Cfg::get('local-pass');
try {
$dbh = new \PDO("mysql:host={$fldHostName}", $fldUsername, $fldPassword);
$dbh->exec("CREATE DATABASE IF NOT EXISTS {$fldDBName}") or die(print_r($dbh->errorInfo(), true));
} catch (PDOException $e) {
die("DB ERROR: " . $e->getMessage());
}
break;
default:
die("Unsupported DB Type: {$dbType}");
}
if (count(\Jackbooted\DB\DBMaintenance::getTableList()) == 0) {
// Put in the base data
$sqlFileName = Cfg::get('tmp_path') . '/base_database.sql';
if (file_exists($sqlFileName)) {
echo "Running the commands in {$sqlFileName} against the database\n";
foreach (explode(';', file_get_contents($sqlFileName)) as $statement) {
DB::exec(DB::DEF, $statement);
}
} else {
die("Base Database file does not exists ({$sqlFileName}) aborting\n");
}
} else {
die("Database already seems to be set up.");
}
echo "audititing Table - AlertsDAO\n";
(new \App\Models\AlertsDAO())->auditTable();
return '';
}
public function index($tName = '')
{
if (($tableName = Request::get('tblName', $tName)) == '') {
return '';
}
$crud = CRUD::factory($tableName, ['topPager' => false])->copyVarsFromRequest('tblName');
if (preg_match('/^tblMod([A-Z]+[a-z]+)/', $tableName, $matches)) {
foreach (Cfg::get('modules', []) as $moduleClass) {
eval($moduleClass . '::' . Module::CRUD_MOD . '($crud);');
}
} else {
switch ($tableName) {
case 'tblNextNumber':
$crud->setColDisplay('fldTable', [CRUD::SELECT, DBMaintenance::getTableList(), true]);
break;
case 'tblSecPrivUserMap':
$userSql = DB::driver() == DB::MYSQL ? Admin::USER_SQL_MYSQL : Admin::USER_SQL_MYSQL;
$crud->setColDisplay('fldUserID', [CRUD::SELECT, $userSql, true]);
$crud->setColDisplay('fldGroupID', [CRUD::SELECT, Admin::GROUP_SQL, true]);
$crud->setColDisplay('fldPrivilegeID', [CRUD::SELECT, Admin::PRIV_SQL, true]);
$crud->setColDisplay('fldLevelID', [CRUD::SELECT, Admin::LEVEL_SQL]);
break;
case 'tblUserGroupMap':
$userSql = DB::driver() == DB::MYSQL ? Admin::USER_SQL_MYSQL : Admin::USER_SQL_SQLITE;
$crud->setColDisplay('fldUserID', [CRUD::SELECT, $userSql, true]);
$crud->setColDisplay('fldGroupID', [CRUD::SELECT, Admin::GROUP_SQL, true]);
break;
case 'tblUser':
$crud->setColDisplay('fldLevel', [CRUD::SELECT, Admin::LEVEL_SQL]);
$crud->setColDisplay('fldTimeZone', [CRUD::SELECT, Admin::TZ_SQL]);
break;
}
}
$resp = Response::factory()->set('tblName', $tableName);
return Tag::hTag('b') . 'Editing Table: ' . $tableName . Tag::_hTag('b') . ' ' . Tag::hRef('ajax.php?' . $resp->action(__CLASS__ . '->csv()'), 'CSV') . ' ' . Tag::hRef('ajax.php?' . $resp->action(__CLASS__ . '->xls()'), 'XLS') . $crud->index();
}
public function signUp()
{
$checkIdSql = 'SELECT COUNT(*) FROM tblUser WHERE fldUser=?';
if (Request::get('_CAP') != Request::get('fldCaptcha')) {
$msg = 'Invalid Security Code ' . $this->newRegistration();
} else {
if (DB::oneValue(DB::DEF, $checkIdSql, Request::get('fldEmail')) != 0) {
$msg = 'A user with email: ' . Request::get('fldEmail') . ' currently exists on this system<br/>' . 'Either choose a new email address or request a new password.' . $this->newRegistration();
} else {
// Generate a password for the user
$pw = Password::passGen(10, Password::MEDIUM);
// Add the User to the Database
$now = time();
if (DB::driver() == DB::MYSQL) {
$sql = <<<SQL
INSERT INTO tblUser
(fldUserID,fldUser,fldFirstName,fldLastName,fldPassword,fldDomain,fldCreated, fldLevel)
VALUES ( ?, ?, ?, ?, PASSWORD(?),?, {$now}, ? )
SQL;
} else {
$sql = <<<SQL
INSERT INTO tblUser
(fldUserID,fldUser,fldFirstName,fldLastName,fldPassword,fldDomain,fldCreated, fldLevel)
VALUES ( ?, ?, ?, ?, ?, ?, {$now}, ? )
SQL;
$pw = hash('md5', $pw);
}
$params = [DBMaintenance::dbNextNumber(DB::DEF, 'tblUser'), Request::get('fldEmail'), Request::get('fldFirstName'), Request::get('fldLastName'), $pw, Cfg::get('server'), Privileges::getSecurityLevel('USER')];
DB::exec(DB::DEF, $sql, $params);
$boss = Cfg::get('boss');
$desc = Cfg::get('desc');
$body = '<h3>New User: <b>%s %s</b><br>Email: <b>%s</b></h3><br>Has joined %s';
// create the email message to notify about a new user
Mailer::envelope()->format(Mailer::HTML_TEXT)->from(Request::get('fldEmail'))->to($boss)->subject('New user has joined ' . $desc)->body(sprintf($body, Request::get('fldFirstName'), Request::get('fldLastName'), Request::get('fldEmail'), $desc))->send();
$body = <<<TXT
Thanks for signing up for %s
Here are your login details
Username: %s
Password: %s
Regards
%s
TXT;
// create the email message to notify the new user of his/her login details
Mailer::envelope()->from($boss)->to(Request::get('fldEmail'))->subject('Welcome to ' . $desc)->body(sprintf($body, $desc, Request::get('fldEmail'), $pw, $desc))->send();
// Let the user know that the registration was succesful
$msg = 'Congratulations you have been signed up for ' . $desc . '<br>' . 'Soon you will receive a confirmation email that will contain' . 'your login details.';
}
}
return Widget::popupWrapper($msg, -1);
}
public function fileChecksumRebase()
{
DB::exec(DB::DEF, 'TRUNCATE tblFileCheck');
$dirList = PHPExt::dirSearch(Cfg::get('site_path'), '/^[^_].*$/');
$len = strlen(Cfg::get('site_path')) + 1;
$fileCount = 0;
foreach ($dirList as $fullPath) {
$fileCount++;
DB::exec(DB::DEF, 'INSERT INTO tblFileCheck VALUES(?,?,?,?)', [DBMaintenance::dbNextNumber(DB::DEF, 'tblFileCheck'), substr($fullPath, $len), filesize($fullPath), sha1_file($fullPath)]);
}
return "Updated {$fileCount} files<br/>" . $this->fileChecksum();
}
public function auditTable()
{
if (in_array(null, [$this->db, $this->tableName, $this->tableStructure])) {
return false;
}
if (!isset(self::$tableList[$this->db])) {
self::$tableList[$this->db] = array_flip(DBMaintenance::getTableList());
}
if (!isset(self::$tableList[$this->db][$this->tableName])) {
DB::exec($this->db, $this->tableStructure);
DBMaintenance::addTableToNextNumber($this->tableName, $this->keyFormat, $this->tableName);
self::$tableList[$this->db][$this->tableName] = 1;
}
return true;
}
protected function insertRows()
{
$rowsToInsert = (int) Request::get('rows');
$insertedCnt = 0;
for ($i = 0; $i < $rowsToInsert; $i++) {
$params = array_merge($this->insDefaults, $this->where);
$paramValues = null;
if (Cfg::get('jb_db', false)) {
$params[$this->primaryKey] = DBMaintenance::dbNextNumber($this->db, $this->tableName);
}
$sql = 'INSERT INTO ' . $this->tableName;
if (count($params) > 0) {
$sql .= ' (' . join(',', array_keys($params)) . ') ' . 'VALUES (' . DB::in(array_values($params), $paramValues) . ')';
}
$insertedCnt += $this->exec($sql, $paramValues);
}
if ($insertedCnt > 0) {
$this->paginator->setRows($this->getRowCount());
}
return 'Inserted ' . $insertedCnt . ' row' . StringUtil::plural($insertedCnt) . Tag::br();
}
public static function checkAuthenticated($username, $password, $hash = null)
{
if (!isset($username) || !isset($password) || $username == false || $password == false) {
return false;
}
if ($hash != null && !self::testHash($username, $password, $hash)) {
$sucessfulLogin = false;
} else {
if (DB::driver() == DB::MYSQL) {
$sql = <<<SQL
SELECT COUNT(*)
FROM tblUser
WHERE fldPassword=PASSWORD(?)
AND fldUser=?
AND fldFails<4
SQL;
$numEntries = DB::oneValue(DB::DEF, $sql, [$password, $username]);
} else {
$sql = <<<SQL
SELECT COUNT(*)
FROM tblUser
WHERE fldPassword=?
AND fldUser=?
AND fldFails<4
SQL;
$numEntries = DB::oneValue(DB::DEF, $sql, [hash('md5', $password), $username]);
}
$sucessfulLogin = $numEntries == 1;
if (!$sucessfulLogin) {
$params = [DBMaintenance::dbNextNumber(DB::DEF, 'tblLoginAttempt'), $username, $password, $_SERVER['HTTP_USER_AGENT'], $_SERVER['SERVER_ADDR']];
DB::exec(DB::DEF, 'INSERT INTO tblLoginAttempt VALUES(?,?,?,?,?)', $params);
}
}
if ($sucessfulLogin) {
self::updateLastLogin($username);
} else {
self::incrementFails($username);
}
return $sucessfulLogin;
}
