Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

the PHP below works fine in instances of insertion, but with a select query of bound parameters, not much luck has come through with the json reading. The application I'm creating now always returns false with the if statement evaluating the number of rows. I have a feeling the issue truly lies with the scope of the mysqli_stmt_store_result($query). The login credentials of the user do not work when attempting to login even though they are correct. Please let me know if more information is needed. The database connection is fine.

//parameter checking
$username = safe(stripslashes(trim($_POST['username'])));
$mypassword=hash('sha256', $salt.$_POST['password']);

//sanitize input parameters
function safe($value)
{
    global $db;

    $secureString = mysqli_real_escape_string($db, $value);

   return $secureString;
} 

//query check
$query= mysqli_prepare($db, "SELECT * FROM Users WHERE username =? AND password =? AND block_status < 1");
//$result=mysqli_query($db,$query);
mysqli_stmt_bind_param($query,'ss',$username,$mypassword);

mysqli_stmt_execute($query);

/* store result */
    mysqli_stmt_store_result($query);

$query2="UPDATE Users SET last_login=NOW() WHERE username ='" . $username . "' AND password = '" . $mypassword . "'";
$result2=mysqli_query($db,$query2);


//if match found, create an array of data and json_encode it
if(mysqli_stmt_num_rows($query)>0)
{


    $row=mysqli_fetch_array($query,MYSQLI_ASSOC);

    $response=array(

        'logged'=>true,
        'name'=>$row['name'],
        'email'=>$row['email']
    );

    echo json_encode($response);
}
else
{
    $response=array(

        'logged'=>false,
        'message'=>'Invalid credentials or your access has been revoked'
    );
    echo json_encode($response);
}

    /* free result */
    mysqli_stmt_free_result($query);

    /* close statement */
    mysqli_stmt_close($query);

    mysqli_close($db);

?>
share|improve this question
 
You don't need to use mysqli_real_escape_string() if you're using parameter binding –  Phil Jul 19 at 4:08
 
Thanks, I will update that once I figure out this scripting error. –  Klinetel Jul 19 at 4:44
1  
@Phil for some very strange reason the OP is using prepared statement for the first query but not for the second one. –  Your Common Sense Jul 19 at 5:27
add comment

1 Answer

up vote 0 down vote accepted

According to the docs, you should be using mysql_stmt_store_result() before calling mysql_stmt_num_rows().

Also, you should use parameter binding for your UPDATE query, but only after you've confirmed a successful login.

I highly recommend PDO over MySQLi. The API is much cleaner and easier to understand. Here's how I would do it...

// assuming your user table has a primary key `id`
$stmt = $db->prepare('SELECT `id`, `name`, `email` FROM `Users` WHERE `password` = ? AND `username` = ? AND block_status < 1');
$stmt->execute(array($passwordHash, $username));
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user !== false) {
    $response = array('logged' => true) + $row;

    $update = $db->prepare('UPDATE `Users` SET `last_login` = NOW() WHERE `id` = ?');
    $update->execute(array($row['id']));
} else {
    $response = array(
        'logged'  => false,
        'message' => 'Invalid credentials or your access has been revoked'
    );
}

$stmt->closeCursor();

header('Content-type: application/json');
echo json_encode($response);
exit;
share|improve this answer
 
I've tried putting it before, but then I get a json error. Should I even bother with parameter binding on select queries? –  Klinetel Jul 19 at 4:43
 
@Klinetel absolutely. I would always bind user input. There's really no other sane option. Also, what JSON error are you getting? –  Phil Jul 19 at 5:02
 
Ok. The application I'm feeding this into says unable to parse JSON string line 1... But, when I move the mysqli_stmt_store_result to the num rows if statement, the error goes away but the if statement will only evaluate to false. –  Klinetel Jul 19 at 5:06
 
@Klinetel I've updated my answer using bind_result() and fetch(). You may be able to use fetch_array() but I'm not really big on MySQLi so don't know exactly how to implement that one. My only other suggestion is to use PDO, the API is much cleaner –  Phil Jul 19 at 5:17
 
I assume the downvote is due to the pseudo-code example. I've updated it to a real one –  Phil Jul 19 at 6:39
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.