使用Prepared语句将MySQL SELECT结果存储在php变量中

Question

I am trying to user prepared statements to find a user record and store the users ID in a php variable to use later on. I would like to echo the variable contents. How do I check the result using Prepared statements?

My CODE:

 if ((isset($_POST['overrideUsername'])) and (isset($_POST['overridePassword'])) and (isset($_POST['overrideUniqueID']))) {



    $overridePasswordInput = $_POST['overridePassword'];
    $overrideUsernameInput = $_POST['overrideUsername'];
    $roleID = '154';
    $overrideUniqueID = $_POST['overrideUniqueID'];

    //Not sure how to properly compare stored passwords vs password given by user...
    $overridePassword = mysqli_real_escape_string($overridePasswordInput);
    $overrideUsername = mysqli_real_escape_string($overrideUsernameInput);





    //connect  to the database 
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if(mysqli_connect_errno() ) {
        printf('Could not connect: ' . mysqli_connect_error());
        exit();
    }

    $conn->select_db($dbname);

    if(! $conn->select_db($dbname) ) {
        echo 'Could not select database. '.'<BR>';
    }


$sql1 = "SELECT users.id FROM users WHERE (users.login = ?) AND (users.password = ?)";

    $stmt1 = $conn->prepare($sql1);
    $stmt1->bind_param('ss', $overrideUsername, $overridePassword);
    $stmt1->execute();
    $stmt1->bind_result($userID);     
    $stmt1->get_result();


    if ($stmt1->get_result()) {

        echo $userID;

    } else {

      echo 'User credentials incorrect. Please try again';

    }

 $stmt1->close();


 //Close the Database connection.
 $conn->close();

 }//End If statement

Further more, this is the pre-existing code the original programmer used to authenticate users into the program:

if(!defined("noStartup")){
$scriptname = basename($_SERVER["PHP_SELF"]);
$phpbmsSession = new phpbmsSession;

//Testing for API login
if(strpos($scriptname,"api_")!==false){
    if(isset($_POST["phpbmsusername"]) && isset($_POST["phpbmspassword"])){
        $phpbmsSession->loadDBSettings();

        include_once("include/db.php");
        $db = new db();
        $phpbmsSession->db = $db;

        include_once("common_functions.php");           
        $phpbmsSession->loadSettings($sqlEncoding);
        $phpbms = new phpbms($db);


        if(!$phpbmsSession->verifyAPILogin($_POST["phpbmsusername"],$_POST["phpbmspassword"],ENCRYPTION_SEED))
            $error = new appError(-700,"","Login credentials incorrect",true,true,true,"json");
    } else
        $error= new appError(-710,"","No login credentials passed",true,true,true,"json");
} else {

    $phpbmsSession->loadDBSettings($sqlEncoding);


    include_once("include/db.php");
    $db = new db();

    $phpbmsSession->db = $db;

    $phpbmsSession->loadSettings($sqlEncoding);

    include_once("common_functions.php");
    $phpbms = new phpbms($db);

    if(!isset($noSession))
        $phpbmsSession->startSession();

      if (!isset($_SESSION["userinfo"]) && $scriptname != "index.php") {

        if(isset($loginNoKick)){
            if(!isset($loginNoDisplayError))
                exit();
        } else{
            goURL(APP_PATH."index.php");
        }
      }

   }

  $db->stopOnError=true;
}//end if

And the verifying function:

function verifyAPIlogin($user,$pass){
    $thereturn=false;
    $this->db->stopOnError = false;

    $querystatement = "SELECT id, firstname, lastname, email, phone, department, employeenumber, admin, usertype
                    FROM users 
                    WHERE login!=\"Scheduler\" AND login=\"".mysql_real_escape_string($user)."\" 
                    AND password=ENCODE(\"".mysql_real_escape_string($pass)."\",\"".mysql_real_escape_string(ENCRYPTION_SEED)."\") 
                    AND revoked=0 AND portalaccess=1";
    $queryresult = $this->db->query($querystatement);
    if(!$queryresult) {
        $error = new appError(-720,"","Error retrieving user record",true,true,true,"json");
        return false;
    }

    if($this->db->numRows($queryresult)){
        //We found a record that matches in the database
        // populate the session and go in
        $_SESSION["userinfo"]=$this->db->fetchArray($queryresult);

        $querystatement="UPDATE users SET modifieddate=modifieddate, lastlogin=Now() WHERE id = ".$_SESSION["userinfo"]["id"];
        $queryresult=@ $this->db->query($querystatement);
        if(!$queryresult) {
            $error = new appError(-730,"","Error Updating User Login Time",true,true,true,"json");
        } else
            $thereturn=true;
    }
    return $thereturn;  
  }

}//end loginSession class

NOTE: I have already tested that my $_POST() values are successfully coming through to my script.

EDIT:: added more code to give a better overall picture of what I'm attempting to do. Any shared tuturials on password encryption/authenticating users would be greatly appreciated.

Thank you!

Answer 1

As I mentioned in the comment, PHP now has a couple built in methods to handle encryption and decryption of passwords that you might find helps solve your problem:

password_hash and password_verify