使用代码激活注册帐户(PHP + JS)

Question

我目前正在做一个项目，并设法获得一个工作注册和登录表单。注册后，用户将使用5字符激活代码发送电子邮件，并被要求将其插入用户配置文件页面，以便将状态从active:0更改为active:1，并获得对站点其他部分的权限。

由于某些原因，激活代码根本无法工作:/

下面的代码是为激活帐户而编写的PHP代码，我使用PDO查询连接到数据库，但我也尝试使用mysqli查询，但似乎不起作用。

<?php
session_start();

    // Allow the config
        define('__CONFIG__', true);
    // Require the config
    require_once "inc/config.php";  //possibly have to change the location
    include_once "inc/classes/DB.php"; //possibly have to change location
    include_once "inc/classes/Page.php";
    include_once "inc/classes/User.php";

    Page::ForceLogin();
//
//$email = filter_input(INPUT_POST['email'] );
//$username = Filter::String($_POST['username']);
//$skills = Filter::String($_POST['skills']);
//$email = filter_input(INPUT_POST['email'] );
//$username = filter_input(INPUT_POST['username'] );

    $return=[];

$User = new User($_SESSION['user_id']);
$username = $User->username;


////Connection Variables
//$host = 'localhost';
//$user = 'root';
//$password = '';
//$db = 'mdb_';
////Creating mysql connection
//$conn = new mysqli($host,$user,$password,$db);




//$username = $User->username;


$activationCode = User::Find(INPUT_GET['activationCode']);

if(isset($_GET['activationCode'])) {
    if(!empty($_GET['activationCode'])) {
        $query = "SELECT * FROM users WHERE username='.$username.'";
        $result = query($con, $query);
        if(ocirowcount($result) > 0){
            while($row = mysqli_fetch_array($result)){
                if($_GET['activationCode'] == $row["activationCode"]){
                    $con->query ("UPDATE users SET active=1 AND credit=100 WHERE username = '.$username.'");
                    $return['error'] = 'Your account is now activated! You have earned 100 Time-banking credits.';
                    //header("Refresh:0");
                }
                else{
                    $return['error'] = 'Code incorrect, please try again';
                }
            }
        }
        echo json_encode($return, JSON_PRETTY_PRINT);
    }
}

//$activationCode = filter_input(INPUT_GET, "activationCode" );
//if(isset($_GET['activationCode'])) {
//    if(!empty($_GET['activationCode'])) {
//        $query = "SELECT * FROM users WHERE username='$username'";
//        $result = mysqli_query($conn, $query);
//        if(mysqli_num_rows($result) > 0){
//            while($row = mysqli_fetch_array($result)){
//                if($_GET['activationCode'] == $row["activationCode"]){
//                    $sql = $conn->query ("UPDATE users SET active=1 AND credit=100 WHERE username = '$username'");
//                    $return['error'] = 'Your account is now activated! You have earned 100 Time-banking credits.';
//                    //header("Refresh:0");
//                }
//                else{
//                    $return['error'] = 'Code incorrect, please try again';
//                }
//            }
//        }
//        echo json_encode($return, JSON_PRETTY_PRINT);
//    }
//}

//$activationCode = filter_input(INPUT_POST, "activationCode" );
//
//  if(isset($_POST['activationCode'])) {
//      $activationCode = Filter::String( $_POST['activationCode'] );
//
//
//
//
//
//      $query = "SELECT * FROM users WHERE username='$username'";
//          $result = mysqli_query($con, $query);
//          if(mysqli_num_rows($result) > 0){
//
//              while($row = mysqli_fetch_array($result)){
//
//                  if($_POST['activationCode'] == $row["activationCode"]){
//
//
//                      $activateUser = $con->query ("UPDATE `users` SET  `credit` = :100, `active` = :1, WHERE `user_id` = :$user_id");
//                      //$sql = $con->query ("UPDATE users SET active=1, credit=100 WHERE username = '$username'");
//
//                      $return['error'] = 'Your account is now activated! You have earned 100 Time-banking credits.';
//
//                      header("Refresh:0");
//                  }
//                  else{
//                      $return['error'] = 'Code incorrect, please try again';
//                  }
//
//              }
//          }
//
//      echo json_encode($return, JSON_PRETTY_PRINT);
//
////      }
//  }



?>

下面的代码是在PDO中创建$con的db类。

class DB {

    protected static $con;

    private function __construct(){
        try {

            self::$con = new PDO( 'mysql:charset=latin1;host=host;port=****;dbname=mdb_', 'root', 'pass'); //change connection string
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false );
            self::$con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        } catch (PDOException $e) {
            echo "Could not connect todatabase."; exit;
        }
    }


    public static function getConnection() {
        //If this instance has not been started, start it.
        if (!self::$con) {
            new DB();
        }
        //Return the writeable db connection
        return self::$con;
    }

Answer 1

这里有几个问题，从混合数据库API到可能的SQL注入、字符串连接问题和更新查询中不正确的SQL语法。

如果在数据库连接中使用PDO，则需要删除对oci* (用于Oracle )和mysqli* (这是不同的API，与PDO不兼容)的所有引用，并使用PDO等价物。

我还将从查询中删除$username，转而使用准备好的语句。$username可能来自您自己的数据库，但我不知道它是如何进入的。如果您没有用户名可以包含的字符的限制，并且当用户名插入数据库时正确地转义，那么它可能包含单引号(或双引号)，这些引号在这段代码中仍然会造成麻烦。底线:如果它最初是用户输入，那么它永远不应该被信任。

// I missed this in the code in your question
$con = DB::getConnection();

if (isset($_GET['activationCode'])) {
    if(!empty($_GET['activationCode'])) {
        // Note the placeholder ":username" -- PDO will fill that with
        // $username for you (see $stmt->execute() below) and take care
        // of adding quotes around it
        $query = "SELECT * FROM users WHERE username = :username";

        try {
            $stmt = $con->prepare($query);
            $stmt->execute(array(':username' => $username));

            if ($stmt->rowCount() > 0) {
                foreach ($stmt as $row) {
                    if ($_GET['activationCode'] == $row["activationCode"]) {
                        // note the syntax: "SET active=1, credit=100"
                        $update = $con->prepare("UPDATE users SET active=1, credit=100 WHERE username = :username");
                        $update->execute(array(':username' => $username));

                        $return['error'] = 'Your account is now activated! You have earned 100 Time-banking credits.';
                        //header("Refresh:0");
                    } else {
                        $return['error'] = 'Code incorrect, please try again';
                    }
                }
            }
        } catch (PDOException $error) {
            $return['error'] = (string)$error;
        }

        echo json_encode($return, JSON_PRETTY_PRINT);
    }
}

注意，只要尝试UPDATE查询，就可以对其进行某种程度的优化。为了方便起见，我还假设您只希望激活代码能够在不活动的帐户上使用，您目前没有检查这些帐户：

$con = DB::getConnection();

if (isset($_GET['activationCode']) && !empty($_GET['activationCode'])) {
    $query = "UPDATE users SET active = 1, credit = 100 WHERE username = :username AND activationCode = :code AND active = 0";

    try {
        $stmt = $con->prepare($query);
        $stmt->execute(array(
            ':username' => $username,
            ':code' => $_GET['activationCode']
        ));

        if ($stmt->rowCount() > 0) {
            $return['error'] = 'Your account is now activated! You have earned 100 Time-banking credits.';
        } else {
            $return['error'] = 'Code incorrect or account is already active, please try again';
        }
    } catch (PDOException $error) {
        $return['error'] = (string)$error;
    }

    echo json_encode($return, JSON_PRETTY_PRINT);
}