使用代码(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注入、字符串串联问题以及UPDATE查询中不正确的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);
}