homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

Converting script from mysql to mysqli

 1:59 am on Dec 23, 2012 (gmt 0)

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');

$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.";




 4:08 am on Dec 23, 2012 (gmt 0)

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);
if (!$stmt->fetch() ) {
// handle error where the id doesn't exist
} 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);
} else {
//handle error preparing the statement

Hope that get's you started.


 3:53 pm on Dec 23, 2012 (gmt 0)

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');

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


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);


 4:04 am on Dec 24, 2012 (gmt 0)

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.


 6:07 pm on Dec 24, 2012 (gmt 0)

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');

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


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved