PHP Db::queryModify方法代码示例

    public function registerUser($data, $language)
    {
        $randomSalt = $this->getRandomHash();
        $saltedPassword = hash('sha512', $data['p'] . $randomSalt);
        $databaseData = [$data['firstname'], $data['surname'], $data['tariff'], $data['email'], $saltedPassword, $randomSalt, $data['startDate'], $data['telephone'], $data['address'], $data['ic'], $data['fakturoid_id']];
        //insert user into DB
        if (!Db::queryModify('INSERT INTO `users` (`first_name`,`last_name`,`user_tariff`,`active`,`email`,`password`,`salt`,`invoicing_start_date`,`telephone`,`address`,`ic`,`fakturoid_id`)
                              VALUES (?,?,?,0,?,?,?,?,?,?,?,?)', $databaseData)) {
            return ['s' => 'error', 'cs' => 'Nepovedlo se zapsat do databáze. Zkuste to prosím později', 'en' => 'Unable to write to the database . Please try again later'];
        }
        //generate activation link...
        $randomHash = $this->getRandomHash();
        if (!Db::queryModify('INSERT INTO `activation`(`validation_string`,`email`,`active`,`timestamp`)
                              VALUES (?,?,1,NOW())', [$randomHash, $data["email"]])) {
            return ['s' => 'error', 'cs' => 'Nepovedlo se zapsat do databáze. Zkuste to prosím později', 'en' => 'Unable to write to the database . Please try again later'];
        }
        //...and send activation link
        $subject = ['cs' => NAME . ' Paralelní Polis - aktivace nového účtu', 'en' => NAME . ' Paralell Polis - activation of new account'];
        $activeLink = ROOT . '/' . $language . '/activation/' . $randomHash;
        $message = ['cs' => 'Zdravím!<br/>
<br/>
Klikem na tento odkaz si aktivuješ účet v systému ' . NAME . ' z Paralelní polis: <br/>
<a href="' . $activeLink . '">' . $activeLink . '</a><br/>
<br/>
Pokud tento email neočekáváš, stačí ho ignorovat. <br/>', 'en' => 'Hi!<br/>
<br/>
Click on this link will activate your account in system ' . NAME . ' from Paralell polis: <br/>
<a href="' . $activeLink . '">' . $activeLink . '</a><br/>
<br/>
If you don\'t recognize this email, please just ignore it. <br/>'];
        $this->sendEmail(EMAIL, $data['email'], $subject[$language], $message[$language]);
        return ['s' => 'success', 'cs' => 'Děkujeme za registraci!</br>Poslali jsme ti email, kde nalezneš odkaz, kterým svou registraci aktivuješ', 'en' => 'Thanks for registering!</br>We have sent you an email, where you can find a link to activate your account'];
    }

 public function deactivateUser($email)
 {
     if (!Db::queryModify('UPDATE `users` SET `active` = ?
                           WHERE `email` = ?', [0, $email])) {
         return ['s' => 'error', 'cs' => 'Nepovedlo se zapsat do databáze; zkuste to prosím za pár minut znovu', 'en' => 'Can\'t access database right now; please try it again later'];
     }
     return ['s' => 'info', 'cs' => 'Uživatel ' . $email . ' úspěšně deaktivován', 'en' => 'User ' . $email . ' is successfully deactivated'];
 }

 public function newTicket($type, $sender, $message)
 {
     Db::queryModify('INSERT INTO tickets (type, title, message, `timestamp`)
                      VALUES (?,?,?,NOW())', [$type, $sender, $message]);
     if (SEND_TICKET_EMAILS) {
         $this->sendEmail(EMAIL, EMAIL, 'Ticket from ' . NAME, $message);
     }
 }

 public function deleteExtra($extraId)
 {
     if (empty($extraId)) {
         return ['s' => 'info', 'cs' => 'Nebyla určena žádná položka', 'en' => 'We didn\'t catch any extra'];
     }
     if (Db::queryModify('DELETE FROM `extras` WHERE `id_extra` = ?', [$extraId])) {
         return ['s' => 'success', 'cs' => 'Položka úspěšně smazána', 'en' => 'Extra is successully deleted'];
     } else {
         return ['s' => 'error', 'cs' => 'Položku se nepovedlo smnazat', 'en' => 'Extra is not deleted'];
     }
 }

 public function changePersonalData($data, $id)
 {
     $databaseData = [$data['firstname'], $data['surname'], $data['telephone'], $data['address'], $data['ic'], $id];
     if (!Db::queryModify('
         UPDATE users
         SET `first_name` = ?, `last_name` = ?, `telephone` = ?, `address` =?, `ic` = ?
         WHERE `id_user` = ?', $databaseData)) {
         return ['s' => 'error', 'cs' => 'Nepovedlo se zapsat do databáze; zkuste to prosím za pár minut znovu', 'en' => 'Can\'t access database right now; please try it again later'];
     }
     return ['s' => 'success', 'cs' => 'Osobní údaje byly úspěšně změněny', 'en' => 'Personal data was successfully changed'];
 }

 public function makeNewAdmin($newAdminId, $newAdminPlacesId)
 {
     $count = 0;
     foreach ($newAdminPlacesId as $a) {
         if (!Db::queryModify('INSERT INTO admins (user_id, place_id) VALUES (?, ?)', [$newAdminId, $a])) {
             return ['error', 'částečná chyba - error u admin id: ' . $a];
         }
         $count++;
     }
     return ['success', 'Vloženo ' . $count . ' nových záznamů'];
 }

 public function assignKey($userId, $keyId)
 {
     if (!($uidKey = Db::querySingleOne('SELECT `uid_key` FROM `lock_attempts` WHERE `id` = ?', [$keyId]))) {
         return ['s' => 'error', 'cs' => 'Nepovedlo se vybrat správný záznam přístupu', 'en' => 'Sorry, we were not able to take right access record'];
     }
     if (Db::queryModify('UPDATE `users` SET `uid_key` = ? WHERE id_user = ?', [$uidKey, $userId])) {
         return ['s' => 'success', 'cs' => 'Povedlo se přidat právo vstupu', 'en' => 'Access was successfully assigned'];
     } else {
         return ['s' => 'error', 'cs' => 'Nepovedlo se přidat práva ke vstupu k uživateli', 'en' => 'Access was not assigned to a member'];
     }
 }

    public function trySendLink($email, $year, $language)
    {
        //inkredintions are correctly set
        if (!isset($email, $year)) {
            return ['s' => 'error', 'cs' => 'Nepovedlo se získat data. Zkus to znovu prosím', 'en' => 'We didn\'t catch data correctly - please try it again'];
        }
        //correct year in antispam
        if ($year != date("Y") - 1) {
            return ['s' => 'error', 'cs' => 'Bohužel, antispam byl tentokrát mocnější než ty', 'en' => 'Nothing happend, antispam was stronger than you'];
        }
        $result = Db::queryOne('SELECT `email` FROM `users`
                                WHERE `email` = ?', [$_POST['email']]);
        //skip all when email ins't the same as typed
        if ($email == $result[0]) {
            $randomHash = hash('sha512', uniqid(mt_rand(1, mt_getrandmax()), true));
            if (!Db::queryModify('INSERT INTO `restart_password` (`validation_string`, `email`, `active`, `timestamp`)
                                  VALUES (?, ?, 1, NOW())', [$randomHash, $result[0]])) {
                $this->newTicket('problem', $_SESSION['id_user'], 'nepovedlo se zapsat do restart_password ve funkci register');
                return ['s' => 'chyba', 'cs' => 'Pokus se nepovedl uložit; zkus to prosím znovu za pár minut', 'en' => 'We failed on saving data. Try it again please after couple of minutes'];
            }
            $subject = ['cs' => NAME . ' Paralelní Polis - žádost o restart hesla', 'en' => NAME . ' Paralell Polis - change password request'];
            $activeLink = ROOT . '/' . $language . '/RestartPasswordByLink/' . $randomHash;
            $message = ['cs' => 'Zdravím!<br/>
<br/>
Na stránce <a href="' . ROOT . '/' . $language . '">' . ROOT . '</a> jsme registrovali žádost o restart hesla.<br/>
<br/>
Heslo si můžeš změnit klikem na odkaz <a href="' . $activeLink . '">' . $activeLink . '</a>. Platnost odkazu je <b>' . round(CHANGE_PASS_TIME_VALIDITY / 60) . '</b> minut.<br/>
<br/>
Pokud tento mail neočekáváš, stačí ho ignorovat. Pokud by ti přesto přišel podezřelý nebo vícekrát za sebou,
prosím konkatuj správce stránek na <a href="' . ROOT . '/' . $language . '/contact">' . ROOT . '/' . $language . '/contact</a><br/>', 'en' => 'Hello!<br/>
<br/>
We are register request about password change on the page <a href="' . ROOT . '/' . $language . '">' . ROOT . '</a>.<br/>
<br/>
You can change your password by clicking on this link: <a href="' . $activeLink . '">' . $activeLink . '</a>. Time validity of this link is <b>' . round(CHANGE_PASS_TIME_VALIDITY / 60) . '</b> minutes.<br/>
<br/>
If you don\'t awaiting for this mail, just ignore it. But if you want to know what to do next,
please contact our webmaster on this page: <a href="' . ROOT . '/' . $language . '/contact">' . ROOT . '/' . $language . '/contact</a><br/>'];
            if (!$this->sendEmail(EMAIL, $email, $subject[$language], $message[$language])) {
                $this->newTicket('problem', $_SESSION['id_user'], 'nepovedlo se odeslat email');
                return ['s' => 'error', 'cs' => 'Nepovedlo se odeslat email s aktivačním linkem; zkus to prosím za pár minut znovu', 'en' => 'We failed in sending email with activation link; try it again please after couple of minutes'];
            }
            $this->newTicket('restartHesla', $email, 'poslan mail s linkem');
        } else {
            //check if we can grab who is logged - serve as primitive honeypot
            if (isset($_SESSION['username'])) {
                $loggedUser = $_SESSION['username'];
            } else {
                $loggedUser = "we dont know :(";
            }
            $this->newTicket("restartHesla", $loggedUser, 'neplatny pokus restartu hesla pro uzivatele: ' . $_POST['email']);
        }
        return ['s' => 'success', 'cs' => 'Ozvali jsme se na zadaný email', 'en' => 'We send as email on desired address'];
    }

 public function unlockFiveAttempts($email)
 {
     //unlock last five attempts
     if (!Db::queryModify('UPDATE `login_attempts` SET `success` = ?
                          WHERE `login` = ? ORDER BY `timestamp` DESC LIMIT 5', [2, $email])) {
         return ['s' => 'error', 'cs' => 'Bohužel se nepovedlo odblokování brutforce systému. Zkus to prosím znovu', 'en' => 'Unfortunately, we failed to unblock brutforce system. Please try again'];
     }
     //nvalidate all others brutforce links
     if (!Db::queryModify('UPDATE `restart_brutforce` SET `active` = ?
                           WHERE `email` = ?', [0, $email])) {
         $this->newTicket('problem', $email, 'nepovedlo se invalidovat platné linky po úspěšném brutforcu ve funkci unlockFiveAttempts');
         return ['s' => 'info', 'cs' => 'Odblokováno, nicméně bohužel ne všechno proběholo korektně', 'en' => 'Unblocked, but not all tasks were completly correct'];
     }
     return ['s' => 'success', 'cs' => 'Brutforce systém úspěšně odblokován', 'en' => 'Brutforce system was successfully unblocked'];
 }

 public static function validateCsrfRequest($returnedToken)
 {
     $storedToken = Db::querySingleOne('SELECT `token` FROM `csrf`
                                        WHERE `user_id` = ? AND `active` = 1
                                        ORDER BY `id` DESC', [$_SESSION['id_user']]);
     //unactive all entries
     Db::queryModify('UPDATE `csrf` SET `active` = 0 WHERE `user_id` = ? AND `active` = 1', [$_SESSION['id_user']]);
     //add referer uri into hash to get stored value
     $returnedRefererToken = hash('sha512', $_SERVER['HTTP_REFERER'] . $returnedToken, false);
     if ($storedToken == $returnedRefererToken) {
         return true;
     } else {
         self::newTicket('warning', $_SESSION['id_user'], 'Possible CSRF attack (returned false on stored token ' . $storedToken);
         return false;
     }
 }

 public function sendContactEmail($year, $email, $message, $language)
 {
     if ($year != date("Y") + 1) {
         return ['s' => 'error', 'cs' => 'Bohužel, nic se neodeslalo, antispam byl tentokrát mocnější než ty', 'en' => 'Nothing happend, antispam was stronger than you'];
     }
     $subject = ['cs' => NAME . 'Paralelní Polis', 'en' => NAME . ' - Paralell Polis'];
     $prefix = ['cs' => 'Kopie emailu zaslaného ze systému ' . NAME . ': ' . PHP_EOL . PHP_EOL, 'en' => 'Copy of email send from system ' . NAME . ': ' . PHP_EOL . PHP_EOL];
     //send email to admin
     $this->sendEmail($email, EMAIL, $subject[$language], $message);
     //and copy to user
     $this->sendEmail(EMAIL, $email, $subject[$language], $prefix[$language] . $message);
     if (!Db::queryModify('INSERT INTO `tickets` (`type`, `title`, `message`, `timestamp`)
                         VALUES (?,?,?, NOW())', ["sent contact email", $email, $message])) {
         return ['s' => 'info', 'cs' => 'Email odešel, ale neuložil se do databáze. Brzo se ozveme', 'en' => 'Email was sent, but didn\'n save in our database. We will be in touch'];
     } else {
         return ['s' => 'success', 'cs' => 'Díky za zprávu, brzo se ozveme', 'en' => 'Thanks for the message, we will be in touch'];
     }
 }

 public function checkForm($link, $p)
 {
     $result = Db::queryOne('SELECT `validation_string`,`users`.`email` FROM `restart_password`
         JOIN `users` WHERE `users`.`email` = `restart_password`.`email` && `validation_string` = ?', [$link]);
     //password must be 128 chars long after user-side hashing
     if (strlen($p) != 128) {
         $this->newTicket('problem', $link, 'hash ve funkci zkontrolovatFormular nemá delku 128 znaků - link: ' . $link . ' a možná přihlášený uživatel: ' . $_SESSION['username']);
         return ['s' => 'error', 'cs' => 'Stalo se něco divného v hashování hesla. Prosím zkuste to znovu', 'en' => 'Something wierd happend in password hashing. Please try it again'];
     }
     $randomSalt = $this->getRandomHash();
     $saltedPassword = hash('sha512', $p . $randomSalt);
     if (!Db::queryModify('UPDATE `users` SET `password` = ? , `salt` = ?
                           WHERE email = ?', [$saltedPassword, $randomSalt, $result['email']])) {
         return ['s' => 'error', 'cs' => 'Nepovedlo se uložení do databáze. Zkuste to prosím znovu', 'en' => 'We failed at database save. Try it again please'];
     }
     //success
     $this->invalidateAttemptsForMail($result['email']);
     return ['s' => 'success', 'cs' => 'Heslo bylo úspěšně změněno', 'en' => 'Password was changed successfully'];
 }

 public function newTicket($type, $sender, $message)
 {
     Db::queryModify('INSERT INTO tickets (type, title, message, `timestamp`)
                      VALUES (?,?,?,NOW())', [$type, $sender, $message]);
 }

    public function updatePaymentStatus($paymentId, $newStatus)
    {
        Db::queryModify('UPDATE `payments` SET `status` = ? 
			WHERE id_payment = ?', [$newStatus, $paymentId]);
    }

 private static function reportProblem(PDOException $e)
 {
     $trace = $e->getTrace();
     $DBcall = $trace[1];
     $functionCall = $trace[2];
     $type = 'error with DB';
     $function = $DBcall['function'] . ' into ' . $functionCall['function'] . ' in file ' . $functionCall['file'];
     $message = serialize($DBcall['args']);
     Db::queryModify('INSERT INTO tickets (`type`, `title`, `message`, `timestamp`)
                      VALUES (?,?,?,NOW())', [$type, $function, $message]);
 }