PHP 注册不向 MySQL PDO 发送数据

Question

我已经在我的本地主机和我的 VPS 上测试了这个注册表。我根本无法让它向 MySQL 发送数据。注册类确实承认表单中的错误，并表示已成功创建帐户，但它从未将其插入数据库。

<?php
class Register {
private $dbObj = null;
private $dbConf = array('host' => 'localhost', 'user' => 'root', 'pass' => '', 'db' => 'sweater');
private $userArr = Array();
private $referred = false;
public function __construct($params){
    try {
        $this->dbObj = new PDO('mysql:host=' . $this->dbConf['host'] . ';dbname=' . $this->dbConf['db'], $this->dbConf['user'], $this->dbConf['pass']);
    } catch(PDOException $e) {
        $this->__return($e->getMessage());
    }

    $this->userArr['username']        = trim($params['playerName']);
    $this->userArr['email']           = trim($params['playerEmail']);
    $this->userArr['password']        = trim($params['playerPass']);
    $this->userArr['passwordConfirm'] = trim($params['playerPassConfirm']);
    $this->userArr['color']           = trim($params['playerColor']);
    if($this->ipExists($_SERVER['REMOTE_ADDR'])){
        $this->__return('You\'re not allowed to register more than 4 accounts per IP on our server!');
    }
    if($this->verifyInput($this->userArr)) {
        if($this->insertUser($this->userArr)){
            echo $this->__return('Your account has been created successfully.', false);
        } else {
            $this->__return('unable to create account? error code 9998');
        }
    }
}

private function verifyInput($userArr){
    foreach($userArr as $key => $val){
        switch($key){
            case 'username':
                if($val == '')
                    $this->__return('You are required to enter a username.');
                if($this->userExists($val))
                    $this->__return("Another player already has that username!");
                if(strlen($val) < 4)
                    $this->__return('Your uername must be at least 4 characters in length!');
                if(strlen($val) > 12)
                    $this->__return('Your username must be less than 12 characters in length!');
                if(!ctype_alnum($val))
                    $this->__return('Your username can only contain letters & numbers.');
                break;
            case 'email':
                if($val == '')
                    $this->__return('You are required to enter an email address.');
                if(!filter_var($val, FILTER_VALIDATE_EMAIL))
                    $this->__return('You have entered an invalid email address.');
                if($this->emailExists($val))
                    $this->__return('A user has already registered with that email address.');
                break;
            case 'password':
                if($val == '')
                    $this->__return('You are required to enter a password.');
                if(strlen($val) < 4)
                    $this->__return('Your password must be at least 4 characters in length!');
                if(strlen($val) > 40)
                    $this->__return('Your password must be less than 40 characters in length!');
                if($val !== $userArr['passwordConfirm'])
                    $this->__return('The passwords you entered do not match.');
                break;
            case 'passwordConfirm':
                if($val == '')
                    $this->__return('You are required to confirm your password for verification purposes.');
                if($val !== $userArr['password'])
                    $this->__return('The passwords you entered do not match.');
                break;
            case 'color':
                if(!is_numeric($val)){
                    $this->__return('Color is not numeric');
                }
                if($val > 14){
                    $this->__return('Invalid color ID');
                }
                break;
        }
    }
    return true;
}

private function insertUser($userArr) {
    try {
        $strQuery = "INSERT INTO users (ID, Username, Password, Email, RegisteredTime, RegisteredIP, LoginKey, LoginToken, Active, Status, Coins, Credits, Badges, Color) VALUES (null, :Username, :Password, :Email, :RegTime, :RegIP, null, null, :Active, 0, 10000, :Credits, '[]', :Color)";
        $objStatement = $this->dbObj->prepare($strQuery);
        $objStatement->bindValue(':Username', $userArr['username']);
        $objStatement->bindValue(':Password', md5($userArr['password']));
        $objStatement->bindValue(':Email', $userArr['email']);
        $objStatement->bindValue(':RegTime', time());
        $objStatement->bindValue(':RegIP', $_SERVER['REMOTE_ADDR']);
        $objStatement->bindValue(':Color',$userArr['color']);
        $objStatement->execute();
        $objStatement->closeCursor();
        if($objStatement) return true;
        else return false;
    } catch(PDOException $e){
        $this->__return($e->getMessage());
    }

}

private function getPlayerCredits($user) {
    try {
        $strQuery = "SELECT Credits from `users` WHERE Username = :Username";
        $objStatement = $this->dbObj->prepare($strQuery);
        $objStatement->bindValue(':Username', $user);
        $objStatement->execute();
        $objStatement->bindColumn('Credits', $credits);
        $objStatement->fetch(PDO::FETCH_BOUND);
        $objStatement->closeCursor();
        if($objStatement) return $credits;
        else return false;
    } catch(PDOException $e){
        $this->__return($e->getMessage());
    }

}

private function userExists($username){
    try {
        $strQuery = "SELECT ID FROM `users` WHERE Username = :Username";
        $objStatement = $this->dbObj->prepare($strQuery);
        $objStatement->bindValue(':Username', $username);
        $objStatement->execute();
        $intRows = $objStatement->rowCount();
        $objStatement->closeCursor();
        return $intRows > 0;
    } catch(PDOException $e){
        $this->__return($e->getMessage());
    }
}

private function emailExists($email){
    try {
        $strQuery = "SELECT ID FROM `users` WHERE Email = :Email";
        $objStatement = $this->dbObj->prepare($strQuery);
        $objStatement->bindValue(':Email', $email);
        $objStatement->execute();
        $intRows = $objStatement->rowCount();
        $objStatement->closeCursor();
        return $intRows > 0;
    } catch(PDOException $e){
        $this->__return($e->getMessage());
    }
}

private function ipExists($ip){
    try {
        $strQuery = "SELECT ID FROM `users` WHERE RegisteredIP = :RegIP";
        $objStatement = $this->dbObj->prepare($strQuery);
        $objStatement->bindValue(':RegIP', $ip);
        $objStatement->execute();
        $intRows = $objStatement->rowCount();
        $objStatement->closeCursor();
        return $intRows >= 4;
    } catch(PDOException $e){
        $this->__return($e->getMessage());
    }
}

private function validateReferral($username){
    try {
        $strQuery = "SELECT ID FROM `users` WHERE Username = :Usrn";
        $objStatement = $this->dbObj->prepare($strQuery);
        $objStatement->bindValue(':Usrn', $username);
        $objStatement->execute();
        $intRows = $objStatement->rowCount();
        $objStatement->closeCursor();
        return $intRows > 0;
    } catch(PDOException $e){
        $this->__return($e->getMessage());
    }
}

private function __return($msg, $error = true){
    $returnArr = Array('error' => $error, 'message' => $msg);
    if($error){
        echo json_encode($returnArr);
        die();
    } else{
        return json_encode($returnArr);
    }
}
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="icon" href="//www.<?php echo $config['WEB_HOST']; ?>/favicon.ico">
    <title>Polar - The #1 CPPS</title>
    <link href="https://cdn.polarcp.com/assets/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.polarcp.com/assets/css/style.main.css" rel="stylesheet">
    <script src="//www.google.com/recaptcha/api.js"></script>
</head>
<body>
<nav class="navbar navbar-inverse navbar-static-top" id="page-nav">
            <div class="container">
           <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" aria-expanded="false">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      <a class="navbar-brand" href="#"><img src="//www.<?php echo $config['WEB_HOST']; ?>/logo.png" width="100" /></a>
    </div>
    <div class="collapse navbar-collapse">
      <ul class="nav navbar-nav navbar-right">
        <li><a href="https://polarcp.com">Home</a></li>
        <li class="active"><a href="#">Register</a></li>
      </ul>
            </div>
        </nav>
    <div class="container">
        <div class="header register"><div class="overlay">
                Create an Account
                </div></div>
                <div style="padding-top:100px"></div>
                <div align="center">
                    <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
                    <!-- Register -->
                    <ins class="adsbygoogle"
                         style="display:inline-block;width:728px;height:90px"
                         data-ad-client="ca-pub-6294131573779014"
                         data-ad-slot="2822232685"></ins>
                    <script>
                    (adsbygoogle = window.adsbygoogle || []).push({});
                    </script>
                </div>
        <div class="row">

            <div class="col-md-8 col-md-offset-2" id="top-pad" style="padding-top:30px">

                <p>You're on your way to joining the <b>#1 CPPS</b>! All you need to do is take a minute or two to fill out the registration form below. Once completed you will be able to join your fellow penguins in the game!</p>
                <div class="row">
                <div class="col-md-8">
                <form id="reg-form">
                <div class="alert alert-danger" id="register-alert" style="display:none">There was an error</div>
                    <div class="form-group">
                    <input type="text" class="form-control" id="playerName" placeholder="Username">
                  </div>
                  <div class="form-group">
                    <input type="email" class="form-control" id="playerEmail" placeholder="Email">
                  </div>
                  <div class="form-group">
                    <input type="password" class="form-control" id="playerPass" placeholder="Password">
                  </div>
                  <div class="form-group">
                    <input type="password" class="form-control" id="playerPassConfirm" placeholder="Repeat your password">
                  </div>
                  <div class="form-group">
                    <span style="font-size:14px;color:#666;">
                    If you were referred to Polar by an existing player, enter their name here and you will both receive a reward. This is completely optional!</span><br/><br />
                    <input type="text" class="form-control" id="playerReferral" placeholder="Player who referred you" <?php if(isset($_GET['ref'])){ echo 'value="' . $_GET['ref'] . '" '; } ?>>
                  </div>
                  <div class="form-group">
                  <div class="g-recaptcha" data-sitekey="6LfhYiITAAAAAJiwF0Meg8v_SZuDXxvw10ImUuAz"></div>
                  </div>
                  <div class="form-group">
                    <button type="submit" class="btn btn-success">Create</button>
                  </div>
                </form>
                </div>
                <div class="col-md-4" id="penguin-preview">
                    <img id="penguin-color" src="https://cdn.polarcp.com/assets/images/colors/1.png" width="180" />
                    <br />
                    <div id="color-picker">
                    <div class="color darkblue selected"></div>
                    <div class="color green"></div>
                    <div class="color hotpink"></div>
                    <div class="color black"></div>
                    <div class="color red"></div>
                    <div class="color orange"></div>
                    <div class="color yellow"></div><br/>
                    <div class="color purple"></div>
                    <div class="color brown"></div>
                    <div class="color pink"></div>
                    <div class="color darkgreen"></div>
                    <div class="color blue"></div>
                    <div class="color limegreen"></div>
                    <div class="color gray"></div>
                    </div>
                </div>
                </div>
            </div>
        </div>
    </div>
    <script src="https://cdn.polarcp.com/assets/js/jquery-1.11.3.min.js"></script>
    <script>
    $(function() {
        $('.color').click(function(e) {
            var color = $(e.target).attr('class').split(' ')[1];
            colorPicker.select(color);
        });
    });

    var colorPicker = {
        selectedColor: 'darkblue',
        colors: {'darkblue': 1, 'green': 2, 'hotpink': 3, 'black': 4, 'red': 5, 'orange': 6, 'yellow': 7, 'purple': 8, 'brown': 9, 'pink': 10, 'darkgreen': 11, 'blue': 12, 'limegreen': 13, 'gray': 14},
        select: function(color) {
            if($('.'+this.selectedColor).hasClass('selected')) {
                $('.'+this.selectedColor).attr('class', 'color '+this.selectedColor);
            }
            this.selectedColor = color;
            $('.'+color).addClass('selected');
            $('#penguin-color').attr('src', 'https://cdn.polarcp.com/assets/images/colors/'+this.colors[this.selectedColor]+'.png');
        }
    }

    $('#reg-form').submit(function(e){
        e.preventDefault();
        var formData = {
            'playerName': $('#playerName').val(),
            'playerEmail': $('#playerEmail').val(),
            'playerPass': $('#playerPass').val(),
            'playerPassConfirm': $('#playerPassConfirm').val(),
            'playerColor': colorPicker.colors[colorPicker.selectedColor],
            'playerReferral': $('#playerReferral').val(),
            'g-recaptcha-response': $('#g-recaptcha-response').val()
        };
        $.post('lib/create_account.php', formData, function(recv){
            if(recv.error){
                $('#register-alert').html('<strong>An error occured:</strong> ' + recv.message);
            } else {
                $('#register-alert').attr('class', 'alert alert-success');
                $('#register-alert').html('<strong>Success!</strong> Your account has been created.');
            }
            $('#register-alert').fadeIn(200);
        }, 'json');
    })
    </script>

</body>
</html>

Answer 1

非常简单的修复!

在第 103 行，您试图在不绑定(bind)所有值的情况下执行语句(您忘记将值绑定(bind)到“:Credits”和“:Active”)，因此我在第 100 行和101 为您做这件事。为了让这些行工作，我必须向 $userArr 添加额外的元素('credits' 和 'active')，这是在第 21 行和第 22 行完成的。

这是经过修复和测试的代码，尽情享受吧;)

<?php
class Register {
  private $dbObj = null;
  private $dbConf = array('host' => 'localhost', 'user' => 'root', 'pass' => '', 'db' => 'sweater');
  private $userArr = Array();
  private $referred = false;
  public function __construct($params){
      try {
          $this->dbObj = new PDO('mysql:host=' . $this->dbConf['host'] . ';dbname=' . $this->dbConf['db'], $this->dbConf['user'], $this->dbConf['pass']);
      } catch(PDOException $e) {
          $this->__return($e->getMessage());
      }

      $this->userArr['username']        = trim($params['playerName']);
      $this->userArr['email']           = trim($params['playerEmail']);
      $this->userArr['password']        = trim($params['playerPass']);
      $this->userArr['passwordConfirm'] = trim($params['playerPassConfirm']);
      $this->userArr['color']           = trim($params['playerColor']);

      //NEW VALUES
      $this->userArr['credits'] = 0;
      $this->userArr['active'] = 0;

      if($this->ipExists($_SERVER['REMOTE_ADDR'])){
          $this->__return('You\'re not allowed to register more than 4 accounts per IP on our server!');
      }
      if($this->verifyInput($this->userArr)) {
          if($this->insertUser($this->userArr)){
              echo $this->__return('Your account has been created successfully.', false);
          } else {
              $this->__return('unable to create account? error code 9998');
          }
      }
  }

  private function verifyInput($userArr){
      foreach($userArr as $key => $val){
          switch($key){
              case 'username':
                  if($val == '')
                      $this->__return('You are required to enter a username.');
                  if($this->userExists($val))
                      $this->__return("Another player already has that username!");
                  if(strlen($val) < 4)
                      $this->__return('Your uername must be at least 4 characters in length!');
                  if(strlen($val) > 12)
                      $this->__return('Your username must be less than 12 characters in length!');
                  if(!ctype_alnum($val))
                      $this->__return('Your username can only contain letters & numbers.');
                  break;
              case 'email':
                  if($val == '')
                      $this->__return('You are required to enter an email address.');
                  if(!filter_var($val, FILTER_VALIDATE_EMAIL))
                      $this->__return('You have entered an invalid email address.');
                  if($this->emailExists($val))
                      $this->__return('A user has already registered with that email address.');
                  break;
              case 'password':
                  if($val == '')
                      $this->__return('You are required to enter a password.');
                  if(strlen($val) < 4)
                      $this->__return('Your password must be at least 4 characters in length!');
                  if(strlen($val) > 40)
                      $this->__return('Your password must be less than 40 characters in length!');
                  if($val !== $userArr['passwordConfirm'])
                      $this->__return('The passwords you entered do not match.');
                  break;
              case 'passwordConfirm':
                  if($val == '')
                      $this->__return('You are required to confirm your password for verification purposes.');
                  if($val !== $userArr['password'])
                      $this->__return('The passwords you entered do not match.');
                  break;
              case 'color':
                  if(!is_numeric($val)){
                      $this->__return('Color is not numeric');
                  }
                  if($val > 14){
                      $this->__return('Invalid color ID');
                  }
                  break;
          }
      }
      return true;
  }

  private function insertUser($userArr) {
      try {
          $strQuery = "INSERT INTO users (ID, Username, Password, Email, RegisteredTime, RegisteredIP, LoginKey, LoginToken, Active, Status, Coins, Credits, Badges, Color) VALUES (null, :Username, :Password, :Email, :RegTime, :RegIP, null, null, :Active, 0, 10000, :Credits, '[]', :Color)";
          $objStatement = $this->dbObj->prepare($strQuery);
          $objStatement->bindValue(':Username', $userArr['username']);
          $objStatement->bindValue(':Password', md5($userArr['password']));
          $objStatement->bindValue(':Email', $userArr['email']);
          $objStatement->bindValue(':RegTime', time());
          $objStatement->bindValue(':RegIP', $_SERVER['REMOTE_ADDR']);
          $objStatement->bindValue(':Color',$userArr['color']);

          //NEW VALUES
          $objStatement->bindValue(':Credits', $userArr['credits']);
          $objStatement->bindValue(':Active', $userArr['active']);

          $objStatement->execute();
          $objStatement->closeCursor();
          if($objStatement) return true;
          else return false;
      } catch(PDOException $e){
          $this->__return($e->getMessage());
      }

  }

  private function getPlayerCredits($user) {
      try {
          $strQuery = "SELECT Credits from `users` WHERE Username = :Username";
          $objStatement = $this->dbObj->prepare($strQuery);
          $objStatement->bindValue(':Username', $user);
          $objStatement->execute();
          $objStatement->bindColumn('Credits', $credits);
          $objStatement->fetch(PDO::FETCH_BOUND);
          $objStatement->closeCursor();
          if($objStatement) return $credits;
          else return false;
      } catch(PDOException $e){
          $this->__return($e->getMessage());
      }

  }

  private function userExists($username){
      try {
          $strQuery = "SELECT ID FROM `users` WHERE Username = :Username";
          $objStatement = $this->dbObj->prepare($strQuery);
          $objStatement->bindValue(':Username', $username);
          $objStatement->execute();
          $intRows = $objStatement->rowCount();
          $objStatement->closeCursor();
          return $intRows > 0;
      } catch(PDOException $e){
          $this->__return($e->getMessage());
      }
  }

  private function emailExists($email){
      try {
          $strQuery = "SELECT ID FROM `users` WHERE Email = :Email";
          $objStatement = $this->dbObj->prepare($strQuery);
          $objStatement->bindValue(':Email', $email);
          $objStatement->execute();
          $intRows = $objStatement->rowCount();
          $objStatement->closeCursor();
          return $intRows > 0;
      } catch(PDOException $e){
          $this->__return($e->getMessage());
      }
  }

  private function ipExists($ip){
      try {
          $strQuery = "SELECT ID FROM `users` WHERE RegisteredIP = :RegIP";
          $objStatement = $this->dbObj->prepare($strQuery);
          $objStatement->bindValue(':RegIP', $ip);
          $objStatement->execute();
          $intRows = $objStatement->rowCount();
          $objStatement->closeCursor();
          return $intRows >= 4;
      } catch(PDOException $e){
          $this->__return($e->getMessage());
      }
  }

  private function validateReferral($username){
      try {
          $strQuery = "SELECT ID FROM `users` WHERE Username = :Usrn";
          $objStatement = $this->dbObj->prepare($strQuery);
          $objStatement->bindValue(':Usrn', $username);
          $objStatement->execute();
          $intRows = $objStatement->rowCount();
          $objStatement->closeCursor();
          return $intRows > 0;
      } catch(PDOException $e){
          $this->__return($e->getMessage());
      }
  }

  private function __return($msg, $error = true){
      $returnArr = Array('error' => $error, 'message' => $msg);
      if($error){
          echo json_encode($returnArr);
          die();
      } else{
          return json_encode($returnArr);
      }
  }
}

//This is just a little extra bit that I added for testing, feel free to use it! :D
$params = array(
  'playerName' => "dibdibs",
  'playerEmail' => "dibdibs@g.com",
  'playerPass' => "passwd123",
  'playerPassConfirm' => "passwd123",
  'playerColor' => "1"
);
$u = new Register($params);
?>

我会把代码保存在我的电脑上，如果您需要更多帮助，请告诉我:)

PS:您正在使用 MD5 来散列密码，您确实应该使用 PBKDF2 之类的东西，但如果您想要一些简单的东西，SHA-512 也可以。做这样的事情......

$hashedPass = "";

for($i=0; $i<1024; $i++){ //It's good to iterate password hashes many times.
  $hashedPass = hash("sha512", $userArr['password']);
}

$objStatement->bindValue(':Password', $hashedPass);