Prepared Statement for sessions

Question

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 $_SESSION

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)){
                $stmt->bind_param("ss",$username,$password);
                $stmt->execute();
                $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");
                    exit();

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

                $stmt->free_result();
                $stmt->close();
                $db->close();
            }

after `->get_result()` invocation, use the `fetch_*` flavors of mysqli, its either `_assoc()` or `_row()`, the same drill — Kevin, Sep 29 '16 at 23:54
`$num_rows` is the __number__ of rows, not the row array/object, so doing `$num_rows['country']` is invalid, you should be seeing warnings/errors in your server log — Patrick Evans, Sep 29 '16 at 23:59
`username` works because you aren't using the DB return, `$username = $_POST['username'];`. — chris85, Sep 29 '16 at 23:59
You may want to try $result = $stmt->fetch(PDO::FETCH_ASSOC); It'll return your result as an assoc array which you can then grab your values from. — Kit, Sep 30 '16 at 00:01

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

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

$stmt->execute();
$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');
    exit();
}

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.

edited May 23 '17 at 12:33

Community

1
1

answered Sep 30 '16 at 00:02

Kevin

41,694
12
53
70

1

Note, loop the `$result->fetch_assoc` if you expect more than 1 record (although having more than 1 record for a username/password seems like a design flaw); or add `limit 1` to the query. – chris85 Sep 30 '16 at 00:05
@chris85 that would be crazy if someone has the same/duplicate credentials and shouldn't happen in the first place – Kevin Sep 30 '16 at 00:06
I only aim to find one row with this fetch, which it achieves. Thank you for this answer, @Ghost – Synyster Sep 30 '16 at 00:08
What is the purpose of get_result()? I can't seem to find it in the PDO documentation. Can someone provide a link? – Kit Sep 30 '16 at 00:13
1

@Rawrskyes [`->get_result()`](http://php.net/manual/en/mysqli-stmt.get-result.php) method is part of the `mysqli` API, not `PDO` – Kevin Sep 30 '16 at 00:14
@Ghost Ah thanks! Makes sense. For some reason I thought I'd read PDO in the question; after reading it again it appears I must just be daydreaming about it! – Kit Sep 30 '16 at 00:19

Prepared Statement for sessions

1 Answers1

Linked