Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

password_verify with prepared statements

Posted on 2016-09-27
10
Medium Priority
?
114 Views
Last Modified: 2016-09-27
I originally used mysqli object oriented way to log users in but am moving over to prepared statements. My problem is that with this code, as long as the email address is in the database, any password I type in works which is obviously terrible! I can just type "ashflajsf" as my password and it lets me in provided the email address is in the database.

$stmt = $link->prepare("SELECT `user_email`, `safe_user_id`, `user_password` FROM `db_users` WHERE `user_email` = ? AND `user_active` = ? ");
		$stmt->bind_param("ss", $email, $user_active);
		$email = clean_user_input($_POST['email']);
		$user_active = 1;
		$stmt->execute();
		$result = $stmt->get_result();
		$numRows = $result->num_rows;
		if($numRows === 1 ) {
			while($row = $result->fetch_assoc()){
			$db_password = $row['user_password'];
			if(password_verify($_POST['password'], $db_password));
			echo "user authenticated";
			} 
				
		} else {
			
			echo "Incorrect login details";
		}

Open in new window

0
Comment
Question by:Black Sulfur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 41818638
Since you are not checking the password in the WHERE part of the SQL statement, that's not surprising.  Maybe like this?
$email = clean_user_input($_POST['email']);
$password = clean_user_input($_POST['password']);
$user_active = 1;
$stmt = $link->prepare("SELECT `user_email`, `safe_user_id`, `user_password` FROM `db_users` WHERE `user_email` = ? AND `user_active` = ? AND `user_password` = ? ");
$stmt->bind_param("ss", $email, $user_active, $password);
$stmt->execute();

Open in new window

0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 2000 total points
ID: 41818648
if(password_verify($_POST['password'], $db_password));

Open in new window

Take the ; of the end off the above statement.
Your echo is firing because you are terminating the if with a ;
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41818653
Actually, I fixed it like this

$stmt = $link->prepare("SELECT `user_email`, `safe_user_id`, `user_password` FROM `db_users` WHERE `user_email` = ? AND `user_active` = ? ");
		$stmt->bind_param("ss", $email, $user_active);
		$email = clean_user_input($_POST['email']);
		$user_active = 1;
		$stmt->execute();
		$result = $stmt->get_result();
		$numRows = $result->num_rows;
		if($numRows === 1 ) {
			while($row = $result->fetch_assoc()){
			$db_password = $row['user_password'];
				
			}
			if(password_verify($_POST['password'], $db_password)) {
				echo "you can login";
			
			} else {
				
				echo "incorrect details";
			}
				
		} 

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:Black Sulfur
ID: 41818659
Ah, yes Julian. I saw that while trying to find it but I think maybe you beat me to it as when I came back here to say I had found it your post was here :)

Anyway, is the way I have done it okay or is there a better way of coding it?

Also, I am so paranoid using prepared statements because I believe that I don't have to use real_escape_string
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41818660
@ Dave, I don't think you need to do that because you check it in the IF statement that you have to use with password_verify
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41818671
Hint - always use { } for if statements to avoid this kind of problem. There is no benefit (IMO) to leaving the { } out. In this case your closing } matches the if for the num_rows - so you don't have anything checking the actual password_verify

There are a couple of other things I would change as well see below
// ASSUME NOT AUTH
$auth = false;

// MAKE SURE YOU GOT A PASSWORD
$post_password = empty($_POST['password']) ? false : $_POST['password'];

// SPIN THE CPU's WHEELS ONLY IF YOU HAVE A password
if ($post_password) {
  $stmt = $link->prepare("SELECT `user_email`, `safe_user_id`, `user_password` FROM `db_users` WHERE `user_email` = ? AND `user_active` = ? ");
  $stmt->bind_param("ss", $email, $user_active);
  $email = clean_user_input($_POST['email']);
  $user_active = 1;
  $stmt->execute();
  $result = $stmt->get_result();
  
  // USE THE RESULT TO DETERMINE IF YOU SHOULD PROCEED
  if ($result) {
    // YOU ARE ONLY INTERESTED IN 1 ROW (THERE SHOULD BE AT MOST 1)
    // SO JUST FETCH IT - YOU KNOW THE result IS GOOD SO JUST GET THE row
    $row = $result->fetch_assoc();
  
    // THE CRUX OF THE FUNCTION - CHECK AGAINST YOUR POST PASSWORD
    if (password_verify($post_password, $db_password)) {
    // ALL GOOD SO WE SET THE $auth FLAG TO true
      $auth = true;
    }
  }
}
// NOW WE CHECK TO SEE THE RESULT 
// WE CAN GET HERE BY A NUMBER OF PATHS
// WE ONLY ALLOW ENTRY IF $auth IS TRUE
if ($auth) {
  echo "user authenticated";
}
else {
  echo "Incorrect login details";
}

Open in new window

1
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 41818869
Well, that's the way I check it on many web sites.  If there is no match in the database for the username and the hashed password, then they can't login.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41819189
I don't know how it would work because you have to first get the password field from the database and then you have to use password_verify on the input vs what the actual record found in the database is. In your code example (unless I have just overlooked it), there is no password verify which HAS to be used with password_hash.

But, I am no expert so please correct me if I am wrong because I want to learn the best way. My understanding is that if I created a password using PHP built in function password_hash, I have to use PHP built in function, password_verify.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 41819213
I don't know for sure what the 'best' way is.  All hash functions that I know of create a text output.  The MD5 function in PHP is identical to the MD5 function in MySQL.

When I create a username and password, I store the hash of the password in the database.  When someone submits a username and password to 'login', I hash the password with the same function and see if there is a match to the username and password in the database.  It's certainly simple.  While there is some other code to cleanup the input values, this is the exact code I use to check the database.  It should return one and only one row if there is a match, none if there is not.
//make the password md5
$tmp_pass = md5($tmp_pass);
//check the database
$query = "SELECT * FROM users WHERE username = '$tmp_name' AND password = '$tmp_pass'";
$result = $link->query($query);

Open in new window

0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41819231
Hi Dave,

yes, agreed. That method works fine with md5, but  not with password_hash and password_verify

http://php.net/manual/en/function.password-hash.php
http://php.net/manual/en/function.password-verify.php
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question