Synyster Synyster - 1 year ago 62
MySQL Question

Prepared Statement for sessions

I've finally decided to start using prepared statements. Though, i am 50/50 on whats correct and not. I'm trying to make a login page with the prepared statements. Though, it seems like it doesn't retrieve any session value except the username


Here's my code:

$username = $_POST['username'];
$password = md5($_POST['password']);

$sql = "SELECT * FROM users WHERE BINARY username=? AND BINARY password=?";
if($stmt = $db->prepare($sql)){
$result = $stmt->get_result();
$num_rows = $result->num_rows;

if($num_rows >= 1){

$_SESSION['loggedin'] = $username;
$_SESSION['country'] = $num_rows['country'];
$_SESSION['email'] = $num_rows['email'];
$_SESSION['avatar'] = $num_rows['u_avatar'];
$_SESSION['is_gm'] = $num_rows['is_gm'];
$_SESSION['user_lvl'] = $num_rows['user_lvl'];
$_SESSION['totalposts'] = $num_rows['post_total'];
$_SESSION['totalcoins'] = $num_rows['coins_total'];
$_SESSION['totalvotes'] = $num_rows['vote_total'];
$_SESSION['secquest'] = $num_rows['sec_quest'];
$_SESSION['secanswer'] = $num_rows['sec_answer'];
$_SESSION['join_date'] = $num_rows['join_date'];

header("Location: /index.php");

} else {
echo "<p class='error_msg'>No accounts could be found with the given credentials.</p>";


Answer Source

Like the comments above, after you have used ->get_result(), then its time to fetch:

$result = $stmt->get_result();
$num_rows = $result->num_rows;

if($num_rows >= 1) {
    $row = $result->fetch_assoc(); // fetch it first
    $_SESSION['loggedin'] = $username;
    $_SESSION['country'] = $row['country'];
    $_SESSION['email'] = $row['email'];
    $_SESSION['avatar'] = $row['u_avatar'];
    $_SESSION['is_gm'] = $row['is_gm'];
    $_SESSION['user_lvl'] = $row['user_lvl'];
    $_SESSION['totalposts'] = $row['post_total'];
    $_SESSION['totalcoins'] = $row['coins_total'];
    $_SESSION['totalvotes'] = $row['vote_total'];
    $_SESSION['secquest'] = $row['sec_quest'];
    $_SESSION['secanswer'] = $row['sec_answer'];
    $_SESSION['join_date'] = $row['join_date'];

    header('Location: /index.php');

It doesn't make sense to use $num_rows['join_date'], as you already know this yields the actual number of rows, it doesn't contain those values that you want. You already checked for it to contain a number if($num_rows >= 1) {

Sidenote: It's time to ditch that md5 and start using password_hash + password_verify combo.