Converting script from mysql to mysqli

[ocon]

It's long overdue, but I'm finally converting my mysql connections to mysqli.

I'm working on a log-in script that now uses the mysqli connections, but I have three questions:

• Am I doing this right? It seems it could be much smoother.
  
• Prepared statements have me in a whirl. How can I convert this to use prepared statements?
  
• With my old mysql script, setting the character set to utf8 saved me a couple of times. I don't see a lot of examples using it for mysqli. How can I tell if I need it?

$db = new mysqli('$server','$user','$pass','$database');
$db->set_charset('utf8');

$email = $db->real_escape_string(strtolower($user_email));
$password = $db->real_escape_string(md5($salt.$user_pass));

$result = $db->query("SELECT * FROM users WHERE email='$email' AND password='$password'");
if($db->affected_rows===1) while($row = $result->fetch_object()){
$userid = $row->userid;
$userlang = $row->userlang;}
else $error = "Not valid.";
$result->close();

$db->close();

[swa66]

The real advantage of using mysqi is to use the prepared statements instead of mesing around with escaping. - But I get that you know that much.

Salts ... you choose one random one per user, and store it with the user and password, not one for all.
Hence you need to add a column and retrieve it from the database in order to redo the hash to verify if the password matches.

Hash algorithm ... md5: please consider it broken beyond repair: use one of the sha-2 variants for new projects.

To get you started: a few examples of using prepared statements:
A query:

//$id contains the id we search for

$sql = 'SELECT name,price,size FROM widgets WHERE id = ?';

if($stmt = $mysqli->prepare($sql)) {
  $stmt->bind_param("s", $id);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($name,$price,$size);
  if (!$stmt->fetch() ) {
    // handle error where the id doesn't exist
  }
  $stmt->close();
} else {
  // handle error where the query can't be prepared
}

An insert:

 // $ref and $ip contain values we want to add to the log table (in the similarly named columns
 // both are strings unescaped

 $sql='INSERT log (ref, ip) VALUE (?,?)';
 if ($stmt = $mysqli->prepare($sql)) {
   $stmt->bind_param("ss", $ref, $ip);
   $stmt->execute();
   $stmt->close();
 } else {
   //handle error preparing the statement
 }

Hope that get's you started.

[ocon]

Thank you so much for the feedback, not only did it get me up and running using prepared statements but I got feedback in areas I didn't even know I was behind in.

I've updated the code:

$db = new mysqli('$server', '$user', '$pass', '$database');
$db->set_charset('utf8');

if($stmt = $db->prepare('SELECT userid, userlang FROM users WHERE email=?')){
$stmt->bind_param('s', $email);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($userid, $userlang);
if(!$stmt->fetch()){$error = 'Not valid.';}
$stmt->close();}

$db->close();}

The above code doesn't check the password right now because I'm wondering how I can incorporate the salted password check into one query:

if($stmt = $db->prepare('SELECT userid, userlang FROM users WHERE email=? AND password==SHA256(CONCAT(salt, ?))')){
$stmt->bind_param('ss', $email, $password);
...}

[swa66]

I'd not send the unhashed password to your database out of principle.

The reason is that you get -if all is well- the password in a SSL encrypted connection.
The reason to use the salted hash is to make sure not to leak the password accidentally (not even to internal users).
Since the sql connection might be to another host and might not be ssl encrypted ... don;t send the password.

So I'd query the database for the hashed password and the salt and do the verification in php.

[ocon]

Yesterday I was struggling how I could make just one query and use SHA256 with MySQL (I don't have MySQL 5.5). With your response I see how much I was over thinking it! With one query PHP can grab the columns I need if just the email matches, then preform a hash/password check using the salt, and if it matches continue using the data.

My major concern now is the script being vulnerable to the now unprepared password.

$db = new mysqli('$server','$user','$pass','$database');
$db->set_charset('utf8');

if($stmt = $db->prepare('SELECT userid, userlang, password, salt FROM users WHERE email=?')){
  $stmt->bind_param('s', $email);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($userid, $userlang, $password, $salt);
  if($stmt->fetch()){
   if(hash('sha256', $salt.$_POST['password']) === $password){...}
   else $error = 'Password not valid.';}
  else $error ='Email address not valid.';
  $stmt->close();}

$db->close();